In our last post, we modified some of the dimension properties and discussed some of the best practices for creating dimensions in SSAS. This post will explain on how to create cubes, cube dimensions and measure groups in SSAS.
Follow the steps below to create cubes:
- Right click on the Cubes folder in the solution explorer and select the New Cube option.
- Click on Next in the welcome page for the Cube Wizard. Select Use existing tables option for the Select Creation Method screen and click on Next. Choose FactInternetSales as the measure group table and click on Next.
- In this screen, we need to select the measures that we need in our cube. Select Order Quantity and Sales Amount as the measures and then click on Next.
- Next, we need to select the dimensions which are related to our fact table. In this case, we need all the three existing dimensions which are Date, Customer and Product. Select all three dimensions and click on Next.
- The next screen allows us to make new dimensions from the Fact, if needed (for e.g., if we need to make fact dimensions for order number). For this demo, we are not making any and hence uncheck them and click on Next.
- Name the cube as Sales and click on Finish.
Now we should be able to see the measure group and cube dimensions in the Cube Structure tab as shown below.
The cube dimensions are the localized versions of the database dimensions, and it is possible to set some properties differently from the database dimension like AttributeHierarchyEnabled, AttributeHierarchyVisible, etc. This means that it is possible to hide an attribute (say Category in Product dimension) in one cube and at the same time, make it visible in another cube. As of now, we will leave the properties as it is.
You would have noticed that there are multiple copies of the Date dimension – Ship Date, Due Date and Order Date. This is known as role playing dimensions and occurs because there are three relations from the fact table to the Date dimension as can be seen from the above picture.
It is also possible to rename the measure groups or the measures from this pane by just right clicking on the object and selecting the rename option. We are not going to do any renaming for the measures now though.
- The relationship between the dimensions and the fact can also be manually specified in the Dimension Usage tab. For example, if we needed to relate the Product dimension with the measure group, we can click on the ellipsis in the intersection and then open the Define Relationship screen to define the join column. For this demo, all the relations were made using the wizard so there is no need of any further step.
- With this, we have our basic cube ready. Right click on the SSAS Tutorial project and select the Process option. Click on Run button to start the processing.
- Once processing is over, click on the Close button. Now we should be able to browse the Sales cube in the Browser tab of the cube. Drag and drop some measures and attributes in the pane and see if you are getting values.
With this, we have come to the end of this post. Keep following this tutorial to learn more about base measures and creating calculated measures.