Monthly Archives: October 2020

ODI

Reconcile the data in ODI between source and target

Leave a comment

So again am back with new challenging task and its solution. Now a days, we have to be very specific with Audit and Reconciliation in matters of data to ensure, no integrity loss.

So to start with, my job has a requirement to reconcile the data counts between Source legacy system and ODI target. Yes specifically am talking about ODI here, as that’s my job stream.

So continuing further, a report was required in lineage form (horizontal) to reconcile. Its easy to create a view or table and enter data row_wise(vertical) to get counts and then apply Pivot and create report. Instead i did it in 1 step by creating the report horizontally.

Solution: I simply used SELECT from <table> using inner join (1=1) for every column and then union all for next rows. See Query sample below:

(select ODI_INSERT_DT,SRC_TABLE_NAME, COUNT1, ODI_SRC_TABLE_NAME, COUNT2,ODI_TGT_TABLE_NAME, COUNT3,ODI_ERROR_TABLE,COUNT4 froM (SELECT SYSDATE AS ODI_INSERT_DT, ‘LEGACY_M’ as SRC_TABLE_NAME ,COUNT() as COUNT1,1 as commonId FROM SCHEMA.TABLENAME ) A inner join (SELECT ‘ODI_STG’ as ODI_SRC_TABLE_NAME,COUNT() as COUNT2,1 as commonId FROM SCHEMA.TABLENAME) B
on A.commonId = B.commonId
inner join
(SELECT ‘ODI_TGT’ as ODI_TGT_TABLE_NAME,COUNT() as COUNT3,1 as commonId FROM SCHEMA.TABLENAME) c on B.commonId = C.commonId inner join (SELECT ‘ODI_ERROR’ as ODI_ERROR_TABLE,COUNT() as COUNT4,1 as commonId FROM SCHEMA.TABLENAME) d
on c.commonId = d.commonId)
UNION ALL
(select ODI_INSERT_DT,SRC_TABLE_NAME, COUNT1, ODI_SRC_TABLE_NAME, COUNT2,ODI_TGT_TABLE_NAME, COUNT3,ODI_ERROR_TABLE,COUNT4 from
(SELECT SYSDATE AS ODI_INSERT_DT, ‘LEGACY_D’ as SRC_TABLE_NAME ,COUNT() as COUNT1,1 as commonId FROM SCHEMA.TABLENAME) A inner join (SELECT ‘ODI_STG1’ as ODI_SRC_TABLE_NAME,COUNT() as COUNT2,1 as commonId FROM SCHEMA.TABLENAME) B
on A.commonId = B.commonId
inner join
(SELECT ‘ODI_TGT2’ as ODI_TGT_TABLE_NAME,COUNT() as COUNT3,1 as commonId FROM SCHEMA.TABLENAME) c on B.commonId = C.commonId inner join (SELECT ‘ODI_ERROR’ as ODI_ERROR_TABLE,COUNT() as COUNT4,1 as commonId FROM SCHEMA.TABLENAME) d on c.commonId = d.commonId)

Output comes like this:

Do try and let me know you views in comment. Enjoy!!