Determining the Appropriate Grain for a Pharmacy Consultation and Prescription Fact Table

Question

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:

• A pharmacy provides consultations to patients. Each consultation might involve multiple assessments.
• A patient can have one initial consultation and multiple follow-up consultations.
• There are multiple pharmacy locations, and each location has several pharmacists.
• A consultation can result in a prescription, which may include one or more drugs being prescribed. Given this scenario, what would be the most suitable grain for the fact table? Should it be at the consultation level, prescription level, or even down to the line item level of each prescribed drug?

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!

Answer 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.