Archives of the TeradataForum

Message Posted: Thu, 18 Aug 2005 @ 16:03:08 GMT

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

<-- Anonymously Posted: Thursday, August 18, 2005 11:44 -->

Hi all,

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.

Regard's

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