How do I connect fact tables for drill down

I need some advice on a project I am modeling for a Hospital.

We have 2 business process' that I am creating individual fact tables.

The big questions is - how do I connect related Fact Tables for drill down ?

Details:

Reimbursements - the grain is each payment/debit transactions line item from 2 diffferent systems, hospital and Dr office's

PatientVisits - the grain is a visit within the hospital

Not every Reimbursement has a Visit because can be from the Dr Office.

We are set on modeling the Reimbursements but need to connect the PatientVisits fact table to the Reimbursements for drill down. They need to pivot off a set of Reimbursements which they query. It is OK to link the 2 fact tables for drill down via the PatientVisits Business Key ?

Reimbursements: ~ 50 Million
SQLID
Source_FK
Facility_FK
ServiceDate_FK
...


PatientVisits: ~ 8 Million
SQLID
VID - Business Key
Provider_FK
Referrer_FK
ServiceDate_FK
ReleaseDate_FK
....


Do I add the VID or create a FK to the Reimbursements table ? PatientVisits is not a dimension (no attributes to query) ... they just need it in drill down.

Also - since the PatientVisits will be made up of FK's - it really isn't effiecient to use a fact table for drill down where they want the list of data from visits.

Do I create 2 PatientVisits ? 1. factPatientVisits and 2. the other with just raw PatienVisits(for drill down)

Thanks
John Burkard

We never join fact tables directly, always through appropriate business keys. This is because it is possible that we could have SCD load-timing issues which cause the surrogate keys to differ, while the business keys are the same.

You need to identify the business key in the Reimbursements model which gets you over to the PatientVisits model. It sounds like the PatientVisits model only has shared conformed dimensions, while the Reimbursements model has some internal dimensions.

If a visit is linked directly to a reimbursement, we would typically have a fact table with all the same keys as the reimbursements model and the facts about the visit in the fact table. We have some multi-fact tables like this where some or all of the surrogate keys are the same (however, we still do not join on the surrogate keys, but on the data in the dimensions) so that it is logically just a vertical partition of the fact table (although the data might be from two sources).

If you gave more information about how the two models were related, I might be able to give more specific help.

Hi John,

As I understood from your post, a Reimbursement may or may not have a PatientVisit associated. Also, the goal is to select an initial PatientVisit and then drill down to the reimbursement(s) associated to it.

If a PatientVisit is linked to a Reimbursement only, I think that you should have a single fact table with the union of all your FKs. For a hospital visit, you could filter on the PatientVisit dimension attributes and you would get two sets of measures for each row: the PatientVisit and the Reimbursement ones. For a Dr. office reimbursement, the PatientVisit FKs would be set to 0=Unspecified (for instance) but still could filter on the Reimbursement dimension attributes. In this case only the Reimbursement measures would be informed. In this scenario, only one fact table is needed.

However, if a PatientVisit may have more than one reimbursement, in my opinion you are facing a degenerated dimension scenario. On the one hand, PatientVisits is a fact table in itself as it tracks the event of a patient visiting the hospital. However, on the other hand, PatientVisits needs to be used as a dimension in the Reimbursements fact table (as a reimbursement can be related to a patient visit to the hospital). So I would create a degenerated dimension key in the PatientVisit fact table, and would add it as a degenerated dimension key to the Reimbursement fact table.

Please do not hesitate to contact me for further clarification or discussion. As stated in the previous reply, some more information would help us to provide a better solution.

If you have a means to add the visit id to the reimbursement fact, by all means, do so. But, as you mentioned, getting such information as it relates to physician encounters is problematic. This is pretty common...

Probably the best you can hope for would be to relate reimbursements to visits based on visit ID (if you can add visit id to reimburesements) or patient and date of service. The latter isn't perfect, and you may need to use a date range if it involves an inpatient stay.