Should rule-derived columns go into the fact table?

View previous topic View next topic Go down

Should rule-derived columns go into the fact table?

Post  Al Wood on Mon Apr 07, 2014 5:08 am

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

Al Wood

Posts : 46
Join date : 2010-12-08

View user profile

Back to top Go down

Re: Should rule-derived columns go into the fact table?

Post  nick_white on Mon Apr 07, 2014 5:43 am

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?

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Should rule-derived columns go into the fact table?

Post  Al Wood on Mon Apr 07, 2014 7:00 am

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

Al Wood

Posts : 46
Join date : 2010-12-08

View user profile

Back to top Go down

Re: Should rule-derived columns go into the fact table?

Post  nick_white on Mon Apr 07, 2014 7:27 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Should rule-derived columns go into the fact table?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum