Analysis Services: Solving Hierarchy Errors of Uniqueness
Multidimensional Cubes provide speed when it comes to retrieving aggregations that are important to business decisions. Being able to slice or group the measures by dimension attributes helps with a quick analysis and flexible/interactive reporting. Configuring these attributes as hierarchies have some details that are not at first obvious. The error message when these problems exist is not extremely helpful for someone new to cubes.
Let’s look at creating a Date hierarchy with Year, Quarter, Month and Day. Our cube already has measures created from a sales fact table and the dimension for a date has been created without any hierarchies.
https://lh3.googleusercontent.com/-fjPEeUO9cDU/VulnpyZwwdI/AAAAAAAAA5o/4lBqUOMS2Dg/clip_image002_thumb%25255B2%25255D.jpg?imgmax=800
Figure 1: Attribute List for Date Dimension
The measures can be displayed in a Pivot Table in Excel. Figure 2 below shows the Sales amount sliced by Sales Territory and Year/Quarter/Month.
https://lh3.googleusercontent.com/-n1RJxtgyeuo/VulnqlDm6mI/AAAAAAAAA5w/Sk2Qq9grIt0/clip_image004_thumb%25255B2%25255D.jpg?imgmax=800
Figure 2: Pivot Table in Excel
Figure 2 shows the Year, Quarter and Month as Rows while the Sales Territory is used as columns with Internet Sales used for Values in the Pivot Table. Users will get frustrated when they have to pick one attribute at a time when logically the hierarchy is known.
To create this hierarchy, you need to edit the date dimension in the cube.
https://lh3.googleusercontent.com/-8le_AmT2gr0/VulnrG1J83I/AAAAAAAAA4Q/MZrt6iO9JBs/clip_image006_thumb%25255B1%25255D.jpg?imgmax=800
Figure 3: Edit Date Dimension in SQL Server Data Tools
To create a hierarchy, you can drag and drop the first or top level of the hierarchy from the Attributes pane into the hierarchies’ pane. We will do this with Year at the top level.
https://lh3.googleusercontent.com/-_e3gEd7QSFg/Vulnr9at3EI/AAAAAAAAA4Y/DrB5CyG_uk4/clip_image008_thumb%25255B1%25255D.jpg?imgmax=800
Figure 4: Drag Year to Create New Hierarchy
To finish this hierarchy, drag the Quarter under the Year followed by the Month and Dates attributes. To complete the Hierarchy, right-click the name Hierarchy, and select Rename. We renamed the hierarchy to Y-Q-M-D.
https://lh3.googleusercontent.com/-CsqAFtG29iA/VulnsZxoQcI/AAAAAAAAA4g/nj4fLikHKKE/clip_image010_thumb%25255B1%25255D.jpg?imgmax=800
Figure 5: Y-Q-M-D Hierarchy Created
We can deploy this project and preview in Excel to see the effects.
https://lh3.googleusercontent.com/-_sMdDwFjsN4/VulntTUQLvI/AAAAAAAAA54/I2qPUm1APtk/clip_image012_thumb%25255B2%25255D.jpg?imgmax=800
Figure 6: Preview Hierarchy Y-Q-M-D in Excel
So, what is the problem at this point? Well, for performance reasons, there is a blue line under the hierarchy name in the Cube project. The message tells us to create an attribute relationship for the hierarchy. This is done by editing the date dimension using the Attribute Relationship tab.
https://lh3.googleusercontent.com/-Auj01b4yOYs/Vulnt21KU4I/AAAAAAAAA6A/QWKWX8ISCyM/clip_image014_thumb%25255B2%25255D.jpg?imgmax=800
Figure 7: Attribute Relationship Does Not Exist.
Y-Q-M-D is a natural hierarchy because a Day is in a Month that is in a Quarter that is in a Year. So, we should be able to show that in the Attribute Relationship for this Hierarchy. You can drag and drop Quarter on Year, then drag and drop Month on Quarter to accomplish this. Dates is the root attribute or key to the dimension.
https://lh3.googleusercontent.com/-Yor3g2a7kmk/VulnutwgFRI/AAAAAAAAA44/lggSxDuu44s/clip_image016_thumb%25255B1%25255D.jpg?imgmax=800 https://lh3.googleusercontent.com/-qrwNQ1SfJN4/VulnvCV0UkI/AAAAAAAAA5A/x09mNeraliU/clip_image018_thumb%25255B2%25255D.jpg?imgmax=800
Figures 8 & 9: Before and After Y-Q-M-D Hierarchy
Now, when we deploy, we get an error. The error message with the red circle and white x do not tell us the problem. The problem is in the last warning indicating that Quarter has duplicates for value 4. In order for attribute relationship to exist, the values in each have to be unique across all occurrences. The Quarter 4 (as well as 3, 2 and 1) are duplicated for every year we have in the data dimension table.
https://lh3.googleusercontent.com/-GiO4B0oZa0A/VulnwVl6IEI/AAAAAAAAA6I/XzwNv1ei2iQ/clip_image020_thumb%25255B2%25255D.jpg?imgmax=800
Figure 10: Deployment Failed
There are a couple of solutions to this problem, but we are only going to look at one. We are going to use multiple columns in the KeyColumn property of the Quarter and Month to create uniqueness. Then, we have to add a column to the NameColumn property in order to have something display for the multi-column KeyColumn property.
https://lh3.googleusercontent.com/--zK-EuDYAdc/VulnxHZiSWI/AAAAAAAAA5Q/BFRfUphkkrg/clip_image022_thumb%25255B1%25255D.jpg?imgmax=800
Figure 11: Changing the KeyColumn of Attribute Quarter
To do this, you have to highlight the Quarter attribute in the Attributes’ pane, then go to the properties. Find the KeyColumn and click the ellipsis. When prompted, add CalendarYear to the Key Columns list and move the Year above the Quarter (Figure 11). Do the same thing for Month, add CalendarYear to the KeyColumn.
https://lh3.googleusercontent.com/-K_hUe9jUNco/VulnxjLeHyI/AAAAAAAAA5Y/OvpzY9hxeog/clip_image024_thumb%25255B1%25255D.jpg?imgmax=800
Figure 12: NameColumn for Month Attribute
The NameColumn needs to be changed from nothing to CalendarQuarter for Quarter attribute and EnglishMonthName for Month attribute (Figure 12). Re-deploy the project and the error should no longer exist and we get a Deployment Completed Successfully.
https://lh3.googleusercontent.com/-b8qcfFVVUgw/VulnyZi07GI/AAAAAAAAA5g/7CFXikG86RE/clip_image026_thumb%25255B1%25255D.jpg?imgmax=800
Figure 13: Deployment Completed Successfully
The use of an Attribute Relationship for natural hierarchies greatly improves the processing and retrieval of data from the cube. This also assists the aggregation builder for indexing the combination of dimension attributes needed for analysis. In the end, the cube can retrieve the aggregation from the month to get a quarter or quarter values to get the year which saved retrieving details to aggregate up the hierarchy.