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