Recently, I was tasked with building a hierarchy using multiple dimensions. I was advised to use a named query or materialized view to accomplish this because SSAS doesn’t have the capabilities to read the columns/attributes from multiple dimensions. Since I have done this before, I decided to take the AdventureWorks database and give a step-by-step tutorial of how to accomplish this request. Let’s get started!
Accomplishing this is a fairly easy process. The key to this process is making sure you have the proper snowflake schema setup in place. We are going to use the AdventureWorks database to build our solution. Here you can see the schema is designed in a snowflake fashion. The first dimension from the fact table is the product dimension, the second is the subcategory dimension, and finally you have the category dimension.
The important aspect to this is that from the fact table, the product dimension is going to have the largest amount of data that joins back to the fact table(s). You can break it down to subcategory and then category, which resembles the opposite of a top-down approach. Instead of going from one to five, you’re going from five to one. Each product is a part of one category.
The next important aspect of creating this hierarchy is to have a foreign key reference in place to identify which product is a part of what subcategory, and likewise, with a subcategory being referenced to a category. The picture above also shows each primary key on all three tables.
I’m going to start with a blank canvas inside the AdventureWorks DW SSAS solution. Right click on Dimensions in the Solution Explorer and then click “New Dimension.” Next, you will be prompted with the dimension creation wizard.
Next check “Use an existing table” and click next.
Next, you will need to supply your main table. It is important to understand that the main table is going to be the table with the most records. In the case of the adventure works database, it’s the products table. The primary key is our key column.
The next page of the wizard identifies the related tables based on how the data source view has been set up. As you can see, the wizard has already identified the product category and product subcategory tables.
Next, select the attribute keys. Notice I said attribute keys because these are the columns we are going to use in the hierarchy to relate to one another.
Take note that I am using the Product Subcategory Alternate Key instead of the Primary Key that is defined on the table. The Alternate Key has the same integer value as the Primary Key. This is purposely used to have a linear attribute relationship instead of a vertical attribute relationship.
From here, you can go ahead and finish the wizard out. Name your dimension and click finish. It will populate everything in the dimension designer view.
Please note that I have listed my key values in my attributes and in my Data Source View pane, there are a total of three physical tables that we will use from our data source.
Next, we want to give our columns some friendly names. I will later change Product key to be “Product,” Product Category Key to be “Category,” and Product Subcategory Key to be “SubCategory .“
Now we need to go into the properties and change the named column for each of these attributes. Essentially, we are identifying the actual column that we want to display in our hierarchy. I want to display the English Name for the attribute that I am using from each table.
Right click on the attribute in the attribute pane and click properties. Scroll down to NAMECOLUMN and select the column you want to display.
Repeat the same exercise for the other columns that you’re using in the hierarchy.
Your attribute relationships should be linear and resemble a top-down approach:
Once this is completed, you need to process the dimension so that you can browse your results. Your results should look like this:
Notice I have created a hierarchy using three tables, and you can drill down into the specific product.
The goal of this blog post is to specifically show how you can create a hierarchy using multiple tables. Can this be done in SSAS? Yes, this can be done; however, it does require having the proper structure setup which is a snowflake schema. A snowflake schema has its pros and con, but we can touch on those in a later post. I hope this helps BI developers properly build their hierarchies when more than one table is required. Stay tuned for more blogs from the RDX bloggers in the near future!