How to handle Early Arriving Facts and Late Dimension in ODI

Hi All, I read an article in the Oracle Blogs about implementing the early arriving facts or late dimensions and this is a re creation of that in my local server.

This is the idea.

In certain cases the fact record will be available but there will not any details of the respective dimensions (Dimension ROW_WID will be null).

In this case we will use a database function to create a row_wid for the dimension and use that to populate the dimension row_wid.

Once the customer details are available in the source application the dimension detailed are filled up via type 2 or type 1 load.

Alright so here is how it is done.



Step 1 : Create a default value table :

CREATE TABLE "W_DEFAULT_VALUES"
  (
    "UNKNOWN_CD"         VARCHAR2(20 BYTE),
    "UNKNOWN_DESC"       VARCHAR2(50 BYTE),
    "TYPE_2_CURRENT_FLG" NUMBER,
    "TYPE_2_END_DT" DATE
  );

REM INSERTING into W_DEFAULT_VALUES
Insert into W_DEFAULT_VALUES (UNKNOWN_CD,UNKNOWN_DESC,TYPE_2_CURRENT_FLG,TYPE_2_END_DT) values ('NA','Not Applicable',1,to_date('01-01-2400 00:00:00','MM-DD-YYYY HH24:MI:SS'));

Step 2: Create a db function 

To create the Warehouse for Surrogate key for the missing dimension by taking in the Natural key of the dimension. This function needs to be triggered only when a late arriving dimension is detected.

PS: I have used the function from the Oracle blog that was available online.
-------------------------------------------------------------------------------
create or replace
PACKAGE LATE_DIMS
AS

FUNCTION D_CUSTOMER_DIM( IN_CUSTOMER_ID IN NUMBER )
  RETURN NUMBER;
  
FUNCTION D_PRODUCT_DIM( IN_PRODUCT_ID IN VARCHAR2 )
  RETURN NUMBER;
  
END LATE_DIMS;
/
-------------------------------------------------------------------------------
create or replace
PACKAGE BODY LATE_DIMS AS
FUNCTION D_CUSTOMER_DIM(IN_CUSTOMER_ID IN NUMBER )RETURN NUMBER
AS
  C_WAREHOUSE_ID NUMBER;
BEGIN
  C_WAREHOUSE_ID := SEQ_W_CUSTOMER_D.nextval;
  INSERT INTO W_CUSTOMER_D
    (
      ROW_WID,
      CUST_ID,
      CUST_TYPE,
      CUST_NAME,
      ADDRESS,
      CITY,
      STATE,
      START_DT,
      END_DT,
      CURRENT_FLAG,
      CREATED_DT,
      UPDATED_DT
    )
  SELECT C_WAREHOUSE_ID,
    IN_CUSTOMER_ID,
    UNKNOWN_DESC,
    UNKNOWN_DESC,
    UNKNOWN_DESC,
    UNKNOWN_DESC,
    UNKNOWN_CD,
    SYSDATE,
    TYPE_2_END_DT,
    TYPE_2_CURRENT_FLG,
    SYSDATE,
    SYSDATE
  FROM W_DEFAULT_VALUES;
  RETURN C_WAREHOUSE_ID;
EXCEPTION
WHEN OTHERS THEN
  IF SQLCODE='-00001' THEN
    DBMS_OUTPUT.PUT_LINE('-00001 -WARNING- '||SQLCODE||SQLERRM);
    SELECT ROW_WID
    INTO C_WAREHOUSE_ID
    FROM W_CUSTOMER_D
    WHERE CUST_ID   = IN_CUSTOMER_ID
    AND CURRENT_FLAG='1';
  ELSE
    RAISE_APPLICATION_ERROR(-20001,'-ERROR- '||SQLCODE||SQLERRM);
  END IF;
  RETURN C_WAREHOUSE_ID;
END D_CUSTOMER_DIM;


FUNCTION D_PRODUCT_DIM(
    IN_PRODUCT_ID IN VARCHAR2 )
  RETURN NUMBER
AS
  C_WAREHOUSE_ID NUMBER;
