Hi reader,

I am facing a challenge. I have a fact table with at least 10 DATE_SIDs, and I would like to know how encapsulate them in separate dimension instead of using referenced dimension. Thank you in advance.

YaBenBI

Regards,

Hi

To my understanding of your scenario, you have a fact table with measures related to different dates e.g. Order Date, Ship Date and Delivery Date etc.

In terms of SSAS, this scenario will go with Role playing dimension with Date Dimension to you don't require to create a separate dimension for eash of DATE_SID.

1. Create one DATE Dimension

2. In SSAS, dimension usage create the relations to Fact table with Date Dimension with corresponding DATE_SID

3. You will get a DATE role-playing dimension for each FACT DATE_SID

4. Rename role-playing Dimensions with your business scenarios

so in this case, you need to have one Date dimension and one Fact table then ending up with 10 Role-playing Date dimensions and Facts.

Hi YaBenBI,

First, I'd create a Date dimension in relational database (I assume it's already there), map all these date columns to the Date dimension by FK relationship using ETL. By using INT columns in fact table instead of DateTime columns, I'll save a lot space because DateTime consumes 8 bytes whereas INT takes just 4 bytes.

Second, I'd create a date dimension  in my cube, which I guess is already there in your OLAP solution and create role playing dimension for all these date columns. So while you browse your cube you will see many Date dimensions which are based on single Date Dimension of your cube.

If you want your dates for information purpose, as Prav suggested, you can create a degenerate dimension.

HTH

Regards,
S