SQLBI - Marco Russo : Analysis Services

Microsoft doesn't play the (traditional) BI client game

Marco Russo (SQLBI) — Mon, 04 Jan 2010 23:31:00 GMT

I know, the title is provocative. It's intentional. I'd like to get feedback about my opinion. My point is simple (but not so short!). PowerPivot (which, you know, I like) and self-service BI are the new buzzwords for BI in Redmond. However, there are...(read more)

Gemini is now PowerPivot

Marco Russo (SQLBI) — Fri, 23 Oct 2009 00:59:27 GMT

The project codenamed Gemini has an official name now: PowerPivot . The complete name should be “PowerPivot for Excel 2010”, but PowerPivot is simpler. I didn’t like that name yesterday, but today it seems better. Microsoft announced this name at the...(read more)

The reviews of Expert Cube Development book

Marco Russo (SQLBI) — Fri, 11 Sep 2009 23:27:00 GMT

I already talked about the Expert Cube Development with Microsoft SQL Server 2008 Analysis Services book I wrote with Chris and Alberto . We found several posts around the blogosphere and I'd like to keep track of them - I'll try to update this post whenever...(read more)

Possible bug with DirectSlice and MeasureExpression

Marco Russo (SQLBI) — Wed, 27 May 2009 07:41:00 GMT

We (I found the issue, Chris created the repro) detected a possible bug using DirectSlice and MeasureExpression.
We just posted it here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=455732

The issue could be very dangerous resulting in wrong query results on cells affected by DirectSlice property and MeasureExpression, depending on the order of the MDX query sent to Analysis Services. We included a complete repro to the bug posted. This behaviour has been tested on both SSAS 2008 and SSAS 2008 SP1.
What I would like to understand is how to use DirectSlice without worrying about this issue...

Please vote the bug if you agree it is dangerouse. I would like to get some feedback from MS too...

UPDATE October 16th, 2009: the bug is not a bug, it's by design - explanation on this post.

I will be at European PASS Conference 2009

Marco Russo (SQLBI) — Wed, 15 Apr 2009 15:33:00 GMT

Even this year I will be a speaker at the European PASS Conference 2009 (April 22-24, 2009 - Neuss, Germany).

Last year I had a session discussing the Many-to-Many Revolution paper.
This year I'm excited to introduce the SQLBI Methodology joining Alberto Ferrari on the stage. We got a lot of feedback until now, but this is the first time we will have the opportunity to discuss it in front of a broader audience (we made the same presentation some weeks ago at the Italian Microsoft SQL Server & Business Intelligence Conference 2009).

If you will attend the conference, stop us and say hello! It's always nice giving a face to a name. See you in Germany!

Drillthrough improvements: call for feedback!

Marco Russo (SQLBI) — Wed, 21 Jan 2009 10:34:38 GMT

We all like Analysis Services, but when it comes to drillthrough, there are a number of issue that we like to be fixed. I added them in a feedback item on Connect – so please vote it and give to drillthrough a chance to improve in future releases!

Here is the list of the required improvements I would like to see:

Enable editing of drillthrough columns order. They can be arranged in any order you want inside the cube XML file, but editor in BIDS forces you to put all the attributes of a dimension near each other, without allowing to change the order of columns of the same dimension. Moreover, you cannot move dimensions up and down.
Enable use of properties in drillthrough and don’t limit the drillthrough only to those attributes with AttributeHierarchyEnabled set to true. I don’t know if there is a structural problem, to me it seems more like a semantic one.
Enable use of drillthrough on calculated members. In my opinion, who write calculated members has the knowledge to write a tuple describing the subset of the cube that you want to drillthrough. A dynamic MDX expression evaluated for each drillthrough request (just like the dynamic MDX caption) would be the best.
Fix behavior of drillthrough with semi-additive measures (or offer a way to customize it - see drillthrough on calculated members). Drill-through on semi-additive measure returns rows that are not considered in the value displayed. For example, a LastNonEmpty of a year returns all the year rows instead of the last day/month available. Best option, in my opinion, is to customize drillthrough for each measure if required, just as suggested for calculated members.
Optimize drillthrough on ROLAP dimension. If a drillthrough uses attributes from a ROLAP dimension, there is a SELECT DISTINCT over the whole dimension table if it has a regular relationship with the measure group, followed by a complex SQL statement which ends in filtering all the fact table. If the ROLAP dimension has a fact relationship (imagine a degenerate dimension with data as Order Number), the SELECT DISTINCT is not executed, but there is still a complex SQL query. When we have a surrogate key used as a primary key in the fact table (which is the case in most of data marts I’ve seen on SQL Server), it would be way faster getting the set of ID’s obtained by the MOLAP measure group and filtering the fact table with them.

