Combine different hierarchy levels using MDX Query

Question

1.Description

Now I have two mdx query, the only differences is the ON ROWS settings. Here is each query and its results.

Query 1 :

SELECT

NON EMPTY
{
  [PLOwner].[PLOwner].Members
}
ON ROWS
,
NON EMPTY
Crossjoin(
  {
    [InfoType].[InfoType].[Risk_RecoveryJTDTable],
    [InfoType].[InfoType].[Equivalent_Notional],
    [InfoType].[InfoType].[Risk_SPC],
    [InfoType].[InfoType].[Risk_PSM],
    [InfoType].[InfoType].[Risk_PV10],
    [InfoType].[InfoType].[Notional],
    [InfoType].[InfoType].[Notional_IMM],
    [InfoType].[InfoType].[PnL],
    [InfoType].[InfoType].[TomorrowPnL],
    [InfoType].[InfoType].[RollPnL],
    [InfoType].[InfoType].[RollDownPnL],
    [InfoType].[InfoType].[Risk_JTD],
    [InfoType].[InfoType].[Risk_Raw_KC],
    [InfoType].[InfoType].[Risk_RR],
    [InfoType].[InfoType].[FundingPnLWCOF],
    [InfoType].[InfoType].[FundingPnLRR],
    [InfoType].[InfoType].[FundingPnLSHW],
    [InfoType].[InfoType].[FundingPnLBox],
    [InfoType].[InfoType].[FundingPnLInterest]
  }
  ,
  {
    DrillDownLevel([Category].[ALL].[AllMember])
  }
  ,
  {
    DrillDownLevel([Label1].[ALL].[AllMember])
  }
  ,
  {
    DrillDownLevel([IsError].[ALL].[AllMember])
  }
)
ON COLUMNS

FROM
  [UnityRiskCube]

WHERE
(
  [Measures].[Risk.SUM],
  [BusinessGroup].[BusinessGroup].[AeJ Flow Credit],
  [Context].[ContextId].[official:Live]
)

Result 1: (I use Excel to show String[][] result from Pivot)

Result 1-click to see the image

Query 2 :

SELECT

NON EMPTY
Crossjoin(
  {
    [PLOwner].[PLOwner].Members
  }
  ,
  {
    [PLGroup].[PLGroup].Members
  }
)
ON ROWS
,
NON EMPTY
Crossjoin(
  {
    [InfoType].[InfoType].[Risk_RecoveryJTDTable],
    [InfoType].[InfoType].[Equivalent_Notional],
    [InfoType].[InfoType].[Risk_SPC],
    [InfoType].[InfoType].[Risk_PSM],
    [InfoType].[InfoType].[Risk_PV10],
    [InfoType].[InfoType].[Notional],
    [InfoType].[InfoType].[Notional_IMM],
    [InfoType].[InfoType].[PnL],
    [InfoType].[InfoType].[TomorrowPnL],
    [InfoType].[InfoType].[RollPnL],
    [InfoType].[InfoType].[RollDownPnL],
    [InfoType].[InfoType].[Risk_JTD],
    [InfoType].[InfoType].[Risk_Raw_KC],
    [InfoType].[InfoType].[Risk_RR],
    [InfoType].[InfoType].[FundingPnLWCOF],
    [InfoType].[InfoType].[FundingPnLRR],
    [InfoType].[InfoType].[FundingPnLSHW],
    [InfoType].[InfoType].[FundingPnLBox],
    [InfoType].[InfoType].[FundingPnLInterest]
  }
  ,
  {
    DrillDownLevel([Category].[ALL].[AllMember])
  }
  ,
  {
    DrillDownLevel([Label1].[ALL].[AllMember])
  }
  ,
  {
    DrillDownLevel([IsError].[ALL].[AllMember])
  }
)
ON COLUMNS

FROM
  [UnityRiskCube]

WHERE
(
  [Measures].[Risk.SUM],
  [BusinessGroup].[BusinessGroup].[AeJ Flow Credit],
  [Context].[ContextId].[official:Live]
)

Result 2: (I use Excel to show String[][] result from Pivot)

Result 2-click to see the image

2.Question

How can i get these data together using one Mdx query? Thanks a lot.

score 0 · Answer 1 · answered May 17 '18 at 13:11

I think you can use the ALL member of the PLGroup hierarchy to be able to combine - you have no choice in that there will need to be the same dimensionality for both:

SELECT

NON EMPTY
{
  [PLOwner].[PLOwner].Members
 *[PLGroup].[PLGroup].[All] //<< or this may be [PLGroup].[All]
,
  [PLOwner].[PLOwner].Members
 *[PLGroup].[PLGroup].Members
}
ON ROWS
,
NON EMPTY
Crossjoin(
  {
    [InfoType].[InfoType].[Risk_RecoveryJTDTable],
    [InfoType].[InfoType].[Equivalent_Notional],
    [InfoType].[InfoType].[Risk_SPC],
    [InfoType].[InfoType].[Risk_PSM],
    [InfoType].[InfoType].[Risk_PV10],
    [InfoType].[InfoType].[Notional],
    [InfoType].[InfoType].[Notional_IMM],
    [InfoType].[InfoType].[PnL],
    [InfoType].[InfoType].[TomorrowPnL],
    [InfoType].[InfoType].[RollPnL],
    [InfoType].[InfoType].[RollDownPnL],
    [InfoType].[InfoType].[Risk_JTD],
    [InfoType].[InfoType].[Risk_Raw_KC],
    [InfoType].[InfoType].[Risk_RR],
    [InfoType].[InfoType].[FundingPnLWCOF],
    [InfoType].[InfoType].[FundingPnLRR],
    [InfoType].[InfoType].[FundingPnLSHW],
    [InfoType].[InfoType].[FundingPnLBox],
    [InfoType].[InfoType].[FundingPnLInterest]
  }
  ,
  {
    DrillDownLevel([Category].[ALL].[AllMember])
  }
  ,
  {
    DrillDownLevel([Label1].[ALL].[AllMember])
  }
  ,
  {
    DrillDownLevel([IsError].[ALL].[AllMember])
  }
)
ON COLUMNS

FROM
  [UnityRiskCube]

WHERE
(
  [Measures].[Risk.SUM],
  [BusinessGroup].[BusinessGroup].[AeJ Flow Credit],
  [Context].[ContextId].[official:Live]
)

Million thanks!!! i use "[PLGroup].[All]" and results are quite similar with what i want. While i found that the result numbers still have problems, the sum of children is not equals father. Anway, i will figure out it later. Thanks for your idea!!! — Frank.Cui, Jul 05 '18 at 10:12

Combine different hierarchy levels using MDX Query

1 Answers1