Multiple Assignments Bug

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?