MDX date range with NON EMPTY clause is not slicing the data for the range

274 views Asked by At

I am working to optimize the query time. But in my cases, case 1 and case 2 are returning the same result. case 1 should return result for a particular data range i.e. [time].[2015].[5].[10] : [time].[2015].[6].[9] instead it is returning only for [time].[2015].[6].[9] as the result of case 2. case 1 returns the correct result i.e. data for the range but only if you remove the non empty clause. removing the non empty clause means it will search the entire data sets which is again taking lots of time. again case 3 returns correct result but operation is even more time then the first approach. Anybody facing such issue or can guide me to for the problem.

CASE 1

WITH 
  MEMBER [Measures].[abc_type] AS 
    [abc].CurrentMember.Properties("abc_type") 
  MEMBER [Measures].[abc_desc] AS 
    [abc].CurrentMember.Properties("abc_desc") 
  MEMBER [Measures].[abc_class] AS 
    [abc].CurrentMember.Properties("abc_class") 
SELECT 
  NON EMPTY 
    {
      [Measures].[abc_type]
     ,[Measures].[abc_desc]
     ,[Measures].[abc_class]
    } ON COLUMNS
 ,NON EMPTY 
    Filter
    (
      {[abc].[abc_id].MEMBERS}
     ,St_contains
      (
        [district].[district_id].[1].Properties("the_geom")
       ,[abc].CurrentMember.Properties("the_geom")
      )
    ) ON ROWS
FROM [analytics_cube]
WHERE 
  [time].[2015].[5].[10] : [time].[2015].[6].[9];

CASE 2

WITH 
  MEMBER [Measures].[abc_type] AS 
    [abc].CurrentMember.Properties("abc_type") 
  MEMBER [Measures].[abc_desc] AS 
    [abc].CurrentMember.Properties("abc_desc") 
  MEMBER [Measures].[abc_class] AS 
    [abc].CurrentMember.Properties("abc_class") 
SELECT 
  NON EMPTY 
    {
      [Measures].[abc_type]
     ,[Measures].[abc_desc]
     ,[Measures].[abc_class]
    } ON COLUMNS
 ,NON EMPTY 
    Filter
    (
      {[abc].[abc_id].MEMBERS}
     ,St_contains
      (
        [district].[district_id].[1].Properties("the_geom")
       ,[abc].CurrentMember.Properties("the_geom")
      )
    ) ON ROWS
FROM [analytics_cube]
WHERE 
  [time].[2015].[6].[9];

CASE 3

WITH 
  MEMBER [Measures].[abc_type] AS 
    [abc].CurrentMember.Properties("abc_type") 
  MEMBER [Measures].[abc_desc] AS 
    [abc].CurrentMember.Properties("abc_desc") 
  MEMBER [Measures].[abc_class] AS 
    [abc].CurrentMember.Properties("abc_class") 
  MEMBER [time].[newtime] AS 
    Aggregate([time].[2015].[5].[10] : [time].[2015].[6].[9]) 
SELECT 
  NON EMPTY 
    {
      [Measures].[abc_type]
     ,[Measures].[abc_desc]
     ,[Measures].[abc_class]
    } ON COLUMNS
 ,NON EMPTY 
    Filter
    (
      {[abc].[abc_id].MEMBERS}
     ,St_contains
      (
        [district].[district_id].[1].Properties("the_geom")
       ,[abc].CurrentMember.Properties("the_geom")
      )
    ) ON ROWS
FROM [analytics_cube]
WHERE 
  [time].[newtime];
1

There are 1 answers

2
Luc On

Ranges of time are always evaluated by Mondrian as a full level scan. It will load all of the members of the given time level and will start iterating over the members until it finds the first bound. It will then create a sublist up until the last member of the range.

There is an enhancement request filed to turn ranges into SQL predicates here.

If you get a chance to test the prototype code, let us know how it works for you