I've updated our paper on aggregation in ProClarity 6.3. I've added information on the SCOPE_ISOLATION property.
In Analysis Services 2000, all calculations were evaluated in the same calculation pass – unless a specific solve order was specified in the calculated member definition. This means that the query scoped MDX could specify a different solve order than the MDX in a cube calculated member - allowing calculations in the query to be solved before calculations in the cube. This ability to change the order of operations gave some flexibility but also added complexity.
In SSAS 2005 the behavior was changed and cube calculations were always evaluated first – regardless of the solve order value sent with the query MDX. This resulted in some broken legacy calculations and many users wanted a way to continue to control the solve order of client side calculations. “In certain scenarios, you may want to run the session-scoped calculated members or the query-defined calculated members before you run the cube-level calculations.” http://support.microsoft.com/kb/917929.
The SCOPE_ISOLATION property, introduced in a hot fix and widely distributed in SSAS 2005 SP1, allows the insertion of solve order of the client calculated members into the cube’s MDX script when the calculated member is evaluated. http://support.microsoft.com/kb/917929.
Please see Scenario 5 in the updated paper and feel free to send us any feedback or questions.
Thank you Steve Pontello for all your help!
I know the paper references SQL Server 2005 SP2 specifically, but does this paper's recommendations work with SQL Server 2008 SP2.
Here is the MDX that I can not retrieve a sub-total too..
[Measures].[Closing] is a Count measure from our Claim measure group.
To me this is a pretty straight forward Calculated Measure and I am not getting Sub-Totals or Grand-Totals.
Thanks for contacting us. These types of questions are however best dealt with in the TechNet forum at http://social.technet.microsoft.com/forums/en-US/proclarity/threads/. If you're not able to find an answer to your question in an existing thread, please feel free to start a new one.
Thanks for your help Joey. Appreciate the explanations in your document and have passed those on to my co-worker. KateT