SQLBI - Marco Russo : SSAS SP2

Strange bug in SSAS 2005

Marco Russo (SQLBI) — Sun, 22 Apr 2007 05:21:00 GMT

I have been notified of this strange bug in Analysis Services 2005 that is easily reproducable.

******************************************************************
PLEASE DO NOT TRY THIS TEST ON A PRODUCTION SERVER! IT HANGS ANALYSIS SERVICES!!!
******************************************************************

Open SQL Server Management Studio
Browse the Adventure Works cube
Put Promotions/Promotions hierarchy on the filter area (no measures in the data area)
Expand Promotions and uncheck "Customer"
Drag Promotions hierarchy from filter to rows
Drill the "Reseller" member - at this point you may observe a long wait but the worst is that msmdsrv.exe process on the server (it's the Analysis Services service) is consuming all available virtual memory - you need to restart Analysis Services service at this point

I tested it on a SP2 server.

First of all, since it is a "Denial of Service" for Analysis Service, it is best to remove the Adventure Works sample from a production server - but you never put sample databases on production server, right? :-)

I hadn't time to test it more to understand what is the real issue here to understand what is the impact on other databases. The strange thing is that if you do not move the Promotions hierarchy on the filter area before, the problem do not arises.

SSAS 2005 SP2 breaks Excel calculated member selection

Marco Russo (SQLBI) — Wed, 07 Mar 2007 20:25:00 GMT

After many tries, today I came to the conclusion that Analysis Services 2005 SP2 breaks the Excel 2007 calculated member selection feature. I already talked about this issue here, but today I found the way to reproduce the error.

This is a scenario that works well:

Analysis Services 2005 SP1 + hotfixes (build 2153)
Excel 2007 RTM
Cube with several calculated members on a hierarchy or attribute (not on measures)
When you build a pivot table with Excel, you can freely select single calculated members from the hierarchy or attribute.

Adding Analysis Services 2005 SP2 even only on the server, you get all the calculated members of the hierarchy or attribute selected, and they are all always visible.

In my opinion, this is a breaking change of SP2. I don't see any reason for this behavior. Remember, is is suffice to update the server to break calculated member selection on any Excel 2007 client, regardless of the client component version you use.

I posted a bug here: please vote the bug to get an higher rating if you think (like me) that this requires a promptly response from Microsoft.

UPDATE February 20, 2010: SQL Server 2008 R2 and Excel 2010 have solved the issue - you need both. Chris Webb posted a description of the fix.