preventing diagnosing and resolving the 20 most common dashboard performance problems n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Preventing, diagnosing, and resolving the 20 most common dashboard performance problems PowerPoint Presentation
Download Presentation
Preventing, diagnosing, and resolving the 20 most common dashboard performance problems

Loading in 2 Seconds...

play fullscreen
1 / 52

Preventing, diagnosing, and resolving the 20 most common dashboard performance problems - PowerPoint PPT Presentation


  • 54 Views
  • Uploaded on

Preventing, diagnosing, and resolving the 20 most common dashboard performance problems . Dr. Berg Comerit Inc. We already covered hardware sizing, compatibility and server options in a prior session, so now we will look at the application, design and interfaces.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Preventing, diagnosing, and resolving the 20 most common dashboard performance problems' - yaphet


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
preventing diagnosing and resolving the 20 most common dashboard performance problems

Preventing, diagnosing, and resolving the 20 most common dashboard performance problems

Dr. Berg

Comerit Inc.

background
We already covered hardware sizing, compatibility and server options in a prior session, so now we will look at the application, design and interfaces.

We will specifically look at dashboard design, query design, connectivity impacts, in-memory processing options as well as dashboard performance monitoring options.

Background
what we ll cover
Background

Connectivity and Backend

Query Performance

Dashboard Design

Infrastructure and In-Memory Processing

Pre-Caching and Aggregates

Performance Testing: Load and Stress

EarlyWatch and The Performance Checklist

Wrap-up

What We’ll Cover …
connectivity and performance
As we covered in the earlier session, the type of connectivity matters for the performance

1. BICS connectors performs well

2. Avoid the MDX interface

(it is slow)

3. Avoid direct

access to the

InfoProviders

since this

bypasses

the BI analytical

engine in SAP

BW.

Connectivity and Performance

Source: SAP AG, 2011

Always pick the fastest interface available for the data source you are building dashboard on

data connectivity crystal reports and live office
Data Connectivity — Crystal Reports and Live Office
  • You can use transient providers to create real-time dashboards on-top of ERP data.
  • You can also use Crystal for detailed drill-down analysis.
  • If you always use the "refresh on load” option for Live Office connections, your users will experience periodic slow performance.

By leveraging the aggregation in Crystal Reports 2011, you can also get faster Xcelsisus dashboard response time.

backend build on a solid performance foundation
Backend - Build on a Solid Performance Foundation

Real example

Modularize the data and create sub-sets of data for really fast dashboarding.

Generic 'metrics' data tables can be created for summarized KPI and scorecard dashboards.

The summary, or snapshot data can be accessed much faster than underlying data tables with millions of records.

backend webi and xcelsisus performance architecture
Backend - WebI and Xcelsisus Performance Architecture

Real example

In this example, the company use snapshots for performance reasons

  • Dashboards for executive users
  • Pre-delivered WebI reports for casual users
  • Ad-hoc WebI reports for power users

The dashboards are only built on the low volume daily snapshot cube (this is also placed in BWA for very high-performance).

what we ll cover1
Background

Connectivity and Backend

Query Performance

Dashboard Design

Infrastructure and In-Memory Processing

Pre-Caching and Aggregates

Performance Testing: Load and Stress

EarlyWatch and The Performance Checklist

Wrap-up

What We’ll Cover …
query read modes
Query Read Modes
  • There are three query read modes that determines the amount of data to be fetched from a database and sent to the application server:
    • 1. Read all data
    • All data is read from a database and stored in user memory space
    • 2. Read data during navigation
    • Data is read from a database only on demand during navigation
    • 3. Read data during navigation and when expanding the hierarchy
    • Data is read when requested by users in navigation

Key Feature: Reading data during navigation minimizes the impact on the application server resources because only data that the user requires will be retrieved

recommendation query read mode for large hierarchies
Recommendation: Query Read Mode for Large Hierarchies

Reserve the Read all data mode for special queries— i.e. when a majority of the users need a given query to slice and dice against all dimensions, or data mining

This places heavy demand on database and memory resources and may impact other BW processes

A query read mode can be defined on an individual query or as a default for new queries (transaction RSRT)

  • For queries involving large hierarchies, it is smart to select Read data during navigation and when expanding this option to avoid reading data for the hierarchy nodes that are not expanded.
  • Recommendations for OLAP Universes & WebI analysis
    • 1. Use of hierarchy variable is recommended
    • 2. Hierarchy support in SAP Web Intelligence for SAP BW is limited
    • 3. The Use Query Drill option significantly improves drilldown performance
    • 4. Look at the 'Query Stripping' option for power users.

10

