MDX Calculated Member SubCube

Question

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.

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.

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

Marty

Meff Meff · Accepted Answer · 2013-07-19T13:35:08

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

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

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.

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 Calculated Member SubCube

1 Answers