Issues executing a DRILLTHROUGH with a ROLAP dimension

Marco Russo (SQLBI) — Sat, 17 Jan 2009 11:21:30 GMT

I recently encountered an issue using DRILLTHROUGH with ROLAP dimension and I wasn’t able to find any good information making searches about it, so I think it’s a good idea writing a few notes. Probably I will write a more complete on the DRILLTHROUGH issue later, but there is a specific issue (a ~~bug~~ feature by design, probably…) that can be hard to resolve.

Let’s start talking about DRILLTHROUGH. If you have some degenerate dimension (attributes in the fact table that are not measures) that you want to be part of a drillthrough action, you can add a dimension with these attributes, making it a fact dimension (or a regular one – a discussion of what is best in a future post). Now, when you process the database, this dimension is copied into the MOLAP storage, just because (by default) a dimension has the StorageMode property set to MOLAP. It works, but it requires some heavy SELECT DISTINCT on your fact table. Not a good idea with large volume of data.

At this point, changing the StorageMode setting to ROLAP might seem a good idea. In SSAS 2005/8 you can define (with the Enterprise version) that a dimension can be ROLAP even if the measure groups are MOLAP. You should pay the cost of SELECTs to the fact table only when a drillthrough action is required.

After changing the StorageMode setting, you have to reprocess the dimension and the cube. Making a drillthrough query, you might encounter an error. With Excel 2007, this error sounds like this message:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘dbo_FactInternetSales’, Column: ‘SalesOrderNumber’, Value: ‘SO43697’. The attribute is ‘Sales Order Number’.

The specific name of table, column value and attribute may very depending on the cube you are using. I’m getting this error with Adventure Works DW 2008 sample (with a modified setting, as you will read later).

If you execute an equivalent drillthrough query in SQL Server Management Studio, like this:

DRILLTHROUGH
Select ([Measures].[Internet Order Quantity],[Date].[Calendar].[Calendar Quarter].&[2001]&[3]) on 0
From [Adventure Works]

you end up in the same error, even if the SSMS console only says in the messages panel:

Executing the query ...

Server: The operation has been cancelled.

However, you can see the same error displayed by Excel by tracing the analysis services activity using SQL Server Profiler.

Well, what is this issue? The exactly same query, with the same data, works fine if the dimension storage is MOLAP. In reality, if you try the original Adventure Works DW 2008 sample, it works fine. The setting I had in a dimension of another project that produced the issue is the ErrorConfiguration/KeyDuplicate property of the dimension. If it is set to “Ignore”, like the original sample, the drillthrough works well in both ROLAP and MOLAP dimension storage setting. But if you set the ErrorConfiguration/KeyDuplicate property of the dimension to “ReportAndStop”, the drillthrough works well when it is set to MOLAP, but it fails with the error I described before when it is set to ROLAP.

I posted this behavior on the Connect site with FeedbackID 400702. But after several hours spent on this issue, I hope to save time to other developers who might encounter the same issue again.

The obvious workaround is to set the ErrorConfiguration/KeyDuplicate dimension property to Ignore. However, I hope this will be explained in a better way by MS.

Gemini and SSAS explained

Marco Russo (SQLBI) — Mon, 06 Oct 2008 22:20:00 GMT

I got some more information about Gemini. As Mosha said, it is Analysis Services. As you know, SSAS has always been available also as a client tool, using the same engine of the server product to create local cubes. The Kilimanjaro release will add several features to this engine that, integrated with the client part of Gemini (which allows end users to define their own model), will give high speed performance to cubes which are stored in-memory and without any pre-calculated aggregation.

