Any views expressed within media held on this service are those of the contributors, should not be taken as approved or endorsed by the University, and do not necessarily reflect the views of the University in respect of any particular issue.

Enterprise Architecture

Enterprise Architecture

Discussion and news from the Enterprise Architecture (EA) service

Old data in new models

One of the challenges we faced in designing our HR data mart was how to map the data from the old HR and Payroll system into the new data model used by the People & Money system.

As an example of the problem, consider the data about which posts are funded from which sources – such as core funding, research councils, or charities.  As with all financial information, this uses a core data structure called the Chart of Accounts.   In the old system, each entry in the chart of accounts had three fields, whereas in the new system this has increased to nine fields.  Also, the account codes and cost centre codes used in these fields have been changed and reorganised.  Mapping an entry from the old chart of accounts to the new can be quite complicated.

The way that salaries are represented has also been changed.  In the old system, all salaries were mapped to a central “spine” salary scale; in the new model, that central spine no longer exists.  As another example, the new model uses a completely different three-level hierarchy for classifying jobs into categories such as “academic”, “clinical”, “IT”, etc.

As I mentioned in my previous post, the data warehouse has no problem in importing the data from both systems.  It stores all the relationships between the data entities, with dates to indicate when these relationships held true.  The challenge arises when we want to provide a consistent view of this data over time, so that our BI analysts can produce trend reports covering the whole period.

To help create this consistent view, we store extra information in the EDW, based on mappings created by the People & Money project.  Those mappings show, for each value in the old data, the corresponding value in the new model. Our database queries check the source of each record and, if the record came from the old system, store the relevant mapped values in the new model.

Some of the transformations are more complicated than a simple one-to-one mapping.  An example is with the change to job categories: at the top level, the mapping is straightforward, but at the more detailed levels, the values in the two models don’t match, and we have had to interpolate values that have the best fit.

Now that we have these transformations in place,  the warehouse has data in both the old and new models, and analysts can report on either or both, as the need arises.

Leave a reply


Report this page

To report inappropriate content on this page, please use the form below. Upon receiving your report, we will be in touch as per the Take Down Policy of the service.

Please note that personal data collected through this form is used and stored for the purposes of processing this report and communication with you.

If you are unable to report a concern about content via this form please contact the Service Owner.

Please enter an email address you wish to be contacted on. Please describe the unacceptable content in sufficient detail to allow us to locate it, and why you consider it to be unacceptable.
By submitting this report, you accept that it is accurate and that fraudulent or nuisance complaints may result in action by the University.