SAP BusinessObjects

Introduction

Chapter 1 - Client Side Performance

TIP 1.1 - Use HTML Interface for Faster viewing/refreshing of Reports

.
Below is a link to our Web Intelligence documentation page on our Support Portal.  Go to the End User Guides section to find the latest Webi User Guide documentation.

TIP 1.2 - Upgrade to BI 4.1 SP03+ for single JAR file Applet Interface

TIP 1.3 - Ensure Online Certificate Revocation Checks aren't slowing down your Applet Interface

TIP 1.4 - Make sure JRE Client Side Caching is working

TIP 1.5 - Ensure you are not running into these known JRE Security Change issues

TIP 1.6 - Choose the right client - Webi Rich Client vs HTML vs Applet Interfaces

• Best interface for report designers and power users who will be creating, modifying and doing advanced analysis of documents and data.
• This interface takes advantage of 64-bit backend servers and can generally handle larger amounts of data/calculations as it utilizes backend servers to do the heavy lifting.
• Since this is a Web Application, timeouts can occur when leaving the session idle or when carrying out long running actions.

• This stateless interface has almost all of the features and functionality that the Applet interface does plus a few additional features of its own.  This should be used by advanced designers and power users that wish to have a stable design environment for larger documents
• Can be used with local data sources and some desktop type data sources such as Excel and Access
• Also can be used in 3-tier mode which takes advantage of backend servers for data retrieval

Chapter 2 - Process Best Practices

TIP 2.1 - Schedule reports to save time and resources

• Provides lower user wait times when implemented correctly
• Allows us to offload processing to non-peak times
• Can help reduce sizing requirements for concurrent users
• Reduces impact on Database during Peak times
• Can combine Instances with Report Linking to produce smaller, faster documents

1. UserA logins to BI Launchpad and navigates to the "Monster Finance Report" document
2. UserA Views this document and clicks the refresh button to get the latest data
3. After about 2 minutes, UserA is not sure what is going on.  The report appears to be refreshing still, but given the fact that UserA is impatient, he suspects that the refresh has "hung" and closes the viewer.
4. UserA decides to test his luck and submit the request again.  This essentially creates a new request for the same data and potentially works against BOTH requests as they compete for resources on the BI servers and the database side.
5. After a few more minutes, UserA gives up and moves on.  Meanwhile he has no idea the amount of resources and time he's wasted in the background.

• UserA Never got his report and had a bad experience
• Backend resources were wasted without any usable results

1. Educate your users to schedule anything that takes over 5 minutes to run
2. Encourage users to Schedule reports that they know they will need throughout the day to non-peak hours before their day begins
3. Schedule Documents to formats that you know your end users will want such as Excel, Text, or PDF.  This can save time and resources during the day
4. Utilize Publications when multiple users have different requirements for the same documents

TIP 2.2 - Use the Retry options when Scheduling to Automate Retries

.

TIP 2.3 - Use Instance Limits to help reduce the # of Instances in your environment

• The number of instances for each object, user, or user group
• The number of days that instances are retained for a user or a group

1. Go to the Folders management area of the CMC.
2. Locate and select the folder for which to set limits, and select Actions/Limits.
3. In the Limits dialog box, select the Delete excess instances when there are more than N instances of an object check box, and enter the maximum number of instances per object the folder can contain before instances are deleted in the box. The default value is 100.
4. Click Update.
5. To limit the number of instances per user or group, Click the Add button beside Delete excess instances for the following users/groups option.
6. Select a user or a group, click > to add the user or group to the Selected users/groups list, and click OK.
7. For each user or group you added in step 6, in the Maximum instance count per object per user box, type the maximum number of instances you want to appear in the BI platform. The default value is 100.
8. To limit the age of instances per user or group, click Add beside the Delete instances after N days for the following users/groups option.
9. Select a user or a group, click > to add the user or group to the Selected users/groups list, and click OK.
10. For each user or group you added in step 9, in the Maximum instance age in days box, type the maximum age for instances before they are removed from the BI platform. The default value is 100.
11. Click Update.

