June 9, 2011 at 3:13 am
I have an MDX query that for each subscriber needs to get the maximum value of measures across a a given timeframe
[font="Courier New"]with
member [Max Measure1] as
max( {[Time].[Day].[Day].&[2010]&[11]&[24] : [Time].[Day].[Day].&[2010]&[11]&[29]}, [Measures].[Meas1])
member [Max Measure2] as
max( {[Time].[Day].[Day].&[2010]&[11]&[24] : [Time].[Day].[Day].&[2010]&[11]&[29]}, [Measures].[Meas2])
member [Max Measure3] as
max( {[Time].[Day].[Day].&[2010]&[11]&[24] : [Time].[Day].[Day].&[2010]&[11]&[29]}, [Measures].[Meas3])
member [Max Measure4] as
max( {[Time].[Day].[Day].&[2010]&[11]&[24] : [Time].[Day].[Day].&[2010]&[11]&[29]}, [Measures].[Meas4])
member [Max Measure5] as
max( {[Time].[Day].[Day].&[2010]&[11]&[24] : [Time].[Day].[Day].&[2010]&[11]&[29]}, [Measures].[Meas5])
member [Max Measure5] as
max( {[Time].[Day].[Day].&[2010]&[11]&[24] : [Time].[Day].[Day].&[2010]&[11]&[29]}, [Measures].[Meas6])
member [Max Measure6] as
max( {[Time].[Day].[Day].&[2010]&[11]&[24] : [Time].[Day].[Day].&[2010]&[11]&[29]}, [Measures].[Meas7])
select
{
[Max Measure1]
,[Max Measure2]
,[Max Measure3]
,[Max Measure4]
,[Max Measure5]
,[Max Measure5]
,[Max Measure6]
} on 0,
extract
(
nonempty
(
{ [Subscriber].[Subscriber].[Subscriber] * {[Time].[Day].[Day].&[2010]&[11]&[24] : [Time].[Day].[Day].&[2010]&[11]&[29]} }
, [Measures].[Meas7]
)
, [Subscriber].[Subscriber]
) on 1
from
MyCube;[/font]
This returns 126779 rows to SSMS within 90 seconds
However when the references to
[font="Courier New"]{[Time].[Day].[Day].&[2010]&[11]&[24] : [Time].[Day].[Day].&[2010]&[11]&[29]} }[/font]
are replaced by
[font="Courier New"]with set [Timeframe] as {[Time].[Day].[Day].&[2010]&[11]&[24] : [Time].[Day].[Day].&[2010]&[11]&[29]} }[/font]
Then the query time more than doubles.
I was under the impression that the set syntax was deigned to reduce cut and paste repitition therefore making code morte readable/maintainable.
Can anybody explain the reason for the degrade in performance?
Regards
John
June 14, 2011 at 8:29 am
When you use the literal range in the select statement it limits the cube result in advance of processing the result.
The set is not being defined until later in the process, so in effect the entire cube is being scanned and then limited to the set.
Try adding your literal range in a subselect statement
...on 1 from (select {[date].[2010].[11].[24]:[date].[2010].[11].[29]} on 0 from mycube) with the sets in place for the rest of the query.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply