Best practices for Web Intelligence report performanceMatthew Shaw, SAP @MattShaw_on_BI

Best practices for Web Intelligence

report performance

• Performance is key for user adoption• Users expect fast response times

• After a certain time, users give up/cancel the task they were doing

• A variety of factors influence Web Intelligence performance• System configuration

• Report design: do not build monster reports

• Semantic layer configuration and query design

• Workflows: using the cache and scheduling reports

• #1 - A fast running applet

• #2 - Steer clear of monster reports

• #3 - Utilize report linking

• #4 - Report design best practices

• #5 - Semantic layer best practices

• #6 - Know the architecture differences

• #7 - Stick to a schedule

• #8 - Server sizing for optimal performance

Tips for better Web Intelligence performance

Everyone hates waiting around for an application to load.

Some Java updates can wreak havoc on load time of the Web

Intelligence applet

Three issues that arise a lot are covered in the next few slides:

• Online Certificate Revocation checks causing delays

• New JRE security changes that cause issues and delays

• Applet comprised of over 60 JAR files, causing many security checks

#1 Tips for a fast running applet

Newer JRE versions automatically check for revoked

certificates online!

• This happens for each JAR file (60+ in BI 4.0)

• Internet connection speed plays a major factor

• Can add several minutes on to load times

Online Certificate Revocation Checks (1/2)

• Tips

– Use only CRLs or choose the “Do not check” option in the Java

Control Panel

– Test On & Off for

Timing differences

– Can use the Java

Console to analyze

– Read this WIKI for

more Details

– See KBA 1904873

Online Certificate Revocation Checks (2/2)

• Oracle has tightened up security requirements for applets

• New JRE versions are released much quicker than our patches

in some cases

• List of known issues and resolutions can be found here:

Web Intelligence and Oracle JRE Known Issues

JRE Security Changes Cause Issues

• BI 4.0 introduced a new architecture for the Web Intelligence

applet

• Previous versions were a single JAR file

ThinCadenza.jar

• BI 4.0 split this into 60+ individual JARs for easier development

and updating

• New JAVA security updates are not friendly to this architecture

Reducing the # of JAR files loaded (1/2)

• Recommendation: Upgrade to BI 4.1 SP03+– This goes back to the older architecture model and contains a single

jar for the applet

– webiapplet.jar is the new name (webiapplet_en.jar is the resource file that accompanies)

– Advantage of only 1 security check, 1 cache check. Generally improves performance by a lot!

NOTE: It is a 44 MB JAR file. Can take a while to load the first time

Reducing the # of JAR files loaded (2/2)

Visit the WIKI for some more JRE Client Side tweaks:

• Ensuring your JRE caching is enabled

• Ensuring the Java next-generation plug-in is used

• Tests you can run to find out where the bottlenecks are for slower load times

Tips for Fine Tuning Performance for the Webi Applet Interface

Other JRE Tweaks

Tip #2 – Steer clear of monster reports

If only 10-20% of a large document is utilized, 80-90% is waste

Avoid using a large # of reports within a document

• 10 reports per document is a reasonable number

• Do not exceed 20 reports per document

Don’t try to account for all scenarios but rather focus on specific

scenarios that add value to the consumer

• 50,000 rows per document is a reasonable number

• Do not exceed 500,000 rows per document

Large documents can mean wasted time

• Creating smaller documents for specific business needs allows for

faster runtime and analysis

• Start with the individual business need and build a document based

on that need

Focus on the Business Need

• By creating smaller, reusable documents you:

Reduce the time it takes to load the document in the viewer

Reduce the refresh time

Reduce the system resources needed on both

the client and server side

Improve the performance while modifying the

document

Create smaller, reusable documents

Tip #3 – Utilize Report Linking

• Instead of using HUGE documents, consider using smaller documents

and linking them together!

• Link reports together using OpenDocument linking and a combination of

Prompts and Filters

– Hyperlink wizard available in HTML interface makes this really simple!

• This removes performance hits for unnecessary parts of the report

• Higher design-time costs but better performance for the end user

Use Report Linking

Use Case for Report Linking:

• Report A is a summary report that summarizes the sales for all 100 sites of Company XYZ

• Report A is scheduled to run each night and take ~20 minutes to complete

• Users can view the latest instance of Report A which takes only a few seconds to load

• Users can drill down into Site data for each of the 100 sites which launches a 2nd report that displays only the site data for the site they drilled on

