RE: Building fact tables in a data warehouse

Almost every column in your dimension and fact table should have a single
column bitmap index on which will allow for star transformation explain plan
which combines all the bitmap indexes and finds the rows in the fact table
very quickly. 

 

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Thomas Day
Sent: Friday, December 21, 2007 10:17 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Building fact tables in a data warehouse

 

I'm just wondering if anyone has any insights on how to speed this up.

 

Our fact tables consist of the primary keys of the involved dimension tables
plus numeric count columns.

 

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.

 

This gives us one row in the fact table.

 

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.  Basically, each row in the
fact table requires full a index scan and a rowid fetch from each of the
dimension tables. 

 

Does anybody have experience or even a theoretical insight into a better way
to do this?

 

Thanks

Other related posts: