Multiple Joins from one Dimension Table to single Fact table

ssas olap olap-cube
user330612 picture user330612 · Mar 17, 2015 · Viewed 8.4k times · Source

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?

Answer

mmarie picture mmarie · Mar 17, 2015

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.

Related questions

SSAS MDX WHERE clause syntax - filtering multiple values from the same hierarchy

I'm quite new to data warehousing and MDX queries, and I realize that I'm probably not using the MDX syntax properly in the case below. I'm using SSAS 2005 on MS SQL Server 2005 and would like to query my cube and …

Read More
OLAP Cube deployment issues

I'm really new to this, so I am probably making a simple mistake. I need to make an OLAP cube using a remote database. After I set up the dimensions and measures and create the cube, I can not get …

Read More
How to determine which SSAS Cube is processing now?

There is a problem when several users can process the same cube simultaniously and as a result processing of cube fails. So I need to check if certain cube is processing at current moment.

Read More