Same dimension definition, but different set of data.

Hi,

I'll do my best to explain...

We have a date dimension that has a few columns that are relative to what we have defined as the beginning day of the week. An example of this is a column
named 'day of week'. This is a numeric column that will hold a 1 for Sunday, 2 for Monday, and so on for the rest of the week for a week we have defined as
beginning on a Sunday. So far so good.

A requirement came up to also carry date dimension information for another set of data based on a different day that starts the week - Monday for example.
This would have the day of week value as 1 for Monday, 2 for Tuesday, etc.

At first I thought no problem... an example of a conformed dimension, but with the data being different I am thinking that does not qualify as a conformed
dimension.

With the above in mind, would I be better off carrying the other set of data in a seperate dimension? Or create another table that holds the differing values
only? or???

I am open to suggestions on what you think is the best way to approach this.

Thank you in advance.

Of course it is conformed dimension. Adding more attributes for different week values is not much different from having financial calendar attributes in the same date dimension. What defines the dimension conformance is whether the dimension is sharable by multiple fact tables in their respective star schema. Just add relevant attributes for different calendar requirements unless the attribute is multi valued.

Thank you Hang.

Yup, I agree with Hang

Just give those fields specific names (same as with the fiscal ones) like:

DayOfWeekProduction
DayOfWeekFinance

you can be creative here:

DayOfWeekFromSunday
DayOfWeekFromMonday



This will be very clear when the user sees the Date dimension in their cube etc. they can read "DayOfWeekFromMonday" drag that onto their report and there would be NO margin for confusion / error.

Thanks for letting me understand!

tgrady -

Duplicating all of your attributes within the existing Date dimension will work to a point. But if you have a large number of attributes that you'll need to duplicate, or worse, if you get an additional requirement to show yet another perspective (based on another day of week as the start), then this approach can get a bit cumbersome.

Another approach can be borrowed from the use of multiple calendars for multiple countries or business units. Could easily apply that technique for your requirement, as you are essentially needing an alternate view of your date dimension.

For details, here's a link to a Kimball article that explains the approach in the section "Multiple National Calendars":
http://www.kimballgroup.com/html/articles_search/articles1998/9812IE.html

And for some additional diagrams and more description of options, here's another post I put together:
http://brentgreenwood.blogspot.com/2011/12/multiple-calendars.html

Hope that helps.
Brent Greenwood