Re: Building fact tables in a data warehouse

Hi Thomas,

> This worked OK for our ETL developers when we were dealing with 10,000 rows
> in the development database.
> Now we're working with a much larger source set and we're talking
> 100,000,000 rows. It doesn't work that well.  
> Does anybody have experience or even a theoretical insight into a better way
> to do this?

One important thing in an ETL process is to know where is the bottleneck. From 
the birds view this can be 
a) in the data source
b) in the transformation
c) in the data target

Except for some cases such as inefficient selects in data source, indexes on 
targets or contention with other processes in prevailing number of cases the 
problem lies in the transformation. This is due to the row-by-row processing 
logic of the majority of ETL tools.
A typical answer is to use parallelism; in principle to start more ETL sessions 
working on partitioned sources and targets. This could indeed help, and with 
your row count will be probably required.
But there is other possibility to shift some transformation logic in the data 
source.

> To build our fact table, let's call it Z  (as I understand the process) we
> grab the primary key of the row in dimension A plus information which
> we can use to find the associated row in dimension B.  We go to dimension B,
> grab the primary key plus information which allows us to
> find the associated row in dimension C, and so on through 8 dimension
> tables.

In this case I'd try to join the fact records with the dimensions in the 
database (e.g. using parallel, partition-wise hash join) possible with 
pre-denormalizing the dimension hierarchy in a single table before the ETL 
process starts (to avoid the cascading joins).

One starting point in troubles with ETL process is to rewrite the process 
(possible simplified) in a CTAS statement. This gives you a quick orientation 
where the problem could be. If the CTAS doesn't work as expected, it is 
probably an a) or c) problem and should be solved in the database. Otherwise 
you get estimation what can be an expected target throughput of the ETL 
transformation.

Regards,

Jaromir D.B. Nemec

Other related posts: