Descendants() function gives me the exact level in the Date hierarchy in SSMS
In MDX #14, I used this query to get the internet sales amount for each month.
The results above are from SSMS, and they are what I was expecting.
In SSRS, the same MDX query will give more than what the Descendants() function is asking
Run this same MDX query in the Query Designer in SSRS, this is what you will get.
We’ve noticed two differences.
- Unlike the results from the SSMS where the returns on the Y axis are not given any column names, all attributes from the Date dimension has a column name, and each column name is the same as the level name in the Calendar hierarchy.
- Unlike the results from the SSMS where only the Month is returned, the SSRS returned 3 more levels in the calendar hierarchy that are above the level Month.
Bad feature or good feature?
Having a column name is certainly a good thing in Reporting Services. But getting columns more than what I asked for can be annoying, although I can, on the other hand, also view it as a good feature, in case I need Year, Semester or Quarter for other purposes such as sorting.
User-defined hierarchies define one-to-many relationships between attributes in a dimension, and these relationships tell the MDX engine how to roll up measure values.
User-defined hierarchies in Microsoft SQL Server Analysis Services define the attribute relationships in a dimension. Once all the attributes and their properties are defined in a dimension, defining the attribute relationships will be the next step. A simple way to understand user-defined hierarchies (the attribute relationships) is that they define one-to-many relationships between attributes in a dimension, and these relationships tell the MDX engine how to roll up measure values.
A good example – Date dimension in the AdventureWorksDW database
A good example is the Date dimension in the AdventureWorksDW database.
If you open the Attribute Relationships in the Date dimension. here is what you will see.
In addition to the obvious one-to-many relationships that are defined between Date and Month Name, Month name and Calendar Quarter, etc., it also tells us that we can roll up a measure value from Date to Month Name, from Calendar Semester to Calendar Year, etc.
Write up some MDX queries to help visualize the Calendar hierarchies
Another way to understand the user-defined hierarchies is to write up some MDX queries.
The above screen shot is from SSMS when you browse the [Adventure Works] cube.The triangle-shaped symbol next to the Date.Calendar indicates a user-defined hierarchy. The rectangle-shaped symbol next to Date.Calendar Year indicates an attribute hierarchy.
Here is a simple MDX query with the DESCENDANTS() function that allows us to get to the different levels in the Calendar hierarchy.
Changing the level number 4 to any number between 0 and 5 and observe the results, you will start to appreciate what the dimension designer has done.
- level 0 = All Periods
- level 1 = Calendar Year
- level 2 = Calendar Semester
- level 3 = Calendar Quarter
- level 4 = Month
- level 5 = Date
Defining hierarchies in a dimension is a critical task
I certainly appreciate the thoughts that the dimension designer has put into it based on the business requirements.