June 18, 2014

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

3834 Views

Objective

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…

s1

Then Click Next…

There is no existing connection. So click on New button

s2

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

s3

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

s4

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

s5

Enter a Data Source Name and Click on Finish Button.

s6

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…

s7

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

Click Next…

s8

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

s9

Click on finish button to create data source view.

s10

The following screen appears after creation of Data source view.

s11

Create New Named Calculation

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

The Year format will look like CY2012, CY2013

s12

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

s13

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

Select to create New Dimension. Click Next

s14

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

s15

Select “DimDate” in Main Table.

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

s16

Select the column name which will appear in the cube dimension

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

s17

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

s18

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

s19

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

s20

Create Hierarchies

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

s21

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.

s22

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…

s23

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.

s24

Create Cube

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

s25

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

s26

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

s27

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

s28

Uncheck the Dimension and click Next

s29

Type a name of the Cube and Click on Finish button

s30

The cube structure will look like this..

s31

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.

s32

Right click on Analysis Services project and deploy the cube

s33

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

  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 Reply

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