Just like every other decision that needs to be made when looking at performance, we need to look at trade-offs to speed up the process of building data warehouses and data marts. One such trade-off is to look at the performance of large data sets. My hypothesis is that landing data on disk (and hence requiring more space) may be worth the added cost based on being able to build the data warehouses and data marts faster.

The Question

What is the best performing way to load a fact table that has 20 different dimensions? As I see it, there are 3 options:

1. Create the table from a single FROM … WHERE clause
   
   
2. Create a view that does the FROM … WHERE clause and join that to the input for the fact table.
   
   
3. Create the table with multiple staging steps joining
   

The Test

20 dimension tables (named dim_a through dim_t)

1 input table (load_detail)

1 fact table (name TheBigFact)

SQL Server 2008

The dimension tables and the single load table were populated with a script and then I used WhereScape RED to generate the stored procedure(s) that will build the fact. The data model is shown in Figure 1.
 

The dimensions were generated with a variable number of rows. The counts are in the table.


The input table is called load_bigfact and contains 8,176,989.

In order to get all rows from my input table, I am going to LEFT OUTER JOIN all of the dimensions and if they do not match, I will put a zero value in the corresponding KEY value in the table. Below is the generated code for building this table.

 INSERT INTO TheBigFact WITH ( TABLOCK )
 (
 )
 SELECT
 FROM
dim_h.a_code
dim_j.a_code
dim_o.a_code

The run started at 14:42:58 and completed at 14:58:27 – for a total of 15 minutes and 29 seconds.

Using a Gigantic View

So, although I would have never done this, I have heard of some folks creating views that bring together a lot of tables and then use that view in their INSERT statement. So I have done this as well. However, our test case may be an extreme case since there is nothing that you can join together in the view itself. I accept that, but wanted to show you the results. Basically, what I did was create a view that brought all of the dimensions together in a single view and joined that to the load table to produce the output to the fact table.

The run started at 15:59:50 and completed at 16:25:59 – for a total of 26 minutes and 9 seconds.

Next Month

Next month, I will perform the test with a different number of joins in separate steps (or stages) to produce the final fact table. The test will be to determine if there are a reasonable number of joins for 20 dimensions to run in a single step that, when added together, would run faster than a single statement. Then we will be able to draw some conclusions on whether the cost of landing more data is worth the time of processing – of course, assuming that there will be time savings, which is my hypothesis.

Recent articles by Chuck Kelley

• The Use of Surrogate Keys
• Dealing with Addresses in the Data Warehouse
• Different Flavors of Design – The Data Vault
• Different Flavors of Design for Business Intelligence Databases