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.