Report Linking Use Case

Tip#4 – Report Design Best Practices

• Best Practice from the field is to use no more than 15 Data

Providers per document

• Data providers run serially so run times add up

• Refresh time and merging of dimensions can cause some big

delays on the Processing Server side

• Using a Data Warehouse to consolidate sources and ETL tools to

produce better reporting sources is a better practice

Limit the number of Data Providers used

• Web Intelligence has great caching mechanisms for documents that have already been viewed

• Cache improves load time of documents

• Using functions such as the ones below means that cache will never be used– CurrentTime()

– CurrentDate()

– UserName()

• These functions require updates to the cached files so they have to be regenerated

Don’t accidentally disable your cache!

• When used, it forces the full document to be calculated during

navigation

• This can make navigating a report much slower

• Can make things especially slow when jumping to the last page

of a large report

Avoid Auto-Fit When Possible!

• CVOM is the new charting engine we use in BI 4.x

• This is hosted by the APS server – Visualization Service

• CVOM is better at creating a large # of smaller charts than creating

large ones with many data points

• May be more efficient to use smaller, more specific charts than

huge ones

Avoid Charts with Many Data Points

• Nested sections can contribute to performance degradation

issues

• This is especially true if conditions are used such as “Hide

section when the following is empty…”

Avoid Nested Sections

• Option called “Use query drill” in the report properties

• Modifies the underlying query when drilling. Utilizing the

database instead of local data

• Reduces the amount of data stored locally for a drill session.

• Can improve performance. Best to test both modes to compare

Test Query Drill for Drill Down Reports

• Scope of Analysis is used to retrieve extra data from the database

• This extra data is stored in the cube for drill down requests

• This is great feature but has a performance impact

• Could utilize the Report Linking for similar results and an on-demand data fetch

Tip: BI Launchpad preferences for Web Intelligence allow you to be prompted when Drilling needs more data

Limit use of Scope of Analysis

• ForEach and ForAll should only be used when really necessary. Use IN when possible instead

• Where operator can also take longer to process behind the scenes. Using If… Then… Else may be better

• Factorizing variables reduces overhead for calculation engine. For example:

v_Sales = [MeasureA] + [MeasureB]

v_SalesEst = [v_Sales] + [MeasureC]

vs

v_SalesEst = [MeasureA] + [MeasureB] + [MeasureC]

Formula Best Practices for Performance

Tip#5 – Semantic Layer Best Practices

• Start with the document needs and build your queries specific to these

• Too often we see mega queries/universes that account for every possible need

• Obviously this is convenient for report designers, but the consumers pay the price

• Purpose of a semantic layer is to simplify the querying of data

TIP: Build queries that only contain objects used in the document(s)

Build lean queries for your documents

• Array Fetch Size sets the maximum number of rows with each fetch from the database

• Tuning this size for your environment can greatly impact performance

• Set at the Universe Connection level

INTERNAL TESTING

Array Fetch Size Optimization (1/2)

AFS Time Records Throughput

10 95 1228000 12926.32 / second

100 37 1228000 33189.19 / second

1000 18 1228000 68222.22 / second

Optim 30 1228000 40933.33 / second

• Optimized by default for new Connections

• DISABLE_ARRAY_FETCH_SIZE_OPTIMIZATION parameter can be

used to disable the optimization**

• If you wish to override the optimized values, you must set the

above parameter to Yes in IDT/UDT

• Information Design Tool Guide (Chapter 18.4.1.7) covers this

option

Array Fetch Size Optimization (2/2)

• Query Filters modify the SQL query to restrict the data fetched and displayed

• Report Filters only modify the displayed data

– Example: A report returns a years worth of data. The consumer is only interested in January data

– Using a Query Filter, the WHERE clause is modified so that only January data is fetched by the query.

– Using a Report Filter to show only January data simply filters the data that is displayed. The full year’s worth of data is still in the underlying cube

Query Filters instead of Report Filters

• Query Stripping is new to BI 4.x. Strips away unused objects

from the query

• Can now be utilized for relational database as well as BICS

based reports (BI4.1 SP3 and up)

• Has to be turned on at the Report, Query and Universe levels

• BICS connections use stripping by default

Ensure Query Stripping is utilized

• For Relational DBs, following parameters must be set:

– Allow Query Stripping option selected in Business Layer

