Wednesday, 2 December 2009

MDX SCOPE error

Had an interesting error today when adding a new Calculated Measure to a cube. When I tried to process the cube BIDS came up with these errors:

Error: A set has been encountered that cannot contain calculated members.
and
The END SCOPE statement does not match the opening SCOPE statement.
After some digging around on the internet, I found that the issue was caused by me adding the new calculated measure to the SCOPE statement for the existing MDX statements for created by the "business intelligence wizard" in BIDS. This wizard adds some time based calculated measures to the time dimension in your cube (e.g. Year To Date, Month on Month Growth) and I thought I would be able to add my new calculated measure to the existing SCOPE statement to get the same time intelligence functionality. Wrong!

It appears that you cannot mix regular and calculated measures in a SCOPE statement, and doing so will generate the above errors. The reasons for this are not clear, and it is certainly a bit of a pain since it means you have to repeat any logic or calculations within the SCOPE statement twice, once for regular measures and again for calculated measures. This, as you can imagine, does not help with the maintainability of MDX scripts.

So, for example;

SCOPE(
              {
                [Measures].[My Regular Measure],
                [Measures].[My Calculated Measure]
              }
)
.. (some MDX assignment here)
END SCOPE;

will cause the above errors. To get around this you have to rewrite it like this;

SCOPE(
              [Measures].[My Regular Measure]
)
.. (some MDX assignment here)
END SCOPE;

SCOPE(

[Measures].[My Calculated Measure]
)
.. (some MDX assignment here)
END SCOPE;
Basically duplicating your MDX assignment code! There is an alternative way around this sometimes, where you can rewrite to query to avoid using the SCOPE statement at all, as discussed here, but with complex scripts this can start to make the code harder to understand.

No comments:

Post a Comment