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.

SSAS #22 – Dimensional Hierarchy Contained in a Single Dimensional Table (Star Schema)

We have been building hierarchical structures in transactional/operational systems for years with technique such as normalization. What about  hierarchical structures in an OLAP system? Experienced data mart/warehouse designers will tell you that they are different because the hierarchy for the dimension is actually stored in a single dimension table.

In a start schema, that is.

Yes, it is true. In a star schema, we do not need to break hierarchies into separate tables. One single dimension table can contain natural hierarchical structures among the dimension attributes.

Sometimes, we use a more normalized structure, and break the hierarchies into separate tables. The result is a snowflake schema.

This blog post is not about star schema verses snowflake schema. It’s about implementing a natural hierarchy that is contained in a single dimensional table in Analysis Services.

In this first blog I’ll show you one simple dimension table that contains such natural hierarchy. Implementing this kind of dimensional hierarchies in SSAS 2008 is simple enough, with a few tricks. I’ll show you how to do that in the next blog post.

Data Source View – Fact & Dimension Table

This is a screen shot of my simple fact table and the simple dimension table.

Besides the key column on the dimension table DimTreatment, there are two attributes, TreatmentType and Treatment.

 

Store one-to-many relationship in a single dimension table

These two attributes contain a natural one-to-many relationship, that is, several treatments can be grouped into one treatment type.

Storing this one-to-many relationship (hierarchy) in a single dimension table allows for the easiest browsing of the dimensional data. In this simple example, users could easily choose a treatment type and then see the list of all the treatments within the treatment type.

In the next post, I’ll show you how to implement the above one-to-many relationship/hierarchy in SSAS 2008.