CJ Travis

One of my passions is Data Warehousing – particularly on the ETL side of the house. Having the opportunity to meet Ralph Kimball while attending his class focused on Dimensional Data Modeling is really what sparked an interest me to improve the performance of our nightly ETL batch processes. While that was a few years ago and I’ve since moved on into the realm of full-time database administration, I try to find time every now and again to build “mini” warehouses just for the fun of reporting – which is the motivation behind this article.

Enough about that, on to the geeky stuff.

Assumptions

For the purpose of this demonstration, let’s assume the following:
DB Platform: Oracle
ETL Process: Manual, code-driven (no Informatica, SSIS, or other ETL GUI)
General PL/SQL knowledge
Natural keys on the source system never change
Assuming warehouse with thousands -> millions+ of records.

Establishing Conventions

A key responsibility of any ETL developer is not only having a firm technical grasp on the tools in front of you, but also a genuine understanding of the business processes (Accounts Receivable, Service, General Ledger, Sales) that drive the reporting needs. This is a very iterative process as you work with your business analysts and end users.

In order to have some consistency in the development process, the following standards and naming conventions are often helpful.

Facts: F_<business process> - F_SALES, F_SRV_REQ
Dimensions: D_<entity> - D_CUST, D_DATE, D_PERS, D_EMP, D_SRV_REQ

I prefix all surrogate keys on my dimension and fact tables with “W_” along with the suffix “_ID” – indicative of a “Warehouse ID”. W_CUST_ID, W_PERS_ID, W_SRV_REQ_ID, W_DT_ID are all good examples. Additionally, it is beneficial to know when each record was added/updated on my dimensions and add the columns W_ADD_DTTM and W_UPD_DTTM to reflect this information. As for the primary keys from the transactional system, I track them on my dimension using the prefix “S<#_column name>“. For example, I might have the following data sources feeding into the data warehouse:

Source       Source Identifier
------       --------------------
Siebel       S1
Excel        S2
SAP          S3
PeopleSoft   S4

This will allow me to easily identify what source(s) are behind a dimension.

The Fun Stuff
Consider the following – We want to build a person dimension in our warehouse – a table that I have constructed as follows:

SQL> desc edw.d_pers;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 W_PERS_ID					    NUMBER(38)
 S1_ROW_ID					    VARCHAR2(20)
 FNAME						    VARCHAR2(30)
 LNAME						    VARCHAR2(30)
 EMAIL_ADDR					    VARCHAR2(50)
 TITLE						    VARCHAR2(75)
 HASHKEY					    NUMBER(38)
 W_ADD_DTTM					    DATE
 W_UPD_DTTM					    DATE

The denormalized person dimension is designed based on those columns which are pre-defined as required for reporting purposes. Consider one of the potential source tables – which could have hundreds of columns – from the source transactional system – in this instance, from Siebel:

SQL> desc s_contact;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROW_ID 				   NOT NULL VARCHAR2(15 CHAR)
 CREATED				   NOT NULL DATE
 CREATED_BY				   NOT NULL VARCHAR2(15 CHAR)
 LAST_UPD				   NOT NULL DATE
 LAST_UPD_BY				   NOT NULL VARCHAR2(15 CHAR)
 DCKING_NUM					    NUMBER(22,7)
 MODIFICATION_NUM			   NOT NULL NUMBER(10)
 CONFLICT_ID				   NOT NULL VARCHAR2(15 CHAR)
 PAR_ROW_ID				   NOT NULL VARCHAR2(15 CHAR)
 ACTIVE_FLG				   NOT NULL CHAR(1 CHAR)
 BU_ID					   NOT NULL VARCHAR2(15 CHAR)
 COURT_PAY_FLG				   NOT NULL CHAR(1 CHAR)
 DISA_CLEANSE_FLG			   NOT NULL CHAR(1 CHAR)
 DISP_IMG_AUTH_FLG			   NOT NULL CHAR(1 CHAR)
 EMAIL_SR_UPD_FLG			   NOT NULL CHAR(1 CHAR)
 EMP_FLG				   NOT NULL CHAR(1 CHAR)
 FST_NAME				   NOT NULL VARCHAR2(50 CHAR)
 INVSTGTR_FLG				   NOT NULL CHAR(1 CHAR)
 LAST_NAME				   NOT NULL VARCHAR2(50 CHAR)
 ...

You get the idea.

Type 1 Slowly Changing Dimensions (SCD)

Arguably the easiest of the SCDs to maintain is that of Type 1. In a Type 1 scenario, existing data in the dimension is overwritten with the new information. Simply put: there is no history for tracking change over time. Returning to my person dimension (D_PERS), let’s say 2 weeks from now a record flows from the source transactional system with a different email address than what already exists for that contact in my dimension. Since we’re managing a Type 1 SCD, we simply overwrite the existing email address. Type 1′s are easy to deploy, but prevents the end user from answering the question of “Why did the data change?”. For more on the various types of Slowly Changing Dimensions, check out wikipedia.

Change Data Capture

At this point, I have created my person dimension named D_PERS. In order to perform change data capture on the incoming data, I’m going to make use of Oracle’s built-in ORA_HASH() function. Among many things, the ORA_HASH() function returns a hash value for a given string w/out sacrificing much in way of performance. This will be the value stored as D_PERS.HASHKEY. The idea is you pass all columns that require an update through ORA_HASH() and save the returned hash on the dimensional table for later use.

To illustrate the hashing functionality:

SQL> SELECT ORA_HASH('FOOBAR') HASH1, ORA_HASH('FOO' || 'BAR') HASH2, ORA_HASH('foobar') HASH3
  2  FROM DUAL;

     HASH1	HASH2	   HASH3
