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];
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