SCD Type 1 in ODI 11g

I have just started developing in ODI 11g and I really got frustrated implementing a simple SCD Type 1. I am using the samplesales schema that comes along with OBIEE and made some modifications. For example, I added a surrogate key named OFFICE_ID to the Offices dimension. I exported the table data to a CSV file and am trying to import that file into the SAMP_OFFICES_D dimension.

For Type 1, you can use the Incremental Update IKM and use database sequences for the surrogate key. For some reason, it wasn’t working. Duplicate rows were getting added even when no changes were made to the data (It should ideally do nothing, even if it was a SCD Type 2). The solution is pretty standard – you only need to know what you’re doing and setting the right options. Read on.

Step 1 – Create the database sequence object

Step 2 – Reverse-engineer your models

Step 3 – Configure the target data store

In the Definition tab of your data store, select Dimension as the OLAP Type. This is important because if you do not mention this as a Dimension and then not define an Update key (which you don’t to implement SCD), then we’ll get the following error.

Figure 1: Error when data store is not set as a Dimension and Update key is set to Undefined

We use the Update Key to define how rows are compared to see if they are the same and since in our case that will be the surrogate key, new rows will be added every time. To avoid that, we set the Update key to <Undefined>.

Side question – How do we then control the specific columns to match on? This can be achieved by setting the Update key to <Undefined> and setting the Key property on the specific columns of the target data store.

Step 4 – Design your interface

Following points should be kept in mind when designing the interface –

1. Select the surrogate key column of the target data store
   
   1. Select Execute On as Target
      
   2. Make sure the Key checkbox is not selected (it gets automatically unchecked if Update key is set to <Undefined>)
      
   3. Clear the Check Not Null checkbox (as this is an auto-generated sequence)
      
   4. Clear the Update checkbox (as we will (/should) never update the surrogate key column)
      
   5. In the implementation box, click on the pencil to edit the expression and then select the sequence that you created. The sequence can either be a project specific sequence or a global sequence (created in Global objects)
      
2. Select the natural key or unique key column of the target data store. In my case, it is going to be OFFICE_KEY (the unique key to identify an Office)
   
   1. Check the Key checkbox; this is the key that will be used by ODI to make comparisons during inserts/updates of the IKM
      
   2. Clear the Update checkbox (because in my case, the OFFICE_KEY will never be updated)
      
   3. If more than one column identify a row as unique then mark each one of them as Key columns in a similar fashion
      

That’s it! Execute the interface and your SCD Type 1 interface should work. I’ll talk about SCD Type 2 tomorrow.