Advertisement

10.06.2008 at 10:23AM PDT, ID: 23791160 | Points: 500
[x]
Attachment Details

Performanceissue with MDX query

Asked by gosi75 in SQR, Microsoft Visual Studio Express, OLAP

Tags: , , ,

Hi

I have a report written in MDX that takes too long time when executing. As I'm quite a beginner in MDX I'm not sure what would be the best way to shorten the executing time. I post the MDX code if someone have a good succession.

cheers,Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:

WITH MEMBER [Measures].[Budget] AS 'SUM([Scenario].[All Members].[Scenario].&[2],[Measures].[Value])' 
 
MEMBER [Measures].[tempAmount] AS 'SUM([Scenario].[All Members].[Scenario].&[1],[Measures].[Value])'
MEMBER [Measures].[Amount] AS '(Parallelperiod([Time].[Monthly].[Year],1,[Time].[Monthly].currentmember),[tempAmount])' 
 
MEMBER [Measures].[LY Raun] AS '(Parallelperiod([Time].[Monthly].[Year],2,[Time].[Monthly].currentmember),[tempAmount])'
MEMBER [Measures].[Amount YTD] as 'SUM(YTD([Time].[Monthly].CurrentMember), [Amount])' 
MEMBER [Measures].[Budget YTD] as 'SUM(YTD([Time].[Monthly].CurrentMember), [Budget])' 
MEMBER [Measures].[LY YTD] as 'SUM(YTD([Time].[Monthly].CurrentMember), [LY Amount])' 
 
SELECT NON EMPTY { [Measures].[Amount], 
                   [Measures].[Budget],
                   [Measures].[LY Amount],
                   [Measures].[Amount YTD],
                   [Measures].[Budget YTD],
                   [Measures].[LY YTD],                        
 
FILTER(  
 
  {          [Account].[All Members].[Account].ALLMEMBERS * 
             [Account].[MemberName].[MemberName].ALLMEMBERS * 
             [Time].[Monthly].[Month].ALLMEMBERS * 
             [LAUSN].[MemberId].[MemberId].ALLMEMBERS * 
             [LAUSN].[MemberName].[MemberName].ALLMEMBERS 
 
}
,([Measures].[Amount] <> 0) Or ([Measures].[Budget] <> 0) or [Measures].[LY Amount]<>0 or [Measures].[Amount YTD]<>0 or [Measures].[Budget YTD]<>0 or [Measures].[LY YTD]<>0)
 
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM 
 
( SELECT ( STRTOSET(@EntityMemberName, CONSTRAINED) ) ON COLUMNS FROM 
( SELECT ( STRTOSET(@EntitySvidNafn, CONSTRAINED) ) ON COLUMNS FROM 
( SELECT ( STRTOSET(@TimeCalendarMonth, CONSTRAINED) ) ON COLUMNS FROM 
( SELECT ( STRTOSET(@TimeYear, CONSTRAINED) ) ON COLUMNS FROM
(Select ( {[Account].[MemberId].&[5007]:[Account].[MemberId].&[5156]}) ON COLUMNS 
FROM [Sky_planning]))))) 
WHERE ( 
IIF( STRTOSET(@TimeYear, CONSTRAINED).Count = 1, STRTOSET(@TimeYear, CONSTRAINED), [Time].[Year].currentmember ), 
IIF( STRTOSET(@TimeCalendarMonth, CONSTRAINED).Count = 1, STRTOSET(@TimeCalendarMonth, CONSTRAINED), [Time].[Calendar Month].currentmember ) ) 
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
[+][-]10.13.2008 at 05:58AM PDT, ID: 22701947

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20081112-EE-VQP-44 - Hierarchy / EE_QW_2_20070628