Here’s an issue that I’ve encountered many, many times over the years on the newsgroup and the SSAS MSDN Forum but which, for some reason, I’ve never blogged about until now. It happens from time to time that when people are developing a cube they find, mysteriously, that all the data has disappeared from it; however, there is data present in the source database and there are no key errors while processing (see here for a recent example). What’s going on?
In almost all cases the cause is that the Calculate statement at the beginning of the cube’s MDX Script has been deleted or commented out by accident when editing other calculations. To simulate this problem, open up the Adventure Works project and check to see that you can browse the cube and see data in there. Assuming you can, then go to the Calculations tab in the Cube Editor, make sure you’re in Script View and you’ll see something like the following:
The first statement in the MDX Script of every cube should be the Calculate statement, shown above. It’s a bit of a hangover from functionality that I remember from beta versions of SSAS 2005 – you could do some interesting things with a Calculate statement back then but the functionality in question got dropped before RTM. It nonetheless still has to be present though, because when SSAS encounters it when the MDX Script is evaluated it triggers the aggregation of data in all the real measures of the cube up from the very lowest level of detail up to the highest.
If you delete it or comment it out, like so:
…then, when you deploy and browse the cube, no aggregation will take place for the measures in the cube. It will look as if there’s no data in the cube at all but that’s not true: if you browse down to the very lowest level of every dimension in a given measure group, you’ll find that there’s data present. For example in the Adventure Works cube the Exchange Rates measure group is dimensioned by the Date and Destination Currency dimensions and if you browse the cube after commenting out the Calculate statement and look at the Average Rate measure you’ll see no values at first (I’ve got the Show Empty Cells option turned on here):
…but if you make sure you’re looking at data from the Date hierarchy of the Date dimension, and the Destination Currency Code hierarchy of the Destination Currency dimension, the two key attributes of the dimensions, you’ll see values are in fact present:
So the moral of this tale is: be careful not to delete or comment out your Calculate statement! After all there’s a good reason why the following warning is put before it on every new cube:
/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
[Incidentally, I would argue that the last line here is dangerous – I’ve never found a good reason to edit or delete the Calculate statement or even put MDX Script statements before it]
Thanks, i had this problem a few days ago and if i had seen your post before it would have been a great help to solve the problem, nice post!
Very good to know!! Thanks for sharing.
Thanks, Chris, I should read your blog earlier.
Thanks for share, it makes me crazy…
Tanks a lot !! it was helpfull
I have a reason to put a calculated measure before the CALCULATE statement:
It’s a unit price (Amount/Units).
I use a Dimension Calculated Member to calculate the %increment from previous year. But if the measure is placed after the CALCULATE then the %incremente of the Amount an the %increment of Units are calculated before and the result has no meaning.
Hmm, I don’t think I understand exactly what you’re doing here, but I still think there’s no need to define calculations before the Calculate statement. You can have complete control over how calculations aggregate up by using scoped assignments, for example as used in this blog post: http://cwebbbi.wordpress.com/2013/05/29/aggregating-the-result-of-an-mdx-calculation-using-scoped-assignments/
Thank you for your reply, I try to explain it better.
SITUATION:
=========
I have a simple Period dimension, with the members: “Current year (Y0)” and “Previous year (Y-1)”, and then the calculated member %Var = (Y0 – Y-1 ) / Y-1
Now, if we want to calculate the variation of the [Units], the formula is:
([Units], [Period].[%var])
Same for the variation of the [Amount]:
([Amount], [Period].[%var])
But, If i do the same for calculating the variation [Price] (which is Amount / Units) it doesn’t work, because Amount and Units are already variations!!
SOLUTION
========
A solution would be a Scope instruction, at the the end of the MDX Script, where I write again the value of the cell [%var]
But I found more elegant (maybe I’m wrong) to simply duplicate the CALCULATE statement, before the default one,
In the previous CALCULATE I define the [Price] measure, in the second CALCULATE statement I define all other measures.
PROBLEM
========
I don’t know if I may face any side consequences…..
Thank you,
Carlo
Probably the most elegant solution would be to scope the %var calculation so it only covers the Units and Amount measures in the first place, I think.
You are a saver of lives, cubes and sanity – until I stumbled upon this I was a frustrated mass of frazzled nerves!
Thanks a lot for you Post, i accidentally deleted calculate along with other calculated members, then i was wondering why data is disappeared when i browse the cube, saw ur post and fixed it. Thank you.
slightly different issue, i was using the currency conversion functionality where i had to set the currency dim as a type currency and i had a default member of GBP. some measures were only showing when the currency was present in the data or where there were GBP values. We scrapped the currency conversion and removed the dimension type currency and set it back to a regular type dim and the default member we removed, however it doesn’t seem to be removing the previous behaviour from the cube. It only displays data still when you drag the currency dim. We have re processed the dims and measure groups in full yet the issue is not going away? Any ideas?
Did you delete the code created by the wizard on the Calculations tab? Did you check the settings on the Dimension Usage tab are the same as they used to be?
Wow.. Thank you so much. This tiny tidbit was driving me crazy for days. For some reason I cannot remember, I deleted the the “Calculate” from the script view when making a calculation.
Thank you so much!
Very very helpful.
I just couldn’t understand what went wrong.
Great post 🙂
Great! its very helpful, I was bogged into this issue and was lost what to do. Thanks, this blog showed me the the path.
Thanks, this post was a great helpful!!