Archives of the TeradataForum

Message Posted: Sat, 09 Nov 2002 @ 19:31:46 GMT

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

Hi,

"Neil Raden" Wrote:

I have to disagree with this comment.

Have you not seen V2R5, if not I would take a look, as they are making your life easier. Here are 3 features to name a few that could be easily used to improve development and query performance of a Dimensional Model.

1) Partitioned Primary Index

As PPI's are limited to INTEGER based fields, they will find a much higher usage in Dimensional Models. Especially where Surrogate Keys have been heavily used.

Within one of our 3NF designs, we would like to partition the table by country and then date. Our problem is that we use a 3 character ISO standard code. To perform the optimisation, we end up creating a surrogate key, and therefore moving away from our true 3NF design. If this was a Dimensional Model, we would have had a "Location" dimension, which would have already used surrogate keys and therefore the optimisation would be far quicker.

This feature alone on a Fact table could improve the query performance quite dramatically.

2) Identity Column / ANSI Standard Sequences

For the first time within Teradata, we have an efficient method of generating surrogate keys. This alone will save alot of time by you developers coming up with wonderful alternative methods, such as RANK, CSUM, InMod routines, etc...

3) Soft RI Used To Eliminate Joins

Single dimension views could be created on Snowflake Models, covering all tables. Then with Soft RI being enforced, Teradata will eliminate any joins to tables that data is not being retrieved from or is not required to complete the query.

This will allow you to create a simple star schema logical model for all of your Dimensional Model designs, and therefore simplifying the requirements for the BI tools being used, who tend to favor the true star schema models.

My opinion:

I believe that their is a place for both 3NF and Dimensional Models within an Enterprise Data Warehouse.

I think that for an EDW to work, you first have to get the base correct, and this can only be done using a 3NF design, as it provides a single version of your data in a format that meets your business model. This design is also the most efficient when it comes to the ETL requirements.

Once this base has been created, and you have a full set of data, it can easily be transformed into the analysis views required.

Because of the preference for BI tools to query on Dimensional Models, we tend to follow these rules:

1) Develop a logical dimensional model on top of the base 3NF model.

2) If the performance of the logical DM does not meet requirements, then physically create the DM.

We have found that the logical dimensional model approach has worked very well and I believe that with V2R5, this approach will become the best approach without a doubt.

One of the biggest benefits we have seen to this approach, has been the flexibility of the solution. If you have a full set of base data in 3NF, it is very easy to change the logical dimensional model to make use of more of the underlying data when required. Therefore creating a faster turn around.

Also by consolidating the dimensional models onto one physical EDW, reduces the ETL / maintenance / development / software / hardware requirements.

Regards

Matthew Winter

www.teratools.com

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