.

TIP 2.4 - Platform Search Tweaking for Performance

Chapter 3 - Report Design Best Practices

TIP 3.1 - Steer Clear of Monster Webi Documents

1. What is the primary purpose of this document?
2. What question(s) does this document have to answer?
3. How many different consumers will be utilizing this document?
4. Can this document be split into multiple documents that service smaller, more specific needs?

1. Avoid using a large number of Reports (tabs) within a Document
   1. 10 or less Reports is a reasonable number
   2. Exceeding 20 reports in a single document should be avoided
2. Creating smaller documents for specific business needs allows for faster runtime and analysis
   1. Utilize Report linking to join smaller documents together.  This is discussed more in TIP 3.2
   2. Aim to satisfy only 1-2 business needs per document.
3. Provide only the data required for the business need(s) of the Document
   1. 50.000 rows of data per document is a reasonable number
   2. Do not exceed 500.000 rows of data per document
4. Do not add additional Data Providers if not needed or beyond document needs
   1. 5 data providers is a reasonable number
   2. Do not Exceed 15 data providers per document

• Reduce the time it takes to load the document initially in the viewer/interface
  • Smaller documents will load quicker in the viewers.  This is because the resources needed to transfer the document and process it initially will be much less with smaller documents.
• Reduce the refresh time of the document.
  • The larger the document, the more time it will take to process the document during a refresh.  Once the report engine receives the data from the data providers, it has to render the report and perform complex calculations based on the document design.  Larger documents with many variables and large amounts of data can take much longer to render during a refresh.
• Reduce the system resources needed on the both the client side and the server side.
  • The resources needed to work with a large document are going to be much greater than those needed for smaller documents.  By reducing the size of your documents, you are potentially reducing the overall system resources, such as CPU, RAM, Disk space, that your system will consume on average.  This can equate to better throughput on your existing hardware.
• Improve performance while modifying the document
  • When modifying a large document, the client and server has to load the document structure and data into memory.  As you add/change/move objects in the reports, this causes client/server communication to occur.  This can slow down the designer as updates require reprocessing on any objects involved.  The more objects in a document, the longer each operation during a modify action can take.
• Improve performance for the consumer during adhoc query and analysis.
  • Slicing, dicing, filtering and drilling actions will perform quicker on smaller documents as well.  This will equate to faster response times to the consumers as they navigate and do detailed analysis on the documents

TIP 3.2 - Utilize Report Linking Whenever Possible

• Sales_Summary is a summary report that summarizes the sales for all 100 sites of Company XYZ Inc.
• Sales_Summary has a hyperlink that allows a user to "drill into" a 2nd Report (Sales_Details) to get the sales details on any of the 100 sites.
• Sales_Summary is scheduled to run each night and take ~20 minutes to complete.
• Users can view the latest instance of Sales_Summary which takes only a few seconds to load.
• Users can drill down into Site Sales data for each of the 100 sites which launches Sales_Details report using Report Linking and a prompt value
• The prompt value filters the Sales_Details report using a Query Filter so that it only displays the sales details for the 1 site that the user drilled into.

1. The Sales_Summary report only contains the Summary details.  Therefore it runs faster than if it contained both summary and detailed data
2. The Sales_Summary report is smaller and will load/navigate much quicker on its own
3. The User can drill down and get a much faster response time because the details report only contains the specific data that they are interested in

TIP 3.3 - Avoid Autofit When not Required

TIP 3.4 - Utilize Query Filters instead of Report Filters whenever possible

TIP 3.5 - Avoid Charts with Many Data Points

..

BI 4.0 introduced a new charting engine that is called Common Visualization Object Model, or CVOM for short.  This is a versatile SDK that provides enhanced charting capabilities to Web Intelligence and other SAP Products.  Web Intelligence utilizes CVOM for creating the charts and visualizations found within the Web Intelligence product.  The CVOM service is hosted on an Adaptive Process Server (APS) and is referred to as the Visualization Service.