reduce the use of conditions and exceptions reporting
Reduce the use of conditions-and-exceptions reporting
  • Conditions & exceptions are usually processed by the application server
      • This generates additional data transfer between database & application servers
  • If conditions and exceptions have to be used, the amount of data to be processed should be minimized with filters
    • When multiple drilldowns are required, separate the drilldown steps by using free characteristics rather than rows and columns
  • BENEFIT: This results in a smaller initial result set, and therefore faster query processing and data transport as compared to a query where all characteristics are in rows

This approach separates the drill-down steps. In addition to accelerating query processing, it provides the user more manageable portions of data.

performance settings for query execution
This decides how many records are read during navigation.Performance settings for Query Execution

In 7.x BI: OLAP engine can read deltas into the cache. Does not invalidate existing query cache.

Examine the request status when reading the InfoProvider

Turn off/on parallel processing

When will the query program be

regenerated based on database

statistics

Displays the level of statistics collected.

filters in queries used in dashboards
Using filters contributes to reducing the number of database reads and the size of the result set, thereby significantly improving query runtimes.

Filters are especially valuable

when associated with large

dimensions, where there

is a large number of

characteristics such as

customers and document

numbers.

Filters in Queries used in Dashboards
the rsrt transaction to examine slow queries
The RSRT Transaction to examine slow queries

P1 of 3

The RSRT transaction is one of the most beneficial transaction to examine the query performance and to conduct 'diagnostic' on slow queries from the BW system.

do you need an aggregate some hints
Do you need an aggregate - some hints

P2 of 3

This suggests that an Aggregate would have been beneficial

get database info
Get Database Info

P3 of 3

In this example, the basis team should be involved to research why the Oracle settings are not per SAP's recommendation

The RSRT and RSRV codes are key for debugging and analyzing slow queries.

HINT: Track front-end data transfers & OLAP performance by using RSTT in SAP 7.0 BI (RSRTRACE in BW 3.5)

debug queries using the transaction rsrt
Debug Queries using the transaction- RSRT

Using RSRT you can execute the query and see each breakpoint, thereby debugging the query and see where the execution is slow.

Try running slow queries in debug mode with parallel processing deactivated to see if they run faster.

slide19

Recommendation for Key Figures in OLAP universes

  • A large number of Key Figures in the BEx query will incur a significant performance penalty when running queries, regardless of whether the Key Figures are included in the universe
  • Only include KFs used for the dashboard in the BEx query (keep it small)
  • This performance impact is due to time spent loading metadata for units, executed for all measures in the query

After SAP BusinessObjects Enterprise XI 3.1 FP 1.1, the impact of large number of key figures was somewhat reduced by retrieving metadata information only when the unit/currency metadata info is selected. However, this is still best practice

18

slide20

The Performance Killers - Restrictive Key Figures

When Restrictive Key Figures (RKF) are included in a query, conditioning is done for each of them during query execution. This is very time consuming and a high number of RKFs can seriously hurt query performance

My Recommendation: Reduce RKFs in the query to as few as possible. Also, define calculated & RKFs on the Infoprovider level instead of locally within the query. Why?:

Benefit: Formulas within an Infoprovider are returned at runtime and held in cache.

Drawback: Local formulas and selections are calculated with each navigation step.

slide21

Dashboard Performance Killers - Calculated Key Figures

Calculated Key Figures(CKF) are computed

during run-time, and a many CKFs can slow

down the query performance.

How to fix this:

Many of the CKF can be done during data loads & physically stored in the InfoProvider. This reduces the number of computations and the query can use simple table reads instead. Do not use total rows when not required (this require additional processing on the OLAP side).

Recommendation for OLAP universes:

RKF and CKF should be built as part of the underlying BEx query to use the SAP BW back-end processing for better performance

Queries with a larger set of such KFs should use the “Use Selection of Structure Members” option in the Query Monitor (RSRT) to leverage the OLAP engine

what we ll cover2
Background

Connectivity and Backend

Query Performance

Dashboard Design

Infrastructure and In-Memory Processing

Pre-Caching and Aggregates

Performance Testing: Load and Stress

EarlyWatch and The Performance Checklist

Wrap-up

What We’ll Cover …
dashboard performance hint the number of rows in the result set
Limit the number of rows in your result set to between 100 - 500Dashboard Performance Hint: The Number of Rows in the Result Set

In exceptional cases when you have leveraged other performance tuning methods, you may extend this to up to 1,000 rows.

