CODEWITHSUNDEEP
mdxolap
user467384
user467384

Reputation: 1167

Displaying multiple hierarchy levels in an MDX query

I'm trying to get the following data from a TFS OLAP cube in a single query

[Work Item].[System_ID] | [Work Item].[System_Title] | [Measures].[BaselineWork]
13426                   | Do some work               | 5

Sounds pretty simple huh? That's what I thought too, but having 0 knowledge of OLAP, TFS and MDX has made this pretty daunting.

SSMS Hierchy

So, I can get this...

SELECT
[Measures].[Microsoft_VSTS_Scheduling_BaselineWork] ON COLUMNS,
[Work Item].[System_Id].MEMBERS ON ROWS
FROM [Team System]
WHERE [Work Item].[System_WorkItemType].&[WPS Task]

and this...

SELECT
[Measures].[Microsoft_VSTS_Scheduling_BaselineWork] ON COLUMNS,
[Work Item].[System_Title].MEMBERS ON ROWS
FROM [Team System]
WHERE [Work Item].[System_WorkItemType].&[WPS Task]

but combining the two has got me stumped.

Upvotes: 1

Views: 2469

Answers (1)

Craig
Craig

Reputation: 1337

I think this is what you're after:

SELECT
[Measures].[Microsoft_VSTS_Scheduling_BaselineWork] ON COLUMNS,
[Work Item].[System_Title].MEMBERS * [Work Item].[System_Id].MEMBERS ON ROWS
FROM [Team System]
WHERE [Work Item].[System_WorkItemType].&[WPS Task]

The multiplication is a cross join between the System_Title and System_ID sets. There is more information here

Upvotes: 1

Related Questions