Archives of the TeradataForum
Message Posted: Thu, 18 Aug 2005 @ 16:03:08 GMT
<-- Anonymously Posted: Thursday, August 18, 2005 11:44 -->
We having our warehouse for 10 years and now we questioning the way we modeled our data. Let's give you a big picture of the way we did it.
Those tables are the dimension table or our warehouse:
CREATE TABLE dimension1 ( NUPI1 INTEGER, different_information_for_this_table ..., NUSI1 INTEGER, BEGIN_DATE_DIM1 DATE , END DATE_DIM1 DATE PRIMARY INDEX ( NUPI1 ) INDEX ( BEGIN_DATE_DIM1_NUSI ) ORDER BY VALUES ( BEGIN_DATE_DIM1 ) INDEX ( END_DATE_DIM1_NUSI ) ORDER BY VALUES ( END_DATE_DIM1 ) INDEX ( NUSI1) ; CREATE TABLE dimension2 ( NUPI2 INTEGER, different_information_for_this_table ..., NUSI2 INTEGER, BEGIN_DATE_DIM2 DATE , END DATE_DIM2 DATE PRIMARY INDEX ( NUPI2 ) INDEX ( BEGIN_DATE_DIM2_NUSI ) ORDER BY VALUES ( BEGIN_DATE_DIM2 ) INDEX ( END_DATE_DIM2_NUSI ) ORDER BY VALUES ( END_DATE_DIM2 ) INDEX ( NUSI2) ; CREATE TABLE dimension3 ( NUPI3 INTEGER, different_information_for_this_table ..., STATUS CHAR(1), BEGIN_DATE_DIM3 DATE , END DATE_DIM3 DATE PRIMARY INDEX ( NUPI3 ) INDEX ( BEGIN_DATE_DIM3_NUSI ) ORDER BY VALUES ( BEGIN_DATE_DIM3 ) INDEX ( END_DATE_DIM3_NUSI ) ORDER BY VALUES ( END_DATE_DIM3 ); CREATE TABLE dimension4 ( NUPI4 INTEGER, different_information_for_this_table ..., BEGIN_DATE_DIM4 DATE , END DATE_DIM4 DATE PRIMARY INDEX ( NUPI4 ) INDEX ( BEGIN_DATE_DIM4_NUSI ) ORDER BY VALUES ( BEGIN_DATE_DIM4 ) INDEX ( END_DATE_DIM4_NUSI ) ORDER BY VALUES ( END_DATE_DIM4 );
this table is the fact table of our warehouse:
CREATE TABLE detail ( NUPI_detail INTEGER, different_information_for_this_table ..., DATE_OF_SALE DATE , NUSI5 INTEGER PRIMARY INDEX ( NUPI4 ) INDEX ( DATE_OF_SALE_NUSI ) ORDER BY VALUES ( DATE_OF_SALE ) INDEX ( NUSI5);
All query to our warehouse look like this format :
SELECT information.... FROM detail LEFT JOIN dimension1 ON detail.NUPI_detail = dimension1.NUPI1 AND detail.DATE_OF_SALE BETWEEN dimension1.BEGIN_DATE_DIM1 AND dimension1.END_DATE_DIM1 LEFT JOIN dimension2 ON dimension2.NUPI2 = dimension1.NUSI1 AND detail.DATE_OF_SALE BETWEEN dimension1.BEGIN_DATE_DIM2 AND dimension1.END_DATE_DIM2 LEFT JOIN dimension3 ON dimension3.NUPI3 = dimension2.NUSI2 AND detail.DATE_OF_SALE BETWEEN dimension1.BEGIN_DATE_DIM3 AND dimension1.END_DATE_DIM3 LEFT JOIN dimension4 ON dimension4.NUPI4 = detail.NUSI5 AND detail.DATE_OF_SALE BETWEEN dimension1.BEGIN_DATE_DIM4 AND dimension1.END_DATE_DIM4;
As you see, all join have date criteria from the detail table for the eligibility of dimension and get only one row from the dimension. All join are LEFT OUTER JOIN.
Create a big view with 20 joins to solve the puzzle give us a very bad performance when a query need only some join. So, we looked at the Soft Integrity Referential but unfortunately with the date criteria from the detail table join to each dimension the Soft Integrity doesn't work.
It's a big deal to install a BI product on this kind of model because the SQL generated is not always that easy.
We would like to known if it's a common way to create a physical table in Teradata to manage the date_of_sale and the eligibility of the dimension?
Any new idea, new concept of modeling will be very appreciate.
Hopefully the modeler community will appreciate this kind of discussion.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|