Question: We are currently implementing PeopleSoft EPM 8.3, initially for reporting and later for analytics, both for finance and HRMS. As EPM 8.3 is a recent version and we do not have and history for its use, I would like to know what approach shall we take involving its staging and ODS schema? Should we dump the entire operational schema into a staging schema and later move data to ODS for strategic or other kind of reporting?


Mike Jennings’ Answer: The ODS in Peoplesoft EPM 8.3 is separated into two areas, one for the staging of data before being processed into the EDW, the second is for operational reporting. The data staging section of the ODS provides initial testing of data quality of the data retrieved from the HR and financial ERP systems. ODS-staged data can be processed further for analytical purposes into the EDW or for operational reporting in the ODS reporting tables.

The reporting section of the ODS can be used for daily or intra-day tactical operational reporting requirements in order to off load this burden from the HRMS and finance systems which primary purpose is online transactions processing. I would question the business need for more than daily updates of the ODS reporting tables since there is a benefit to letting the data age in the ERP systems before being moved (e.g., data entry corrections, cancelled transactions). PeopleSoft has not provided many tables in the ODS reporting section of EPM which leaves it to you to design and implement operational reporting tables. These tables are fed through the ODS staging tables. PeopleSoft application engine programs can be used to move and consolidate data from the staging section of the ODS to the reporting area. You can design these tables to be efficient for reporting by reducing the need for table joins and summarize data. Since EPM in this version is intended to perform all operational and analytical reporting for the ERP environment, all data need for these business purposes need to be staged into the environment. Depending on your business requirements and portions of EPM purchased, information in the EDW can be used for analytical simulation and modeling. Analytical forecasts from these models can then be closed loop feed back into the HRMS and financial application as updates.

Joe Oates’ Answer: It is not possible to give a complete discussion of how to turn a logical model into a physical design. Also, I am not sure whether the question pertains to transaction processing systems or data warehouse. I will attempt to give a summary of some of the important points based on my experience.

  1. One or more entities will result in a physical table. Use your experience and understanding of how often certain tables will be accessed together.
  2. Relationships in a logical model generally become foreign keys in a physical design. Of course, if two or more entities are combined into a single physical table, there will be no foreign keys involved.
  3. Subtypes are a little trickier. In data warehouse implementations, if possible, I usually combine supertypes and subtypes into the same table and add a column to indicate the particular kind of subtype and "overload" the columns in the part of the table used for the subtypes. I then create views for each subtype so that the column names make sense for the particular kind of subtype. This simulates subclassing in an object-oriented environment. Sometimes there are a couple of columns that may not always have a value, depending on the subtype, but this is a small price to pay for avoiding a join. If this approach does not seem appropriate, then each subtype should be represented by a separate table.

Keep in mind that implementing a physical design with 1,000 tables is much more complex than doing the same thing with 150 denormalized tables. The more complex a system is, the more time and cost will be involved in developing, implementing, testing and maintenance.
Clay Rehm’s Answer: Since there is no other history, I would suggest you load what you do have into your staging area and ODS now, for no other reason but to test that environment and to test it with your users.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access