In all my years of working with OBIEE, I have seen a special
interest in performance testing and tuning in the recent years. I have been
involved in recent past with one of the active engagements for an OBIA system
for performance optimization. This lead to a lot of interesting reading and
some on the job application, thought should share it with you all.
While Optimizing Performance the most challenging task is to
know where to begin. Most of the time Performance issues are raised or known
only when we are actually in the performance test phase of the project. Post
Analysis we should take the right approach for optimizing the performance and
not some shortcut ways. If the analysis suggests that the rpd metadata /
dashboard design/ built is the suspect or the underlying data model is not
modelled properly optimization techniques are not going to work and redevelopment
should be considered as it is rightly said
If a system that has a fundamental
design flaw cannot be optimized - it has to be redesigned.
By default whenever OBIEE reports take a long time to return
results, end users tend to say "OBIEE does not perform well". There might be
different reasons for it other than OBIEE some of them might be the underlying
database, network or infrastructure. We will discuss more about what we can do
within OBIEE for performance optimization considering we have all other levers
OBIEE Reports creates a SQL query with the help of BI server
and that is purely driven by how the data modelling is done is executed
directly against the underlying database. A quick look at the SQL query getting
generated can give us an idea whether best practices of OBIEE design are
followed or not. By following the best practices in OBIEE design the SQL query
generated can be made free of unnecessary clauses and nesting statements.
OBIEE itself has many out of box functionality for
performance optimization such as usage of aggregate tables, using cache,
changing the log levels and so on but are not enough to handle the performance
The components involved in Performance Tuning are
With the help of monitoring tools like Enterprise Manager
Metric Palette, Performance monitor, Server related statistics are collected
and should be acted accordingly.
Would list some of the performance optimization techniques
less known yet effective
Try Tuning the Connection backlog buffering and
Statement Cache on Oracle Weblogic server.
Try adjusting the accept backlog value which might be set too low due to which connections are dropped or
refused at the client.
Oracle BI Server performance can be improved by
prohibiting RPD updates. By doing this Oracle BI server is not needed to handle
Various configuration options can be used to
limit the maximum number of rows that can be downloaded, included in mail or
Limit the maximum number of rows that can be
processed to render a table. This significantly improves performance as it
reduces the system resources that will be consumed by user session.
Providing Permission to objects by groups/roles.
Union requests, consumes more memory as they
generate more temp file , hence should be avoided.
Load to BI resources can be reduced by avoiding
usage of guided navigation
Session variables use should be minimized.
Dimensional Hierarchy should be modelled
accurately so that Oracle BI optimizer can select the most economical source
Content tab of logical table sources should be
used to limit the number of rows returned from the database
Exploiting aggregate tables, pre-calculated
measures aggregated over a set of
dimensional attributes are stored in aggregated tables
Make NULL and NOT Null column in sync with RPD
and Database to avoid full outer join with NVL
Index for all Foreign Key columns (for Summary reports) and Bitmap Join Index
(lowest granularity based query for detailed data)
Usage tracking should be switched off if not
needed as it will avoid extra DB Operations that run against each query.
Remove Snow flaking and Create Foreign keys for
each dimension into Fact
I would like to emphasize that no
single solution or configuration can take care of all performance issues. Other than listed above there are many more
settings / ways that helps in performance optimization, my aim was to list some
of them covering highlights. Performance optimization might target Database,
Environment parameters, Data model, Repository Design or Report Design having
the same objective. While performance cannot be optimized by applying certain
set rules and standards, having faced different challenging environment and
It's all about working around and coming up with the best
solutions for the project understanding the project flows and business
scenario's. To have an optimized performance application we need to follow the
best practices and design standards.