CODEWITHSUNDEEP
ssasmdxolap-cube
yeahthisisrob
yeahthisisrob

Reputation: 81

MDX OLAP Cube Query Optimization

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]

Upvotes: 0

Views: 161

Answers (1)

GregGalloway
GregGalloway

Reputation: 11625

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.

Upvotes: 2

Related Questions