Insert Unspecified Record in Dimension - ODI KM Customization

A typical requirement in any data warehousing project is to populate an “unspecified” row for each dimension. This will enable the capability of always making inner joins between the facts and dimensions and thus improve performance in all our OLAP queries.

Step 1: Duplicate IKM Oracle Incremental Update (MERGE)

Step 2: Rename to Custom IKM Oracle Incremental Update (MERGE)

Step 3: Navigate Options and create new Option UNSPECIFIED_RECORD and enter other parameters as shown below.

Step 4: Navigate to Tasks to add new Task and below Target Command 

declare

v_count number:=0;

begin

Execute immediate 'ALTER SESSION SET NLS_DATE_FORMAT = ''YYYYMMDD''';

Execute immediate 'ALTER SESSION SET NLS_TIMESTAMP_FORMAT = ''YYYYMMDD''';

if ('<%=odiRef.getTable("L", "TARG_NAME", "A")%>' like '%_DIM%')  and '<%=odiRef.getOption("UNSPECIFIED_RECORD")%>'='1'

THEN

SELECT COUNT(*) INTO V_COUNT FROM <%=odiRef.getTable("L", "TARG_NAME", "A")%>;

if V_COUNT =0 THEN

insert into <%=odiRef.getTable("L", "TARG_NAME", "A")%> ( <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "", "")%>)

select

<%=odiRef.getColList("\n", "\tCASE WHEN '[DEST_DT]' LIKE 'VARCHAR2%' AND TO_NUMBER ('[LONGC]')> TO_NUMBER('11') THEN 'Unspecified'

WHEN '[DEST_DT]' LIKE 'VARCHAR2%' THEN '0'

WHEN '[DEST_DT]' LIKE 'DATE%' THEN '19000101'

WHEN '[DEST_DT]' LIKE 'TIMESTAMP%' THEN '19000101'

WHEN '[DEST_DT]' LIKE 'NUMBER%' THEN '0'

ELSE NULL END [COL_NAME]",",\n","\n","")%>

from dual;

commit;

END IF;

END IF;

END;

Step 5:  Save and close the window , This step should be placed before Merge Rows step.

 

Step 6: Now we are good to use this Custom IKM in any dimension (table name with suffix as _DIM) as per code . Make sure we select Insert Unspecified Record option as True in KM option.