Over the last several weeks, I showed how to load a Tabular model in SSAS using not only data from SQL Server, but also from other data sources as long as you can create a link between each table and at least one other table. Last time, I showed how to display the data in the model using Excel after creating a simple summation measure on the Sales Amount in the Contoso sales table. In the resulting pivot table, I showed how to manually define a dimension hierarchy by dragging more than one field to the rows area of the Field list. (Of course, I can also build a hierarchy of dimension values for the columns as well.) However, I suggested that leaving it up to the end user to define the hierarchy may not always be best because it assumes that the user knows what the hierarchy should look like. That may not always be the case.
A simple example might be a date hierarchy. I might think that everyone would naturally use the hierarchy Year -> Quarter -> Month as shown in the following figure.
That is not the only way to display sales data. For example, I could be interested in how sales vary over the years within a specific month of a quarter as shown in the next figure.
On the other hand, defining the hierarchy Month -> Quarter -> Year, while technically yielding correct numbers, is not useful since the field Quarter serves no real purpose.
So how can I protect users from defining inappropriate hierarchies? I could define the hierarchy as part of the model. I can only create a hierarchy from fields within the same table. If I want to create a hierarchy for Year -> Quarter -> Month, I can use fields already in the Date table. Returning to SSAS and Model.bim tab, and display the model with the diagram view (remember the diagram view can be turned on using the button in the lower right of the Model.bim window. Then locating the top level of my hierarchy, I right click on Calendar Year to display the following menu.
From this menu, I select Create Hierarchy. This creates two rows at the bottom of the table. The first labeled Hierarchy1 will be the name for the hierarchy. Notice the different icon before this name compared to the other fields in the table designating it as a hierarchy. The second row is indented indicating that it is a part of the hierarchy and shows the field CalendarYear as the first field in the hierarchy definition.
To add the next field to the hierarchy, CalendarQuarter, I locate and right click on the field name. This time I choose Add to Hierarchy from the first dropdown menu to display a second fly out menu. In this menu, I select the hierarchy to which I want to add the field. Initially, I only have the one hierarchy so it seems obvious that I want to add the field to that hierarchy. However, a table can have more than one hierarchy defined. In those cases, the need to specify which hierarchy to add the field to become critical.
Similarly, I would add Month as the third field to the hierarchy. By default, each field I add to the hierarchy is added to the bottom of the existing hierarchy. So what if I accidently select the fields for the hierarchy In the wrong order? Do I have to delete the hierarchy and start all over? Fortunately that is not the case. All I need do is right click on the field and use one of the options: Move Up or Move Down to change the order of the fields in the hierarchy. Notice that I can also remove a field from the hierarchy if I added the wrong field. I can even move a field to a different hierarchy (assuming I had more than one) if I dropped the field into the wrong hierarchy.
I might also want to change the name of the hierarchy since Hierarchy1 is not a very descriptive name. By right clicking on the hierarchy name, I can choose Rename to give the hierarchy a better name such as YQM to indicate years -> quarters -> month.
Now I can use the Analyze in Excel option in the Model drop down menu to open another instance of Excel to build a pivot table. However, if I want to continue working on the previous pivot table, I can open that instance of Excel and from the Analyze ribbon in the Pivottable Tools group, select Refresh All to update the PivotTable Fields list. Then by replacing the individual dimension fields in my Rows area with the new YQM hierarchy definition, I will see a pivot table like the following pivot table in which I can click on the boxes before the year and quarter values to expand or collapse the hierarchy.
By right clicking on the value, I can use the Expand/Collapse option to display a fly out menu that allows me to expand or collapse all of the fields at that level among other options. In summary, creating a hierarchy eliminates the need to decide which fields to include in the hierarchy and the order of those fields.
Next time, I will look at how to build the hierarchy when the fields needed are not all in the same table.
C’ya next time.