SSAS #23 – Implement dimensional hierarchy in SSAS 2008
Let’s continue from my previous post, SSAS #22 – Dimensional Hierarchy Contained in a Single Dimensional Table (Star Schema).
I need to assume that you have done all the following so far:
- Created an Analysis Services Project in BIDS
- Created a Data Source to point to your star schema relational database (with proper connection string and impersonation information)
- Created a Data Source View with the fact table(s) and dimension table(s) you need for your project.
Step 1 – Create a dimension in Dimension Wizard
In my example, I need to create a dimension DimTreatment, with one key and 2 attributes.
Right click on Dimensions folder, and select New Dimension…
Dimension Wizard will pop up. Select Use an existing table to create the dimension.
Step 2 – Select a Key column and a Name Column
In the next Specify Source Information window, select your dimension table. The key column is automatically picked up by the wizard because the key column is defined in the DSV. The key column is also the default for the Name column. Because I do not have a description column for the key column, so leave the key column as the default for the name column.
Step 3 – Select dimension attributes
In the Select Dimension Attributes window, make sure you select all the attributes you need from your dimension table.
Step 4 – Dimension Structure Tab – Showing Attributes without Hierarchies
This is the final result from the Dimension Wizard. It shows all the dimensional attributes you have selected. No hierarchical structures yet.
If you are a very observant person, you will see a blue squiggly under the name of the dimension. Pointing the cursor to it.
"Create hierarchies in non-parent child dimensions."
This is one of the many Best Practice Warnings in SSAS 2008. The warning is saying that you need to create some sort of hierarchies in your dimension table. As a best practice, that is.
Step 5 – Create a hierarchy
We are happy to oblige in the case. We do need a hierarchy between attributes, Treatment and TreatmentType. We also hope that the blue squiggly will go away,
Simply drag attribute TreatmentType to the Hierarchies tab, and Treatment as well. Also rename the hierarchy name to something meaningful rather than the default Hierarchy.
You will probably get annoyed now, because we are getting more blue squiggly now. This one is under the name of the hierarchy.
“Attribute relationships do not exist between one or more levels in this hierarchy. This may result in decreased query performance.”
This Best Practice Warning is telling us to create attribute relationships.
Before I take a break, I just want to show you a snapshot of the new visual Attribute Relationships tab in the Dimension Designer in SSAS 2008.
I’ll continue in the next blog post to discuss why we need to create attribute relationships and how to do it in SSA 2008.