THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

SSAS 2008 calculated members still don’t love Excel 2007

I was tempted to start this post by this sentence “Analysis Services 2008 is not a new full release of SSAS” but as you can see, I resisted to it! :)

Ok, it’s a joke. However, how do you interpret the answer “In the next full release of SSAS” when I asked “Does it mean [the fix for the Excel 2007 calculated members selection issue will be solved] ‘in Office 14’, ‘in SSAS 2008’, ‘in Office 2007 SP1’ or ‘in SSAS 2005 SP3’?” (see this item on Connect for further details).

In 2007 (more than one year ago) I assumed that the answer corresponded to the ‘in SSAS 2008’ choice, but now I understand why the answer was slightly different…

Today I retried the calculated members issue I described in a previous post. Long story short: you cannot select individual calculated members on non-measure dimensions when the serve is SSAS 2005 SP2 or later build. Today I tested this behavior with SSAS 2008 and it still produce the same issue with Excel 2007. The reason I didn’t tested this issue before is that I prefer to use my DateTool solution, which is much more flexible and compatible than the one based on calculated members. But the issue is still there if you want to use calculated members for other reasons.

So, what will be the “next full release of SSAS”? I hope to read an answer from a blue-badge :-)

UPDATE: A member of the SSAS team answered in the comments - no good news, but at least I appreciate the transparency!

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.

Published Monday, December 8, 2008 5:28 PM by Marco Russo (SQLBI)



Akshai said:

There is new functionality in AS 2008 to add support for multi-selection of calculated members but it would have needed additional work from Excel to enable it.

And it has unfortunately turned out that even the work that was done is insufficient for Excel's requirements -- we have been attempting to try and make this happen in an Office SP, but due to unforeseen technical limitations of the AS 2008 solution it has not worked out.

Therefore, it will have to wait for another release of AS -- perhaps in Kilimanjaro (although not yet finalized) -- and of Excel.



December 8, 2008 2:11 PM

Chris Webb said:

So... another three or four years of waiting before a serious limitation of Excel as an Analysis Services client tool gets fixed? Yippee! And still no clear direction on Microsoft's client tool strategy either.

December 8, 2008 4:51 PM

James Luetkehoelter said:

Marco (and Chris),

I think you guys have hit the nail on the head. Analysis Services is a great mulit-dimensional database engine. But that is useless without a way to really, REALLY consume the information it can provide. I'm sorry, a pivot table or chart is simplistic. Performance Point seems to have stalled or is being diverted from open analysis to analysis portals to the Dynamics line of business.

It does leave the door open for a ton of 3rd party vendors to create all sorts of client tools, but all will have their own quirks due to the MS-ism of SSAS (meaning unless the 3rd party tool is geared specifically to expose SSAS functionality, we run into the same problems we currently face - I want transparent write-back).

And pushing forward an engine and limited tools is a bit like putting the cart before the horse if you don't already have some concept of data quality/MDM (whatever the term du jour is for it). The thing I worry about more than anything is maintaing authoritative data - yet the primary client tool MS pushes is Excel, giving the user the ability to copy and paste data into other spreadsheets and play with it as they will.

I guess that's why we all have jobs, huh?

December 11, 2008 8:17 PM

Marco Russo (SQLBI) said:

I like the idea that MS want to preserve our jobs! :)


December 12, 2008 1:26 PM

Vikram Sridharan said:

We worked with Microsoft to enable writeback for one of our applications and I distinctly remember the requirement of retrieving the underlying rowset of data getting escalated and finally answered by akshai. It would really be cool to have transparent writeback within Excel 2007 but unfortunately it isn't the case. However there still are not so transparent ways of enabling the same.Have posted about one option here ...

August 22, 2009 9:14 AM

SQLBI - Marco Russo said:

You know, selecting calculated members in Excel is not an option. I talked about this issue more than

February 19, 2010 8:07 PM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement