SSAS cube: force Budget measure granularity drill down to the leaf level of calendar dimention (split Quarter Budget between Monthes, Weeks and Dates)

Question

Our Budget/Plan has Quarter grane, while Fact sales data is stored Dayly. To build Plan-Fact reports for Monthes, Weeks and Dates - I need to split Budget evenly between Calendar dates. How can I do this within a Cube?

After hours of googling I've found references to [IgnoreUnrelatedDimensions] property of Measure Groups only. But it's purpose is different - it stops populating (copying) of measure's values from the most close hierarchy level down to it's children beneath grane border. I do not need to populate values, I need to split them evenly between all children

I've found TCONVERT() function for Oracle OLAP. It converts time-series data from one dimension of type Day, Week, Month, Quarter or Year to another (here it is: https://docs.oracle.com/cd/B19306_01/olap.102/b14346/dml_x_stddev012.htm). I need a similar function for SQL Server OLAP, I suggest. Can't find it so far. Help !! — Globe, Apr 27 '15 at 05:46

score 0 · Accepted Answer · answered Apr 27 '15 at 13:15

Here is a workaround for SSAS calendar granularity depth, which I was offered on behalf of two SSAS architects, who used to know what they are talking about: I should prepare all necessary grained data in the data warehouse and load it to the SSAS Cube ready-to-use without additional tricks there in mdx model. I did it this very way and now my reports are fine.

Nevertheless, it would be interesting to look at native SSAS decision for calendar grannularity drilling too. If anybody knows it - post it here, please!

SSAS cube: force Budget measure granularity drill down to the leaf level of calendar dimention (split Quarter Budget between Monthes, Weeks and Dates)

1 Answers1