.

Out of the box, this service is already added to the default APS but depending on the usage of visualizations in your deployment, you will likely want to split out the APS services according to the System Configuration Wizard or APS Splitting Guidelines.

.

If we click the Edit Common Services option on the right-click menu of the APS in the CMC, we will see it listed as the following:

.

.

The reason this is relevant for performance is this service can become a bottleneck in situations where the generation of charts takes a long time due to resource or sizing issues.  It is important to ensure you are sized correctly with this service to ensure it doesn't become a bottleneck.  We discuss this in more details later on in the Sizing chapter.

.

When we spoke to the developers for the CVOM component and asked them for advice on fast performing visualizations, they gave us a tip based on their testing and development knowledge.  They recommended to avoid using large charts with many data points and instead try to use multiple smaller charts with fewer data points within your reports.

.

The reason behind this is that the CVOM components can produce charts much quicker when they do not have many data points to contend with.  Obviously some business needs may require this still but whenever possible, the recommendation is a smaller number for better performance.

.

DOC - Webi User Guide - Chapter 4.3 - discusses Charting with Web Intelligence

.

.

TIP 3.6 - Limit use of Scope of Analysis

.

As quoted from the Webi User Guide:

.

.

Below is a example of using Scope of Analysis to add Quarter, Month and Week in the scope even though the Result Objects only include Year:

.

.

What this essentially does is modifies the query to include Quarter, Month and Week in it.  This of course would return more data and could take longer to return.

.

In short, you should ensure that Scope of Analysis is used consciously and that the majority of report consumers will benefit from it.  An alternative is Report Linking as discussed above.

.

TIP 3.7 - Limit the # of Data Providers Used

.

Best practices from the field is to limit the # of data providers to 15 or less for faster performing reports.  If you have a need for more than 15 data providers, then you may want to consider a different way of combining your data in a single source.  Using a proper ETL Tool and Data Warehouse is a better way to achieve this and pushes the consolidation of data to a data warehouse server instead of the BI Server or Client machine.

.

The current design of the Webi Processing Server is to run Data Providers in series.  This means that each data provider is run one after another and not in parallel as you might expect.  So, the combined total runtime of ALL of your data providers is how long the report will take to get the data.

.

Here is a representation of the combined time it might take for a report with multiple data providers:

.

.

Another consideration for reports with a lot of data providers is that merging dimensions between multiple sources adds overhead into the processing time.  Keeping it simple will certainly result in a better performing report.

.

.

TIP 3.8 - Don't accidentally Disable the Report Caching

.

Web Intelligence utilizes disk and memory caching to improve the performance of loading and processing documents & universes.  This can provide a faster initial load time for common reports and universes when implemented correctly.

.

The good news is that caching is enabled by default so in most cases this will be happening automatically for you and your users behind the scenes. There are a few situations where cache cannot be used though so we wanted to make sure report designers were aware of these:

.

The following functions will force a document to bypass the cache:

.

CurrentDate()

CurrentTime()

CurrentUser()

GetDominantPreferredViewingLocale()

GetPreferredViewingLocale()

GetLocale()

GetContentLocale()

.

If you use these within your document, then cache will not be utilized.  These functions are quite common so it is important to be aware of the potential impact on caching they can have.

.

At the current time, caching is done at a document level and not an individual Report (tab) level.  Therefore, if these functions are used anywhere in the document, the cached copies will not be used for subsequent requests.

..

TIP 3.9 - Test Using Query Drill for Drill Down Reports

.

What is Query Drill?  As quoted from the Web Intelligence User Guide:

.

"When you activate query drill, you drill by modifying the underlying query (adding and removing dimensions and query filters) in addition to applying drill filters.

.

You use query drill when your report contains aggregate measures calculated at the database level. It is designed in particular to provide a drill mode adapted to databases such as Oracle 9i OLAP, which contain aggregate functions which are not supported in Web Intelligence, or which cannot be accurately calculated in the report during a drill session.

