Month and Date Conformed Dimensions

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

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

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.

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,

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

Thanks,
Brian

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.

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.

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?

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 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?