4 minute read
Custom Development

How to prepare a simple OLAP cube using SQL Server Analysis Services

This blog is created to help people who want to learn OLAP (Online analytical processing) Cube in SSAS. It also helps to analyze data in multi-dimensional format for smarter business decisions.

This blog solutions has been created using Adventure Works DW 2012 database.

Create Data Sources

Select Data sources in Solution Explorer and right click on data sources –> New Data Sources.

The following screen appears…


Then Click Next…

There is no existing connection. So click on New button


The following connection manager screen appears. Enter the Server Name Where SQL Server is Installed and Choose Database name and Click Ok.


Now we can see a connection string created in left side of the Data Source Wizard, Click Next..


Click on “Use the service account” Radio button and Click Next


Enter a Data Source Name and Click on Finish Button.


Create Data Sources View

Select Data source View in Solution Explorer and right click on data source view –> New Data Sources view. The following screen appears…


We can see a data sources is present in the left section of the Data source view wizard.

Click Next…


The following screen appears with all the tables that exists in the adventure works database.

Select “DimDate”, “DimProduct”, “DimCustomer” and “FactInternetSales” from the Available objects.

Click the “>” button to move those objects to Included objects.

Click Next…


Click on finish button to create data source view.


The following screen appears after creation of Data source view.


Create New Named Calculation

Select and right click “DimDate” to create New Named Calculation.

The Year format will look like CY2012, CY2013


Click Ok and the following Screen appears. The “CalendarYearDesc” named calculation is created in “DimDate” Dimension.


Now to create three dimension like Date, Product and Customer (Create Data Dimension)

Select to create New Dimension. Click Next


Select “Use an existing table” radio button and click Next


Select “DimDate” in Main Table.

Select “DateKey” as Key column and “FullDateAlternetKey” as Name column. Click Next


Select the column name which will appear in the cube dimension

Change the attribute for Year, Semester, Quarter and Month from regular to specific.


Click on Attribute Type to get the list of all attribute under the calendar


Type a name of the New Dimension and Click on Finish Button to Create the Date Dimension


Next is to create the Product and Customer dimension with the above mentioned steps.


Create Hierarchies

In Dimension structure Tab sequentially drag and drop “CalendarYear, CalendarSemester, CalendarQuarter, Month and Date” to Hierarchies.


Create Attribute Relationships

Go to attribute relationship tab. Select and right click on “Date -> CalendarQuarter”. Change the Name to Month in Source Attribute and Change the Relationship Type to Rigid and Click on Ok button.


Again follow the abovementioned steps for CalendarSemester and CalendarYear as mentioned below:

Select and right click on “Date -> CalendarSemester”. Change the Name to CalendarQuarter in Source Attribute and Change the Relationship Type to Rigid and Click on Ok button.

Select and right click on “Date -> CalendarYear”. Change the Name to CalendarSemester in Source Attribute and Change the Relationship Type to Rigid and Click on Ok button.

The final hierarchy will look like the following…


View Data in Browser

Go to Browser Tab and Click on Process. The following screen will appear. Click the Run button to process dimension data.


Create Cube

Select and right click on cube to create New Cube. Click Next…


Click on “Use existing tables” radio button and Click Next


Select the check box where fact tables appear and uncheck all the dimension check box and Click Next


Select the measure field of the fact table and Click next…


Uncheck the Dimension and click Next


Type a name of the Cube and Click on Finish button


The cube structure will look like this..


Cube Deployment

Go to Analysis Service project and go to properties. Type the server name cube deployment and Type the name of the SSAS database. Click Ok.


Right click on Analysis Services project and deploy the cube

Arghya Roy

About Arghya Roy

Arghya Roy is a vibrant and performance-driven Microsoft Certified IT professional with 11 years of experience. He has enriched expertise in Project Execution, Implementation and Integration in Microsoft Technologies. He has gained hands-on experience in cloud computing, messaging, hypervisor migration, co-existence and migration working on different projects at Netwoven.

He started his career as a Junior Executive Engineer in Hardware & Networking domain and gradually moved towards technologies, which allowed to have footprints in SMBs and enterprise business markets. Arghya holds a Diploma and Certification in Computer Application & Hardware Engineering from IGNOU & IIHT respectively.


4 replies on “How to prepare a simple OLAP cube using SQL Server Analysis Services”

I would like to ask you “which program are u using and where can I download it” ? I dont know anythings about that but I have to do it because this is my homework :S

Hi Pedro,

I suggest the following:

1. Open SQL Server Data Tools where solution is created.
2. Then Right click on Analysis Service Project in Solution Explorer
3. Then Click on Add  New Item from the list
4. Select Data Sources and Click on add button to add data sources to the solutions.

I have sql server 2012 develop edition, all installed and sucessufull (SQL Server + Integration Services + Analysis Services).

After connect to Microsoft Analysis Server 11.0.5058.0, and create a new database, he’s appear and all options
but when i open the “data sources” and try find at right click the option “new data source” … dont appear..
only get this 2 options:
“reports >”
anyone can help me ?

Hello Subhrajit Mitra, thank you for this tutorial. I’m completly new about analysis services and I’m looking about this subjet. So, I’m trying follow your steps, but I can’t go further from the first one… 🙂 In solution explorer I don’t have Data Sources. So, please, can you help-me? I’m trying in my new Analysis Server instance – is that so?

Leave a Reply

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

Enter Captcha Here :