.

Query drill is also useful for reducing the amount of data stored locally during a drill session. Because query drill reduces the scope of analysis when you drill up, it purges unnecessary data."

.

Performance gains can be appreciated by reducing the amount of data that a Webi document stores locally and by pushing some of the aggregation to the database server side.

.

Performance gains may or may not be realized by using this option but it is simple enough to test it out to see if it will improve performance for you.  To enable this option, go into the Document Properties and check the "Use Query Drill" option.  Below is a screenshot of the option:

.

.

TIP 3.10 - Mandatory Prompts vs Optional Prompts

.

This tip came to me while investigating a support incident.  The customer I was working with noticed that reports took significantly longer to refresh when his prompts were Optional vs Mandatory.  we were seeing a 30 second difference in even one of the more simple reports he had for testing.  We investigated this through the traces and noticed that the SQL Generation functions were executing twice when Optional Prompts were involved and this was adding to the overhead of running the report.
.

This was happening in XI 3.1 SP7 on the customers side so it was with a legacy UNV universe.  I could replicate the issue internally with our simple eFashion universe but since it executes very quickly, the extra time was barely noticeable in my own testing.  I collected my internal logs and BIAR file and asked a developer for a quick review.

.

The developer confirmed that the delay I saw from the SQL Generation functions as suspected.  He then did a code review to see why this was happening.  His explanation was that Optional prompts may or may not have values and therefore the the SQL generation could change after the prompt dialog appears.  For example, if an optional prompt value is not selected, then the Where clause will omit that object.  With Mandatory prompts, the SQL structure will always be the same before and after prompts are selected so it does not need to regenerate the SQL after a value is selected.

.

So, in short, Optional vs Mandatory can give different performance results so it should be considered before choosing one vs the other.  As with many of the other tips in this doc though, this does not mean that you should not use Optional prompts.  They are useful and are often necessary, but they are a factor and as long as you know this, you can optimize your report design.

.

.

..

Chapter 4 - Semantic Layer Best Practices

TIP 4.1 - Only Merge Dimensions that are needed

TIP 4.2 - Build Universes & Queries for the Business Needs of the Document

TIP 4.3 - Array Fetch Size Optimizations

TIP 4.4 - Ensure Query Stripping is Enabled

.

TIP 4.5 - Follow these Best Practices for  Performance Optimizing SAP BW (BICS) Reports

TIP 4.6 - Using Index-Awareness for Better Performance

• Joining and filtering on key columns are faster than on non-key columns.
• Fewer joins are needed in a query, therefore fewer tables are requested. For example, in a star schema database, if you build a query that involves filtering on a value in a dimension table, the query can apply the filter directly on the fact table by using the dimension table foreign key.
• Uniqueness in filters and lists of values is taken into account. For example, if two customers have the same name, the application retrieves only one customer unless it is aware that each customer has a separate primary key."

TIP 4.7 - Using Aggregate Awareness for Performance

TIP 4.8 - Utilizing JOIN_BY_SQL to avoid multiple queries

TIP 4.9 - Security Considerations for the Semantic Layer

1. Review your business requirements and reduce/remove any unnecessary Data/Business Layer Security profiles at the Universe level.
2. Consider using the Change State / "Hidden" option in the Business Layer for fields that you do not want any users to see.
3. Consider using Access Levels in the Business Layer to control which users have access to objects
4. Reduce the # of User Groups/Roles that your Users are apart of
5. Test performance with an Administrator User and compare it to a Restricted User to gauge the impact on Performance

Chapter 5 - Formula & Calculation Engine Tips

TIP 5.1 -  Use Nested Sections with Conditions with caution

TIP 5.2 -  Use IN instead of ForEach and ForAll when possible

TIP 5.3 - Use IF...THEN...ELSE instead of Where operator when possible

TIP 5.4 - Factorize (Reuse) Variables

Chapter 6 - Sizing for Performance

Chapter 7 - Architectural Differences between XI 3.1 & BI 4.x