Home > MDX Challenges > MDX #14 – Visualize User-defined Hierarchies

MDX #14 – Visualize User-defined Hierarchies

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.

clip_image001

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.

clip_image002

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.

clip_image003

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.

  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: