3 Replies Latest reply on Apr 16, 2019 9:39 AM by Nico Heinze

    data reocncillation in informatica

    shashank mishra Seasoned Veteran

      source had

      id name

      1  sha mis

      2  rah mau

      3  abc def


      taget has


      id  name

      1   sha

      2   rah

      3   abc


      how to do  data rocnillation that in target i have got all the records from source?

        • 1. Re: data reocncillation in informatica
          Nico Heinze Guru

          Join the source and the target by ID (using a Joiner with Full Outer Join).

          Forward the output records of the JNR to an EXP.

          Within the EXP, check these details:

          - ID from source is not NULL (meaning record in target does originate from source),

          - ID from target is not NULL (meaning all source records have been written to the target),

          - SubStr( NAME_FROM_SOURCE, 1, Length( NAME_FROM_TARGET))

                  = NAME_FROM_TARGET




          • 2. Re: data reocncillation in informatica
            shashank mishra Seasoned Veteran

            its really helpful .. like you have elaborated things for  getting name correct.. can we also add record count and other things to verify data uploaded to target is correct or not?

            • 3. Re: data reocncillation in informatica
              Nico Heinze Guru

              Sure you can. Just count the records that you've written to the target using an Aggregator.

              However, this has one disadvantage (very rarely, but it can happen): there have been cases (I know of two such cases) where a DB driver has indicated that records had been written, but the database did not contain those records. So it MAY be that the counter has been set correctly, but the database indicates differently.


              From this point of view it doesn't make too much sense to rely on such "self-made" counters. Count the records in the target database after the session has ended (e.g. using a second mapping), then compare this number against the number of records in the source.


              Having written that, this is an extremely wide topic which we cannot discuss here in all aspects. This is something where you need professional advice and help if you really want to go a "safe" route (as mentioned earlier, no route is 100% safe, but that's part of this complex story).


              Having written that, you will have to decide for yourself how much "safety" you want to achieve. You can get many numbers from simple SELECT COUNT(*) statements, and you can get quite a few numbers from the repository (via the so-called Metadata Exchange views, described in the Repository Guide for PowerCenter). And there are some more possible approaches. Simply experiment a bit, and be ready to encounter some requests which cannot be fulfilled with technical measures alone. That's normal.