Tuesday, February 14, 2012

Drill down matrix report slow performance

I have created OLAP cubes and we are creating reports on top of them. The reports take incredibly long to render (40 seconds to 5 minutes). The MDX for the reports runs in less than 15 seconds when executed as a query in Mgt. Studio. Browsing is also fast. The reports are drill down matrix-type reports and they do eventually display properly. Not that this should help with the mechanics of the issue but the report data source query will show how we drill across dimensions:

<code>

SELECT NON EMPTY { [Measures].[Number of Queries Run] } ON COLUMNS, NON EMPTY { ([ClientProjectDim].[Project ID].[Project ID].ALLMEMBERS * [UserDim].[UserName].[UserName].ALLMEMBERS * [QueryDim].[Status].[Status].ALLMEMBERS * [DateDim].[Date Hierarchy].[ClusteredHourOfDay].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Ad Hoc] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

</code>

The RS and security logs don't have anything meaningful. As the MDX works well when executed as a query but not in the reports I can't tell if it's a rendering issue somewhere. Has anyone experienced this issue and is there a resolution aside from restructuring the report?

I am experiencing similar issues but no answers, did you ever resolve youra?|||We did not resolve this, but rather restructured our reports. You might consider this. You can set up your reports with params for drill-through.|||Thanks, however I did parametize this report. I will try to restructure and do this long and laborious way. I noticed in the AS browser and MDX query designer they are using nice drill through tree for the parameters, but no drill through in the rendered form. Is that due to a property setting I am missing.

No comments:

Post a Comment