Wrong measure calculation
Let's say we have the following cube : Cube (Date_id*, Address_id*, Patient_id*, Treatment_id*, ConsultationNumber).
During a specific date, a patient could have only one address, but could have zero or many treatments !
March 2013 | Place 1 | Patient 1 | Treatment 1 || 1 Consultation
March 2013 | Place 1 | Patient 1 | Treatment 2 || 1 Consultation
As you can see, the patient has many treatments, but it's during a specific date (one consultation), so when grouping the treatment's column, i want the measure calculation to be correct, 1 in this case, and not the sum of the child's measures (which gives 2 in this case) !
March 2013 | Place 1 | Patient 1 | All treatments || 1 Consultation
If you have any idea how i can do this, please let me know !
Last edited by YOoSlim; 09-21-2013 at 08:24 AM.
Many-to-many dimensions are a tricky problem, and show up frequently in health care. Kimball's got a whole chapter in the data warehouse toolkit on topics like this. In some simple cases an option is to capture and apply weighting factors to adjust the measure values. For example, if a patient has 2 fact table records for a single encounter you can adjust measure values by .5. This option starts to get complex if more than one multi-valued dimension is present, though.
I'd recommend that if you have any significant amount of analysis happening *without* the many-to-many dimension that you consider a separate base cube that excludes the dimension. That keeps the simple case simple, and you can focus on your many-to-many strategy just in the cases where it's necessary. You can then bring the cubes together in a virtual cube for cross analysis.
Thank you for your answer !
You talked about Kimball's book that i already had, so i searched about the healthcare's chapter and understood the solution he offered.
No problem, glad to help. And just so you know, many-to-many dims and semi-additive measures are a strong area of interest in Mondrian, and there will hopefully be enhancements to make this sort of analysis easier in the future. Vote for MONDRIAN-1572 and MONDRIAN-962 if this is important to you.