Chris Webb's BI Blog

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

Multiple Assignments Bug

leave a comment »

I don’t usually blog about the bugs I find in AS because, well, bugs usually get fixed pretty quickly and are usually only seen in very specific scenarios. However I’m going to make an exception here because this bug is very easy to reproduce, it can lead to inconsistent values being returned and it doesn’t look like it’s going to be fixed any time soon.

Here’s the repro on Adventure Works (I used the Simple version, but I’ll assume it works on the regular version too):

  1. Comment out the MDX Script apart from the Calculate statement
  2. Add the two following assignments:
    ([Measures].[Reseller Sales Amount])=1;
    ([Measures].[Reseller Sales Amount], [Employee].[Employees].&[291])=2;
  3. Run the following query on a cold cache and note that the value returned for the employee Stephen Y. Jiang is 11 (all other non-leaf members show values aggregated up from their leaf members too):
    select [Measures].[Reseller Sales Amount] on 0,
    [Employee].[Employees].members
    on 1
    from [Adventure Works]
  4. Now run the following query which only returns data for Stephen Y. Jiang without clearing the cache and note that the value returned is now 1:
    select [Measures].[Reseller Sales Amount] on 0,
    [Employee].[Employees].&[272]
    on 1
    from [Adventure Works]
  5. Now rerun the query from step 3 and note that the value for Stephen Y. Jiang is still 1.

I have a case with PSS open about this, and from what I can gather the problem is to do with making multiple assignments to the same cell; I’ve only reproed it with a parent/child hierarchy, but I’m told it could appear on a regular hierarchy as well. In my opinion it’s the values returned in Step 3 that are incorrect since an assignment on the member Jae B. Pak should only trigger an aggregation from leaf level for that member’s siblings; the values from Step 4 for Stephen Y. Jiang and the other non-leaf members are right.

The job I’m working on at the moment is a financial application, and the only workarounds seem to be liberal use of FREEZE or making sure that you don’t try to assign values to the same cell more than once, neither of which are exactly feasible in my situation: I’m already dealing with a vast amount of code in the MDX Script and getting either approach to work is next to impossible. I would assume that most financial applications will have parent/child hierarchies (eg on an Accounts dimension) and multiple overlapping assignments in them, so be warned! I wonder if this is going to be a problem for PerformancePoint applications?

Written by Chris Webb

November 22, 2007 at 7:03 pm

Posted in MDX

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,072 other followers

%d bloggers like this: