Should rule-derived columns go into the fact table?

[Al Wood]

Hi,

I'm building a fact table to present Radiology Examinations. These Exams are a Service which is provided by various departments within our hospital, and sometimes by external hospitals. External data is fed into our Radiology system via an interface, and merges in with our own Exams.

Our Finance dept are very keen to see who is the Service Provider for each Exam, and there is a set of rules for finding this out from columns in the extracted data. I have contained these rules in a look up table and a MSSQL Server TVF, which runs quickly.

Should this rule-derived column go into the fact table? The Finance and System users can alter the values in the lookup table and in this way they can change the rules. If an Exam a few weeks old is modified in the Radiology system, it's data will be extracted the next day but other Exams in the same month will not. The Exams data cannot be extracted in full; we can only get a limited amount, so each day we extract any records changed in the last 7 days.

If I put Service Provider in the fact table, and the ETL applies to the fact table any recent changes from the source system, the fact table will be updated and this Exam may be assigned a Service Provider by a different rule than other Exams around the same time.

I would greatly appreciate any suggestions.

Al Wood

[nick_white]

If I've understood this correctly, you don't want the Service Provider key to change in your Fact table once it has been set for a particular fact row?
In which case why don't you just change your ETL not to update it when updating your fact table rows or, if you are doing delete/inserts rather than updates, pick up the Service Provider key from the old fact record and use that when creating your new fact record?

[Al Wood]

Yes, you understood.
Thanks, that's a good way to avoid altering past Service Providers. I guess the wider question is, when they want to redefine past rules, how do I apply those only in the correct date range? I'm thinking that I need to add date ranges to the rules in the lookup table, and modify the TVF.
Thanks,
Al

[nick_white]

Yes - just add from and to effective dates to your rules. For the current version of a rule populate the to effective date with a date in the future - so you can use the SQL 'BETWEEN' syntax and you don't get any of the complexity of having to allow for nulls in your logic

[Sponsored content]