ODI - Slowly Changing Dimensions in 0 to 60

Want to see understand how to setup your datastores for supporting dimension loading concepts such as type 2 slowly changing dimensions? See the viewlet here.  The viewlet provides a very quick look at setting up a datastore for supporting slowly changing dimension data loading using ODI 11g. It uses the IKM Slowly Changing Dimension and shows dimension members being versioned when for example a marital status change happens.

Comments:

I'm curious why there is an overlap between the first row and the second row with regards to the C_EFF and C_EXP columns. The first row has a C_EXP of "2013-02-26 14:24:15.0" and the second row has a C_EFF with the same value. If the question was asked of the data, what was the value of C_MSTAT at "2013-02-26 14:24:15.0" (using a between without a subquery or analytics etc.), then you get two rows back. I'm assuming that if the C_MSTAT changes at a later point in time to 'Divorced', then the CURRENT_IND will not help in the query as both Single and Married with have a CURRENT_IND of '0'.

Posted by guest on February 27, 2013 at 05:23 PM PST #

I think this was an oversight from me! In my interface I had SYSDATE as both the expiration date AND the effective date. What I can do is have;

effective date = current_timestamp
expiration date = current_timestamp - interval '1' seconds

or whatever units (other than seconds) you wish for. Then you will have records with effective/expiration dates that you can use SQL between clauses.

Hopefully that answers your question.

Cheers
David

Posted by David on February 28, 2013 at 10:05 AM PST #

Thanks for that - it clears it up fine. Obviously this is critical as there is no point to store SCD2 data without being able to access the historical data and answer these type of 'at this time' questions. It would obviously also get more complex when the dates and times of effective/expiry need to come from a central store due to captures of data occurring overnight and loads not necessarily being in real time.

Cheers
Glen

Posted by glenm on February 28, 2013 at 02:49 PM PST #

Hi Glen

Yeh, agreed. You can tell I didn't query my resultant data in context of a historical report otherwise I would have spotted that one.

Cheers
David

Posted by David on February 28, 2013 at 02:54 PM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
5
6
7
8
9
10
12
13
14
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today