X

The Oracle BI Application Blogs provides the latest and tips and tricks on Oracle BI Applications Product (OBIA)

ETL Design for sharing the sequence ID

Introduction

This blog describes an ETL design to share the same sequence ID among several tables. Party Dimension is an exceptional case where Records from different streams merge into one table. Organization, person and group party types are loaded into separate tables W_PARTY_ORG_D, W_PARTY_PER_D & W_PARTY_GROUP_D and later get merged into a master table W_PARTY_D.


These dimensions are Slowly Changing dimension out-of-the-box in the BI Application. They also carry a slowly changing type1 WID named as SCD1_WID. It holds the  same value as for the new record also in case of SCD type 2 change. For example, if Organization Name is changed from ‘ABC Software’ to ‘ABCD Software’, the  current record would still have the same value for SCD1_WID.


This WID is populated with a sequence generated numeric value. Knowledge modules (KM) use a separate DB sequence for each target table while loading data into W_PARTY_ORG_D, W_PARTY_PER_D & W_PARTY_GROUP_D tables resulting in different sequence numbers for SCD1_WID column in master table W_PARTY_D. The following steps describe an approach to share the same sequence ID.

Step 1:

KM driven option ‘OBI_SCD1_WID’ should be disabled to refrain it from creating separate DB sequences. Set the default value of OBI_SCD1_WID IKM option to ‘false’ as shown in the screen shot below.

Step 2:

Create a mapping to populate the helper table W_PARTY_PER_T1_D table. The source for this mapping should be the corresponding staging table W_PARTY_PER_DS. The mapping expression for SCD1_WID column should read from the sequence created from the previous load stream (in this case W_PARTY_ORG_D load). Set the mapping expression as NEXTVAL (W_PARTY_ORG_S1W) and uncheck ‘Update’ checkbox.

Step 3:

In the flow tab, DETECTION_STRATEGY IKM option should be set to ‘NONE’.

Step 4:

Configure LP components to execute the scenarios loading W_PARTY_PER_T1_D, W_PARTY_PER_D & W_PARTY_D in serial mode in the order as follows.

Join the discussion

Comments ( 6 )
  • guest Wednesday, December 7, 2016

    Hi Archana Dixit,

    Trust you are doing well.

    I am working on OBIA 11.1.1.9 and I am struggling to understand this SCD1_WID process.

    1. If I understand well, T1_D tables are temporary tables ? only used for the ETL process

    2. So, now by default OBIA is working AS IS (Last record)

    3. So, what's happen when I set SCD2 in ODI tables ?

    4. From OBIEE, if I want to leverage both AS IS and AS WAS (eff_from_dt and eff_to_dt), should I change the whole RPD ?

    5. Why there are no T1_D tables for each dimension ? Does it meant that those tables are still working in AS WAS mode ?

    Thank you for pointing me to more documentation,

    KR

    Avraham Aidan


  • archana dixit Thursday, December 8, 2016

    Yes, T1_D tables are used by ETL only. This table is available for dimensions that can be configured for SCD2. For example: if W_PARTY_ORG_D can be set up for SCD2, there will be a corresponding W_PARTY_ORG_T1_D table.

    You may refer to the following blog for more info on how scd2 works on BIApps.

    https://blogs.oracle.com/biapps/entry/how_does_type2_flg_work

    I don't think you need to make any changes to the RPD in order to enable SCD2 for a specific dimension (provided SCD2 type is allowed for that dim in BIApps). You may just have to configure the TYPE2_FLG parameter in BIApps Configuration Manager accordingly.


  • Avraham Aidan Thursday, December 8, 2016

    Thank you for answering.

    Now:

    Does it mean that SCD1_WID with CURRENT_FLG columns in the dimensions tables are used now to join to the fact tables instead of ROW_WID column ?

    Why do I see SCD1_WID columns as well in some fact tables (W_GL_REVN_F,..)

    My customer wants to have SCD2 in each dimension ! is it doable in ODI setup ? and then create manually the T1_D tables and the rest of the process ?


  • archana dixit Thursday, December 8, 2016

    My comments:

    1. SCD1_WID is typically used to facilitate join between SCD2 and SCD1 dimensions.

    2. Can not comment on SCD1_WID in fact tables. You may want to refer to BIApps documentation for that.

    3. Yes, it is doable to have SCD2 for each dimension. However, this warrants a lot of customization in ODI data model, ETL and possibly in RPD.


  • guest Wednesday, December 14, 2016

    Hi Archana,

    You said in Step 2:

    Create a mapping to populate the helper table W_PARTY_PER_T1_D table ?

    Searching in ODI on %T1_D I don't find any interface ? are they no OOTB interface for the OOTB TYPE 2 dimensions ? like Employee?

    Where is the documentation about it?

    Thanks,

    Avraham


  • archana dixit Wednesday, December 14, 2016

    The mapping folder name is SIL_PartyPersonDimension_GenerateSCD1Key. Pls check BIApps documentation guide for more details. There is no separate document for this.


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.