February 3, 2009 at 5:01 am
Hi
I have an MDX that is performing poorly. I have built aggregation using Usage based optimization but still slow. When I run the query without the WHERE clause it executes in 1 second, but as soon as I add the WHERE clause that filters on a specific clientid and a daterange it takes very long.
Is there anyway of rewriting this to be quicker or building more aggregations down to each clientid.
I have pasted the MDX query below:
WITH
MEMBER [Measures].[METADATA ROW HIERARCHY LEVEL] AS [Books].[Book].CurrentMember.Level.Ordinal
MEMBER [Measures].[METADATA ROW HIERARCHY MEMBER KEY] AS [Books].[Book].CurrentMember.UniqueName
MEMBER [Books].[Book].[METADATA COLUMN HIERARCHY LEVEL] AS [Measures].CurrentMember.Level.Ordinal
MEMBER [SDM Clients].[Dim SDM Client].[ClientFilter] AS '[SDM Clients].[Dim SDM Client].&[336217]'
MEMBER [Measures].[TCV] AS '[Measures].[GBP TCV]'
MEMBER [Measures].[Volume] AS '[Measures].[GBP Volume]'
MEMBER [Origins].[Origin].[OriginFilter] AS Aggregate({[Origin].[CMIS], [Origin].[RBSI], [Origin].[UlsterBank], [Origin].[Sales]})
MEMBER [Deal Dates].[Deal Date].[DateFilter] AS Aggregate( LastPeriods(12,[Deal Dates].[Deal Date].[Month].&[200902]))
SELECT { [Measures].[METADATA ROW HIERARCHY LEVEL]
,[Measures].[METADATA ROW HIERARCHY MEMBER KEY]
,[Measures].[Volume]
,[Measures].[TCV]
} ON COLUMNS,
{
[Books].[Book].[METADATA COLUMN HIERARCHY LEVEL]
,NONEMPTY(TOPCOUNT([Books].[Book].[Book].Members,5, [Measures].[TCV]))
} ON ROWS
FROM Smart
WHERE
(
[SDM Clients].[Dim SDM Client].[ClientFilter]
,[Deal Dates].[Deal Date].[DateFilter]
,[Origins].[Origin].[OriginFilter]
)
February 3, 2009 at 8:40 am
Just to let you, incase some else has a similar performance issue, I have solved this by moving the query filters out from the WITH MEMBER clause and built into the WHERE clause. Makes a huge difference, query time down from 28 to 1 sec.
February 4, 2009 at 7:09 am
Just out of curiosity, were they in the WITH clause mostly to get all your "declarations" out of the way up front, or for a different reason? I'm still learning the ins and outs of MDX, and I fear I always will be!
Rick Todd
February 4, 2009 at 9:45 am
To be honest it is something I picked up from other developers and thought that was the right way to do it which is obviously not. :crazy:
I ran the queries and had a look in profiler at what is happening and it is very different. When it is in the WITH MEMBER clause it does thousands of SubCube Queries and when in the WHERE clause only 3 SubCube queries. Not exactly sure why, maybe someone can explain?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply