User defined hierarchy in an MDX query

Question

Following on from this question regarding calculating a member I have 2 calculated members defined as:

MEMBER [Asset].[Class].[Fixed Income Derivatives]
AS
AGGREGATE(
    {
       [Asset].[Class].&[Fixed Income],
       [Asset].[Sub Class].&[Derivatives]
    },
    [Measures].CurrentMember
)
MEMBER [Asset].[Class].[Fixed Income Non-derivatives]
AS
AGGREGATE(
    {
      [Asset].[Class].&[Fixed Income],
      EXCEPT([Asset].[Sub Class].[Sub Class],[Asset].[Sub Class].&[Derivatives])
    },
    [Measures].CurrentMember
)

I can use these in an MDX query as follows:

SELECT
  {
   [Measures].[Market Value]
  } ON 0,
  NON EMPTY
  { 
   [Asset].[Class].[Fixed Income Derivatives],
   [Asset].[Class].[Fixed Income Non-derivatives]
   [Asset].[Class].[Class]
  } ON 1
  FROM [Asset]

And this gives me output as follows:

Class-----------------------|-MarketValue
============================|=============
Fixed Income Derivatives    | 12345
Fixed Income Non-derivatives| 54321
Fixed Income                | 66666
Property                    | 123
Equity                      | 987

Note that the first 2 rows are actually constituant parts of Row 3. Now, I can do some magic with the client code which reads this data to turn that table into a hierarchy, but - and here's the question - can I do it using MDX? Or am I just complicating things? Im not adverse to making changes in the cube if necessary, or if I could define this hierarchy there.

Answer 1

AFAIK, you can't use MDX to get a result that is supposed as hierarchy at the client side without some magic with the client code or changing the cube structure. Even though you can find a trick to fake the client the result as an hierarchy i don't suggest you to go that way.

Here is my suggestion. If i understand correctly, you want to get a result that is kind of an unbalanced hierarchy tree that looks like below.

[Asset].[Class]

• [Fixed Income]
  • [Asset].[Class].[Fixed Income Derivatives]
  • [Asset].[Class].[Fixed Income Non-Derivatives]
• Property
• Equity

This kind of unbalanced trees can only be implemented by parent-child hierarchies.

You can either try to balance the hierarchy by adding an intermediate level (or leaf levels) that includes Property and Equity members or create a parent-child hierarchy to achieve even deeper level unbalanced tree.

Edit

Here is an article about parent-child dimensions.

Answer 2

Perhaps adding the calculated member this way:

WITH MEMBER [Asset].[Class].[Fixed Income].[Derivatives] AS ...