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

View previous topic View next topic Go down

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

Post  ubethke on Tue Feb 03, 2009 11:26 am

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

ubethke

Posts : 28
Join date : 2009-02-03

View user profile http://www.business-intelligence-quotient.com

Back to top Go down

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

Post  warrent on Tue Feb 03, 2009 1:54 pm

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.
avatar
warrent

Posts : 41
Join date : 2008-08-18

View user profile

Back to top Go down

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

Post  ubethke on Tue Feb 03, 2009 2:02 pm

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...

ubethke

Posts : 28
Join date : 2009-02-03

View user profile http://www.business-intelligence-quotient.com

Back to top Go down

Another way for maintaining SCD

Post  JacekA on Thu Oct 22, 2009 8:56 am

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

JacekA

Posts : 3
Join date : 2009-10-22

View user profile

Back to top Go down

requesting for Oracle code generator for maintaining SCD-2 and 3

Post  niranjanpanda08 on Fri Jan 15, 2010 1:52 am

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


Last edited by niranjanpanda08 on Sat Jan 16, 2010 3:51 am; edited 1 time in total (Reason for editing : kindly forward to my mail account)

niranjanpanda08

Posts : 1
Join date : 2010-01-15

View user profile

Back to top Go down

Request for PL/SQL code for SCD

Post  ishaqbaig on Sat Mar 27, 2010 9:13 am

Dear Jacek,

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

Regards

Ishaq
ishaqbaig@yahoo.com
ishaqbaig@gmail.com

ishaqbaig

Posts : 1
Join date : 2010-03-27

View user profile

Back to top Go down

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

Post  Shiril.Dubey on Thu Apr 01, 2010 3:23 am

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

Shiril.Dubey

Posts : 1
Join date : 2010-04-01

View user profile

Back to top Go down

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

Post  mourakshit on Mon Apr 05, 2010 10:26 am

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

mourakshit

Posts : 1
Join date : 2010-04-03

View user profile

Back to top Go down

Request for the PL/SQL code

Post  Aditya on Wed Apr 07, 2010 3:07 am

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

Aditya

Posts : 1
Join date : 2010-04-07

View user profile

Back to top Go down

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

Post  Piast on Wed Apr 07, 2010 4:19 pm

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

Regards
Piast

piast@yahoo.com

Piast

Posts : 2
Join date : 2010-04-07

View user profile

Back to top Go down

The code and demo

Post  JacekA on Thu Apr 29, 2010 7:10 am

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).

JacekA

Posts : 3
Join date : 2009-10-22

View user profile

Back to top Go down

Thanks a lot

Post  Piast on Mon May 03, 2010 2:05 pm

Thanks Jacek.
Best regards,
Piast

Piast

Posts : 2
Join date : 2010-04-07

View user profile

Back to top Go down

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

Post  etiennes on Mon Jun 28, 2010 6:53 am

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

Regards

etiennes

Posts : 1
Join date : 2010-06-28

View user profile

Back to top Go down

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

Post  nagainbox on Wed Jun 30, 2010 7:41 pm

Hi JacekA

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

Regards
Naga

nagainbox

Posts : 1
Join date : 2010-06-30

View user profile

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum