SQL Server Performance

Performance Isssue with Dynamic MDX Parameters in SSRS

Discussion in 'SQL Server 2005 Reporting Services' started by ludwig, Jan 22, 2009.

  1. ludwig New Member

    Hi,
    I have a Report in SSRS with a SSAS Cube as datasource, that takes 4 parameters. These parameters are dynamiclly build on the front end application (Windows Forms) and then passed to the Report which is displayed using the .Net Reportviewer control. The Report is published to a Remote Report Server. I need to be able to do this , as the parameters are based on what user logged in to the application.
    My problem is as follows:
    The MDX query builder in SSRS builds a query that looks like this:
    SELECT NON EMPTY { [Measures].[Session Duration in HhMmSs], [Measures].[Bytes Down], [Measures].[Total Bandwidth in Mb], [Measures].[Bytes- Up], [Measures].[Total Duration in Sec], [Measures].[Usage Session Count] } ON COLUMNS, NON EMPTY { ([Time Period].[Day Date].[Day Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@UpstreamSuppliersUpstreamSupplierHierarchy, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@UpstreamSuppliersUpstreamSupplierCompany, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@UpstreamSuppliersUpstreamSupplierType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromTimePeriodDayDate, CONSTRAINED) : STRTOMEMBER(@ToTimePeriodDayDate, CONSTRAINED) ) ON COLUMNS FROM [WG Usage])))) WHERE ( IIF( STRTOSET(@UpstreamSuppliersUpstreamSupplierType, CONSTRAINED).Count = 1, STRTOSET(@UpstreamSuppliersUpstreamSupplierType, CONSTRAINED), [Upstream Suppliers].[Upstream Supplier Type].currentmember ), IIF( STRTOSET(@UpstreamSuppliersUpstreamSupplierCompany, CONSTRAINED).Count = 1, STRTOSET(@UpstreamSuppliersUpstreamSupplierCompany, CONSTRAINED), [Upstream Suppliers].[Upstream Supplier Company].currentmember ), IIF( STRTOSET(@UpstreamSuppliersUpstreamSupplierHierarchy, CONSTRAINED).Count = 1, STRTOSET(@UpstreamSuppliersUpstreamSupplierHierarchy, CONSTRAINED), [Upstream Suppliers].[Upstream Supplier Hierarchy].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
    The problem is that when I run this query in SSAS with hard coded parameter values, it returns the data in seconds, but as soon as I run the Report in SSRS wit dynamic parameters, it takes minutes!!! So the perfmance issue lies with the dynamic parameters. Is there any way to optimize the MDX or anything else I can do to speed up my queries? My users will not like having to wait 10 minutes for a report. We decided to use SSAS with SSRS because it was suppose to be a faster way of getting data, but now it seems very very slow. Any help will be much appreciated.
    Ludwig (South Africa)
  2. satya Moderator

    Welcome to the forums.
    I see the main problem in getting the data back from SSAS and here the most important part is DIMENSION PROPERTIES for the table columns that will instruct AS to return not only the key column, but also additional properties.
    These properties could be used automatically in Reporting Services, they are called extended field information for Analysis Services. They won't appear in dataset, but they are retrieved at query time. While the name of member can be retrieved using Fields!FieldName.Value, extended information is accessible through Fields!FieldName("Property name").
    http://www.ssas-info.com/analysis-s...-mdx-parameters-in-reporting-services-reports fyi.
  3. ludwig New Member

    Thank you for the welcome and the reply.
    I'm not sure if I understand you correctly, I'm new to MDX and SSRS, but my problem is not getting the data back from SSAS. The report and all sub reports on it functions correctly as I want it to with the MDX parameters, but the performance is a big issue. From what I have gathered I guess the issue is that when SSRS retrieves the dataset from the cube, it retrieves the full dataset (hierarchy) first, and then applies the parameters to it in memory somewhere between SSAS and SSRS????? What would actually speed up the process enormously would be if SSRS could retrieve the dataset with parameters applied on SSAS cube, without having to load the complete hierarchy dataset into memory as my hierarchy structures in the cube are HUGE!!!! When I use hard coded dimension filters in SSRS instead of dynamic parameters, the perfomance is excellent, but as soon as I change a dimension filter to a dynamic parameter the performance becomes utterly slow.
    Kind Regard
    Ludwig
  4. satya Moderator

    Did you look at the link referred above and I now doubt the SSRS & SSAS 2005 engine whether they can take such an advantage of using dynamic parameters, see http://msdn.microsoft.com/en-us/library/bb934106.aspx that refers about usage scenario as it relates to 2008 version. If I see any reference for 2005 version I will refer or let us wait for other members opinion too.
  5. ludwig New Member

    Thank You,
    Yes, I did look at the link you posted and I am going to try a few things and will come back to you. After looking at the last link you posted on SSAS 2008, Microsoft had the following to say: "The usage of dynamic parameters in your MDX code will prevent your code from gaining improved performance."
    This bothers me greatly and is really frustrating. With my unique situation I have little other options than to use dynamic parameters. If there was any way to get dynamic parameters to behave in the same way that a dimension filters in handled by SSRS, that would solve the problem. But this does not seem to be the case.....or even possible
  6. satya Moderator

    Here you go I found this http://blogs.msdn.com/chrishays/ blog post and search for DYNAMIC string in that blog post that will get you details.
    I still doubt whether 2005 can take such an advantage of dynamic parameters.
  7. ludwig New Member

    Ok, I think I've found a solution to the problem, although not an ideal one. There seems to be no real solution to improving speed with dynamic MDX parameters in SSRS using a SSAS Cube as datasource, but something that does improve speed tremendiously is instead of using the SSAS data provider to connect to a cube, I will use the OLE DB data provider to connect to the OLAP database with star schema and bypass the problem with dynamic MDX parameters and use normal SQL queries instead.
    Perhaps somewhere in the future Microsoft will improve on this, but for now this seems to be my only option.
    Thank you for the responses
    Kind Regards
    Ludwig
  8. satya Moderator

  9. LearnBI New Member

    Hey, I had same problem. However, I didn't figure out how to using OLEDB data provider to connect OLAP and passing the parameter to speed up. Would you give an example. Thanks a lot

Share This Page