Tabular Hierarchies Across Multiple Tables

Recently I was looking at the Adventure Works database and I noticed how Product, ProductCategory, and ProductSubCategory are their own separate tables. It got me to thinking about how someone might want to take attributes from each of those tables and create one hierarchy for reporting when you are looking at it. In DAX, this can be accomplished by using the RELATED() function. By using the related function in our table with our lowest level attributes, we can bring in the higher levels and create our hierarchy. This is really useful, especially if our data source isn’t necessarily a data warehouse and may be something more transactional with many tables split all over the place.

 

The Related function excepts one argument, which is the name of the table/column that we want to pull back. Let’s start with a simple diagram of our 3 tables:

 

image

 

Since our Product dimension is our lowest level of granularity of these attributes, what we want to do is bring the Product SubCategory and Product Category fields into the product dimension. Change your view to the data view in the product dimension and add a calculated column to pull in the subcategory:

=RELATED(DimProductSubcategory[EnglishProductSubcategoryName])

You can see our RELATED function at work. What we’re telling the function to do is to find the relationship that exists between product and product subcategory, and bring back the product sub category name. Once you click enter, rename the column to sub category name. Next we want to bring in the product category. Add a new calculated column, only this time our RELATED function is going to point 1 column farther, into the Product Category table. The RELATED function is really useful for this kind of analysis, especially since it can traverse multiple tables. For any old school excel pros out there, take that vlookup! Below is our new column with both Product Sub Category and Product Category added:

image

 

Hide your key column attribute, and then go to diagram view and hide your subcategory and category tables. To hide the entire table, right click on the table itself and select hide from client tools. Create a new hierarchy in your dimension where you added your related columns to, and added the category and subcategory to them. The final design looks like the below:

 

image

 

And that’s it! Now we have all of our related attributes in one table that we can create hierarchies off of. This is very useful for multiple snowflakes in a warehouse or transactional databases.

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