Role Playing Dimensions?

[dankwart]

Hi Folks,

I'm into a project using SSAS. I'm stuck when getting a little deeper into dimension usage:

What I got:
A time dimension table "dimTime"
A fact table "factEvent" with the two measures: "start" and "end"

What I want:
To display (in the cube browser for now) for every year/month/day how many events started and how many events ended.

2008 2009
started ended started ended
234 352 342 563

How can I achieve this? Full details please, I already tried a lot...

 

[RiverGuy]

You don't need a roleplaying dimension for this. A roleplaying dimension would help you if you wanted to slice by different date information. If I understand your project correctly, I would have the fact table modeled like so

factEvent
-----------
TimeKey INT/DATETIME,
Started INT,
Ended INT

Your TimeKey would join to your dimTime dimension, and your Started and Ended measures would be summarized.

This would be a summarized fact table -- telling you how many events started or ended on each date.

But, I think you might be using DATETIMEs for your start and end measures. If you want to model this table closer to the atomic level, the schema would look something like the following

factEvent
---------
TimeKey INT/DATETIME,
EventKey INT,
EventActionKey INT,
EventCount INT --(Probably always 1)

dimEventAction
---------
EventActionKey INT,
EventActionName VARCHAR(20) --(Contains either "Started" or "Stopped") 

dimEvent
---------
EventKey INT,
EventDescription VARCHAR(100)

In the second scenario, you would have one record each time something happened to one of your events -- if an event started, it gets a record. If two events started on the same date, you get two records, etc. You would also have only one measure, but another dimension to report on the type of actions -- either "Started" or "Stopped."

 

[dankwart]

Thank you for the help. I will evaluate whether I can use one of those methods.


Cheers!

 

[MDXer]

Be careful when you simply use Started and Ended. Depending on what started and ended represent you could acheve this by simply using built in aggregate types.

Example would be if you were reporting stock in a warehouse. If your time dimension was built in the right way you could have your Starting stock be an aggregate type of FirstChild and Ending be aggregate of LastChild based on a daily measure of current stock.