Slow MDX Custom Distinct Count Formula

Question

I have a question related to creating a (more efficient) custom Distinct Count Measure using MDX.

Background

My cube has several long many to many relationship chains between Facts and Dimensions and it is important for me to be able to track which members in certain Dimensions do and do not relate to other Dimensions. As such, I have created a "Not Related" record in each of my dimension tables and set those records' ID values to -1. Then in my intermediate mapping fact tables I use the -1 ID to connect to these "Not Related" records.

The issue arises when I try to run a normal out-of-the-box distinct count on any field where the -1 members are present. In the case that a -1 member exists, the distinct count measure will return a result of 1 more than the true answer.

To solve this issue I have written the following MDX:

CREATE MEMBER CURRENTCUBE.[Measures].[Provider DCount]
 AS

//Oddly enough MDX seems to require that the PID (Provider ID) field be different from both the     linking field and the user sliceable field.

SUM( [Providers].[PID Used For MDX].Children , 
//Don't count the 'No Related Record' item. 

IIF( NOT([Providers].[PID Used For MDX].CURRENTMEMBER IS [Providers].[PID Used For MDX].&[-1]) 
   //For some reason this seems to be necessary to calculate the Unknown Member correctly.
   //The "Regular Provider DCount Measure" below is the out-of-the-box, non-MDX measure built off the same field, and is not shown in the final output.

   AND [Measures].[Regular Provider DCount Measure] > 0 , 1 , NULL )  
), 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Distinct Count Measures' ;

The Issue

This MDX works and always shows the correct answer (yeah!), but it is EXTREMELY slow when users start pulling Pivot Tables with more than a few hundred cells that use this measure. For less than 100 cells, the results are nearly instantaneously. For a few thousand cells (which is not uncommon at all), the results could take up to an hour to resolve (uggghhh!).

Can anyone help show me how to write a more efficient MDX formula to accomplish this task? Your help would be GREATLY appreciated!!

Jon Oakdale

[email protected]

Jon

Alex Peshik Alex Peshik · Accepted Answer · 2015-01-16T12:25:00

You can use predefined scope to nullify all unnecessary (-1) members and than create your measure.

SCOPE ([Providers].[PID Used For MDX].&[-1]
      ,[Measures].[Regular Provider DCount Measure]);
THIS = NULL;
END SCOPE;

CREATE MEMBER CURRENTCUBE.[Measures].[Provider DCount]
 AS
SUM([Providers].[PID Used For MDX].Children
   ,[Measures].[Regular Provider DCount Measure]), 
VISIBLE = 1;

By the way, I used in my tests [Providers].[PID Used For MDX].[All].Children construction since don't know, what is dimension / hierarchy / ALL-level in your case. It seems like [PID Used For MDX] is ALL-level and [Providers] is name of dimension and hierarchy, and HierarchyUniqueName is set to Hide.

Slow MDX Custom Distinct Count Formula

1 Answers