Over the last several weeks, I’ve taken you through the steps on how to build a basic cube in SQL Server Analysis Services (SSAS). To do that, I’ve been using the Contoso Retail database to illustrate the concepts. Today I will cover one last topic before taking a break from SSAS to cover some of the new features in PowerPivot 2012. I will show you how to build an attribute hierarchy for one of the Contoso Dimensions.
If you have been following along you may say, “That’s no big deal. I’ve been creating hierarchies from the very beginning by choosing two or more attributes from a dimension for the rows or columns.” For example, you may have dragged Category over to the row label area of the cube to display sales by product category. Then perhaps you dragged Subcategory over to the right of Category to create a simple hierarchy showing subcategory sales within each category. Perhaps you even dragged Product to the right of Subcategory so users could drill down through the subcategory to see the individual product sales.
Yes, you did create a hierarchy. In this case, you created a natural hierarchy. A natural hierarchy is one in which the order of the attributes really only makes sense when ordered one way. In this case, subcategories exist within categories. Categories do not exist within subcategories. Similarly, products exist within subcategories. Subcategories do not exist within products. Another good example of a natural hierarchy might be to begin with years and drill down to quarters, then months, and finally days of the year. (Yes, it is possible that you may want to look at the sales for the fourth quarter across all of the years for which you have sales data, but that is a future discussion.)
Sometimes, it makes sense for the developer to predefine the hierarchy in the cube definition so that the user need drag only the hierarchy definition to the row or column of the table. This insures that the user places the attributes in the correct order. But that is not the only reason to predefine the attribute hierarchy.
By default, SSAS automatically relates every attribute in a dimension to the dimension key. In the example above, that dimension key would be the Product ID. Therefore, even subcategories are related to the appropriate Product ID. Similarly, every category is related to its appropriate Product ID. Suppose you wanted a sum of the sales for Recording pens, a subcategory of Contoso’s products. SSAS can scan through the Contoso retail sales data for sales belonging to the Recording Pen subcategory and sum the values. Similarly, if you wanted to know the total sales of audio products, SSAS can scan through the Contoso retail sales data summing sales where the product belonged to the product category, Audio.
While this method returns the correct answer, SSAS has is a faster method. SSAS stores intermediate values of measures from the fact table by the dimensions defined in the cube. In other words, it stores sums of sales by product ID if product ID is a dimension key. However, it does not store intermediate values for other dimension attributes such as subcategory or category. To get the sum of sales for categories, it adds the previously calculated sales for each product in the subcategory. Similarly, to report the sales by category, it adds the sales for each product in the category. You might ask, “Wouldn’t it be faster to just add the sales subtotals for the subcategories within the selected category?” Yes it would, but without defining attribute relationships, SSAS does not make these pre-calculated values available. Granted, for the size of the Contoso Retail cube, this difference in how long it takes to calculate values for higher level groups in a hierarchy may be insignificant. On the other hand, if you have a large cube with millions of rows of data, this difference can become significant. Therefore, let’s see how to build hierarchies after defining Attribute Relationships.
To begin, open the Date dimension and click on the Dimension Structure tab. Drag the fields you want to use as attributes from the Data Source View to the Attributes panel. Note that you do not have to include all attributes. In fact, the larger your database, the more selective of the attributes you will want to be in order to maintain good performance. The following figure shows the selection of attributes I decided to include in my Date dimension.
Next, click on the Attribute Relationships tab at the top of the screen. This shows the three panels shown in the next figure. The top panel graphically displays the currently relationships. Of course initially, there are no relationships defined between the attributes of the Date dimension. In fact, we can see this by looking at the panel in the lower right which shows that the field Date, which is the key field, is related to all the fields in the dimension. Furthermore, SSAS defines this relationship by default as a many-to-one relationship. In other words, there are many dates within each Calendar Month, Calendar Quarter, Calendar Year and all of the other fields.
Displaying key values are often not very user friendly. That is why most key values also have a corresponding description field that contains a user friendly description. In this step, I will associate an attribute label with the attribute key value for each month, quarter, and year field by using the Name Column property. We covered this topic on June 23rd in Customizing a Dimension. Be sure to also set the Order By attribute to use the Name property, not the Key, AttributeKey, or AttributeName property. (Note, that while using the Name property for Year, you may not always want to do this such as when displaying month names. In that case, you may still want to sort by the column key value which typically is a numeric value for the month with January corresponding to “1”.) You can change the field Type property to one of the date types to help SSAS understand how to aggregate the data.
When you are done, the main panel of the Attribute Relationship tab page shows the main attribute values with blue underlines beneath their names. This indicates that SSAS is expected an attribute relationship to be associated with these fields, but that relationship has not yet been defined.
Before, defining those relationships, let’s remove the label columns as attributes since we will no longer need them to be displayed as separate attributes in the dimension. Remember we just defined each the other key attributes to use a label as an alias value when displayed in the cube. You don’t want to confuse users by having two separate attributes within a dimension that both appear to add the same filter to the cube. Select each label attribute previously added. Right click on any of the selected items and choose Delete from the popup menu to remove them
We now have a clean list of attributes and can update the attribute relationships instead of defaulting to relating everything to the date key.
Right click on an attribute in the attributes list. I generally work the hierarchy from the lowest level (most detailed attribute) to the top level. Therefore, in this case, I’ll begin with Calendar Month because the attribute relationship between Date and Calendar month already exists. When I right-click on Calendar Month and select new Attribute Relationship from the popup menu, the Create Attribute Relationship dialog appears (if you go back and edit the relationship later, it will say Edit Attribute Relationship, but it is the same dialog) and automatically puts CalendarMonth in the Source Attribute field. Select Calendar Quarter as the Related Attribute. The Relationship type can be either Flexible or Rigid. In this case, we can say that the relationship between months and quarters is fixed and will never change and select Rigid. (Products might get moved to a different subcategory occasionally and should therefore by defined as relationship type flexible.)
Click OK to complete the attribute relationship. Then define a similar attribute relationship between Calendar Quarter and Calendar Year, Fiscal Month and Fiscal Quarter, and Fiscal Quarter and Fiscal Year. When you have completed these attribute relationships, the top panel should visually display the relationships as in the following figure. Note also that the Attribute Relations panel has also been updated to not only show our new relationships, but to also remove the unnecessary relationships from the Date field.
Next, return to the Dimension Structure tab. Select Calendar Year (the top level of the calendar date hierarchy) and drag it into the center Hierarchies panel. This will begin a new hierarchy definition. Next select and drag Calendar Quarter to a spot beneath Calendar Year where it says <new Level>. Similarly drag over Calendar Month and Date. Finally, right-click on the title bar of the hierarchy and select Rename from the menu that appears. Change the name from the generic Hierarchy to Calendar.
Similarly, create a second hierarchy by dragging Fiscal Year over from the Attributes panel to the Hierarchies panel, but below the Calendar hierarchy. As done above, drag over Fiscal Quarter, Fiscal Month, and Date to the new hierarchy definition. Change the name of this hierarchy from Hierarchy to Fiscal.
Next I recommend saving all the changes made so far by click the Save All button in the SSAS main menu or by selecting Save All in the File dropdown menu.
Next I must reprocess the Date dimension because of these changes. The Process button is in the menu bar of both the Dimension Structure and the Attribute Relation tab page. Click Run and wait a few moments for the processing to complete.
Close the Process Progress and Process Dimension dialogs when they complete. Just to make sure that I did not miss anything, I rebuild and deploy the cube definition. As we saw in an earlier blog, you should see the message Deployment Completed Successfully in the Status area in the lower right of the screen.
Finally, open the cube by double clicking on the cube name in the Solution Explorer panel. Before you can use the new cube data, you will need to click the Reconnect button found in the menu bar of the Browser tab page.
Again a few seconds could pass until this process completes. Now you should see an updated Dim Date dimension list long with right side of the page. Notice the two new entries, Calendar and Fiscal, at the bottom of the list with the pyramid shaped stack of blue boxes. This indicates that these hierarchies are pre-defined hierarchies that are ready to use.
If I drag Calendar to the Row Fields area and place Sales Amount in the Detail Fields area, I can see sales by year.
Also notice the box with the plus sign before each of the year labels. I can click on plus sign to drill down into calendar year to display first quarters, then months, and finally days within each month. The following figure shows some of the drill down options to calculate Sales Amounts.
Note, I could also open the hierarchy on the left side and select calendar quarter instead of the attribute with the rectangular set of blue boxes and drag Calendar Quarters directly to the Row Fields area. Doing this uses the predefine calendar quarter summary data that the cube now has. It also automatically includes the rest of the hierarchy (months and days) should I want to drill down further.
Sorry, this was rather long, but I wanted to reach a good stopping point in this discussion.
C’ya next time when I take a look at PowerPivot 2012.