How to load a Slowly Changing Dimension Type 2 with one SQL Merge statement in Oracle

This is based on Design Tip 107 ("Using the SQL MERGE Statement for Slowly Changing Dimension Processing") and does sth. similar in Oracle

You can access the solution at http://www.business-intelligence-quotient.com/?p=66

Uli,
Thanks for working through this and making it available to the group. I tried to do it on Oracle for the design tip, but my local copy of Oracle is 10g. Even with 11, the need to identify new versus changed rows in the extract process may be an issue more often than one would think. Many of the organizations we see do not have the mechanisms in place to support this kind of change data capture. In any case, it looks like it's time for me to upgrade.
--Warren

p.s. I'm going to ask the moderator to move this to the ETL topic since it's more about implementation than modeling.

Warren,

this should work on Oracle 10G also. It is also my experience that more often than not the information on changed or new records is not available from source. This is also the case for the project I work on currently. In the main source system they just have an activity_date that acts as a catch all for both updates and inserts...

Hello all. I have made an Oracle code generator for maintaining an SCD. It is written as a stored procedure in PL/SQL, it generates and executes the PL/SQL code on-the-fly, without creating any schema object (stored procedures etc). It uses bulk processing and takes following parameters as input:
1. source table/view name - actually a whole SELECT could be provided as well I think (although I didn't try).
2. target table name
3. natural key (comma-separated list of column names)
4. name of surrogate key column in target table
5. comma-separated list of columns treated as type 1 (overwritten on change)
6. comma-separated list of columns treated as type 2 (versioned attributes, new row added, previous closed)
7. Oracle sequence name used to assign surrogate keys
8. names of "date-from", "date-to" target table columns
9. name of "current-record-flag" target table column.
It returns number of new values (actually new), number of overwritten records and number of versioned records.
The procedure has 220+ lines of code, so I shall not paste it here, but I can send it to anyone interested.
Regards,
Jacek Adamowicz

Hi ! JacekA. i need the plsql code for for maintaining the SCD implementation.
it generates and executes the PL/SQL code on-the-fly, without creating any schema object (stored procedures etc). It uses bulk processing and takes following parameters as input:
1. source table/view name - actually a whole SELECT could be provided as well I think (although I didn't try).
2. target table name
3. natural key (comma-separated list of column names)
4. name of surrogate key column in target table
5. comma-separated list of columns treated as type 1 (overwritten on change)
6. comma-separated list of columns treated as type 2 (versioned attributes, new row added, previous closed)
7. Oracle sequence name used to assign surrogate keys
8. names of "date-from", "date-to" target table columns
9. name of "current-record-flag" target table column.
It returns number of new values (actually new), number of overwritten records and number of versioned records.


as described by you in this blog. i would be grateful if u respond.

Regards ,
NIRANJAN PANDA...
niranjanpanda08@gmail.com

Dear Jacek,

Can I have a copy of the PL/SQL for maintaining the SCD implementation.

Regards

Ishaq
ishaqbaig@yahoo.com
ishaqbaig@gmail.com

HI Jacek,

Please send the source code for maintaining the SCD implementation Through PL/SQL using Merge Statement. I did the SCD Type 2 In PL/SQL Couple of times but not through MERGE STATEMENT.
In case you aren't able to send, Can you explain the algorithm.

Regards
Shiril
Shiril123@gmail.com

JacekA,

Can I have a copy of the PL/SQL for maintaining the SCD implementation. I have written up code and would love to reuse anything which will be suitable from your package.

Mou Rakshit
mourakshit@yahoo.com

Hello JackA,
I have newly started working in this field. It will be of great help to me if u can mail me that PL/SQL code. Thanks a lot in advance

my email id aditya.arca@gmail.com

Thanks
Aditya

Hi Jacek,
Can I have a copy of the PL/SQL for maintaining the SCD implementation?

Regards
Piast

piast@yahoo.com

Dear All,

Sorry for not looking at the forum for a couple of weeks.
I managed to publish the code at scribd, so please download the source code and demo from these links:

http://www.scribd.com/doc/30687029/Update-SCD-sql
http://www.scribd.com/doc/30687026/Update-scd-test-case-sql

1) update_scd.sql - it created the generator procedure itself. It is named UPDATE_SCD and is created in your current schema. It requires access to DBMS_SQL and DBMS_UTILITY packages, but these are normally granted to PUBLIC. And off course you will need the right to create a procedure.

2) update_scd_test_case.sql - instead of documentation :-)
All the functionality and usage is presented there. It is an SQL*Plus script.

The procedure actually makes possible to maintain both type 1 and type 2 columns in the same dimension table - we simply define as input parameters the set of columns which should be versioned or overwritten.

The procedure does not use MERGE statement. The algorithm is actually quite simple, but thanks to PL/SQL arrays (INDEX BY tables), bulk fetch and bulk bind capabilities, it should be quite efficient.

The procedure update_scd creates anonymous PL/SQL block and executes it. You can easily modify the procedure so that the generated code is displayed in DBMS_OUTPUT instead or before executing in order to deeply understand how it works.
The generated block works as follows:

1. It creates a cursor comparing source dataset with dimension table. For every source table row which differs on at least one versioned or overwritten column it finds out also the operation to do: Insert new value (I), Insert new version (V), Overwrite record (U).
   
2. We bulk fetch the cursor (currently hardcoded 30000 records) into PL/SQL arrays (INDEX BY tables). We do it in loop off course.
   
3. We sort these tables by operation flag. Because we know there may be only 3 possible sort key values, the sorting may be done in linear time (not n*log(n) ). Sorting is based on bookkeeping of array indexes (bounds of I/V/U segments in array) and switching elements.
   
4. We do DML operations: INSERTs and UPDATEs using BULK BINDs, i.e. FORALL INSERT, FORALL UPDATE statements based on coherent ranges of array indexes (that's why we had to sort arrays).

Thanks Jacek.
Best regards,
Piast

Hi Jacek,
Can I also have a copy of the PL/SQL for maintaining the SCD implementation?
My email is etiennelsmith@yahoo.com

Regards

Hi JacekA

Thank you very much, you scripts from scribd were very useful for me.

Regards
Naga