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

Say that there was a record:

ID PatientId DoctorId MeditacionId DateTimeId TreatmentId Quantity Current
1 1 1 1 202105031250 1 10 TRUE

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:

ID PatientId DoctorId MeditacionId DateTimeId TreatmentId Quantity Current
1 1 1 1 202105031250 1 10 FALSE
2 1 1 1 202105041250 1 5 TRUE

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.

  • 1
    This would just depend on what your use cases are and what you want your cube to represent. If you need historical data then you can't filter on Current = TRUE as you noted. Or you can make two Views if you need the data both ways. You could also add your Quantity field only on the rows that make sense to add, based on whatever your logic is, and treat the non-additive rows as 0s, with a CASE statement for example.
    – J.D.
    May 4 at 12:05
  • 1
    Thank you J.D. I observed that the majority of my problem originated from the fact that I was overwriting the DateTime FK. I resorted to doing role-playing dimensions for DateTime and introducing multiple dates. So, I now have Created_DateTimeId but also, Updated_DateTimeId and Imported_DateTimeId. So, I can now create multiple CUBEs with filtering Current = TRUE and target the DateTime I need for specific purpose... May 6 at 12:22
  • Cool sounds like that should work!
    – J.D.
    May 6 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.