Reputation: 224
I’m working on a data warehouse for a pharmacy business process and need some advice on determining the appropriate grain for the fact table.
The business process is as follows:
I think the grain of the fact table should be at the prescription line-item level. Each row in the fact table will represent a single line item, which is part of a prescription. Every prescription is associated with one consultation.
What do you think of the following schema:
Would this schema be appropriate for capturing the data at the required grain? Any feedback or suggestions would be appreciated!
Upvotes: 0
Views: 47
Reputation: 1
To determine the grain, you need to look at the facts that you are bringing in with each record.
In the model you show above, the prescription_line_item table is showing a drug price and drug quantity. So you need to bring the grain down to that level if those are the facts you are reporting.
You said that every prescription is associated with one consultation. In that case I'd think that your grain is simply: Consultation -> Drug.
The rest of the keys will carry along and describe the Consultation. However, I'm not sure how Assessment factors into this. You said that there could be multiple assessments per consultation. Is a drug also associated with an assessment? If so, then the grain would be: Consultation -> Assessment -> Drug.
Now if there are assessments that don't have drugs, those wouldn't show in this table. But this fact table is for prescription_line_items, so that should be okay. If you need ALL assessments, then it sounds to me that you're answering a different business question and you should consider making a separate Assessment fact table.
It seems like you're on the right track, but need to figure out exactly what question(s) you are being asked to provide answers for with this data.
Upvotes: 0