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.
