Here in this post I will try to show you all the steps to create an SQL Server Analysis Services(SSAS) cube.

Level of this post is beginner. Later I will try to show you little complex examples.

Required Software – SQL Server 2008 R2

Database – AdventureWorks [Download Link]

Lets know the basics concept first:

1. Dimension :  A table that represents a real world entity (eg Customer, Product etc)
2. Fact : A table that contains foreign keys to dimension tables and the numeric values (eg. measures) that we want to summarize, average etc (eg Orders)
3. Dimensional Model/Star Schema - A group of dimensions and fact tables designed for analysing a business process where a fact table is joined to one or more dimension tables via a foreign key

Objective

Objective of this article is to create a simple cube to analyse data. Which will be using 4 dimension tables (Customer, Geography, Product, Time) and 1 Fact table(Internet Sales).

Here are the steps:

1. Open SQL Server Business Intelligence Development Studio from Start Menu

2. Click File –> New –> Project

3. Select Analysis Services Project template as shown below.

4. Name the project

5. Once project is created Right click on Data Source folder in Solution Explorer and select New Data Source…

6. Click Next in dialog box to proceed.

7. Set Data Connection for Data Source by clicking New… button

8. Enter Server name and select authentication which you want to use. Select AdventureWorksDW database .

9. Click Finish

10. Specify the credentials which will be used by Analysis Services to connect to data source. I am using service account.

11. Finish Data Source Creation wizard.

12. The Data Source View in SSAS is a logical model of related tables,views and queries from one or more data sources.The OLAP and data mining projects iin SSAS are based on this model.The Data Source View contains the Meta data from the selected data sources and any relationships that are defined for them.This cached Meta data allows working with SSAS projects without a continuous active connection to the data source.

To Create a New Data Source View…

Right click on Data Source Views Folder and select New Data Source View…

13. Click Next

14. Created data source will be listed. Click Next to proceed.

15. Include the objects (DimCustomer, DimGeography, DimProduct, DimTime, FactInternetSales) and Click Next and then Finish.

16. Here we can see all 4 dimension tables and 1 fact table.

17. Select one by one all the tables from the list and give a friendly name. (Eg. Customer, Product etc.)

18. Now Its time to create Dimension which will be used to browse the cube.

Right click on Dimensions folder and select New Dimension…

19 Click Next

20. Since we have already added 4 dimension tables in Data Source View So select Use an existing table and click Next.

21 Select Data source view as Adventure Works DW and Main Table as Customer table.

Click Next

22 Unselect Geography table . Here related tables will be listing. We can ignore all the related tables. Later these tables can be added if required.

23. Select Dimension Attributes which are required. Click Next.

24. Name the dimension and finish the wizard.

25. Create dimension for Geography, Product and Time as explained above.

26. Right Click on Cubes folder in the Solution Explorer and select New Cube…

27. Click Next

28. Select Use existing tables and click Next.

29. Select InternetSales table for measure groups and click Next.

30. Select the measures which you want to include and click Next.

31. Select all 4 Dimensions and click next.

32. Unselect InternetSales. Since we do not want to add InternetSales as a Dimension. We are using this table as measure group.

33. Name the cube and finish the wizard.

34. Now we have created few dimensions and 1 cube which will be using all 4 dimensions to analyse the data.

35 Process the project- Right Click on the Project MSCoder_SSAS_Test and select Process.

It will process all the dimensions and cubes available in the project.

36. Click Run

37 Below is the Process Progress window where we can see the result. Close the window once processing is completed.

38. Browsing the cube.

Go to Browser tab of TestCube. Drag any Measure(Eg Sales Amount) from Internet Sales and drop it in the middle of this screen and drag Dimension attributes from any dimension and drop it in the area where vertical line is seen.

Result

Dimension attribute – First name(Customer Dimension)

Measure – Sales Amount(Internet Sales Fact)

Dimension attribute – Gender(Customer Dimension), First name(Customer Dimension)

Measure – Sales Amount(Internet Sales Fact)

Dimension attribute – Size(Product Dimension), First name(Customer Dimension)

Measure – Sales Amount(Internet Sales Fact)

Dimension attribute – Calendar Quarter(Due Date Dimension), First name(Customer Dimension)

Measure – Sales Amount(Internet Sales Fact)

Dimension attribute – Calendar Year(Due Date Dimension), Calendar Quarter(Due Date Dimension), First name(Customer Dimension)

Measure – Sales Amount(Internet Sales Fact)

Happy Learning

MSCoder