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.
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 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.
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.
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..
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