I’m designing distribution and price monitoring (DPM) cube. I’m using MS Analysis Services. For the time being I’m trying to design it as simple as possible and solve semiadditive measure problem.
Dimensions: Store (Levels : All, Store Chain , Store Name) , Product ( Levels: All, Product Brand , Product name) , Time( Levels: Year, Month , Day).
Measure : Facing (numeric value showing , how many units of given product in a given store customers can see on the shelf). Salesmen can measure Facings any time they want (every day or once a month).
Measure is additive on Store and Product dimensions. On Time dimension empty values should be filled with last non-empty values. Monthly value is given month last day value, Yearly value is given Year last month value. I can do it using calculated member, but the result is correct only on leaf level of Store and Product dimensions. If we are looking at non-leaf level member, our MDX statement takes last non-empty value of this non-leaf member, but I need sum of children ( calculated using last non-empty values ) . If I put custom rollup formula (something like RollupChildren(Store.Currentmember, “+”)) to non-leaf levels, then it gets too slow (even with these 3 dimensions , but I need to add more dimensions), I think because everything is calculated on-the-fly. The other solution is to create fact table records for each day, each Store and each Product and then fill them with needed values, but then I’ll get fact table with hundreds of billions rows!!!
Any ideas?
Dimensions: Store (Levels : All, Store Chain , Store Name) , Product ( Levels: All, Product Brand , Product name) , Time( Levels: Year, Month , Day).
Measure : Facing (numeric value showing , how many units of given product in a given store customers can see on the shelf). Salesmen can measure Facings any time they want (every day or once a month).
Measure is additive on Store and Product dimensions. On Time dimension empty values should be filled with last non-empty values. Monthly value is given month last day value, Yearly value is given Year last month value. I can do it using calculated member, but the result is correct only on leaf level of Store and Product dimensions. If we are looking at non-leaf level member, our MDX statement takes last non-empty value of this non-leaf member, but I need sum of children ( calculated using last non-empty values ) . If I put custom rollup formula (something like RollupChildren(Store.Currentmember, “+”)) to non-leaf levels, then it gets too slow (even with these 3 dimensions , but I need to add more dimensions), I think because everything is calculated on-the-fly. The other solution is to create fact table records for each day, each Store and each Product and then fill them with needed values, but then I’ll get fact table with hundreds of billions rows!!!
Any ideas?