Separate DimDate, DimMonth, DimYear tables?

View previous topic View next topic Go down

Separate DimDate, DimMonth, DimYear tables?

Post  kimron on Mon Nov 14, 2011 10:21 pm

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!

kimron

Posts : 1
Join date : 2011-11-14

View user profile

Back to top Go down

Re: Separate DimDate, DimMonth, DimYear tables?

Post  ngalemmo on Mon Nov 14, 2011 11:16 pm

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Separate DimDate, DimMonth, DimYear tables?

Post  BoxesAndLines on Tue Nov 15, 2011 11:36 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Separate DimDate, DimMonth, DimYear tables?

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