For weeks I’ve been fiddling with MDX SCOPE statements and not fully understanding them. I read they are “subcube assignments”, and initially took that literally, thinking the assignment was creating physically persisted calculations in the SSAS database. But I came to realize this assignment is virtual. This statement says it well:
“When working with the [Measures].[Staffing %] measure and we are calculating it for the last fortnight, its value is equal to the fortnight previous to it times 1.05.”
More generally, when the members of the current tuple for the current cell are the same as the members specified by the collection of SCOPE statements before the THIS=x*y assignment, perform that calculation. Otherwise return NULL, or an aggregated value if built.
Also, it seems a CALCULATE statement can be nested inside a SCOPE statement to force aggregations within that SCOPE. I might have to try that, and…
View original post 65 more words