Marco's Blog

Marco,

Good to have you back in blog scene. Probably it has everything to do with hierarchies. Area must be a level within project. I will dig in this later and give you some result in the morning.

Marco,

I found a solution for your problem, but before I'll give it you I'll try to describe why your MDX query returns multiple areas.

In the Data Warehouse database we can exactly see which entities depends on the Area dimension. Area depends on itself for creating a hierarchy and furthermore “Current Work Item” and “Work Item History” both fact tables uses Area too. An Area isn’t stored at the “Work Item” entity, since this entity must behave as a dimension table in SSAS.

Entities referring to itself will create an unconditional amount of levels. e.g. If I create Area 0, Area 1, Area 1.1, Area 1.1.1, Area 2, I’ve also created 3 levels in the cube (Level 0, Level 1, Level 2). This can go on and on. Open the TFSWarehouse Cube in Analysis Server and check how many levels you’ve created so far. The first level is [Area] and every sub-level has the following format [Area #]. If a Work Item was related to Area 1.1.1 and I used your query, it would return 3 records (Area 1, Area 1.1 and Area 1.1.1).

Now back to your Query

This part is causing the problem:

DESCENDANTS([Area].[Parent_ID].[Area].ALLMEMBERS)

• DESCENDANTS means every related object in a lower level must be shown.

• [Area] Dimension

• [Parent_ID] Attribute

• [Area] Level

You could replace the level in your case by e.g. [Area 1], this will result in one result for this query, but in another query it may not. So it’s better to find the lowest leaves of all your descendants by adding LEAVES as an option to DESCENDANTS function.

DESCENDANTS([Area].[Parent_ID].[Area].ALLMEMBERS,,LEAVES)

In simple scenarios Descendants(,,LEAVES) is equivalent to returning the members from the last level of the hierarchy, i.e. hierarchy.Levels.Members(hierarchy.Levels.Count-1),

For more info check the following link about MDX Function Reference (MDX). msdn2.microsoft.com/.../ms145970(SQL.90).aspx

Good luck,

Mike

bloggingabout.net/.../mglaser

Ps. In the next coming months I will spend some posts on the TFS data model, because there’s so much to talk about.