According to Ashvini Sharma, these new features might not be available to traditional SSAS developers in the Kilimanjaro release. However, cubes created from end-users will expose all SSAS metadata as usual. For this reason, these cubes will be immediately available to any SSAS-enabled client.

Now, it is questionable to call these entities cubes, since (but this is my speculation) they could have a design very unusual if compared to traditional cubes.

Anyway, in the long term, all these new features (which have still not be disclosed in detail) will be available at any level. Just in the first release, the server instance of SSAS will be used by Excel Services. When you create a model into Excel, the local engine will be used. When the same spreadsheet will be published to SharePoint, the Excel Services engine will use an instance of SSAS (but may be more than one instance on more servers) to make the calculation on these data.

Unfortunately, not all information about internal details can be disclosed now. However, it seems that SSAS will have a bright future.

MDX best practices analysis using MDX Studio

Marco Russo (SQLBI) — Sun, 20 Jul 2008 10:19:05 GMT

Mosha released a new version of MDX Studio (0.4), which offers an "MDX Analysis" function that suggest best practices for a given MDX query.

I tried the tool with some MDX query and it offers very good suggestion (when possible with link to further information about the issue) for both the MDX beginner and the MDX veteran. Sometime, the suggestion cannot be applied because you really want to make a particular "slow" calculation, but in general the approach is pretty good. A further step would the a "pluggable" architecture to extend best practices rules, just as FxCop does. However, chances are that not so many peopla have the time to implement additional rules, so it could be a useless effort and Mosha's time would be better spent on extending existing rules in the future.

Many-to-Many Dimensions: Query Performance Optimization Techniques

Marco Russo (SQLBI) — Sat, 22 Dec 2007 14:51:02 GMT

More than one year ago I published a paper about design patterns for many-to-many dimension relationships. Since then, I built other models and I got more experience about possible performance optimizations. Unfortunately, I still hadn't time to write about it and this topic requires verbose document and analysis to be reproducible and understandable by everyone. Luckily, now there is a white paper (Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques) downloadable from Microsoft site that explore this world and offers an interesting set of scenarios and possible optimizations, with numbers that explain when, what and how you can expect to optimize a model based on one or more many-to-many dimension relationships.

I already read the document and it is pretty good. Unfortunately, in the real world there are still complex scenarios that cannot be optimized with the techniques used here. I hope that in future versions the SSAS engine will provide some more advanced optimizations for similar cases. One of the first step could be simply automating the optimizations made "by hand" like the Matrix Relationship Optimization shown in the paper. Another would be the simplification for defining efficient aggregations when many-to-many relationships are involved (now you could create a lot of aggregations that are unused when M2M are involved, and sometimes users query a cube only using M2M relationships - you have to tune the aggregations manually).

DefaultMember, subcubes and non-aggregatable attributes

Marco Russo (SQLBI) — Mon, 01 Oct 2007 21:35:41 GMT

Today I discovered that DefaultMember might result in a member other thant the default dimension member.

Reading documentation, I got the idea that DefaultMember would be always the default member defined for a dimension into a cube. In reality, the default member could change into a subcube that excludes the original default member from a calculation. This is important because writing MDX Scripts you cannot anticipate any possible use of your cube... and you should carefully consider when DefaultMember is the real appropriate syntax on a case-by-case basis. You have to be particularly careful whenever you use DefaultMember on a non-aggregatable attribute.

Let's look an example.

In Adventure Works you can write the following query.

WITH MEMBER Measures.DiffActual 
     AS (Scenario.Scenario.CurrentMember, Measures.Amount) 
        - (Scenario.Scenario.DefaultMember, Measures.Amount)