The Length of each records (# of columns) and the data type also impacts performance

Returning query result sets with few records of a numeric type or with keys and indicators provides for the best dashboard performance

divide and get performance
Divide and Get Performance

Link to Details Dashboard

Drilldown Options

Split your dashboards into logical units & get new data when drilldowns are executed. This keeps the result set for each query small and also decreases the load time for each dashboard

excel performance considerations what to avoid
Excel Performance Considerations - What to Avoid
  • The logic you build into your Excel Spreadsheet is also compiled into the flash file when you export it.
  • Since some 'daisy-chain' functions are very time consuming, you should be careful to not add to many condition in the data. Lookup functions and conditioning that should be avoided include:
      • Lookups
        • Mid strings (MID)
        • Right and left strings (RIGHT/LEFT)
        • Horizontal Lookups (HLOOKUP)
        • Vertical Lookups (VLOOKUP)
      • Condition
        • General conditioning (IF)
        • Count if a condition is true (COUNTIF)
        • Sum if a condition is true (SUMIF)

Complex logic and nested logic creates large swf files takes a long time to open. Try to keep as much of the calculations and logic in the query instead of the spreadsheet.

slide26

The BI Analytical Engine and Sorting

Sorting is done by the BI Analytical Engine. Like all computer systems, sorting data in a reports with large result sets can be time consuming.

Reduce the number of sorts in the 'default view'. This will provide the users with data faster. They can then choose to sort the data themselves.

User Sorts themselves

Hint: Reducing the text in query will also speed up the query processing time

25

slide27

Dashboard Objects that Can Cause Slow Performance

These are dashboard objects you need to carefully consider before employing them.

26

what we ll cover3
Background

Connectivity and Backend

Query Performance

Dashboard Design

Infrastructure and In-Memory Processing

Pre-Caching and Aggregates

Performance Testing: Load and Stress

EarlyWatch and The Performance Checklist

Wrap-up

What We’ll Cover …
it is all about performance performance performance
It is all about Performance, Performance, Performance

It is hard to build a fast dashboard with many queries and panels without BW Accelerator. This provides in-memory processing of queries that is 10-100 faster.

What we simply do is placing the data in-memory and retrieving it much faster. There are also some limited OLAP functionality that can be built in BWA 7.3, but most data processing still occurs in the BI Analytical engine.

You can also place non-SAP data in-memory, using BOBJ data Services.

bw accelerator performance increases real example
BW Accelerator Performance Increases - real example

Number of Queries

Seconds

The major improvement is to make query execution more predictable and overall faster

Number of Queries

Seconds

bi analytical engine s query executing priorities
BI Analytical Engine’s Query Executing Priorities

Query ExecutionWithout SAP NetWeaverBW Accelerator

Query ExecutionWith SAP NetWeaver BW Accelerator

Information Broadcasting /Precalculation

Information Broadcasting /Precalculation

Query Cache

Query Cache

Aggregates

SAP BW Accelerator

InfoProvider

Aggregates can be replaced with SAP BW Accelerator, while the memory cache is still useful.

what we ll cover4
Background

Connectivity and Backend

Query Performance

Dashboard Design

Infrastructure and In-Memory Processing

Pre-Caching and Aggregates

Performance Testing: Load and Stress

EarlyWatch and The Performance Checklist

Wrap-up

What We’ll Cover …
different uses of the mdx and the olap cache
Different Uses of the MDX and the OLAP Cache

The OLAP Cache is used by BW as the core in-memory data set. It retrieves the data from the server if the data set is available.

The Cache is based on First-in --> Last out.

This means that the query result set that was accessed by one user at 8:00am may no longer be available in-memory when another user is accessing it at 1:00pm.

Therefore, queries may appear to run slower sometimes.

The MDX cache is used by MDX based interfaces, including the OLAP Universe.

use the bex broadcaster to pre fill the cache
Use the BEx Broadcaster to Pre-Fill the Cache

Distribution Types

You can increase query speed by broadcasting the query result of commonly used queries to the cache.

Users do not need to execute the query from the database. Instead the result is already in the system memory (much faster).

the memory cache size
The Memory Cache Size

The OLAP Cache is by default 100 MB for local and 200 MB for global use

This may be too low...

Look at available hardware and work with you basis team to see if you can increase this.

If you decide to increase the cache, use the transaction code RSCUSTV14.

WARNING: The Cache is not used when a query contains a virtual key figure or virtual characteristics, or when the query is accessing a transactional DSO, or a virtual InfoProvider

monitor application servers and adjust cache size
Monitor Application Servers and Adjust Cache Size

To monitor the usage of the cache on each of the application servers, use transaction code RSRCACHE and also periodically review the analysis of load distribution using ST03N – Expert Mode

PS! The size of OLAP Cache is physically limited by the amount of memory set in system parameter rsdb/esm/buffersize_kb.

The settings are available in RSPFPAR and RZ11.

correct aggregates are easy to build
Correct Aggregates Are Easy to Build

We can create proposals from the query, last navigation by users, or by BW statistics

Create aggregate proposals based on queries that are performing poorly.

  • Create aggregate proposals based on BW statistics. For example:
  • Select the run time of queries to be analyzed
  • Select time period to be analyzed
  • Only those queries executed in this time period will be reviewed to create the proposal
activate the aggregate
Activate the aggregate

The process of turning 'on' the aggregates is simple

what we ll cover5
Background

Connectivity and Backend

Query Performance

Dashboard Design

Infrastructure and In-Memory Processing

Pre-Caching and Aggregates

Performance Testing: Load and Stress

EarlyWatch and The Performance Checklist

Wrap-up

What We’ll Cover …
performance testing load and stress
Load testing is done to 20% of the named user base

Turn off the cache (we assume all hits 'new data')

Execute the Dashboard URLs using a tool or a simple JavaScript

Monitor database, portal and BI system load

Log response time and have multiple browsers and PCs hitting the data from multiple locations (network testing)

Stress Testing is done at 40% of named user base

The test is done the same way as on the load testing, just with more 'users'

The system may not be able to pass at this level, but the break-points are identified

Performance Testing: Load and Stress

All Dashboard systems should be load tested to 20% of user base prior to go-live

server locations and network capacity
Having a central global install of BI 4.x with many users, can cause significant network load and performance issuesServer Locations and Network Capacity

Consider the network topology, capacity and the user locations before implementing global dashboards

what we ll cover6
Background

Connectivity and Backend

Query Performance

Dashboard Design

Infrastructure and In-Memory Processing

Pre-Caching and Aggregates

Performance Testing: Load and Stress

EarlyWatch and The Performance Checklist

Wrap-up

What We’ll Cover …
earlywatch reports in solution manager
EarlyWatch reports provide a simple way to confirm how your system is running and to catch problems

A “goldmine” for system recommendations

EarlyWatch reports are available since Solution manager version 3.2 SP8.

The more statistics cubes you have activated in BW, the better usage information you will get. Depending on your version of SAP BW, you can activate 11-13 InfoCubes. Also, make sure you capture statistics at the query level (set it to 'all').

EarlyWatch Reports in Solution Manager

System issues can be hard to pin-down without access to EarlyWatch reports. Monitoring reports allows you to tune the system beforeuser complains

information about an pending disaster
Information about an pending 'disaster'

This system is about to 'crash'

The system is growing by 400+ Gb per month, the app server is 100% utilized and the Db server is at 92%.

This customer needed to improve the hardware to get the query performance to an acceptable level

the dashboard performance checklist
1. The hardware servers - Check Sizing

2. The server locations and networks - Check Loads

3. Query review - Look at database time, calculation time and design

4. Interface review - Make sure you are using the best for the data source

5. Dashboard review - Look at Excel logic, container usage, number of flash objects, sorts, size of result set & simplification opportunities

6. In-memory review - Look at cache usage, hit rations and BWA usage

7. Review data sources - Examine if snapshots can be leveraged and look for

possibilities to create aggregates

8. Examine compatibilities between browsers, flash and office versions

9. Review PC performance issues - memory, disk and processors

The Dashboard Performance Checklist

Performance is complex, look at more than one area (i.e. web portal bottlenecks and LDAP servers)

what we ll cover7
Background

Connectivity and Backend

Query Performance

Dashboard Design

Infrastructure and In-Memory Processing

Pre-Caching and Aggregates

Performance Testing: Load and Stress

EarlyWatch and The Performance Checklist

Wrap-up

What We’ll Cover …
resources
Tuning SAP BusinessObjects Solutions for Optimal Performance: Tips from the Trenches by Chris Dinkel

Requires log-on at www.SAPInsider.com

SAP Business Objects Tuning by Steve-Bickerton

wp.broadstreetdata.com/wp-content/uploads/BOCX-Speaker-Performance-Tuning_-Steve-Bickerton.pdf

SAP MarketPlace for Sizing guidelines

SBO_BI_4_ 0_Dashboard_designer.pdf - requires log-on to service.sap.com

Resources
7 key points to take home
Dashboards are all about performance, performance and performance

You have to spend time on the backend performance tuning

Avoid direct querying of high data volumes, create summaries instead

Consider in-memory processing for all critical dashboards

Your interface to the data will impact the performance - avoid MDX

Size your hardware one size 'too big' - it is hard to make a second 'first impression'.

Use a gradual rollout of your dashboards, monitor the performance and conduct load and stress tests before any major go-lives.

7 Key Points to Take Home
your turn
Your Turn!

How to contact me:

Dr. Berg

bberg@comerit.com

disclaimer
SAP, R/3, mySAP, mySAP.com, SAP NetWeaver®, Duet®, PartnerEdge, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and in several other countries all over the world. All other product and service names mentioned are the trademarks of their respective companies. Wellesley Information Services is neither owned nor controlled by SAP.Disclaimer