MDX计算的成员子多维数据集 [英] MDX Calculated Member SubCube

问题描述

我对MDX的这种深度还比较陌生，但这是我的难题.我的目标是使用.Net存储过程实现计算所得成员.计算(XIRR)将基于一组现金流量日期和现金流量金额.理想情况下，这将是我的多维数据集中的计算，可以作为Excel/浏览器用户的度量.

I am relatively new to this depth of MDX, but here is my dilemma. My goal is to implement a calculated member using a .Net Stored Procedure. The calculation (XIRR) will be based on a set of cash flow dates and cash flow amounts. Ideally this would be a calculation in my cube that is available as a measure to Excel/Browser users.

因此，从简单开始，我只是尝试实现自己的COUNT个计算所得的成员/度量(甚至不使用.Net)，以根据当前上下文对给定维度中的成员数进行计数.因此，可以说我有一个带有客户ID关键字的维度客户.假设我的数据库中总共有100个客户.因此Count(Customer.CustomerId.AllMembers)为100.现在，当您开始使用浏览器并说出Customer.CustomerId.& 1，Customer.CustomerId.& 2(客户ID 1和2)上的过滤器时， count计算所得的成员返回2，但它返回的总数为100.我尝试使用存在.我确信有些事情我根本上还不了解.

So to start simple I am just trying to implement my own COUNT calculated member/measure (not even using .Net) to say count the # of members in a given dimensions based on the current context. So lets say I have a dimensions Customer with a Customer Id Key. And let's say there are a total of 100 customers in my database. So Count(Customer.CustomerId.AllMembers) would be 100. Now when you start using the browser and say filter on Customer.CustomerId.&1, Customer.CustomerId.&2 (customer id 1 and 2) I would expect my count calculated member to return 2 but it returns the total 100 count. I have tried using exists. I am sure there is something that I am just fundamentally not understanding yet.

希望这是有道理的，非常感谢对SSAS/MDX和计算有深入了解的人员提供的任何帮助.预先感谢.

Hopefully this makes sense, would hugely appreciate any help from someone that has a good understanding of SSAS/MDX and calculations. Thanks in advance.

马蒂

推荐答案

您可能在这里遇到了一些问题，当我尝试做类似的事情时就遇到了.

You may have some issues here, I did when I tried to do a similar thing.

您计算出的成员不接受客户子选择，这是正常的.从理论上讲，您将创建一个动态集，然后在计算所得的成员中使用该集来强制在已创建过滤器的子多维数据集的上下文中评估维数. Mosha在这里有一篇很好的文章:

Your calculated member is not honouring the client sub-select, which is normal. What in theory you would do is create a dynamic set, and then use that in the calculated member to force the dimension count to be evaluated in the context of the subcube your filters have created. Mosha has a good article here: http://sqlblog.com/blogs/mosha/archive/2007/08/25/mdx-in-katmai-dynamic-named-sets.aspx

所以您最终会得到类似的东西:

So you'd end up with something like:

CREATE DYNAMIC SET CurrentCube.Customers AS
EXISTING(Customer.CustomerId.CHILDREN);

CREATE MEMBER CurrentCube.Measures.CustomerCount AS
Customers.COUNT

现在您真正遇到的问题是SSAS中的错误

Now the real problem you'll have is a bug in SSAS https://connect.microsoft.com/SQLServer/feedback/details/484865/calcuated-member-with-a-reference-to-dynamic-named-set-kills-the-cubes-performance so the code above, which will probably work just fine locally, will kill a production cube. This was an exciting learning experience for me.

看看您能否获得任何解决方法，我无法实现.

See if you can get any of the workarounds to work, I couldn't.

我能够得到想要的东西，但是我必须创建查询范围的动态集作为MDX查询的一部分，而无法将其创建为多维数据集对象:

I was able to get what I wanted, but I had to create query-scoped dynamic sets as part of the MDX query, I wasn't able to create it as a cube object:

WITH DYNAMIC SET Customers AS
EXISTING(Customer.CustomerId.CHILDREN);

MEMBER Measures.CustomerCount AS
Customers.COUNT

SELECT
Measures.CustomerCount 
ON COLUMNS
FROM [Cube]
WHERE Customer.CustomerId.&[1]

让我们知道您的生活吧.

Let us know how you get on.

这篇关于MDX计算的成员子多维数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！