SELECT 
{ [Scenario].[Scenario].[Forecast], 
  [Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM [Adventure Works]

This query provides this result:

I'm not interested in the real meaning of this query. This is only an excuse to see the DefaultMember in action against a non-aggregatable attribute. The DiffActual calculated member has a formula that use Scenario.Scenario.DefaultMember. The default member for Scenario.Scenario attribute is Actual. The DiffActual row shows the difference between the scenario in column and the Actual scenario.

Now, if we encapsulate the previous query into a subquery that apparently should return the same members, we can write this query.

WITH MEMBER Measures.DiffActual 
     AS (Scenario.Scenario.CurrentMember, Measures.Amount) 
        - (Scenario.Scenario.DefaultMember, Measures.Amount)
SELECT 
{ [Scenario].[Scenario].[Forecast], 
  [Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM 
  (SELECT 
    { [Scenario].[Scenario].[Forecast], 
      [Scenario].[Scenario].[Budget] } ON COLUMNS
FROM [Adventure Works])

Before executing this query, what is the expected result? First time I thought "it should be the same", but in reality it is different, like shown here:

What happened? Simply, the SSAS calculation engine needs a default member included in the "context" - when you use a subquery, you are defining a subset of the cube space that must have an existing default member for each attribute - if a default member is outside of the subcube, it is replaced by another member of the same attribute.

In the case we are exmining, the default member of the subcube becomes Forecast. This default member depends on the order of members of the same attribute used in the subcube. If we invert Forecast and Budget members only in the subcube, the default member becomes Forecast instead of Budget, changing the calculated member result (based on DefaultMember syntax).

WITH MEMBER Measures.DiffActual 
     AS (Scenario.Scenario.CurrentMember, Measures.Amount) 
        - (Scenario.Scenario.DefaultMember, Measures.Amount)
SELECT 
{ [Scenario].[Scenario].[Forecast], 
  [Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM 
  (SELECT 
    { [Scenario].[Scenario].[Budget], 
      [Scenario].[Scenario].[Forecast] } ON COLUMNS
FROM [Adventure Works])

This is the result for the query above:

To avoid this issue you can use a specific member instead of the keyword DefaultMember. In our example, the solution is:

WITH MEMBER Measures.DiffActual 
     AS (Scenario.Scenario.CurrentMember, Measures.Amount) 
        - (Scenario.Scenario.[Actual], Measures.Amount)
SELECT 
{ [Scenario].[Scenario].[Forecast], 
  [Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM [Adventure Works]

With this syntax, any use of this query as a subcube in another query will not have the side effects we have seen before.

Why this is so important? Simply because Excel 2007, often used as a client by end users, makes heavy use of subqueries in MDX generated by PivotTable. I had to substitute all DefaultMember in MDX Scripts with well-known member names (this also affects my DateTool dimension, which I will update soon with this and other improvements).

Final note: a special thanks to Mosha Pasumansky for illuminating me on hidden secrets of DefaultMember. Mosha also said that using the member name of default member (instead of DefaultMember keyword) improves query performance.

DateTool dimension: an alternative Time Intelligence implementation

Marco Russo (SQLBI) — Sun, 02 Sep 2007 13:41:00 GMT

Year-To-Date and Difference-Over-Previos-Year (or Year-Over-Year Growth) are among the most required features of any user. Some OLAP client (like ProClarity) offers features that try to solve this problem client-side, but I don’t like this approach given that you might have a server-side feature doing that (so you don’t discriminate Excel users).

One trivial approach is creating a calculated measure for each combination of measure and date-calculation. While this works, it results in a multiplication of the number of measures, making the cube hard to use. Analysis Services offers a feature called Time Intelligence Wizard that creates some calculated members on dedicated Date attributes. This works in MDX but has several limitations:

· It limits the measures on which the calculation are applied (each time you add a new measure, you need to update the wizard-generated MDX scripts)

· It only applies to selected hierarchies – it doesn’t work, for example, if you cross Month and Years on rows and columns.

· It doesn’t work well with Excel 2007 after you install Analysis Services 2005 SP2 – see my rants here.

While the first two issues can be solved by using the same architectural approach of Time Intelligence Wizard, simply writing a different MDX Scripts, solving the third issue (Excel 2007 compatibility) requires a different architecture. For the sake of Google/MSN Live desperate users, I’m going to describe “how to make calculated members working on non-measures dimension with Analysis Services 2005 SP2 and Excel 2007”. I hope that Microsoft will consider a similar approach on a future version of Time Intelligence Wizard…

You can download the solution I'm going to describe on SQLBI.EU web site, under DateTool Project.

The issue

Let’s start describing the Excel 2007 SP2 issue. I created a small subset of Adventure Works sample cube. Calendar Date Calculations is the wizard-generated attribute. With BIDS browser (built using OWC 11) you can put this dimension on the columns of a pivot table with Calendar hierarchy placed on rows, and you can select any set of members from the Calendar Date Calculations attribute. In this example I unchecked one of the members generated by Time Intelligence Wizard.

With Excel 2007 the same cube is usable only if you enable the “Show calculated members from OLAP server” on the PivotTable Options dialog box (it’s unchecked by default).

Now we can generate a PivotTable similar to the one generated with BIDS browser.

Unfortunately, we cannot select single members from the Calendar Date Calculations attribute. We only have an “all or nothing” option based on the PivotTable Options settings we’ve seen above. This is the resulting Excel 2007 PivotTable.

The solution

I defined a dedicated dimension for time-related calculated members. Each formula has its own “real” members, instead of calculated ones. This solves the Excel 2007 issue at the price to require a dimension process instead of a simple MDX deployment (changing MDX Scripts for new calculated members wouldn’t require cube reprocessing). Since I don’t want a cube space growth, I simply put the dimension in the cube without a relationship with any measure group.

This is the real tricky and most non-intuitive part of the game. The DateTool dimension is seen as a regular dimension by any client, but it can be changed and reprocessed without any need to reprocess any measure group (because it has no relationship!).

The DateTool dimension could contain a single attribute with all time-related calculated members. However, I prefer an approach that uses two sets of different calculated members, which are orthogonal and might be crossed together. For example, I would put Year-To-Date calculation in one attribute and Year-Over-Year Growth in another one, so that I can obtain the Year-Over-Year Growth of a Year-To-Date calculation without the need to create a dedicated calculated member. In other words, I use these two sets to separate aggregations from comparisons formulas.

In practice, I would need two independent dimensions, and I do that from a relational point of view. I define two views in a separate schema on my data source (I could also use a separate Data Source View with self-contained named queries, but I prefer using relational views as a general way to decouple relational schema from multidimensional one – reasons for this would fill a whole dedicated article).

CREATE SCHEMA DateTool
GO

CREATE VIEW [DateTool].[DateAggregation] AS
SELECT 0 AS ID_Aggregation, 'Regular' AS Aggregation
UNION ALL
SELECT 1 AS ID_Aggregation, 'Year To Date' AS Aggregation
UNION ALL
SELECT 2 AS ID_Aggregation, 'Last 12 Months' AS Aggregation
UNION ALL
SELECT 3 AS ID_Aggregation, 'Total Current Year' AS Aggregation
GO

CREATE VIEW [DateTool].[DateComparison] AS
SELECT 0 AS ID_Comparison, 'Regular' AS Comparison
UNION ALL
SELECT 1 AS ID_Comparison, 'Previous Year' AS Comparison
UNION ALL
SELECT 2 AS ID_Comparison, 'Diff. Over Previous Year' AS Comparison
UNION ALL
SELECT 3 AS ID_Comparison, 'Diff. % Over Previous Year' AS Comparison
GO

Since I want to build a single dimension with two attributes, I create another view to build a junk dimension with the desired cardinality.

CREATE VIEW [DateTool].[DateTool] AS
SELECT 
    a.ID_Aggregation, 
    s.ID_Comparison, 
    CAST( ID_Comparison AS VARCHAR ) + ' - ' + CAST( ID_Aggregation AS VARCHAR ) AS Description
FROM DateTool.DateAggregation a
CROSS JOIN DateTool.DateComparison s
GO

At this point I can import these views in the Data Source View. I specify Primary Keys and Relationships manually because they can’t be inferred by relational metadata (they don’t exist at that level).

The resulting DateTool dimension is very simple.

At this point there is the necessary MDX Script that solves all DateTool members. This single part would require another post or two and I don’t have time to explain that now, but you can see the complete AdventureWorks based solution downloading it from SQLBI.EU.

The interesting part is the result provided by Excel 2007 with this solution. Here is an example of the Calendar hierarchy crossed with a selection of the calculated members available in the Aggregation attribute of the DataTool dimension.

With this dimension, there are no limitations like those involved by calculated members (see the previous example made using Time Intelligence Wizard). In the previous PivotTable there is a member unchecked, like you can see in the following picture.

Just using this technique you could translate each calculated measure generated by Time Intelligence Wizard into a “regular” dimension fully usable by Excel 2007 users. The presence of two independent attributes makes the user able to build more complex PivotTables like the following one: the highlighted column displays the Year Over Year Growth over the Year To Date value.

Undoubtedly, such a feature is comfortable more to advanced users that to inexperienced one, since often a rename of the resulting report is required to better describe query results.

Finally, one of the limitations of Time Intelligence Wizard is that generated formulas work on a limited part of the cube: only one time hierarchy and only a selected set of measures. With the MDX Scripts I used, there are no similar constraints, making the cube easier to navigate. In the following example, I put years on columns and months on rows, displaying the Year-To-Date value and the difference with the previous year for selected data.

Unfortunately, with this approach we lose compatibility with other clients, like the Browser provided by BIDS and SQL Server Management Studio. A PivotTable like the previous one would not display any data: the different way Excel 2007 and OWC 11 manage dimensions metadata produces this discrepancy between results obtained by difference OLAP clients. I am not sure if this can be solved just changing MDX Scripts definition – I made many tests until now and I described the best compromise I obtained until now, but I’d like to get feedback about issues, possible improvements and eventually alternative architectural solutions.

If you have time to take a look at the solution, please send me your feedback and/or any other comments at marco.russo (at) sqlbi.eu or using the comments for this post.

Red color for negative numbers in Analysis Services 2005

Marco Russo (SQLBI) — Sat, 28 Jul 2007 14:03:35 GMT

Sometimes there is a simple way to solve an issue. For example, if you want to color all measures (including calculated measures) in red when the value is negative, you can simply write:

SCOPE( Measures.AllMembers );
    FORE_COLOR(THIS) = IIF( Measures.CurrentMember < 0, 255, 0);  // 255 = RED
END SCOPE;

MDX Script is so better than classical properties for each measure in these cases...

Beware changing the Attribute Key

Marco Russo (SQLBI) — Mon, 16 Jul 2007 12:58:00 GMT

BI Developer Studio does a lot of automatic changes when you change something. Each dimension is stored in a separate file, but many of the dimension informations are copied into cube files. When you modify a dimension which is already used by one or more cubes in the same project, the editor automatically updates many of these informations.

Each automatic behavior has a cost. In this case, the issue is when you want to change the Attribute Key of a dimension. In reality it shouldn't be a common practice, but if you want to hide surrogate keys from the end users (see this previous post) then you might go in the wrong direction. After many tests, the most confortable way to do this is the following (we assume a Product dimension with an attribute key named Product):

Rename the current Attribute Key, giving an attribute name which will be hidden to end user (i.e. ProductKey)
Create the new Attribute with the desired application key (drag-and-drop it - suppose we have a ProductCode field).
Rename the created Attribute (i.e. ProductCode -> Product)
(optional) if the ProductKey attribute has a NameColumn defined, copy this definition in the new Product attribute (for the NameColumn property)
(optional) set the NameColumn property of ProductKey attribute to None
Move all the attribute relationships from ProductKey attribute to Product attribute, except the relationship between ProductKey and Product
Change the Cardinality property of the Product relationship (under ProductKey attribute) to One (if your application key has the same cardinality as the surrogate key - this is not true in Slowly Changing Dimensions, for example)
If you have user defined hierarchies, change the SourceAttribute from ProductKey to Product where necessary

At this point the attribute key is not changed - you only renamed it, but its internal key is the same and all references from the cube are still valid. If you try other routes (for example, I tried creating a new attribute for the surrogate key only, assigning to it the Attribute Key role in Usage property), related cubes might require a manual correction.

I'll be happy to get feedback on better ways to get this job done.

Strange behavior with KEY0, KEY1, KEYn in SSAS

Marco Russo (SQLBI) — Wed, 04 Jul 2007 11:26:00 GMT

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...):

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

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.

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.