Debugging a Calculated Measure

Is there any practical way to find out a problem with a calculated measure? The one I have created is obviously wrong (I get a #value! in the cells), but other than that I get no details telling me where I am going wrong.

Other than make changes and process the whole cube again I cannot think of how I can get to the root of it.

You can try using the built-in calculation debugging facilities within BIDS. In the Calculations tab for the cube, view the calculations in script view and add a breakpoint for the calculation in question. Then, on the Debug menu, select Start Debugging. That will open a browser within the calculations tab that will allow you to "step" through the calculations...

This sometimes, however, doesn't help much if you need to determine what is happening "inside" a calculation as the debugger will only step "over" calculations. In the debugger browser, you can put different dimensions and measuers into play and see what happens as each calculation is evaluated and created. But you can't really see the effects of the various MDX functions and such within a given calculation if it is complex. To deal with this issue, you may have to break a calculation up into various pieces by creating multiple calculations that "build up" to the final calculation you need. Then, in the debugger browser, you can "step" through the various pieces of the calculation to see how things are happening...

HTH,

Dave Fackler

|||

Thanks for that.. I'll give it a whirl.

|||

I must be doing something wrong.. I set a breakpoint and put her into debug.. the only options I get are step over and I cannot see any of the details or set watches.. this isn't debugging.. it's highlighting! I can't see how I'm supposed to discover what my error is!

When I run the MDX I get a matrix of errors, but nowhere do I get access to the errors...If I run in ProClarity I get a matrix of empty cells...

I have run the MDX expression as an MDX query, using a specific member and it works fine.

Can someone cast an eye over this and point me in the right direction.. I'd appreciate it so much!:

CREATE MEMBER CURRENTCUBE.[MEASURES].[SensitivityChangeFromPreviousDay]

AS Case

// Test for current coordinate being on (All) member.

When [BusinessDate].[BUSINESSDATE].CurrentMember.Level Is [BusinessDate].[BUSINESSDATE].[All]

Then "N/A"

Else

(

// Calculate the difference between the current date and the previous date
// for the GBPEQUIV_SENSITIVITIES measure

(

[BusinessDate].[BUSINESSDATE].CurrentMember, [Measures].[GBPEQUIV-SENSITIVIES]

)

-

(

ParallelPeriod

([BusinessDate].[BUSINESSDATE].CurrentMember.Level,

-1,

[BusinessDate].[BUSINESSDATE].CurrentMember

),

[Measures].[GBPEQUIV-SENSITIVIES]

)

)

End,

VISIBLE = 1;

|||

I agree it is not full debugging in the same sense as debugging with C++ or other programming languages. You can get details of the error by pointing cursor to it, or doing double click on the cell with error. Perhaps if you will provide both the query and the error, the forum will be able to help.

It looks like you are trying to do Previous Day calculation, and there are several inefficiencies and potential correctness problems with your expression. You can take a look at this blog for more details about how to do it better: http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx

|||

Thanks for that Mosha.. you're a bit of a superstar on the quiet!

Actually, I did build the code from an example in the Fast Track MDX book... but this article has so much useful info it hurts!

|||

Ok.. found the error.. I was using a member expression as opposed to a member expression. Surely this is a basic syntax error and should be caught by the suntax checker?

BTW: There is not one mention that I can find in BOL to debugging calculated members....

|||

> a member expression as opposed to a member expression. Surely this is a basic syntax error and should be caught by the suntax checker?

Can you please point out the exact location of the error.