Year-to-Date Query Improvement Through Transformation Tables

A quick comparison here between two methods of deriving year-to-date metric values from a data warehouse fact table.

Suppose we have a simple fact table of the following form:

SQL> desc fct_sales
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------- 

PRODUCT_CD                                NOT NULL NUMBER
 LOCATION_CD                               NOT NULL NUMBER
 DATE_OF_SALE                              NOT NULL DATE
 SALES_AMT                                 NOT NULL NUMBER

We have a business requirement to report sales by year-to-date. This is a resource intensive query because we have to report every day’s sales against every subsequent day in the calendar (or fiscal) year.

Given a dimension table “dim_sales_dates” of the structure :

SQL> desc dim_sales_dates
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------- 

DATE_OF_SALE                              NOT NULL DATE
 MTH_OF_SALE                               NOT NULL VARCHAR2(8)
 QTR_OF_SALE                               NOT NULL VARCHAR2(7)
 YR_OF_SALE                                NOT NULL VARCHAR2(4)

Method 1 relies on modifying the join between the date dimension table and the fact table to the following form:

select   d.mth_of_sale,
         sum(sales_amt)
from     fct_sales f,
         dim_sales_dates d
where    f.date_of_sale between trunc(d.date_of_sale,'yyyy')
                            and d.date_of_sale and
         d.mth_of_sale in ('2005 M05','2005 M06')
group by d.mth_of_sale
/

The potential problem with this is that the high number of joins required between the two tables and the relatively small data volume for the dimension table suggests that a hash join would be the optimum method. However as is documented in the Database Performance Tuning Guide the hash join can only be used for equijoins, for reasons that are obvious if you know how hashes work.

An alternative is to materialize the result of the BETWEEN join in a transformation table that relates DATE_OF_SALE to those dates prior to it in the year. Here is a sample structure:

SQL> desc trn_sales_dates_ytd
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------- 

DATE_IN_YTD                               NOT NULL DATE
 DATE_OF_SALE                              NOT NULL DATE
 MTH_OF_SALE                               NOT NULL VARCHAR2(8)
 QTR_OF_SALE                               NOT NULL VARCHAR2(7)
 YR_OF_SALE                                NOT NULL VARCHAR2(4)

The column DATE_IN_YTD contains all values of date from 1st January for the year of DATE_OF_SALE up to and including DATE_OF_SALE — the primary key here is of course (DATE_OF_SALE, DATE_IN_YTD).

We can now rewrite the YTD query as follows:

select   t.mth_of_sale,
         sum(sales_amt),
         count(*)
from     fct_sales           f,
         trn_sales_dates_ytd t
where    f.date_of_sale = t.date_in_ytd and
         t.mth_of_sale in ('2005 M05','2005 M06')
group by t.mth_of_sale
/

The hash join is now permitted and theoretically the query performance can improve.

Here is a complete test script, and here is the result of an execution on Oracle 10.2.0.2.0.

The demonstration shows the following:

1. Use of a nested loop in method 1 anda hash join in method 2
2. A drop in the consistent gets from 109,971 to 2,550
3. An improvement in wall clock time from 18.51 seconds to 4.98 seconds.

Conclusion

Implementing transformation tables to allow the use of equijoins in data warehouse queries can provide performance benefits that make them well-worth considering.