MDX 查询中用户定义的层次结构
继 这个关于计算成员的问题之后，我有 2 个计算成员定义为：
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可以在 MDX 查询中使用这些，如下所示：
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]
这给出的输出如下：
Class-----------------------|-MarketValue
============================|=============
Fixed Income Derivatives | 12345
Fixed Income Non-derivatives| 54321
Fixed Income | 66666
Property | 123
Equity | 987
请注意，前 2 行实际上是第 3 行的组成部分。现在，我可以对读取此数据的客户端代码进行一些魔法，以将其转换为将表放入层次结构中，但是 - 这就是问题 - 我可以使用 MDX 来做到这一点吗？或者我只是让事情复杂化了？如果有必要的话，或者如果我可以在那里定义这个层次结构，我并不反对在多维数据集中进行更改。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
AFAIK，如果不使用客户端代码或更改多维数据集结构，则无法使用 MDX 获得客户端层次结构的结果。尽管您可以找到一种技巧来伪造客户的结果作为层次结构，但我不建议您这样做。
这是我的建议。
如果我理解正确，您想要得到一个不平衡的层次结构树的结果，如下所示。
[资产].[类别]
这种不平衡树只能通过父子层次结构来实现。
您可以尝试通过添加包含
Property和Equity成员的中间级别（或叶级别）来平衡层次结构，也可以创建父子层次结构以实现更深的级别不平衡的树。编辑
这是一篇文章< /a> 关于父子维度。
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]
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
PropertyandEquitymembers or create a parent-child hierarchy to achieve even deeper level unbalanced tree.Edit
Here is an article about parent-child dimensions.
我最终只返回类和子类，并在客户端代码中构建层次结构。
I ended up just returning both Class and Sub Class, and building the hierarchy in client code.
也许可以这样添加计算成员：
Perhaps adding the calculated member this way: