|
Summary:
I am about to add/create an
ETL map to the EPM ODS load in addition to the PeopleSoft delivered maps. When I
looked at the ETL maps delivered by PeopleSoft, they do not seem to be simple
'pass-through' maps. They include lookup tables, and some associated
transformations. If I want to add a map, how do I know what the dependencies are
or the sequences of tables associated with my table? Is there any way I can
lookup this information?
Answer1
The general rule is that if the record definition shows a prompt table for any
field, those are the ones you should do a lookup against. You can refer to the
simpler PeopleSoft delivered mapping to get a lookup to work.
If you have a table that would have all those prompts then you can decide
whether or not to add all of those lookups. I cannot speak for any Financial
tables but for PS_JOB you would want lookups against at least Emplid (PS_PERSON)
and DEPTID(PS_DEPT_TBL) and Jobcode (PS_JOBCODE_TBL). We
have had many tables that we just pass through a map and do no lookups (for
example: confirmed payroll data). The reason we did no lookups is because: 1)
The data is right because the pay cycle completed and 2) It can't change because
here at HIG we do not open up prior cycles especially if the info went to GL.
I think the PeopleSoft delivered map was looking up the "more important" fields
and that is how we would have done it here. I know first hand how much more time
creating a lookup can add to working on a map, but it's good for our warehouse
not to have bad data in it.
There is a wizard in Informatica that can help you create a simple pass through
map. Then you could add the ETL_JOB_CTL info to it (we do that here also). It's
under Mappings/Wizards/Getting Started. We made our own template here at the
HIG. We added all of the tables required for the ETL_JOB_CTL
stuff and saved it as TEMPLATE. Now when we have to create a map from scratch we
start with that.
Answer2
I think that using using prompt tables to determine what Referential Integrity
checks (hereafter RI) should be included in a map is imperfect. It would be
better to use the "join recommendations" that come out of PSQUERY which are
derived from the record hierarchy.
Unfortunately, that too is imperfect because it assumes that the developer was
diligent about identifying the record hierarchy in PeopleTools. What's best is
to think about what aspects of data integrity are salient to your application
and code RI into your map based on those aspects and the record hierarchy. Once,
you have got this reasonably comprehensive set of checks, run your map, look at
what fails, and remove checks that kick out data for problems that are hard to
fix and which don't matter to you.
*Questions excerpted from
ITToolBox.com*
Disclaimer: Contents are not reviewed for correctness and are not endorsed or
recommended by PeopleSoft-Planet.com. | 
