How to handle Historical Dimension/Detail objects?

building semantic layer
1

Ok I have something new (yet again) that I’ve never needed before in any previous universes I’ve worked on.

The basic idea is that we have a single table with dimension objects (Lets call it Employee), and all the data reflects the current status of various dimensions such as Current Position, Job Title Etc…

Whenever something changes in that table, which can happen at any time, a record is written to a sort of log file which indicates what employee, date and field was modfied. It indicates the NEW value only, not the old value.

So over the course of an employee’s career, we might have about 3-5 diffferent job titles in the log file. (Other items change more often but just using Job Title as my example here).

Now lets say someone wants a report of all employee’s who were “Department Managers” as of Jan 1, 2005 (or some point in the past).

From a logic point of view, essentially I need to determine the record change that happened PREVIOUS to the date specified in the report; so that I can get the last previous value of job title prior to the date requested. Any changes that happened after that date are not of any concern since the user wants history as of that date.

What I’m trying to wrap my head around is how to depict this in the universe, or if it should be in the universe as a view or something from the SQL side.

Because of the nature of the log file, I can’t directly join it to the employee since the “Field Affected” column is basically dynamic whereas the employee’s are not.

Has anyone come across anything like this in thier fields? How did you handle it? Was it in the database? in the Universe? or perhaps even on the report itself?

JPetlev (BOB member since 2006-11-01)

2

What you are after are so called slowly changing dimensions (SCDs).
Please, look at my post here and a Ralph Kimball’s website here.

To me it looks like you will have to change the process to incorporate slowly changing dimensions (SCDs) properly modelled in your datamart.

Andreas :de: (BOB member since 2002-06-20)

3

Yes I started looking into SCD’s right after I posted this as SQL 2008 seems to have some built in wizards to help with the process.

This is all great for Going Forward… but I have dozens of years of history already in these log files.

The previous system (a Unidata based system) has a lot of report logic to provide quasi-historical reports but there is no where directly in BOBJ to put that logic.

I think what I’m going to end up doing is to try to convinice the SQL DBA to start incorporating SCD’s directly in our SQL side for future changes. (We can’t change the underlying system where the data is pulled from, but I think SQL will be able to track this anyway I’m hoping).

Then I have to convince them to do the work via Stored procedure to essentially ‘recreate’ some amount of that history to pre-populate the table as if SCD was being tracked all along… that’s not going to fly but I don’t see any other way to get the old history into our tables.

JPetlev (BOB member since 2006-11-01)