MDX SCOPE subcube assignments

Aaron's MSBI / SQL / Functional-programming space

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:

SSAS 2008 SCOPE

“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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s