A few days ago I got an interesting question. The question was “When you build an hierarchy in SSAS, what’s actually happening to it on disk?” As in, how does the file system actually place it in files so it can easily identify the hierarchy and keep performance up? Sure, dragging all of those attribute relationships around in the dimension GUI is “fun”, but what does it do, and what do you gain? To answer these questions, let’s look at the process from beginning to end. For the below example, I’m just using the date dimension that’s available in Adventure Works.
Open up your date dimension and create your hierarchy. In this example, our hierarchy has 4 levels: Year, Semester, Quarter, and Month.
Make sure you set your key columns to use collections so your attributes stay unique, and set your attribute relationships properly. If you do not do this, you wind up creating what is called an “unnatural” hierarchy, which acts completely different behind the scenes than the natural hierarchy we’re creating here. An easy way to identify an unnatural hierarchy while you’re creating an SSAS dimension is the hierarchy has a yellow warning symbol above it. A quick definition of the 2:
- An unnatural hierarchy is where the attributes don’t “really” belong with each other, except for reporting. For example, Marital Status –> Education. Therefore, your attribute relationships tab would have no relationship between these attributes, except at the key level.
- A natural (user-defined) hierarchy is where each attribute is a property is a member property of the attribute above it. For example, Date (Year –> Quarter –> Month –> Day, or Country –> State –> City). Since each level is a member property, we can use the attribute relationships to define cardinality of one or many appropriately, as in the below:
And process it.
Now, we’re ready to talk about the file structure. When building a natural hierarchy, SSAS creates an actual physical file “materializes the hierarchy” within the file system that it can use to reference the hierarchy level extremely quickly, in something called an AHSTORE file. For example, this is what our same calendar hierarchy above actually looks like in the file system:
You can in the screenshot where AS created a hash file for each level (English Month Name, Calendar Quarter, Calendar Semester, and Year) so it could traverse the hierarchy faster.
Conversely, with an unnatural hierarchy, no such hash ahstore files are created, leading to bad performance and difficulty navigating your dimension through MDX queries. Here is a screenshot of the customer dimension with an unnatural hierarchy between Marital Status and Education:
In conclusion of this post, the importance of creating natural hierarchies and avoiding unnatural hierarchies cannot be understated.