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

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

By Arghya Roy  |  Published on June 18, 2014

Netwoven Blog

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…

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

Then Click Next…

There is no existing connection. So click on New button

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

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

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

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

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

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

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

Enter a Data Source Name and Click on Finish Button.

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

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…

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

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

Click Next…

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

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…

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

Click on finish button to create data source view.

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

The following screen appears after creation of Data source view.

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

Create New Named Calculation

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

The Year format will look like CY2012, CY2013

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

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

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

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

Select to create New Dimension. Click Next

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

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

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

Select “DimDate” in Main Table.

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

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

Select the column name which will appear in the cube dimension

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

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

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

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

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

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

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

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

Create Hierarchies

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

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

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.

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

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…

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

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.

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

Create Cube

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

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

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

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

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

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

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

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

Uncheck the Dimension and click Next

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

Type a name of the Cube and Click on Finish button

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

The cube structure will look like this..

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

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

4 comments

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

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

    1. 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 >”
      “refresh”
      anyone can help me ?

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

Leave a comment

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

Unravel The Complex
Stay Connected

Subscribe and receive the latest insights

Netwoven Inc. - Microsoft Solutions Partner

Get involved by tagging Netwoven experiences using our official hashtag #UnravelTheComplex