BEGIN
  C_WAREHOUSE_ID := SEQ_W_PRODUCT_D.nextval;
  INSERT
  INTO W_PRODUCT_D
    (
      ROW_WID,
      PROD_ID,
      PROD_LINE,
      PROD_DESC,
      CREATED_DT,
      UPDATED_DT
    )
  SELECT C_WAREHOUSE_ID,
    IN_PRODUCT_ID,
    UNKNOWN_CD,
    UNKNOWN_DESC,
    SYSDATE,
    SYSDATE
  FROM W_DEFAULT_VALUES;
  RETURN C_WAREHOUSE_ID;
EXCEPTION
WHEN OTHERS THEN
  IF SQLCODE='-00001' THEN
    DBMS_OUTPUT.PUT_LINE('-00001 -WARNING- '||SQLCODE||SQLERRM);
    SELECT ROW_WID
    INTO C_WAREHOUSE_ID
    FROM W_PRODUCT_D
    WHERE PROD_ID = IN_PRODUCT_ID;
  ELSE
    RAISE_APPLICATION_ERROR(-20001,'-ERROR- '||SQLCODE||SQLERRM);
  END IF;
  RETURN C_WAREHOUSE_ID;
END D_PRODUCT_DIM;
END LATE_DIMS;
/

Step 3: Create SEQUENCE ODI and DB

CREATE SEQUENCE "SEQ_W_CUSTOMER_D" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 106 NOCACHE NOORDER NOCYCLE ;

CREATE SEQUENCE "SEQ_W_PRODUCT_D" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 68 NOCACHE NOORDER NOCYCLE ;

CREATE SEQUENCE "SEQ_W_STATUS_D" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 38 NOCACHE NOORDER NOCYCLE ;

Sequences created :

Step 4: Create Dimension Interfaces: 

In this we are using customer and product dimensions as example and interfaces are created to populate the dimensions and the fact table. 

I am not mentioning the steps to create interfaces in SCD TYPE 2 here.

Dimension Interfaces

• Warehouse.W_CUSTOMER_D (Type 2)
• Warehouse.W_PRODUCT_D (Type 1)

Fact Interface

• Warehouse.W_ORDERS_F

Interfaces Created : Customer - SCD TYPE 2 and product SCD TYPE 1

Step 6 : Create user functions to invoke the database function earlier created

CASE

     WHEN $(SurrogateKey) IS NULL THEN LATE_DIMS.D_PRODUCT_DIM($(NaturalKey))

     ELSE $(SurrogateKey)

END

Create the similar function for LATE_CUSTOMER

Step 6 : Create Fact Interface

Step 7 : Customize IKM incremental Update for the custom functions

This modification is needed because in the incremental update there is a step 'Sub- select Inline View' which will fail because our package have DML statements that are not allowed inside a select statement.

We do not what to use this step, hence we have to modify the IKM Oracle Incremental KM

Create a duplicate of the incremental update KM as shown below

 To properly manage this restriction, we are going to create a new KM option in the IKM Oracle Incremental Update that will allow us to only execute step  “sub-select inline view”  if the KM option is set to “True”.  By default, the option will be set to “True”, but for the Orders Fact interface, the option will be set to “false”.

Create a new option in the KM called “INLINE_VIEW” of type “Check Box” with a default value of “True” as illustrated

Save your new KM Option.  Select the “Details” tab of the KM, and open the “Sub-Select Inline view” step as shown in Figure 

Unselect the “Always Execute” option and only check the “INLINE_VIEW” option.  This mean that the step will only be executed if INLINE _VIEW option is set to “True” (which by default, this is how we implemented the option).  The idea is that the behavior of the KM will not change.  

But for our Orders fact interface we are going to set this option to “false”.

Step 8 : Update the fact interface with selecting the new IKM and changing the INLINE_vIEW to FALSE and save the interface

Step 9 : We are now complete with the setup and now to test if out late arriving dimensions is going to work.

Insert a data in the source table and leave the dimension table as is.

Run the fact interface and see that the new record is available in the FACT TABLE and the customer wid is populated with the value returned by our PL SQL function. 

Check the customer dimension table 

Check the source table

Now our early fact and late dimension records are both available in the fact table.

Reference : Oracle Blog 

Thanks,
Sandeep

SHARE SHARE SHARE

About test