Month and Date Conformed Dimensions

View previous topic View next topic Go down

Month and Date Conformed Dimensions

Post  scruzloose33 on Mon Mar 12, 2012 11:33 pm

Kimball mentions that, according to the bus architecture, all similar dimensions should be either exactly the same or strict subsets of the most detailed dimension. In the Data Warehouse Toolkit, he makes quick mention that a "month" dimension should be a subset of the "date" dimension that just contains the rows indicating the first or last day of each month. But, what if you want to store more details for each month that might not make sense at the date level? For example, I would want to store things like the number of working days in the month, the total number of days in the month, etc. It doesn't seem to make sense to add these aggregate numbers for each record in the date dimension as it wouldn't really match the grain of the dimension.

How should this be handled? I don't foresee any issues adding these values only to the month dimension, but I wanted to get other people's opinions since this goes against the strict subset rule for conformed dimensions.

Thanks,
Shane

scruzloose33

Posts : 5
Join date : 2012-02-06

View user profile

Back to top Go down

Re: Month and Date Conformed Dimensions

Post  John Simon on Tue Mar 13, 2012 12:28 am

As usual, Ralph is correct. There is nothing wrong with having the number of days in a month in your date dimension.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Month and Date Conformed Dimensions

Post  Vishy on Tue Mar 13, 2012 2:03 am

There is no hiding, solution should be what John suggested. Month Name or Month level attributes will always repeat 28-30-31 times depending upon number of days in a month. Number of days in a month can not be used as additive measure for date dimension , reporting guys should take care of it.

Vishy

Posts : 75
Join date : 2012-02-28
Location : India

View user profile

Back to top Go down

Re: Month and Date Conformed Dimensions

Post  Jeff Smith on Tue Mar 13, 2012 1:08 pm

The month dimension is a view of the date dimension. In my date dimension, I have a column that is the Number of the Date in the month. I also have a column with a 1 for week day and 0 for weekend days and a 0/1 field called business days.

The Month view selects distinct from the month relevant fields on up, but it also has the min and max day_key and min and max calendar dates, sums the business days, etc. No reason it can't be done. It's not like it'll slow the performance of the view.

Date dimensions are wierd. They technically can be used as a date fact tables.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Month and Date Conformed Dimensions

Post  bciampa on Tue Mar 13, 2012 3:05 pm

Jeff,

If a month dimension is a view built on the date dimension, how is the surrogate key of this month dimension assigned?

Thanks,
Brian

bciampa

Posts : 8
Join date : 2012-02-24

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

Back to top Go down

Re: Month and Date Conformed Dimensions

Post  ngalemmo on Tue Mar 13, 2012 4:03 pm

Jeff Smith wrote:Date dimensions are wierd. They technically can be used as a date fact tables.

Oooooh... let's not go there. Besides, don't confuse factual information with 'fact' in the dimensional sense. Both dimensions and fact tables contain factual information from which you can report and do stuff with. A 'fact table' is a specific structure in a dimensional model.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Month and Date Conformed Dimensions

Post  Jeff Smith on Tue Mar 13, 2012 5:50 pm

That's why I said Date dimensions are wierd. Because they can act as both. If you wanted to know how many days in a six month period, you could answer that with the Date dimension. I mean, it's not like someone is going to create a factless Date fact table with 1 field - the date dimension key.


Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Month and Date Conformed Dimensions

Post  ngalemmo on Tue Mar 13, 2012 6:59 pm

Dimension only reporting is fine... and necessary depending on the question. Its is not contrary to dimensional modeling principles. It is just a different use case. If you can gather additional information from the DW using dimension only queries, who here is going to stop you?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Month and Date Conformed Dimensions

Post  Vishy on Wed Mar 14, 2012 1:12 am

not only date dimension any dimension can be used as fact and if we continue on this approach .. we will end up reaching a place from where we started : )

Vishy

Posts : 75
Join date : 2012-02-28
Location : India

View user profile

Back to top Go down

Re: Month and Date Conformed Dimensions

Post  Jeff Smith on Wed Mar 14, 2012 11:20 am

Vishy wrote:not only date dimension any dimension can be used as fact and if we continue on this approach .. we will end up reaching a place from where we started : )

Actually, any Type SCD can be used as a fact table - a badly designed fact table. If you wanted to know how many products were offered and you had a Type SCD production dimension, you get that answer from that table.

What would a Date Fact Table look like?

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Month and Date Conformed Dimensions

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