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?
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.
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.