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.
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.
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
Already have an account? Log In
Don't have an account? Register for Free Unlimited Access