Archives of the TeradataForum

Message Posted: Fri, 08 Nov 2002 @ 16:23:48 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Dimensional Modeling serves 'datamarts' very well when there are a finite number of known business questions. However, if the dimensions are so numerous as to make the FACT table very DEEP (redundant data rows by dimension) and WIDE, then most RDBMS, such as Oracle, will see significant performance degradation, even for the known, 'optimised' queries.

Dimensional Models do not do well in an Enterprise Data Warehouse, primarily because an un-normilized model will not allow the 'unknown business question' to be asked and expect a resonably performing query to give the 'right' answer, also know as the ad-hoc query. Most EDW's contain multiple subject matter from mulitible and diverse data sources.

To homoginize this data, a normalized (3NF) model works best. This allows for the constant addition of new subject matter by way of integrating new, normalized entities and relationships, thereby eliminating database re-design for the dimensional model, or creating new sub-model fact tables that carry similar, related data redundantly.

Additionally, when the needs of the business change, or when the business questions change, or become more sophisticated, the DM typically has to go through a re-design and new implementation, wheras, as long as the subject matter remains constant, a 3NF, subject orientated database, would not need to be re-designed or re-constructed to meet the business needs, since it can do so dynamically.

In an EDW with many 'Fact' tables (DM) containing similar information, one' begins to ask which Fact table contains the data I'm most interested in, that I can query in a straightforward and efficient manner, and get the 'truthful' answer to my question? This is not the case with a 3NF implementation which has the right data in the right place for any business question contained within the subject matter.

From a technical standpoint, you also run into the proverbial "wall" of disk space much sooner in a DM rather than a 3NF implementation. You do have to create more 'joins' in posting similar queries where a fact table virtually eliminates join issues, but with Teradata, who cares. Joins are a piece of cake for Teradata. And the accuracy of the data is more easily verified via simple referential integrity rules identified in the model, wheras most Fact tables have to be validated with complex load logic and subsequent complex post load data validation routines.

Finally, I will say this, in some cases, BOTH models have there place in the total solution for Enterprise Data Warehousing to contain, at it's core a 3NF data model, and additionally, might include application specific Dimensional Models that are SOURCED from the 3NF database. These applications may vary over time, come and go, based on the business need and the type of end-user query tools. But the subject matter remains constant and stable in the 3NF model, allowing for great flexibility in accomodating those changes over time.

Michael E. McBride
Teradata Database Administrator
Teradata Certified Master
American Eagle Outfitters

	<Prev	Next>		<<First	<Prev	Next>	Last>>