Not sure if this is the right place to ask.
I am working on an ETL procedure that gathers the data about medication consumption and I have something like this:
Dim_Patient, the dimension for all the patients in the system Dim_Doctor, all the doctors in the hospital Dim_Medication, all the medications available Dim_DateTime F_Consumption: Id(PK) PatientId(FK) DoctorId(FK) MedicationId(FK) DateTimeId(FK) TreatmentId(DD) Quantity Current
Say that there was a record:
And that for any reason there was a mistake, say, in quantity, which has been noticed the next day. For example, there were not 10 units administered but 5. After the ETL, I would end up with something like this:
How do you set up the
CUBE with this data? The quantity here is clearly non-additive.
Given that I work with Postgres, I would have created a
MATERIALIZED VIEW with
GROUP BY CUBE(...), filtering only
Current = TRUE, but then I would lose track of history.
Do I even need to include historical data in the CUBE?
I am looking forward to hearing any thoughts on this.