Preventing, Diagnosing, and Resolving the 20 Most Common Dashboard Performance Problems

makeshiftklipInternet and Web Development

Oct 31, 2013 (3 years and 7 months ago)

105 views

©
2012
Wellesley Information Services. All rights reserved.

Preventing, Diagnosing,

and Resolving the 20

Most Common Dashboard
Performance Problems


Dr. Bjarne Berg

Comerit

What We’ll Cover …


Backend Database Data Design


Exploring query performance


Dashboard design and Hardware Sizing


Increasing query performance with infrastructure and in
-
memory
processing


Leveraging pre
-
caching capabilities and aggregates


EarlyWatch Reports, Performance Testing and Server location


Wrap
-
up

1

Problem #1: Back End


Build on a Solid

Performance Foundation

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

Problem #2: Back End


Dashboard Performance
Architecture


In this example, the company uses snapshots for performance reasons


3


Dashboards for

executive users


Pre
-
delivered SAP

BusinessObjects

Web Intelligence

reports for casual users


Ad hoc SAP BusinessObjects Web
Intelligence reports for power users



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

What We’ll Cover …


Backend Database Data Design


Exploring query performance


Dashboard design and Hardware Sizing


Increasing query performance with infrastructure and in
-
memory
processing


Leveraging pre
-
caching capabilities and aggregates


EarlyWatch Reports, Performance Testing and Server location


Wrap
-
up

4

Problem #3: Query Read Modes


There are three query read modes that determine 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


5

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

Problem #4: Reduce the Use of Conditions & Exceptions Reporting


Conditions and exceptions are usually processed by the
application server


This generates additional data transfer between database and
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 drill
-
down
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 SAP BW Query Execution

This decides how many records are read during navigation

7

Examine
the
request status
when reading
the InfoProvider


In
SAP NetWeaver
BW 7.x the BI
Analytical engine
can read deltas into
the cache. Does not
invalidate existing
query
cache.

Displays the level of
statistics
collected

Turn off/on parallel
processing

When will the
query program be

regenerated based
on
database
statistics?

Problem #5: Filters in BW 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


8

Filters are especially
valuable when associated
with large dimensions,
where there is a large
number of characteristics
such as customers and
document numbers


Problem #6: The RSRT Transaction to Examine Slow Queries


9

P1 of
3

The RSRT transaction is one

of the
most beneficial transactions to
examine the query performance and
to conduct “diagnostics” on slow
queries from the SAP NetWeaver
BW system


Do You Need an Aggregate


Some Hints

10

This suggests that an Aggregate
would have been beneficial

P2 of
3

Get Database Info

11


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

P3 of
3

HINT: Track front
-
end
data transfers
and
OLAP
performance by using RSTT in SAP
NetWeaver BW 7.3
(RSRTRACE in
SAP BW
3.5)

Problem #7: Debug Queries Using the RSRT Transaction


12

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



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

Problem #8: 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 key figures and RKFs on the
InfoProvider level instead of locally within the query. Why?


13

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


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

#9: Dashboard Performance Killers


Calculated Key Figures


Calculated Key Figures (CKF) are computed during

runtime, and many CKFs can slow down the query

performance


How to fix this


Many of the CKF can be done during data loads and 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 requires additional
processing on the OLAP side)


14

Recommendation for OLAP universes


RKF and CKF should be built as part of the
underlying BEx query to use the SAP NetWeaver 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 Cover …


Backend Database Data Design


Exploring query performance


Dashboard design and Hardware Sizing


Increasing query performance with infrastructure and in
-
memory
processing


Leveraging pre
-
caching capabilities and aggregates


EarlyWatch Reports, Performance Testing and Server location


Wrap
-
up

15

Functionality vs. Performance


What Wins?

16

Problem #10: Dashboard Performance Hint


The Number
of Rows in the Result Set

17

Limit the number

of rows in your

result set to

between 100


500


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

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

performance


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


Divide and Get Performance

Drill
-
down
o
ptions

Link to Details
Dashboard


Split your dashboards into logical units and 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

18

Problem #11: 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 not to add too many conditions 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)


19

Complex logic and nested logic create large SWF files and take a long time to open. Try to
keep as much of the calculations and logic in the query instead of the spreadsheet.

These are dashboard objects that you need
to carefully consider before employing

Problem #13: Dashboard Objects That Can Cause Slow Performance

20

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

User Sorts themselves

Problem #12: The BI Analytical Engine and Sorting


Sorting is done by the BI Analytical Engine


Like all computer systems, sorting data in a

report 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.

21

