Versioning Fact rows?

[rfsalas]

I designing an accumulating snapshot table to track student enrollment (regitester, enrolled, exited) and currently wrestling with a requirement. The client basically is asking for adding a CurrentFlag and effective dates columns to the fact table in order to be able to reflect any corrections in the transactional system such as deleting enrollment rows, corrections (changes in one of the fact table NK attributes), etc. When I ask for the reasoning of scuh requirment I hear that they need the ability to reproduce the reality of source systems in a point in time, as well as leaving an audit trail of such chnages.

Some of the issues I see are:
* Changing the grain of the fact table
* This seems to combine 2 different business process: student enrollment Vs. data audit trail in source system.
* Complicates aggregation logic

Have any of you seen similar requirements or scenarios? Do you see any other drawbacks in the proposed approach?

Thanks

[ngalemmo]

I assume you already have a transactional fact table (atomic) covering enrollment, and the table in question is a snapshot for some given point in time (such as end-of-month). The atomic facts should give you a complete history of what happened and when, but it is not unusual for there to be a request to provide 'revised' snapshot numbers based on retroactive changes.

If you do need to do this, having effective period dates (reflecting when it was known) is a common way of handling it. Change numbers would only be loaded in the normal snapshot cycle (once an month, for example). To keep the snapshot table as small as possible, for each snapshot period you need only store changed rows of past counts along with the normal set of current period counts.

[rfsalas]

I assume you already have a transactional fact table (atomic) covering enrollment, and the table in question is a snapshot for some given point in time (such as end-of-month).

Thanks for your quick reply.

No, this is not periodic snapshot table but rather a cumulative one. The goal of this table is to capture the student enrolment as it moves through different stages (register date, enroll date, exit date). We have a second periodic snapshot to freeze some end of period reports, but that is not subject to the request of 'versioning' I described earlier. I still think creating this versioning of rows in the accumulating snapshot table is not a good idea.

I haven't considered having a transactional fact table, and at this point I am wondering if that could be a better way to repsent those adjustment that happen in the source system.

[da_2030]

You can still create type 2 for accumulating snapshot table.
1. Grain of your fact table will change from NK to NK+Effective Date ( PK will be Surrogate_key + Effective Date)

2. You may need to put Latest and historical rows on separate partitions/tables for performance reasons .

3. I do not know about your data volume but since it is accumulating snapshot table, number of rows will be less than transaction table as you are flattening multiple rows into columns - different enrollment stages(register, enrolled, exited).
One issue could be performance related - for point in time reporting, depending on volume of changes captured in type 2.

4. You can still aggregate on Latest records.

[Sponsored content]