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

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.

image

 

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.

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

%d bloggers like this: