Build Your First Cube in SSAS


Introduction

The OLAP cube is a technique that holds the data in the optimized form and is also used to analyse the data with quick response. It is generally used for getting quick results from multiple dimensions and fact tables.

OLAP Cube Creation

1. First, create a data warehouse in Microsoft SQL Server studio. For instance, following is a sample Sales data warehouse –

1.1

2. Create new analysis service project in Microsoft Business Intelligence Development Studio –

2

3. Create new data source by right-click on Data Sources in Solution Explorer-

3 4

Now chose available connections or create new connection and click on next button-

5

Select Inherit option and click on Next button-

6

Click on Finish button. The data source will be created.

4. Create new data source view by right click on Data Source Views in Solution Explorer-

7

Click on Next button-

8

Select data source and click on Next button-

9

Move Fact table on right pane-

10

Click on Add Related Tables button-

11

Click on Next button-

12

Enter data source view name and click on Finish button, the data source view will be created.

5. Create new cube by right click on Cubes in Solution Explorer-

13

Click on Next button-

14

Click on Next button-

15

Select Fact table and click on Next button-

16

Select measures for fact table which you want-

17

Click on Next button-

18

Select Dimension tables and click on Next-

19

Enter cube name and click on Finish button. The cube will be created like this-

20

6. Now modify dimensions for queries. In Solution Explorer, double click on dimension Dim Product -> Drag and Drop Product Name from Table in Data Source View and Add in Attribute Pane at left side.

21

7. Now deploy the project. First right click on project name in solution explorer and click on properties –

22

Now click on OK button

8. Now right click on project name in solution explorer and click on deploy; the project will deploy and the message will be displayed –

23

9. Now right click on project name in solution explorer and click on process –

24

10. Now click on Run button and process has been completed –

25

11. Now right click on Cube and click on Browse

26

12. Add dimensions and fact fields like above image. The cube is ready to get quick results like below-

27

Conclusion

The OLAP Cube is mainly used for the following things-

  • It helps in diminishing the query time e.g. if the user query in the cube, it takes very less time as compared to simple query because in simple query, the data get summarized from different tables etc.
  • Some client software reporting tools only use the OLAP data source for the quick reporting.
  • If you have OLAP cube, no need to worry about performance. You only enjoy the Cube.

Other Interesting Posts

Advertisements
E-Books Store