Yesterday we attended Oracle Open World wearing our old Team PeopleSoft garb. We were trying to get noticed so that PeopleSoft customers attending Open World could find an expert in the crowd. We are after all, PeopleSoft Experts. Interestingly there was only one person I ran into that asked such a question of me personally. As it turns out I was unable to answer the question on the spot, but after half a night's sleep, I think I have an answer that solves the issue using a tool that I built for PeopleSoft several years ago called Cube Manager.
Cube Manager is a tool that was slightly ahead of its time. The goal of the tool was to allow customers to create Data Marts using the available PeopleSoft objects; specifically Records, Queries, and Trees. The part that makes it ahead of its time, is the fact that one cube definition can be targeted to several different targets, one of which is a star schema. The star schema output is what I'll focus on for this posting, but the general idea should work for the other supported targets as well.
The issue posed was this; using the PeopleSoft HR software there is a bit of a problem when using Trees to track departmental or organizational changes in conjunction with the Job Tracking functionality. The underlying issue is that the Tree doesn't necessarily have enough information to correlate to a specfic Job Transfer transaction so that when you try to do any Business Intelligence using both pieces of information (the effective dated tree and the transactions) there is no direct link between the two pieces of data.
After sleeping on it for a while last night, it dawned on me that Cube Manager was built specifically to help with this type of problem. In essence, the customer needs to build a dimension and a fact that can use a common set of linking information. Now a part of this issue is the fact that there isn't a clear set of linking information, but with Cube Manager and a star schema it may be possible to create a set of attributes that can be easily linked in a star schema. Now it is true that I haven't used Cube Manager in several years, but the concepts behind Cube Manager have driven much of the development I've done over the past several years.
The solution is this, since the organizational or department tree is basically a dimension and the set of job transactions are facts, it is possible to create a simple star schema with two dimensions and one fact in the PeopleSoft database that can link the needed data. The two dimensions are time and the org structure. The fact is defined by a job transaction query. As long as the individual's name or department is represented in both the tree and the job transaction the linking should be easy to perform. The result of the cube definition should be a star schema that is defined as a set of records in PeopleSoft. The records can then be used in Queries and thus reports. Also, since the cube definition is reuseable, updating the data in the records can be done each time a set of job transfers or organizational changes are made.
Labels: PeopleSoft
© 2011 Grey Heller Solutions.
All rights reserved.