Have an interesting Challenge; thought will reach out to you if you can help
Currently I am working on SSAS 2008 OLAP Cube, whereby most aggregate level measures are pre-calculated using the C# Code as part of ETL and are stored in Data Warehouse as well as Measure Groups
in Cube (MOLAP).
These are mostly non-additive measures have to choose this approach for performance reasons, considering the complexity of the aggregations and data volumes.
Data Volumes are Huge (about 300+ Million Rows/ per day), 98 % of measures are non additive / semi additive.
Cube is used primarily for advance analytics and will be eventually used for data mining like time series , what
if analysis and scenario analysis
Excel is used as front end .
Question is how can we replace the aggregate
level data for various dimensions attributes
(Totals and Grand Totals) from pre-calculated measures, those are also available in the Cube as measure groups?
We are currently using Scope and Root statement which is not working as expected
SCOPE ([D1].[H1].[A1], M1)
Root (D1) = <Get Pre-calculated value for M1 from related Measure Group for D1].[H1].[A1], >
SCOPE ([D1].[H1].[A2], M1)
Root (D1) = <Get Pre-calculated value for M1 from related Measure Group for D1].[H1].[A2] >
Thanks in Advance
View Complete Post