June 21, 2011 at 11:45 am
Hi,
I have a weird situation. I need to apply to my fact tables, different time dimension for different business group. Some when to have the current date as the real current date, some need to have the current date as the last day of the previous month.
When do the building of some of the cubes, I can use a different version of my time dimension table. The issue is more on the online reporting. As of now, we have 2 dimensions tables, this mean the user need to know which one to use.
Am I the only one in this situation? Any suggestions/idea on how to manage that?
Thanks
June 23, 2011 at 6:07 pm
I'm going to take a stab at this - though I'm not sure I understand what you're asking...
If you have different fact tables, they should be able to use the same date dimension. If you have one fact table that uses the current date as the date for the record, then your ETL can lookup that date key for that fact table, and for another fact table where the date is the last day of the previous month, your ETL can lookup that date key. Just because the dates are different in each fact table doesn't mean you need a separate dimension.
On the reporting/presentation side, you just need to make it clear which dates the user is using. What reporting tool are you using?
June 24, 2011 at 2:02 am
You have two options:
1. You have only one time dimension, but with different attributes that describe the "various possibilities." If needed, you can use role-playing dimensions to split that singe time dimension up in several dimensions.
2. You use seperate time dimensions, as you stated earlier, and then yes, your users need to know which one to use. So give them good descriptive names. (And hey, it's what they ask: different time dimensions, so they should be able to keep them apart)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 24, 2011 at 6:10 am
A version of Koen's solution #1 works fine for us. Date/Time dimension table includes about thirty attributes to help slice and dice the data in different ways.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply