SSRS report with SSAS as the data source performance bottleneck

0
votes

I've searched web and StackOverflow for a long time but it was not useful for me.

I created an SSRS Report which uses Cube as Data Source. In SSMS the cube's query is fast and about Maximum 4 seconds, but the report shows the results after 1 minute.

I used SQL Server Profiler on Cube and I understood that Serializing the Results is the most time-consuming event on my report. First of all, I didn't find any solution to decrease "serialize the result" events and then I don't know how to improve my server utilization because despite of this slow report, its resource utilization (CPU, NETWORK, MEMORY) is at most 5 percent !!

by the way, my report returns about 300 thousand rows in every execution. (ROWS_RETURNED ~ 300000)

Mdx Query

 SELECT NON EMPTY{[Measures].Members} ON COLUMNS, NONEMPTY ( ( NONEMPTY((STRTOSET(@BankSelect))) * NONEMPTY ( STRTOSET(@BranchSelect) ) * NONEMPTY  ( STRTOSET(@DimTimeShamsiFullDate))* NONEMPTY ( (STRTOSET(@BranchTypeSelect) )) *  NONEMPTY ( (STRTOSET(@StateSelect) )) * NONEMPTY ((STRTOSET(@CitySelect)) * NONEMPTY (STRTOSET(@RowTypeSelect))) * NONEMPTY ( (STRTOSET(@MoneyStatusSelect))) * NONEMPTY ((STRTOSET(@MoneyTypeSelect))) * NONEMPTY ((STRTOSET(@MoneyUnitNameSelect)) ) )) ON ROWS FROM (SELECT ( STRTOSET(@DateInFrom)) ON COLUMNS FROM [SinapDW]) CELL PROPERTIES VALUE

Any help is appreciated :)

Thank you.

1
reporting-servicesssassql-server-data-toolscubesql-server-profiler
Please include the MDX query – GregGalloway
could it be parameter sniffing? – Harry
@Harry I Know lots of StrToSet and parameters make report slow, but the report is dynamic and I had to use them. – masoumeh
@GregGalloway I wrote my Dataset's MDX query. – masoumeh
@masoumeh you've already done the optimizations I was going to suggest (CELL PROPERTIES VALUE). I think omitting the DIMENSION PROPERTIES clause means you return only the MEMBER_CAPTION property for each of your dimension attributes which is the minimum you can return. I suspect it's just a large resultset so takes some time to serialize and send over the network. – GregGalloway

1 Answers

1
votes

If your problem is network serialization or performance I would suggest having a look at the performance whitepaper on how to optimize the network packets SSAS sends. These documents provide insights on how to optimise resource usage for SSAS and also how to use its inner settings for optimisations based on your workloads.

SQL SSAS 2008R2

SQL SSAS 2012 & 2014