Sizing Servers Correctly (#14): The Sizing Tool

SAP has
provided a sizing
tool for the BI
environments. It
is based on
Flash and is
actually a
dashboard itself.


Download it

from
SDN at:
http://tinyurl.com/9yo5ag4

22

This tool can help you size your BI 4.0 environments
with a few key assumptions and inputs.

Output Area
(Sizing Results)

Input Areas
(items and users)

The Sizing Tool


Entering Users

First, you have
to enter the
estimated
active
concurrent
users (ACU) for
the following
user types:



Information
Consumers


Business Users


Expert Users

23

The Sizing Tool

On
-
Line Help User Definitions

The tool provides on
-
line
definitions of the user types
and guidelines on how to
determine Active Concurrent
Users (ACU). This is defined as
approximate 10% of the active
users.

24

Many dashboard users is large

organizations may be classified
as Information Consumers
.
They may not wait 5 minutes
between clicks, but typically do
little drill
-
down and filtering.

The Sizing Tool


Assumptions


The next step is to make an assumption on the size of dashboards.


The sizing tool classifies small dashboards as having 25 rows in the
result set, medium having 250, and large dashboards having 2,500 rows.

25

Assumptions:
the tool was based on supporting two queries per
dashboards, and benchmarked was for accessing two relational data
sources. One with 6 dimensions with 77,000 entries and 400,000 line
items, and one with 6 dimensions with 7,000 rows and 40,000 line items.

The Sizing Tool


Output

The output of the tool is measured in SAP Application Performance Standard (SAPS).
100 SAPS is defined as 2,000 fully business processed order line items per hour.


It is a measure that hardware vendors can use to decide which of their configurations
can meet your performance requirements. All hardware vendors are familiar with this
measure and this is what you will provide them when requesting a hardware quote.

26

The Sizing Tool


Memory Requirements

27

The sizing tool also provide a sizing estimate for the
hardware memory required for each of the tiers.
This is measured in Gigabytes

The Sizing Tool


Terminology


If you get stuck on the
terminology used in SAP
sizing and performance
benchmarking, there is a
link to the SAP benchmark
glossary in the tool.

28

There are also performance benchmark and installation guides
available on SAP Marketplace for individual software components.

The Sizing Tool


Saving your Sizing example

29

Your BI and dashboard sizing effort can be saved or printed
from the tool and you can have many scenarios

The Sizing Tool


Demo

30

DEMO

The Sizing Tool


Companion Guide


With the BI sizing tool, there is also
a sizing companion guide written
by Jason DeMelo.


This document explains how each
tool was benchmarked and the
assumptions made when building
the sizing tool.


You can download it from:


https://service.sap.com/~sapdownload/011000358700000307202011E/S
BO_BI_4_0_Companion_V4.pdf
(requires log
-
on)

31

Involve your basis team in the sizing effort and also make sure that
the assumptions you made are realistic from a functional standpoint
(i.e. how complex and intensive are your dashboards really).

What We’ll Cover …


Backend Database Data Design


Exploring query performance


Dashboard design and Hardware Sizing


Increasing query performance with infrastructure and in
-
memory
processing


Leveraging pre
-
caching capabilities and aggregates


EarlyWatch Reports, Performance Testing and Server location


Wrap
-
up

32


It
is hard to build a fast dashboard with many
queries and panels without
SAP NetWeaver

BW Accelerator or SAP HANA


This
provides in
-
memory processing of queries
that is
10 to 1000 times faster





Problem #15: It Is All About Performance, Performance,
Performance

What we simply do is place the data in
-
memory and access it

faster


For BWA, there is also some limited OLAP functionality that can
be built into SAP NetWeaver BW Accelerator 7.3, but most data
processing still occurs in the BI Analytical engine (unlike HANA
which does it all in
-
memory)


You can also place non
-
SAP data in
-
memory for HANA and BWA using
SAP BusinessObjects Data Services

33

Looking Inside SAP HANA


In
-
Memory Computing Engine
(IMCE)

Disk Storage

Data
Volumes

BusinessObjects Data Services

Log

Volumes

AAAA

Metadata
Manager

Authorization

Manager

Transaction

Manager

Relational
Engine



-
Row Store

-
Column Store

Load
Controller

SQL Script

Calculation

Engine

Replication Server

SQL Parser

MDX

Session
Manager

Inside the Computing Engine of SAP HANA we have many different components
that manage the access and storage of the data. This include MDX and SQL
access, as well as Load Controller (LC) and the Replication Server.

SAP HANA


Vendors and Appliance Options


The vendors that provide SAP HANA solutions include Cisco, Dell,
IBM,
Intel,
HP,
and Fujitsu as of
Sept. 2012


SAP HANA generally consists of:


The database and database
clients


HANA
studio (P2 repository)


Load controller and
Sybase
replication server


The host agent and LM
structure files


Op. Sys. configuration, SAPCAR & SAP JVM


The update
manager for SAP HANA


Fujitsu

HP DL 580 G7

Dell R910

IBM System
x3950 X5

Cisco UCS C460 M2

*per node (can link several servers together)

Reporting and BI Tools on HANA

ERP

Database

HANA Appliance

In

Memory
Computing
Engine

Sybase
Replication
Server

SAP BW

3
rd

Party

SAP BusinessObjects 4.0

Others

SQL (JDBC / ODBC)

DBSQL

BICS

SQL (JDBC / ODBC)

MDX (ODBO)

BusinessObjects Data Services

Real
-
time

A great benefit is the real
-
time loading of SAP HANA from
ERP. This can provide real
-
time analytics to end
-
users.

Currently, HANA supports Excel 2010 standard MDX

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

Seconds

Number of Queries

Number of Queries

Seconds

SAP NetWeaver BW Accelerator Performance Increases


Real Example

37

What We’ll Cover …


Backend Database Data Design


Exploring query performance


Dashboard design and Hardware Sizing


Increasing query performance with infrastructure and in
-
memory
processing


Leveraging pre
-
caching capabilities and aggregates


EarlyWatch Reports, Performance Testing and Server location


Wrap
-
up

38

Problem #16: Different Uses of the MDX and OLAP Cache


The OLAP Cache is used by SAP NetWeaver 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


䱡st 潵o


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


Therefore, queries may appear to run slower sometimes



39

The MDX cache is used by MDX
-
based
interfaces, including the OLAP universe

Use the BEx Broadcaster to Pre
-
Fill the Cache

40

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 OLAP Cache is, by default, 100MB for local and 200MB 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


The OLAP 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

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


42

P.S.! The
size of
the 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

Problem #17: Correct Aggregates Are Easy to Build

43

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


Create aggregate proposals based
on
BW statistics. For example:


Select the runtime of queries to
be analyzed


Select the time period to be
analyzed


Only those queries executed in
this time period will be

reviewed to create the proposal


Create aggregate proposals based
on
queries
that are performing
poorly

What We’ll Cover …


Backend Database Data Design


Exploring query performance


Dashboard design and Hardware Sizing


Increasing query performance with infrastructure and in
-
memory
processing


Leveraging pre
-
caching capabilities and aggregates


EarlyWatch Reports, Performance Testing and Server location


Wrap
-
up

44

Problem #18: Performance Testing


Load and Stress

Load testing is done to 20% of the named users


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


Execute the dashboard URLs using a tool or 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



45

All dashboard systems should be load tested

to 20% of user base prior to Go
-
Live

Problem #19: Server Locations and Network Capacity


Having a central global install of SAP BusinessObjects BI 4.x with
many users can cause significant network load and performance
issues


46

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

EarlyWatch Reports (#20) in SAP 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 have been available since SAP Solution
Manager version 3.2 SP8


The more statistics cubes you have activated in SAP NetWeaver
BW, the better usage information you will get


Depending on your version of SAP NetWeaver BW, you can
activate 11
-
13 InfoCubes


Also, make sure you capture statistics at the query level (set
it to “all”)

47

System issues can be hard to pin
-
down without access to
EarlyWatch
Reports
.
Monitoring
reports
allow
you to tune the
system
before

a user complains.


Information About a Pending “Disaster”

48

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 & calculation time, & 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, and simplification opportunities

6.
In
-
memory review


Look at cache usage, hit rations, and SAP NetWeaver BW
Accelerator 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 Microsoft office versions

9.
Review PC performance issues


Memory, disk, and processors



49

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

What We’ll Cover …


Backend Database Data Design


Exploring query performance


Dashboard design and Hardware Sizing


Increasing query performance with infrastructure and in
-
memory
processing


Leveraging pre
-
caching capabilities and aggregates


EarlyWatch Reports, Performance Testing and Server location


Wrap
-
up

50

Resources


Tuning SAP BusinessObjects Solutions for Optimal
Performance: Tips from the Trenches by Chris Dinkel


The SAP BusinessObjects seminar (SAPinsider, 2011)


SAP Business Objects Tuning by Steve Bickerton


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


SDN Sizing Tool


http://tinyurl.com/9yo5ag4


51

7 Key Points to Take Home


Dashboards are all about performance, performance, and
performance


You have to spend time on the back end 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



52

Your Turn!

53

How to contact me:

Dr. Bjarne 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 wor
ld.

All other product
and service names mentioned are the trademarks of their respective companies. Wellesley Information Services is neither owned

no
r controlled by
SAP.

54