Hi, We are using the following MDX query to query TFS Cube. We are getting time out problem on some TFS servers having huge number of work items. We need help in fine tuning the MDX query. Quick points to note – The MDX query seem to be consuming high parsing time The query when executed for 1 day of date on production server it took approx 12 sec to parse After parsing the query, in execution mode the MDQ query consumes lot of time In execution mode, the query takes more than 9 minutes The MDX query is embedded inside a RDL report On production server, SSRS time out is set to 2min MDX Query used in the report WITH MEMBER Measures.[Remaining Work] AS IIF([Measures].[Microsoft_VSTS_Scheduling_RemainingWork]= null or [Measures].[Microsoft_VSTS_Scheduling_RemainingWork]<=0, (IIF([Measures].[Microsoft_VSTS_Scheduling_BaselineWork]= null,0,[Measures].[Microsoft_VSTS_Scheduling_BaselineWork])), [Measures].[Microsoft_VSTS_Scheduling_RemainingWork]) SELECT { Measures.[Remaining Work] } ON COLUMNS, non empty { (STRTOMEMBER(@StartDateParam):STRTOMEMBER(@EndDateParam)) *[Work Item].[System_Id].[System_Id] } ON ROWS FROM [Work Item History] WHERE ( { [Work Item].[System_WorkItemType].&[Bug], [Work Item].[System_WorkItemType].&[Requirement], [Work Item].[System_WorkItemType].&[Task] } ,STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]") ,STRTOSET(@IterationPath) StartDateParam and EndDateParm takes values of this type : [Date].[Date].&[2008-03-22T00:00:00]. So , it’s taking values from Date dimension . Let me know if you need any further details. Thanks, Swami
Welcome to the forums. Have you observed any latency from the Reporting Server to that Analysis server, as you state that the MDX query is embedded in RDL file. Refer to these articles that will help to identify the root cause: http://sqldb.wordpress.com/2006/05/...ocessing-location-and-expression-arrangement/ http://www.sql-server-performance.com/articles/biz/optimizing_processing_p1.aspx http://sqlblog.com/blogs/mosha/archive/2008/10/22/optimizing-mdx-aggregation-functions.aspx FYI
Thanks for responding. Parsing of the query itself is consuming lot of time when tried to compile from a SQL editor inside analysis server. After the query parsing is over, the executing time consumes 9 times the parsing time which itself seem to be hogging the entire processing time. When the same is executed from an RDL file, it times out as the default timeout on reporting services is set to max of 2min. Since the parsing & execution is taking more than 9 minutes, there is no way the RDL file can wait until 9 min. Can you please suggest/modify the provided query and let me know if I could try anything different to enhance the performance of the query. Appreciate your help. Thanks, Swami