OBIEE performance tuning guides are abundant these days. It seems any developer with a passing knowledge of OBIEE can make suggestions on how to make your reports run faster... "change this variable, disable logging, check this box." But what do the experts say?
Hogwash. "OBIEE performance tuning" implies performance issues can be solved with specific suggestions and solutions. But all customers are different, and thus the solutions are unique.
So instead of tuning, we optimize. Instead of dictating what must be changed, we work with the business to investigate solutions.
The simplest way to distinguish between tuning and optimizing is to look at the tasks. If the tasks are generalized and boilerplate, we're tuning. If the tasks include environment analysis and comparison of approaches, we're optimizing. Optimization provides true business value, where tuning only provides a facade of improvement.
How do we start optimizing? The best starting place is to have an understanding of where to start looking. Let's begin with best practices.
OBIEE Design Practices We Should All Observe
These are by no means practices you have to observe, but they’re good practices that keep OBIEE 12c and 11g running smoothly.
Tip: Copy and paste this list into a Word Document to create a checklist while you work on reconfiguring your OBIEE design.
- Ensure that an accurate Star model in the Logical (BMM) layer has been created to the RPD.
- Support user queries by producing aggregate tables.
- You can use Usage Tracking to find dimension levels that could benefit. (Use Summary Advisor for Exalytics).
- Take advantage of the Aggregate Persistence Wizard. It can do the preliminary work for you by loading metadata into the RPD for you.
- Create associated aggregated dimension tables.
- Don’t use OBIEE to extract data on the front-end.
- If users want to do a data dump, do it for them outside of OBIEE.
- To continue to use the metadata model in the RPD without it causing major problems in Presentation Services, use an ODBC or JDBC call into the BI Server to directly get the data dump out.
- Sit with your users to learn their workflow. You may be able to get them to their end answer more quickly in an analysis/report.
- Avoid building the RPD against database views; instead, put underlying tables into the physical layer of the RPD.
- Lessen the workload of the BI Server. Wherever you can, you should push work down to the database.
- Make sure you’re checking the percentage of data the BI Server is pulling back from the database returned to the user.
- If your BI Server’s temp directory is filled with large files, it’s having to crunch a lot of data.
- Your BI Server will be less efficient if one report triggers a high number of database queries.
- Ensure that the size of your hardware is sufficient enough to support the BI Server and the Presentation Server.
- Is your hardware configuration balanced throughout the stack?
Interested in a more in-depth look? Get the eBook...
Taking OBIEE Optimization a Step Further
The above steps are more like guidelines you should follow to always keep your system running smoothly. To further optimize OBIEE performance, you can take the more in-depth steps that we’ll cover in this section.
BI Server Caching
Caching can help improve your performance, but you need to make sure you’re managing it properly. Caching can improve performance in the following ways:
- Rather than re-executing the Physical SQL against the source databases for a query that has already been made, you can store it in the cache.
- When you’re not executing the Physical SQL on your databases, you reduce the workload on the database and the amount of network traffic. This frees up your databases for queries that aren’t cached.
- The cache doesn’t store the data from your database — it stores the result of a Logical SQL query sent to your BI Server. Your BI Server is already doing a lot of work; you can improve performance by caching the results so the query doesn’t have to be processed again.
The BI Server cache is smart — it doesn’t only recognize a direct match on a previous query. It can also recognize a subset or an aggregate of an existing cache entry.
To benefit your users, rather than them having to wait for someone to run a query, you can pre-seed the cache. There are two ways to do this:
- You can use an agent that is set to run after your ETL batch loads data.
- You can also do it directly from an ETL tool through ODBC/JDBC using the SASeedQuery statement.
You can also purge the cache by:
- Event Polling Table
- ODBC/JDBS command to the BI Server that is triggered when an ETL data load is completed
You also need to think about cache persistence time, which tells you how long an entry stays in the cache, rather than how often you’re purging it. Cache persistence time is useful if you’re making frequent changes to your source data and you want a lag in the data your user sees to get a better response time for end users.
Using Temporary Files and/or Fast Disk
You probably already know that OBIEE writes and stores temporary files, like your cache data, to your disk. You may be able to improve performance by moving some of your temporary files to your RAM disk or fast disk. Keep in mind that some of these temporary files can be very large.
Static Content Caching with Web Tier
A typical OBIEE system will have WebLogic acting as the application server and the HTTP server. You might be able to improve response time by using an addition HTTP server, like Oracle HTTP Server (OHS). That way WebLogic is simply the application server, and OHS can handle things like compressing static files, enabling things to run faster.
Scale Out / Scale Up
Scaling out refers to adding more physical servers that you can extend your BI domain on to. Scaling up refers to increasing the components that run on an existing server.
If you have a component that is close to reaching capacity, you should consider scaling. This is not something you should try otherwise — it will only increase the complexity of your system configuration and ultimately give you more work to do.
Tips for Optimizing Your Database
In this section, we’ll cover a few tips for database administrators looking to optimize OBIEE.
Here we’ll go over various technique so you can evaluate the physical implementation of your data model. These techniques include:
- Appropriately using Parallel query
- Partitioning
- Appropriate indexing
- Using correct data types, especially on join columns
- Using materialized views for holding pre-built aggregations of your fact data
- Statistics
- Ensuring that they are representative and accurate
- Using a proactive statistics management strategy, not simply relying on auto stats jobs
- Knowing that incremental statistics can be useful
- Resource manager is also a useful tool — it gives you granular control over things like parallelism
An Explanation of Oracle’s Tuning Document
You’re probably already aware of Oracle’s OBIEE tuning document. It’s a list of settings you can evaluate. However…
- There’s no context or method included on how to evaluate these settings.
- This document can be very useful, but it’s mostly useful at the end of a performance test, when you’re starting to resolve problems you’ve found.
- It doesn’t detail evaluation parameters. It simply tells you what to do without explaining why. For a system that performs well, you need to understand how it works and why — not simply implement a few changes you don’t fully understand.
- This document gives you the last step to optimizing your performance, giving the impression that’s all there is to performance optimization.
Optimizing OBIEE performance is something that should always be on your mind, but can often fall by the wayside. By taking the steps above, you can help alleviate some pain points and slowdowns you might be experiencing or prevent those problems in the future.
The Other Way to Optimize Oracle BI
Feeling overwhelmed? Take a deep breath.
It’s easy for me to provide the suggestions above, but it’s another thing entirely to actually make these changes. Performance optimization is difficult. Not to mention, causes for poor performance can vary significantly. The suggestions above may not be exactly what your organization requires. Or worse, your performance issues may be caused by something even more obscure, so your efforts may be all for naught.
Make it easier on yourself and your organization — outsource these optimization tasks. Your BI developers are better utilized by implementing business requirements, rather than parsing log files or analyzing SQL. Save your time, save your money, and leave performance issues to experts who fix them daily. It’s what we’re here for. Even better, our knowledge and experience are now available to you and your team through OptimizeBI.
Schedule a Custom OptimizeBI Demo:
OptimizeBI is the performance optimization tool tailormade for OBIEE, OAC, and ODI. By providing a full-stack overview of the environment, performance issues can be identified and resolved quickly. Coupled with experienced US-Analytics experts, your performance issues will be a thing of the past.
You'll be in touch with an optimization expert within 1 business day!
