Cornell Human Resource/Payroll Data Warehousing Web Site - FAQ's As people post questions related to Human Resource/Payroll Warehousing, we will try to collect and organize them here with their answers. Each topic will be listed alphabetically with its original question (or comment) followed by the reply provided. Remember that web pages are searchable - if you have a topic you are looking for, try a "Find" (usually a Ctrl+F), or just use the Table of Contents links below. PeopleSoft Effective Dating PeopleSoft Effective Dating Q: What is effective dating and why is it needed? A: Effective dates are used by PeopleSoft to store history. When data is changed in a row, a new row is added using the system date of today. Thus history is retained and can be accessed by reading the row with the older effective date. This methodology becomes more complicated when related data is stored in different tables. In this case, the correct related data is obtained by comparing the effectives dates. For example, Two tables "A" and "B" have related data. To get the correct data from table "B" that relates to the row you are processing from table "A" you must find the row in table "B" that has an effective date less than or equal to the effective date of the row you are processing in table "A". This results in a complicated conditional join that effects SQL access. In an attempt to improve SQL access, the HRPDW schema removes the requirements of the complicated conditional join by either denormalizing the data, i.e. combining the related fields into the same row, or by creating surrogate keys that can join the rows of two tables. In the latter case, the effective dating has been determined in the data load process. Return to TOC New Questions? Let us know... Please Note! If you are posting a question or reporting a data mart error, in order to help us get you answers or diagnose a problem more quickly, please be explicit in describing the error. For example, in the body of the message, saying... "What happened to the employee table?" ... is less effective than saying: "When I try to query the payroll.employee table in production, I get the error: 272: I-NET#-272: No SELECT permission." Thanks for your input! Return to TOC Last modified 05/18/01