MDX OLAP Cube Query Optimization

Question

Problem: I'm trying to write a MDX query that will show the first date a member has measure values.

Data obstacles: 1. I don't have access to the data warehouse/source data 2. I can't request any physical calcs or CUBE changes

Looking for: I know this goes against what a CUBE should be doing, but is there any way to achieve this result. I'm running into locking conflicts and general run time issues.

Background: After some trial and error. I have a working query but sadly it's only is practical when filtered for <10 employees. I've tried some looping but there are ~60k employee ids in the cube with each one having 10-20 emp keys (one for each change in their employee info).

//must have values for measure 1 or 2
WITH 
set NE_measures as
{
[Measures].[measure1] ,
[Measures].[measure2]
} 

//first date with measure values for each unique emp key
MEMBER [Measures].[changedate] AS
 Head
 (
 NonEmpty([Dim Date].[Date].[Date].allMEMBERS, NE_measures)
 ).Item(0).Member_Name

SELECT  non empty  {[Measures].[changedate]} ON COLUMNS,
non empty   [Dim Employee].[Emp Key].[Emp Key].allmembers ON ROWS

FROM [Cube]

GregGalloway GregGalloway · Accepted Answer · 2019-01-26T12:48:21

Try this:

MEMBER [Measures].[changedate] AS
Min(
 [Dim Date].[Date].[Date].allMEMBERS, 
 IIF(
  NOT(ISEMPTY([Measures].[measure1]))
  OR NOT(ISEMPTY([Measures].[measure2])),
  [Dim Date].[Date].CurrentMember.MemberValue,
  NULL
 )
);

I’m assuming the KeyColumn or ValueColumn is more likely to sort properly than the name. So if MemberValue doesn’t work then try Member_Key.

The most efficient way of accomplishing this would be to add a date column in the fact table with measure 1 and measure 2 then create a AggregateFunction=Min measure on it. But you said you couldn’t change the cube so I didn’t propose that superior option.

MDX OLAP Cube Query Optimization

1 Answers