MDX 如何把部分相同的维度对应的measures信息join起来?
0
如何把部分相同的维度对应的measures信息join起来?
现在有两个KPI,一个是Target customers_none,它只和[Organization].[MICS]这一个维度有关系,
还有一个KPi,是Target customers,它和[Organization].[MICS]有关系,另外还和两个维度有关系。
现在想把这两个kpi输出到一个结果集中,应该如何写MDX语句啊?
以下是两个分别取KPI的mdx
现在有两个KPI,一个是Target customers_none,它只和[Organization].[MICS]这一个维度有关系,
还有一个KPi,是Target customers,它和[Organization].[MICS]有关系,另外还和两个维度有关系。
现在想把这两个kpi输出到一个结果集中,应该如何写MDX语句啊?
以下是两个分别取KPI的mdx
MDX1
SELECT NON EMPTY {
[Measures].[Target customers_none]
} ON COLUMNS,
NON EMPTY { ([Dim Orgnization].[Organization].[MICS].ALLMEMBERS ) } ON ROWS
FROM [ECMS DW]
MDX2
SELECT NON EMPTY {
[Measures].[Target customers]
} ON COLUMNS,
NON EMPTY { ([Dim Orgnization].[Organization].[MICS].ALLMEMBERS [i] [Dim Product].[Brand NK].[Brand NK].ALLMEMBERS [/i] [Dim Product Level].[Product Level].[Product Level].ALLMEMBERS ) } ON ROWS
FROM [ECMS DW]
Target customers_none
MICS Target customers_none
PETSH3001 11
PETZJ3017 6
Target customers
MICS prod level Target customers
PETSH3001 S2-D5XA-7 A 3
PETSH3001 S2-D5XA-9 A 1
PETSH3001 S2-D5XA-9 B 3
PETSH3001 S2-D5XA-9 C 4
PETSH3001 S2-D5XA-9 D 3
PETZJ3017 S2-D5XA-9 B 2
PETZJ3017 S2-D5XA-9 C 1
PETZJ3017 S2-D5XA-9 D 3
想要的结果
MICS prod level Target customers Target customers_none
PETSH3001 S2-D5XA-7 A 3 11
PETSH3001 S2-D5XA-9 A 1 11
PETSH3001 S2-D5XA-9 B 3 11
PETSH3001 S2-D5XA-9 C 4 11
PETSH3001 S2-D5XA-9 D 3 11
PETZJ3017 S2-D5XA-9 B 2 6
PETZJ3017 S2-D5XA-9 C 1 6
PETZJ3017 S2-D5XA-9 D 3 6
没有找到相关结果
重要提示:提问者不能发表回复,可以通过评论与回答者沟通,沟通后可以通过编辑功能完善问题描述,以便后续其他人能够更容易理解问题.
5 个回复
felixwon 2014-06-30 回答
赞同来自: redarmy3
SELECT NON EMPTY {
[Measures].[Target customers],[Measures].[Target customers_none]
} ON COLUMNS,
NON EMPTY { ([Dim Orgnization].[Organization].[MICS].ALLMEMBERS [Dim Product].[Brand NK].[Brand NK].ALLMEMBERS [Dim Product Level].[Product Level].[Product Level].ALLMEMBERS ) } ON ROWS
FROM [ECMS DW]
redarmy3 - 一句话介绍 2014-07-01 回答
赞同来自: 梁勇
sELECT ({
[Measures].[Target customers],[Measures].[Target customers_none]
}) ON COLUMNS,
NONEMPTY({[Dim Orgnization].[Organization].[MICS].ALLMEMBERS [Dim Product].[Brand NK].[Brand NK].ALLMEMBERS [Dim Product Level].[Product Level].[Product Level].ALLMEMBERS },[Measures].[Target customers]) ON ROWS
FROM [ECMS DW]
felixwon 2014-07-01 回答
赞同来自: redarmy3
[Measures].[Target customers],[Measures].[Target customers_none]
} ON COLUMNS,
NON EMPTY { filter.[Organization].[MICS].ALLMEMBERS [Dim Product].[Brand NK].[Brand NK].ALLMEMBERS [Dim Product Level].[Product Level].[Product Level].ALLMEMBERS, [b][Measures].[Target customers_none]>0]/b } ON ROWS
FROM [ECMS DW]
redarmy3 - 一句话介绍 2014-07-01 回答
赞同来自:
redarmy3 - 一句话介绍 2014-07-01 回答
赞同来自: