Hierarchies in SSAS Multidimensional: What’s really happening up (or down) in there?

  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.

image

 

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:

image

 

And process it.

 

image

 

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:

 

image

 

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:

 

image

In conclusion of this post, the importance of creating natural hierarchies and avoiding unnatural hierarchies cannot be understated.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s