Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Role Playing Dimensions?

Role Playing Dimensions?

Role Playing Dimensions?

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...

RE: Role Playing Dimensions?

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


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


EventKey INT,
EventActionKey INT,
EventCount INT --(Probably always 1)

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

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."


RE: Role Playing Dimensions?

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


RE: Role Playing Dimensions?

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close