Recently one of my customers in IT industry, had a P&L drilldown report aggregating the P&L metrics from the company level->client level ->account level -> project level -> employees at the row level while aggregating the data across date hierarchy Calendar Year->Calendar Semester ->Calendar Quarter -> Month at the Column Group Level with Adjacent Column Group aggregating the data across Geography Hierarchy Country -> State ->City.
The report has number of expressions to derive few P&L metrics and each row group had a sort order defined as well.
The report consists of a single tablix which was fetching around 1-2 lacs of records depending various parameterized filters. The stored procedure responsible for fetching the data for the report runs within 50 sec, while the report was taking 30-50 mins to render on the report server. Yes you read it correct it took 30-50 mins to render and in this blog post I would like to explain why it was taking so long and how we improved the performance by simplifying the report.
To reproduce the scenario, I created a similar drilldown tablix report using AdventureworksDW2012 database with following Row Grouping and Column Grouping
Row Groups
Product Category -> Product Sub Category -> Color -> Size -> Product
Column Groups
Calendar Year -> Calendar Semester -> Calendar Quarter -> English Month Name
Adjacent Column Group
Country -> State -> City -> Reseller
Measure
Order Quantity
In my report, I did not add any expression since we could still see the performance dip if this report. The report takes approx 15 sec to load as compared to 123 ms to execute to the query used to fetch the data for the report.
Now, as we all know, whenever we want to tune performance of the report, we need to first check the execution log to identify where the report is spending more time
SELECT * FROM dbo.ExecutionLog3 WHERE ItemPath like '%Drilldown%' ORDER By TimeStart DESC
As seen in the above Execution Log, the report is spending most of it in processing. In order to improve of the report we need to tune the report processing time. The Report Processing time is mainly time spend in performing grouping, aggregation, sorting evaluating expressions. Also, report processing is a memory intensive operation and if the report server is installed on a memory constrained server, it is likely to get OOM errors or slowness of reports on that server when this report is being processed.
For small sets of data, such reports doesn’t consume much memory and renders within acceptable time for interactive viewing. However as the granularity of dimensions is lowered, the dataset size increases and thereby the processing time and memory requirements increases exponentially and when such reports are rendering in production environment with concurrent users, the performance degrades.
To improve and tune the processing of such kind of reports is to simplify the design of the report. For such kind of reports, it also very important to understand the requirement of the business user, who will be consuming this report. Since analyzing 1 lac records of data interactively from a single report view is non-realistic expectation.
Most Business Users would first like to see highly summarized data at Top level of the Hierarchy and if the Metrics needs further investigation, they might want to drill down to the Next level for a specific Member of Top level Hierarchy. So in case of the above report, the analyst is interested to view the data at the Product Category Level, and if he wants to investigate further on Bikes Product Category, he needs to expand the Bikes Category to list all the Sub Category listed under Bikes Category.
To meet this requirement of the business user as well as simplify the design of the report, we can break the above report in 3-4 report as mentioned below
- The Main Report returning the Data aggregated at Product Category Group Only with a Drill Through Action defined on Product category column passing current selected Category field as a parameter to the second level drill through report.
- The Sub Category Level Report with Category as one of the hidden parameters which helps to filter the Dataset to that specific Category and aggregating the Data at Sub Category Level Alone with a Drill Through Action defined on Product SubCategory column passing current selected Sub Category as a parameter to the third level drill through report.
- The Color->Size->Product Level Drill Down Report with Sub Category as hidden parameter which filters the Dataset to that specific Sub category and thereby reduces the dataset to 30-40K records which is rendered by row grouping of Drill down in report in few seconds
With this approach, the following is the rendering time for 3 reports. As seen in the collection time for rendering all 3 reports is around 8-9 sec which is a good improvement as compared to single Drill down report
Thus, by simplifying the drill down report to 3 simple reports using Drill Through Actions we are able to meet the requirements of business users as well as improve the processing of our reports to acceptable time for interactive viewing. In case of my customer, we reduced the time for rendering the report from 30-50 min to 20 sec which was well appreciated
Hope this helps!!!
Parikshit Savjani
Premier Field Engineer