– Enable Query Stripping option selected in Query Properties of the

Webi Document

– Enable Query Stripping option select in Document Properties of the

Webi Document

• USE_ENHANCED_QUERY_STRIPPING parameter only optimizes

the SELECT and GROUP BY clauses but doesn’t modify the joins

and other clauses

More on Query Stripping

• Merging dimensions is necessary in many cases

• This has a performance impact as logic has to be applied to

create a merged dataset

• Unmerge dimensions that are not needed in a document to save

some time

Only merge dimensions that are needed

Tip #6 - Know the Architecture Differences

• XI 3.1 and below were fully 32-bit programs

• BI 4.x introduces many new 64-bit servers

• Main advantage is increased resource allocation with 64-bit

architecture

– No more 2GB memory limitations for WebI Processing Servers

• Can utilize 64-bit client drivers

• Changes some of the sizing recommendations

32-bit vs 64-bit Processing Servers (1/2)

New 64-bit processes in BI 4.x• Web Intelligence Processing Server• Adaptive Processing Server• Adaptive Job Server• Connection Server (64-bit in most cases)• Central Management Server (CMS)• File Servers• Tomcat/Application Servers (64-bit JVM support)

A few 32-bit processes• Connection Server (32-bit on some Windows)• Web Intelligence Rich Client• Universe Designer / Information Design Tool

32-bit vs 64-bit Processing Servers (2/2)

• Key to identifying a bottleneck is isolating the cause of the slowdown

• WebI documents can utilize many servers for processing

– Adaptive Processing Server (DSL Bridge)

– Adaptive Processing Server (Adaptive Connectivity)

– Adaptive Processing Server (Visualization Service)

– Connection Server 32-bit

– Web Intelligence Processing Server

• Case and Point – BI 4.x Architecture Diagram

Know which servers are involved!

Interactive Diagrams are available to help you learn about the servers

that are involved

• View a Webi document on-demand link

• Refresh a doc in two-tier mode link

• Refresh a doc in three-tier mode link

• Refresh a doc based on multisource universe link

• Refresh a doc based on a dimensional universe link

• Run a scheduled Webi document link

Learn these Web Intelligence Workflows

Tip #7 – Stick to a Schedule

• Scheduling allows lower user wait time when implemented correctly

• Allows you to offset processing to non-peak times

• Can help distribute load and reduce sizing requirements for

concurrent users

• Reduces impact on database during peak times

Why Use Scheduling?

• Best practice is to schedule any report that takes over 5 minutes to

refresh

• Allow power users to schedule reports On-Demand if needed

• You can use Report Linking + Scheduled Instances to strike a balance

between view time and age of data

• Use the cache settings to pre-cache XLS/PDF

Tips on Scheduling Web Intelligence Documents

Tip #8 – Server sizing for optimal performance

• BI 4.x is not sized for performance out of the box

• 64-bit processing servers changes the game vs 3.1

• Adaptive Processing Server is heavily utilized now

Refer to Sizing Companion Guide for details

Sizing for Performance

• 64-bit Webi Processing Server means no more memory

limitations

– May no longer need exactly 1 WIPS process per CPU core.

Large scale test on 24 core machine had just 5 WIPS.

• Adaptive Processing Server (APS) is heavily used for some

WebI workflows• BICS connections utilize DSL Bridge Service

• UNX Universes use DSL Bridge Service

• Charting uses Visualization Service

• MSU uses Data Federation Service

• Use System Configuration Wizard to help split the APS

Sizing – Things to know for Web Intelligence

• Location can make a big difference

– Ideally place your Processing Tier near your database

– This is where most of the data transfer will occur

– Use a fast connection between the WebI server and your database

• Using local storage for Cache and Temp directories can improve

performance

– Local storage is generally much faster I/O than network storage

• CPU speed DOES matter!

– More relevant on older hardware but 1200Mhz processors, even if there are

128 cores, will process documents slower

Sizing – Things to know for Web Intelligence

• #1 - A fast running applet

• #2 - Steer clear of monster reports

• #3 - Utilize report linking

• #4 - Report design best practices

• #5 - Semantic layer best practices

• #6 - Know the architecture differences

• #7 - Stick to a schedule

• #8 - Server sizing for optimal performance

Tips for better Web Intelligence performance

Thank you

• Contact

– Matthew Shaw, SAP

– @MattShaw_on_BI