Separate DimDate, DimMonth, DimYear tables?

[kimron]

New to DW, I'm just trying to make sure our initial design is correct as we get going. I'm told the best flexibility and ease of ETL is to go snowflake with a fully normalized dimension model, and then use flattened views to be read by (and processed into) the cube. Sound good so far or disagreements?

So, given normalized snowflake, does that mean separate day, month, and year dimension tables?

Here is the first scenario that I'm modeling for: commoditiy futures prices. On a given date, a given commodity will have prices for future periods (e.g. Dec 2011, Mar 2012, June 2011). Pretty simple.

FactFutures
DateKey
CommodityKey
MonthKey
YearKey
SettlePrice

Does this seem right? Some suggest I shouldn't have DimMonth and DimYear tables and just use the 1st day of the month and just use the DimDate table. But their seems like a hack to me as the period (e.g. Dec 2011) has nothing to do with a particular date within the month. It's just the "December 2011" market on the Chicago Mercantile Exchange. I.e. the appropriate level of granularity is to the month, not lower.

Any help getting me going is greatly appreciated.

Thanks!

[ngalemmo]

Sound good so far or disagreements?

Have you been talking to a Teradata salesman?

What you describe is not the premise for dimensional modeling. It is a different way of looking at the problem with a vocabulary does not include the word "normalize".

The concept behind dimensional modeling, from a relational database point of view, is to create simple data structures that are easy for the business to understand and have a schema that performs well in most database environments.

The existence of 'big data' and large MPP systems has complicated matters. There is a fairly wide divergence as to how well any particular system performs against a strict star schema. So, depending on your system, how you design your physical model will vary significantly.

As far as dates go, I've not see a reason to have separate dimensions solely to provide a hierarchy.

[BoxesAndLines]

You need to buy Kimball's dimensional modeling book. You've been misled down a path to poor performance.

[Sponsored content]