Late Arriving Dimensional Details

[bdouglas]

I work in an environment where dimensional details are regularly back dated and they need to continue to be type 2 tracked.  So, in tasking myself with solving this issue I find data manager lacking and wanted some input.

From my research, data manager has no built in functionality for delivering late arriving dimensional details.  The only built in functionality is simply writing these records to a separate table.  From there custom SQL programming would be required to handle these records.  Am I missing a huge piece here or is that on the nose?

At this point, I'm resolving myself to using SSIS to handle this single problem.  I've shyed away from SSIS as much as possible to keep our ETL process within a single environment.  Any suggestions regarding this issue would be greatly appreciated.

Thanks

[MFGF]

Hi,

Automating the process of including late arriving dimension rows into the warehouse is a very difficult task.  Consider the implications in terms of all your fact rows linking to dimensions via surrogate keys - you could potentially have to identify thousands or millions of fact rows across multiple tables and regenerate surrogate keys for them based on a single late arriving dimension. Either that or completely regenerate all your fact tables from scratch to introduce appropriate skeys.

Either way, Data Manager helps you as far as it is able by at least identifying the late arriving dimension rows and allowing you to isolate them so you can figure out the scale of the problem to insert them.  I can't ever see it doing more than this to be honest.

Probably not what you wanted to hear...

Regards,

MF.

[bdouglas]

It's exactly what I expected to hear though.  Late arriving dimensional details points more towards procedural and process issues than software and design issues. 

I'm in k-12 education and the nature of the beast is that dimensional details can be backdated.  An example would be a student that withdrew from our district 5 years ago returns today but the entry record is not input for several days.  The result is that the student's dimensional details (grade level, address, etc.) is not updated for several days while fact records (attendance, enrollment, etc) can be back dated and generated in the source system.  In a district of 25k students we can easily have that happen a thousand times a year with high mobility rates.

We already re-build several of our fact tables completely nightly because we can (less then 5 million records) and the other fact tables we just rebuild the last year nightly.  Unfortunately, there is literally no closing of the books with educational data as it can change for the life of any student.

Thanks for the response and I wanted to share my experiences in case anyone else has this issue.