I have a simple fact table with four dimensions and two measures. Dimensions: Location, Date, Drug, Drug route
Measures: Count of drugs dispensed; Count of patients as each location for the date
The granularity of the data is rolled up to the month level. Every combination of Location, Date, Drug and Route has the Drug Count and Patient Count pre-aggregated. Records with the same Location and Date will also have the same Patient Count.
The dispensed drugs are fully additive, but I only want the patient days to SUM over locations and dates. However, I can't figure out how to make the semi-additive options work across the 2 drug-related dimensions.
Is this beyond the Business Intelligence package? What would be my next approach?
I think this can be solved with a "Distinct Count" aggregation.
I would go back to the SQL view on the fact table, and add a calculated column to concatenate Patient Id, Location Id and date. Then in the SSAS cube definition, I would set the "Count of patients" measure to use the "Distinct Count" Aggregate Function, based on that "patient+location+date" column.