| EFFDT Again | KEVIN RESCHENBERG
05-24-2004 |
I once worked for years in a certain building. Access to the office area was through
a locked door in the elevator lobby. Next to this door was a large picture,
with a prominent caption below it (naming an art gallery or institution of some sort).
I faced and passed that picture over 1000 times. One day an odd question popped
into my head. What did the caption on that picture say? I couldn't remember. I asked
a few coworkers and nobody else knew either.
Sometimes we tend to ignore or forget about familiar things.
In a post last month, I discussed the use of EFFDT—specifically,
whether to choose a row that was effective as of the date of another row (from JOB, for example) or
a row that is effective as of today. I suggested that for most reporting where the user is not
given an as-of date run control option, using today's effective-dated rows in subselects makes
sense; but that when an as-of date option exists, more analysis is needed.
I was working on an interface to another system. This interface included employee information
plus items such as the name of the employee's department. I thought it was clear that we would
send the currently effective JOB information and the currently effective department information,
even if the current row from the department table was dated later than the current JOB row.
The client, however—a person with many years of experience working with PeopleSoft systems—thought
that the department row effective as of the date of the JOB row should be used. This surprised me
very much. He based this on the fact that the job data pages display related information based on
the JOB effective date.
Of course he's right about that. I had not really thought about this for a long time, but each
row on the job data pages displays related information as of the effective date of that row.
This is the standard behavior of the component processor that is building these pages.
If the row is dated 1/1/2004, the department name displayed on that row will be the name as it
existed on 1/1/2004, not as it exists today. This certainly makes sense when you are viewing
history. But it means that even the "top" (current) row on this page displays information
that is "old". If an employee was hired two years ago and has had no activity
in JOB, the department name, location name, and similar items displayed on this page will
show information current as of two years ago.
Even though this information is "old", we can still argue that this is correct, since the
job data pages are intended to manage and show history. They are not for looking up current
related information.
Although the job data pages (and other pages built by the component processor) work this way,
we find that reporting generally doesn't. For example, the EMPLOYEES
denormalized table uses information effective as of its as-of date (generally the date
it's rebuilt). This is good. (I was getting a little worried after talking to the client and
went immediately to check on this!)
So how does this company, a very well-organized shop with routine processing and few surprises,
maintain its data accurately? It turns out that the standard business processes employed by
the client make the whole issue largely moot. Changes to setup tables such as the department table
are tightly controlled, and when there are changes, a new JOB row is inserted
for all employees affected by the change. Although that's not what I would have expected,
it works for this company. The client used a different example—the job code table—to point
out that in many cases a change is important enough that every employee affected should
be checked and should get a new row in history.
An added bonus is that the employee's history is very detailed,
and—importantly—it is less critical to decide which effective dates to use; it works
out pretty much the same either way.
Note that this business process doesn't extend to all areas of the system. Some tables are always
joined together using the same effective dates, by design. For tables that have a parent-child or
sibling relationship, there is no question about which effective date to use. Examples would be
JOB and COMPENSATION (parent-child) or JOB and JOB_JR (where the unfortunately named JOB_JR
is actually a brother, not a "junior" to JOB).
Another obvious example would be the Benefits Administration setup tables. Each year we will
create a new set of rows in these tables for that year's program. The 2005 open enrollment
will be controlled by the 1/1/2005 EFFDT on all of these tables and will apply to all eligible
employees, even if an employee's last JOB row is dated before 1/1/2005.
To me, the design of the system clearly points to the fact that effective-dated tables
can be allowed to evolve independently of each other, and that this gives us the flexibility to report
either current information (using the latest row from each table) or historical point-in-time
information. But, as usual, the details all depend on a company's customs and business processes.
Those of us on the technical side must have a good understanding of these processes—a much
better understanding, certainly, than I had in the case described above.
Your comments?
|