---------- ---------- ----------
3338140869 3338140869 2559319480

The following code utilizes the Oracle MERGE statement to process my Type 1 SCD.

 MERGE INTO   D_PERS D
           USING   STG.S1_S_CONTACT C
              ON   (D.S1_ROW_ID = C.ROW_ID)
      WHEN MATCHED
      THEN
         UPDATE SET
            D.FNAME = C.FST_NAME,
            D.LNAME = C.LAST_NAME,
            D.EMAIL_ADDR = C.EMAIL_ADDR,
            D.TITLE = C.JOB_TITLE,
            D.HASHKEY =
               ORA_HASH(   ROW_ID
                        || C.FST_NAME
                        || C.LAST_NAME
                        || C.EMAIL_ADDR
                        || C.JOB_TITLE),
            D.W_UPD_DTTM = SYSDATE
                 WHERE   D.HASHKEY <>
                            ORA_HASH(   ROW_ID
                                     || C.FST_NAME
                                     || C.LAST_NAME
                                     || C.EMAIL_ADDR
                                     || C.JOB_TITLE)
      WHEN NOT MATCHED
      THEN
         INSERT              (S1_ROW_ID,
                              FNAME,
                              LNAME,
                              EMAIL_ADDR,
                              TITLE,
                              HASHKEY,
                              W_ADD_DTTM,
                              W_UPD_DTTM)
             VALUES   (
                          C.ROW_ID,
                          C.FST_NAME,
                          C.LAST_NAME,
                          C.EMAIL_ADDR,
                          C.JOB_TITLE,
                          ORA_HASH(   ROW_ID
                                   || C.FST_NAME
                                   || C.LAST_NAME
                                   || C.EMAIL_ADDR
                                   || C.JOB_TITLE),
                          SYSDATE,
                          SYSDATE
                      );

      COMMIT;

Let’s put this code into Layman’s terms. The above code can be broken down into 3 easy pieces. The first three lines of the MERGE establish the join condition between the source table (S_CONTACT) and the target table (D_PERS) that we are MERGing into based on the natural key (S_CONTACT.ROW_ID = D_PERS.S1_ROW_ID).

 MERGE INTO   D_PERS D
           USING   STG.S1_S_CONTACT C
              ON   (D.S1_ROW_ID = C.ROW_ID)

The next section defines the first “WHEN” condition – which instructs Oracle what to perform on data that has been matched (based on S_CONTACT.ROW_ID = D_PERS.S1_ROW_ID). In this scenario if first name, last name, email address or job title has changed, we will UPDATE (i.e., overwrite) those columns in addition to the HASHKEY and W_UPD_DTTM (to indicate the record has been updated).

     WHEN MATCHED
     THEN
        UPDATE SET
           D.FNAME = C.FST_NAME,
           D.LNAME = C.LAST_NAME,
           D.EMAIL_ADDR = C.EMAIL_ADDR,
           D.TITLE = C.JOB_TITLE,
           D.HASHKEY =
              ORA_HASH(   ROW_ID
                       || C.FST_NAME
                       || C.LAST_NAME
                       || C.EMAIL_ADDR
                       || C.JOB_TITLE),
           D.W_UPD_DTTM = SYSDATE
                WHERE   D.HASHKEY <>
                           ORA_HASH(   ROW_ID
                                    || C.FST_NAME
                                    || C.LAST_NAME
                                    || C.EMAIL_ADDR
                                    || C.JOB_TITLE)

We could avoid using ORA_HASH and save on storage at the cost of performance by re-writing this SQL:

     WHEN MATCHED
     THEN
        UPDATE SET
           D.FNAME = C.FST_NAME,
           D.LNAME = C.LAST_NAME,
           D.EMAIL_ADDR = C.EMAIL_ADDR,
           D.TITLE = C.JOB_TITLE,
           D.W_UPD_DTTM = SYSDATE
               WHERE D.S1_ROW_ID = C.ROW_ID
                 AND (D.FNAME <> C.FST_NAME)
                  OR (D.LNAME <> C.LAST_NAME)
                  OR (D.EMAIL_ADDR <> C.EMAIL_ADDR)
                  OR (D.TITLE <> C.JOB_TITLE )

By writing the ORA_HASH() value to D_PERS.HASHKEY in advance, we can optimize our SQL by cutting out redundant OR statements for each column we want to monitor for change. The new hash value will differ from the stored value if any of the hashed column’s data has changed – thus triggering our update/insert. My milage with the ORA_HASH method has shown significant performance boosts with thousands – millions of updates. For one, you can index the HASHKEY column – where as indexing the character based columns (name, email, job) won’t provide the same benefit.

The third and final piece of code is the longest but also the most straightforward. It applies to all new records in the source system that do no already exist in the dimension.

      WHEN NOT MATCHED
      THEN
         INSERT              (S1_ROW_ID,
                              FNAME,
                              LNAME,
                              EMAIL_ADDR,
                              TITLE,
                              HASHKEY,
                              W_ADD_DTTM,
                              W_UPD_DTTM)
             VALUES   (
                          C.ROW_ID,
                          C.FST_NAME,
                          C.LAST_NAME,
                          C.EMAIL_ADDR,
                          C.JOB_TITLE,
                          ORA_HASH(   ROW_ID
                                   || C.FST_NAME
                                   || C.LAST_NAME
                                   || C.EMAIL_ADDR
                                   || C.JOB_TITLE),
                          SYSDATE,
                          SYSDATE
                      );

That’s all there is to it. If first, last, email, or title ever change, it will be captured and overwrite the existing record. I can see this method of ETL being advantageous to smaller IT shops with relative budgets who are looking to roll a data warehouse. More advanced “transformations” are certainly possible, with more advanced solutions. I’ll save those for another day.