THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Strange behavior with KEY0, KEY1, KEYn in SSAS

I just found that this query has different behavior (with breaking differences in result) with different builds of Analysis Services 2005.

This is the query (I used a bitmap because this query throws an error when I try to put it in text in Community Server blog - if someone want to check this other issue...):

image

With Analysis Services 2005 SP1 (build 9.00.2047) we get the expected result:

image 

When we move to Analysis Services 2005 SP2 (I tested with builds 9.00.3050, 9.00.3054, 9.00.3152) you get a different number of columns: the KEY0 keyword returns one column for each field that is part of a composite key, instead of only the required part.

image

The good news is that the build 9.00.3175 (that you can obtain asking to PSS) returns to the right behavior of build 9.00.2047.

I looked for other information about this bug on the web but I haven't found anything. This feedback seems very similar but it still doesn't work with build 9.00.3175, thus it isn't the same.

UPDATE: this issue is described in KB938585 that is part of KB936305 (that refers to build 3175). Thanks to Andrea for pointing me to the existing articles.

Published Wednesday, July 04, 2007 2:26 PM by Marco Russo (SQLBI)
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AUggetti said:

It is a regression in SP2 and is explained in this kbArticle:

938585 FIX: Additional data columns for ascendant members are returned unexpectedly when you run an MDX statement against an instance of SQL Server 2005 Analysis Services that has SQL Server 2005 Service Pack 2 installed

http://support.microsoft.com/default.aspx?scid=kb;EN-US;938585

As you can see the problem is already fixed with the Cumulative Update 2:

936305 Cumulative update package 2 for SQL Server 2005 Service Pack 2 is available

http://support.microsoft.com/default.aspx?scid=kb;EN-US;936305

50001286  938585  FIX: Additional data columns for ascendant members are returned unexpectedly when you run an MDX statement against an instance of SQL Server 2005 Analysis Services that has SQL Server 2005 Service Pack 2 installed  

Andrea

July 4, 2007 8:40 AM

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement