Role playing dimensions with SSAS

Role playing dimensions in SSAS are very useful.    Lets say you have a fact table with LandingPagePathId, ExitPagePathId, PagePathId.    Now in your database you only need to have one dimension table dPath.  All your paths are then stored in the same table.

We only need to have one Dimension defined in SSAS Path which links to the dPath table.

Now In your cube.  Right click in dimensions and click add new dimension

A new window will appear.

Select your Path dimension.  When it is added if you already have one called Path it will then be named “Path 1”.  Rename this to Landing Path.

Now over on the Dimension Usage tab you have the new dimension  “Path (Landing Path)”   Path is the dimension it truly came from (Landing Path) is the name you gave it.

To define the relationship you simply have it link though LandingPagePathId instead of PagePathId.

You can make another one for ExitPagePathId the same way.   This way you only have one dimension in your project and if you make any changes to it it will be reflected in all your role playing dimensions as well.

About Linda Lawton

My name is Linda Lawton I have more than 20 years experience working as an application developer and a database expert. I have also been working with Google APIs since 2012 and I have been contributing to the Google .Net client library since 2013. In 2013 I became a a Google Developer Experts for Google Analytics.

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.