Same dimension definition, but different set of data.

View previous topic View next topic Go down

Same dimension definition, but different set of data.

Post  tgrady on Wed Jun 22, 2011 4:21 pm

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.


tgrady

Posts : 7
Join date : 2011-06-22

View user profile

Back to top Go down

Re: Same dimension definition, but different set of data.

Post  hang on Thu Jun 23, 2011 6:57 am

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.


hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Same dimension definition, but different set of data.

Post  tgrady on Thu Jun 23, 2011 3:45 pm

Thank you Hang.

tgrady

Posts : 7
Join date : 2011-06-22

View user profile

Back to top Go down

Re: Same dimension definition, but different set of data.

Post  ian.coetzer on Thu Jul 07, 2011 6:43 am

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.
avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 36
Location : South Africa

View user profile

Back to top Go down

Re: Same dimension definition, but different set of data.

Post  bettybarbor on Wed Dec 21, 2011 8:58 pm

Thanks for letting me understand!

bettybarbor

Posts : 1
Join date : 2011-12-21

View user profile

Back to top Go down

Alternate Calendar

Post  BrentGreenwood on Mon Jan 02, 2012 12:06 pm

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

BrentGreenwood

Posts : 6
Join date : 2011-12-25
Location : San Diego, CA

View user profile http://brentgreenwood.blogspot.com

Back to top Go down

Re: Same dimension definition, but different set of data.

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum