2

I have a fact table that has 4 date columns CreatedDate, LoginDate, ActiveDate and EngagedDate. I have a dimension table called DimDate whose primary key can be used as foreign key for all the 4 date columns in fact table. So the model looks like this. enter image description here

But the problem is, when I want to do sub-filtering for the measures based on the date column. For ex: Count all users who were created in the last month and are engaged in this month. This is not possible to do with this design, coz when I filter the measure with create date , I can’t further filter for a different time window for engaged date. Since all the connected to same dimension, they are not working independently.

However, If I create a separate date dimension table for each of the columns, and join them like this then it works. enter image description here But this looks very cumbersome when I have 20 different date columns in fact table in real world scenario, where I have to create 20 different dimensions and connect them one by one. Is there any other way I can achieve my scenario w/o creating multiple duplicated date dimensions?

user330612
  • 2,189
  • 7
  • 33
  • 64
  • I got this working with Role playing dimensions feature...but I have a new problem..I see that SSAS creates separate dimensions for each date in the cube... I want all these dimensions to be grouped under a single dimension or dimension folder.. Is that possible? – user330612 Mar 17 '15 at 22:16

1 Answers1

5

This concept is called a role-playing dimension. You don't have to add the table to the DSV or the actual dimensions one time for each date. Instead add the date once, then go to the dimension usage tab. Click Add Cube Dimension, and then choose the date dim. Right-click and rename it. Then update the relationship to use the correct fields.

There's a good article on MSSQLTips.com that covers this topic.

mmarie
  • 5,598
  • 1
  • 18
  • 33
  • 1
    Looks like this is what I wanted. Didnt knew that this functionality works out of the box ie, when you have multiple connections to same dimensiuon from fact table the cube dimension wizard automatically creates multiple role playing dimensions in the usage tab..pretty cool! – user330612 Mar 17 '15 at 22:15