Multidimensional Modeling with Microsoft SQL Server – (Adventure Works Tutorial)

Multidimensional Modeling (Adventure Works Tutorial)

Table of Contents


Multidimensional Modeling (Adventure Works Tutorial) Lesson 1: Defining a Data Source View within an Analysis Services Project Creating an Analysis Services Project Defining a Data Source Defining a Data Source View Modifying Default Table Names Lesson 2: Defining and Deploying a Cube Reviewing Cube and Dimension Properties Deploying an Analysis Services Project Browsing the Cube BI Questions and answers

Lesson 1: Defining a Data Source View within an Analysis Services Project

Creating an Analysis Services Project


  • Click file > new > project  to create a new project
  • Select analysis services multidimensional model
  • Rename the project analysis services tutorial


Defining a Data Source

  • Clicking on the data source > new data source
  • We will select create database based on existing or new connection
  • Using the service account
  • Enter data source name


Defining a Data Source View

  • Right click on the data source view folder
  • Select new data source view
  • Selecting existing data source
  • Select required tables and fact tables
  • Click finish to complete the wizard


Modifying Default Table Names

  • Right click on solution explorer
  • Change name to useful name

Lesson 2: Defining and Deploying a Cube

  • Click on cube wizard
  • Select option from the screen






Reviewing Cube and Dimension Properties


Deploying an Analysis Services Project

  • Right click on the project in solution explorer
  • Click deploy
  • Wait for the success message

Browsing the Cube

  • Click on the cube
  • Click on browse tab
  • Click on excel icon
  • Export data to excel
  • Generate reports




BI Questions and answers


What is the sales amount by each city ?
The report tells us the different amount of sales separated by cities. As we can see there are cities with highest and lowest sales.We can also see that the average sale amount is between 10k.


What was the recent sale in each city ?
The report tells us which city had last sale of our project and how much it was charged for. This report can tell us which cities are not currently selling the product so that we can focus on that particular city.


How much product was sold in each class and in each color segment ?
This report tells us which product of which color are in demand. This lets us see which kind of color segment in popular among the users.


Which gender ordered how much product for a particular color in each class segment ?
This lets us target customers based on genders. We can know which gender likes which kind of products and how much. This can tailor our marketing strategy for specific type of products.



Piyush Tripathi

Leave a Reply

Your email address will not be published / Required fields are marked *