Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Why has all the data in my cube disappeared?

with 15 comments

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:

image

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:

image

…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):

image

…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:

image

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]

Written by Chris Webb

October 20, 2011 at 7:48 pm

Posted in Analysis Services, MDX

15 Responses

Subscribe to comments with RSS.

  1. 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!

    jp

    October 24, 2011 at 12:59 am

  2. Very good to know!! Thanks for sharing.

    Uma

    October 31, 2011 at 11:02 pm

  3. [...] don’t see any data at the moment because the Calculate statement hasn’t been executed yet (see here for more details on what the Calculate statement does). If you hit F10 to step to the next [...]

  4. Thanks, Chris, I should read your blog earlier.

    Eric

    August 7, 2012 at 1:32 am

  5. Thanks for share, it makes me crazy…

    lilysava

    August 28, 2012 at 4:50 am

  6. [...] don’t see any data at the moment because the Calculate statement hasn’t been executed yet (see here for more details on what the Calculate statement does). If you hit F10 to step to the next [...]

  7. […] I hit the semi-panic button a few days ago when my cube data disappeared.  I was working with calculated measures and edited some MDX.  I had made a mistake, deleted the bad code and reprocessed the cube.  No data.  That’s bad.  Reprocessed all my dimensions and the cube again, still no data.  Chris Webb saved the day with this great blog post about the problem:  http://cwebbbi.wordpress.com/2011/10/20/why-has-all-the-data-in-my-cube-disappeared/ […]

  8. Tanks a lot !! it was helpfull

    Andres

    August 16, 2013 at 4:11 pm

  9. 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.

    carlo

    October 11, 2013 at 5:25 pm

    • 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/

      Chris Webb

      October 11, 2013 at 7:30 pm

      • 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

        grigoletti

        October 12, 2013 at 9:01 am

      • 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.

        Chris Webb

        October 13, 2013 at 9:49 pm

  10. […] There are two types of calculation on an SSAS cube: those explicitly defined in the MDX Script (ie those seen on the Calculations tab of the cube editor); and semi-additive measures, unary operators and custom rollups, which are defined in the model itself. This second type of calculation is added to the cube when the Calculate() statement fires, and the more of them there are the longer it takes SSAS to work out where they should be applied in the space of the cube. In my customer’s case there were several large (80000+ members) parent/child hierarchies with unary operators as well as a few semi-additive measures and so this was the reason why Calculate() was so slow. Up to now I had only known that Calculate() triggers the aggregation of data up through the cube, which is why if you delete it the cube seems to contain no data. […]

  11. […] There are two types of calculation on an SSAS cube: those explicitly defined in the MDX Script (ie those seen on the Calculations tab of the cube editor); and semi-additive measures, unary operators and custom rollups, which are defined in the model itself. This second type of calculation is added to the cube when the Calculate() statement fires, and the more of them there are the longer it takes SSAS to work out where they should be applied in the space of the cube. In my customer’s case there were several large (80000+ members) parent/child hierarchies with unary operators as well as a few semi-additive measures and so this was the reason why Calculate() was so slow. Up to now I had only known that Calculate() triggers the aggregation of data up through the cube, which is why if you delete it the cube seems to contain no data. […]


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

Follow

Get every new post delivered to your Inbox.

Join 3,240 other followers

%d bloggers like this: