We are often asked how to identify reports that are consuming too many resources on the server. It is usually Webi reports that are the culprit. So what are the best practices for Webi Report development to avoid such issues? Webi is a fabulous tool for end users. Users have the ability to select the data elements they want to report on, adding filters that make sense, formatting the data, etc. There are so many possibilities. However, Webi isn’t always a “fabulous” tool for the Business Objects Administrator; it can result in large data sets to be accessible to users that want it all or users still learning how to develop reports.
Users frequently create reports that return a lot of data, it doesn’t always make sense to us as BOBJ administrators. I have seen users that haven’t given up on their old systems, so they use Business Objects to extract data from the new system to populate the old system. They are essentially using their Business Intelligence tool for data dumping. Report developers are also given free power to develop reports with too many queries, reports tabs, and variables resulting in poor performing reports.
Sometimes users try to return a lot of data, but sometimes it happens unintentionally. Run-away queries often occur when reports are not properly filtered because users don’t know any better or are unaware of how much data might be returned.
Poor performing report can bring your Business Objects environment to its knees. Every BOBJ Admin has received the call about the system running slow, users cannot log in, reports are hanging… It is tough always being in reactive mode. I prefer to be proactive and try to prevent some of these issues before they impact your environment.
We won’t discuss database design or optimization that should be your first step when setting up a reporting system. Any complex processing is most effective when done at the database level. Maintaining data in a data warehouse that is already aggregated is the best approach. These steps are recommendations to follow after your database has been optimized for reporting.
Top 10 Best Practices for optimizing Webi Reports:
(Many of these can be found in an SAP post here, written in 2014 but still relevant today).
- Put restrictions in place on the Universe, set row limits in Universe Designer or IDT.
- Adjust the Array Fetch Size for each universe. The Array Fetch Size sets the maximum number of rows for each fetch from the database. Tuning the size for your environment can significantly improve performance. This will need to be tested to see what works for your environment. Find SAP suggestions here.
- Make sure reports have filters or prompts. Optional prompts can be used but there should be at least one required prompt. If all prompts are optional, users can bypass filtering the data.
- Use query filters rather than report filters. Query filters reduce the amount of data returned to the report, it is more effective to fetch smaller data sets. Report filters are applied after the query runs.
- Limit the number of data providers (5) and report tabs (10).
- Create smaller reports and link them together using OpenDocument. Sometimes you only want summary data, so you should have a report that only shows the summary data. If the user wants details, give them the option to click an OpenDocument link to get the details.
- Remove unused report variables.
- Remove any unused objects from the query.
- Avoid merged dimensions.
- The Formula statements “ForEach” and “ForAll” should only be used when necessary. It is recommended to use the “In” statement instead.
Now, you are probably wondering how you can locate reports that don’t follow these best practices. 360Eyes, one of the solutions in 360Suite, can help you locate those types of reports. 360Eyes is a tool that provides metadata concerning your BOBJ environments, combining CMS, Auditor, and File Store data into a data mart. There are 40+ prebuilt Webi reports and 4 Universes that can be used to query the Business Objects metadata in 360Eyes.
Suggestions for identifying Webi Reports not following Best Practices with 360Eyes:
1.First, determine which reports are returning too many rows. Set limits based on what you think is reasonable.
This data can be extracted from 360Eyes.
2.Identify reports with too many data providers.
The 360Eyes report below lists reports and the data provider count for each.
3.Locate reports with too many report tabs.
The 360Eyes report below shows lists reports and the report tab counts for each.
4.Remove unused report variables or unused objects from the query.
The 360Eyes report below shows unused report variables and unused objects:
5. Search for reports using ForEach and ForAll in report variables.
The 360Eyes report below allows you to filter on the syntax in all variables in all reports:
This is just an example of the useful information you get from 360Eyes. Just knowing the possibilities will give you ideas for how you can identify poor performing reports to improve your BI platform performance overall.
Request a Trial today from 360Suite here.
How our customers used and conquered with 360Suite? Find out more!