Wednesday 15 February 2012

ssas - Multiple Joins from one Dimension Table to single Fact table -


I have an actual table in which 4 date columns have been created, log-date, active date and angiegred date. I have a dimension table named DimDate whose main key can be used as a foreign key for all 4 date columns in the facts table. So the model looks like this. Enter image details here

But the problem is, when I want to sub-filtering For dates based on the Date column, for pre: Count all the users who were created in the last month and are engaged in this month. It is not possible to do this with this design, when I filter the measurements with creating a date, then I can not filter more for a different time window for the engagement date. Since all are connected to the same dimension, they are not working independently.

However, if I create a separate date dimension table for each column, and thus join them, then it works. Enter image details here But when it is 20 different date columns in the actual table in the real world scenario, this is very It sounds cumbersome, where I have to make 20 different dimensions and add them one by one, is there any other way that I can achieve my scenario, in which many duplicated date dimensions can be created ? This concept is called a Role-Playing Dimension. You can tell the table to the DSV or the actual dimensions on each date. Do not add it once, add a date instead, then go to the Dimension Usage tab instead. Click Add Cube Dimension, and then select Date Dim. Right-click and rename it. Then update the relationship to use the correct areas

There is someone covering this topic.


No comments:

Post a Comment