Interesting Question - Compare 2 tables

[mirogak]

Hi everyone,

I am using Informatica 7.1.5 with Oracle 8g database.

Ok here is my situation

Our current ETL tool is DataStage and we bought Informatica and we are in the middle of converting all of our existing ETL jobs.

So what I do is, redesign an existing ETL job in Informatica to do EXACTLY the same thing as the original DataStage ETL job. Thus, as part of my unit testing, I have to make sure that not only the row count in the target table is the same but that every row has exactly the same data in each column, as with the original table.

In this regard I have to compare two tables that in theory should be identical.

Imagine this:

DataStage ETL job populates target Table A
Informatica mapping/workflow populates target Table B

Table A and Table B SHOULD be identical, if I have done the redesign in Informatica correctly.


If I do this testing in SQL, within the database and check for each column, it is very time consuming.

I was hoping there would be a cool way to do this in PowerCenter Designer. Perhaps something like

- create a new mapping
- make Table A your Source table
- make Table B a lookup transformation
- drag all the ports from Table A to the lookup transformation, and then check for equality
- then somehow use a router transformation that gives me all the PASS and FAIL. The failures would be where for a single row, the data in TableA.colX did not match TableB.colX. However, I want the mapping to tell me for which row, which columns (ports) did not match.

Is this possible?

Thanks,
mirogak

 

[blom0344]

Let's assume that you do a very good job and expect very few differences.
One way to perform a simple pre-check is the following:

select * from table A
minus
select * from table B

Identical tables would yield an empty query result.
There is a pretty good chance that you may not have to design the type of mappings that you looking for.

In any case you can limit the effort by performing the pre-check (which is a simple exercise anyway)



Ties Blom