How to speed-up your slow-running reports

Guest tip-master: Hau Ngo | Summerlin Analytics 

Your once lightening-fast reports have begun to show their age.

What was once a delightful experience of pressing a button and gleaming insight from your data is now an excuse to take step away from your desk to get some coffee. If you are lucky, the report will have finished running in the 10-minutes you’ve been gone.

But it doesn’t have to be that way.

If your reports are running slow after gaining some data over the years, then the following 3-step exercise plan can get it back into shape … and all without setting up a project charter for the development team. Okay. Maybe a small bribe if you’re not already on good footing and you don’t have access to the below transaction codes.

Here are three things that you can do to fix those long-running reports:

1. Identify Long Running Queries

It kind of goes without saying … but you need some metrics to back up the assertion that certain reports are taking longer to finish than others.

Luckily, SAP has giving you the metrics related to the query runtimes are captured in two predefined views RSDDSTAT_OLAP and RSDDSTAT_DM.

Run transaction SE16 to view the contents.

2016-10-17-22_55_52-query-runtime

 

 

 

Simply sort descending by RUNTIME (measured in seconds) and find the trouble query in OBJNAME.

Want more information on these two tables? Check this out.

2. Use the OLAP Cache

If you find yourself often running a report for the same set of values, then asking the report to fetch its data from the global cache is much faster than reading the data from the underlying models and aggregating the results.

To access the query settings …

  • Run transaction RSRT
  • Enter the query technical name
  • Click on the Properties button

2016-10-17-23_02_09-query-monitor

 

 

 

 

For BW 7.4, here is a good starting point and the values that you should set.

2016-10-16-16_09_54

For BW 7.3 and earlier, I’ve found the following settings to work well.

  • Read Mode = H – Query to be read when you navigate or expand hierarchies
  • Cache Mode = 2 Main Memory Cache with Swapping

For more information on OLAP cache and the meaning behind each setting, check out this helpful wiki: https://wiki.scn.sap.com/wiki/display/BI/OLAP+Cache

3. Apply Filter Values

More of a best practice than a ‘How-To’, ensure that you’re making use of the report selection filter parameters and select only the data you need. Asking the system to retrieve and aggregate unnecessary data may take a while (depending on the volume and data model).

Placing a smaller request (such as sales orders placed last month for a particular sales organization) will generally give you a faster return.

Summary

To recap, find the long running queries, enable caching, and select smaller data samples to speed up your query performance.

Often, the BW development team in large organizations is under-staffed or over-worked (or both.)

The above tips should give you the do-it-yourself knowledge to identify which reports have been running long and to apply some best practices for reporting execution on your own.

If that’s not enough, then we may need to trim the underlying data model. (More to come in a future hot-tip.)

P.S. I’ve prepared a special cheat sheet for readers of thehau-ngo-image ConsultAce, Ace in your Inbox Newsletter. Click on this link to find out how to breeze through your next functional design and win praise from your BW developer. (http://bit.ly/2ef64DS)

 

By | 2018-01-31T17:41:13-05:00 November 3rd, 2016|