So far in this book, you have installed your system, set up your database sources, created dashboards, and have monitored the usage. This is not the time to sit back and relax! You have installed OBIEE on a machine with the suitable levels of CPU, disk speed, and RAM, but is your system actually performing well?
In this chapter, we will learn some common techniques to reduce common bottlenecks that can exist in the process of delivering dashboards and reports to the users. We will look across the whole system, defining poor performance and where required, take steps to improve the performance.
If a dashboard loads in 20 seconds, is that good or bad performance? We tend to answer this question in terms of user expectations and technical capability. User expectations are what we manage every day on a project and sometimes these are really easy to meet, other times totally impossible! We recently had a client who was switching from a very slow reporting system (which will remain nameless) where a report would take 45 minutes to run. When we replaced the report with a dashboard that took 20 seconds, they were delighted. A similar dashboard, at another recent investment-banking client, was deemed to be far too slow at 20 seconds, as they required information in less than 3 seconds per dashboard page. Because the first client was happy with a 20-second wait, there was no further performance work undertaken, even though I knew that we could get the report down to less than 3 seconds. The banking client demanded better performance so we spent hundreds of man hours reducing the dashboard time down to a 1 second response.
To calculate potential efficiency savings, add up the total number of hours per year that your users wait for information and set a reasonable target that they should be waiting. That target should even go as low as 1 second per query!
If your client is happy with the response times, but your current system is technically capable of reduced dashboard delivery times, then should you spend time delivering these improved times? The simple answer is "yes". As your system grows, with more users and more dashboards, there will certainly be a hit to performance overall. Improving the performance of every dashboard, request, and report will help to increase total capacity and keep your project being funded and expanded. There will probably be a need to prioritize the order in which to improve performance of dashboards, and for this I suggest a combination of client priorities and picking on the slowest ones first. In practice, this means that the estimated total time to develop a dashboard should always include an element at the end to review performance and to make suitable changes.
As we have seen in the previous chapters, there are several components in your OBIEE system. Each one of these can be tuned to help improve performance, along with some other factors. The areas to look for the most gains are:
- Hardware
- Database design
- BI Server
- Web Server
- Domain settings
- Network
We will look at each of these to see the techniques that could be employed. For each of the preceding areas there are three main ways to improve performance:
- Do less work: It means making parts of the system work with smaller datasets, such as using aggregate fact tables
- Do the same work, but faster: It means getting better equipment that can cope with the volumes of data and with the system components
- Cheating: It means getting the results ready before the user needs them
There are no silver bullets when it comes to improving performance, but there are some recent developments in hardware that come pretty close. Massive gains can be had in request response times by buying some impressive new hardware. The first piece of hardware that has been available for a few years is the Oracle Exadat machine. This Oracle Exadata Storage and Database machine has transformed the ability of Oracle databases by bringing together several technology advances into the one box.
The statistics for a full system are very impressive:
- 576 CPU cores
- 24 TB RAM for database processing
- 280 CPU cores dedicated to SQL processing
- Eight database servers
- 14 storage servers
- 360 TB of flash cache
- 40 Gbit/sec InfiniBand
- Up to 224 TB space per rack
- Up to 18 racks can be connected
The preceding stats can transform even the most stubborn client demands into reality. For example, a recent proof of concept that we undertook reduced a dashboard response from over 3 minutes to 8 seconds. Another recent client uses an Exadata box and has over 2 terabytes of live data that is regularly used in dashboards. So far, the performance has been so good that no tuning has been required by the end users (although we will still tune for expansion).
At the same time that OBIEE 11g was released, Oracle also released the Exalytics in-memory machine. Another super-fast machine from the Sun part of Oracle, but this time aimed directly at OBIEE users (Exadata is for any database use). The Exalytics machine contains a huge amount of memory and lots of processing power, along with some in-memory software that will optimize the way the data is extracted for OBIEE to use. Also included in the machine is the OBIEE software itself, thereby reducing the need for another set of application servers.
Its impressive stats include:
- 3 terabyte of RAM
- 72 CPU cores on 10 quad-core chips
- InfiniBand networking
The use of OBIEE on an Exalytics machine enables a special Admin tool feature called Summary Advisor. This tool uses the usage tracking statistics to provide advice on which summary tables will improve performance, and then helps to generate in-memory TimesTen database tables that are then integrated into the BI Server Repository. These tables are normally aggregated data that will respond quicker than going to your database source. The result is instant response times on dashboards that previously could take minutes and, therefore, will allow for more concurrent users, and more advanced features, such as the new graphical tools and master detail instant reports.
If you combine the power of the Exadata Storage and Database machine with the clever power of the Exalytics machine, you will have a superb platform to delight end users. The Exadata Database machine is used for the warehouse and it is the source for a large detailed analysis, with the Exalytics machine providing the summary aggregated data and Essbase cubes.
Another way to use hardware to help with performance is to spread the load. If your budget does not allow for Exadata machines then you could try adding more commodity machines and use a larger cluster of OBIEE servers. Remember that the OBIEE server software is designed for a certain number of users, and that 1,000 users is normally the limit, but feel free to add more servers to lower the average number of users per server.
You can also split the database over several servers using a cluster or even split some data out to a separate database.
Do not rule out any option, even if you have a limited budget.
The investment in the machines described previously is not insignificant, but the benefit to end users is huge. Take our large user base example and you can see that even a few seconds can add up to a big reduction in efficiency for an organization. You will also save a large amount of developers' and DBA time, which for external consultancy, or even internal teams, can be relatively expensive.
The database is crucial to the reporting performance and it can be improved in two ways:
- Database configuration
- Database design
The first way is with the configuration of the database in terms of the parameters used and the structure of the data files/tablespaces. There are a large number of options, on many types of database and, therefore, too many to go through them in this book (we recommend that you read Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo), but what we can say is that there will certainly be some parameters that will make a big difference!
Work closely with the database administrators to tune the settings that meet your needs. The settings that normally have a large impact on an Oracle database are those relating to memory and those relating to the storage such as block sizes and tablespace structures. Testing of the database performance should be done in isolation of OBIEE, by use of an SQL development tool, such as the Oracle SQL Developer, but use the SQL generated by the OBIEE server as a basis for tuning.
The second area for a database to be tuned is with the object structures. Using a smaller set of data or the smart use of indexes, stats, joins, and views can make a huge difference to speed. An example of design is to take a table with a large number of rows, say 10 million records, and create a materialized view that aggregates the data into a result set of say, 10,000 rows. When a user runs an analysis that only displays the aggregate result then they will hit the smaller table. This can be achieved either by adding the aggregate into the OBIEE repository (see the next section), or by making the materialized view available to rewrite queries.
For full details on designing a database for performance, please see Chapter 15, Reporting Databases, techniques for creating a reporting database.
Once you have created a smaller dataset, as shown earlier, you need to use it when it matches the scope of the dimensions in your analysis. There are two ways you could do this:
- Separate fact tables
- Aggregate logical table sources
Using a separate logical fact table for aggregate facts is not a commonly used option. The only time it is suitable is where the aggregate facts are not simple sums (or simple averages). Aggregate logical table sources is my favorite feature of the OBIEE Server. We can create an aggregate table (or materialized view) and use this directly in the BI Server Repository, alongside the detailed fact tables. The BI Server then chooses which table. Let's walk through a working example.
The steps are:
- Create an aggregate materialized view.
- Import the new object into the physical layer in OBIEE.
Remember that it is best practice to create an alias for your physical layer objects. See the chapter on developing the BI Repository for the import metadata step:
- Add the new physical table as an additional logical table source (LTS).
- Map the fields as appropriate. You can map the same column to both the logical table sources.
- Set the content level so that the BI Server knows how to use the new LTS. The logical table source content level needs to be set to detail for the matching level, but not set for the other dimensions.
Now we have created a summary set of data and added it to the BI Server Repository.
The BI Server will now use the smaller table to answer requests if it is suitable, which will normally speed up the response times.
Follow these tips to improve performance:
- Try to avoid using normal (opaque) views unless they are the only way to introduce hints and an SQL that performs well
- Make sure you leverage the features of the database, and understand what they mean to performance
- Try not to overload the BI Server and database with lots of queries by keeping to the general rule of 10 or less requests per dashboard page
When a user request is made, the result of the request can be stored in a local file on the BI Server. This is called a cached result. The BI Server normally works by sending a query code to a database, file, or Excel, and processing the results it gets. The slow part of the process is usually the waiting for the data source system to get the answer together. Passing the call to the data source, and having the result already, is a great way to cheat the system into being faster. Luckily, it is possible to get the results before the user runs their dashboard, and then store these results in a cache. Cache entries are results that are stored in a file on the BI Server machine and the BI Server knows when it can use these pre-prepared results, instead of going to the database to get the answer. If the same request is made again, by the same user or even a different user, then the result cache is used instead of issuing an SQL against the database.
Cache is not the only answer to all your performance issues. In fact, it should be treated as a last resort and only as a temporary solution, until you get the hardware/database performance that you need. There are limitations as to how much you can store in the cache, in terms of both individual result sets and the total overall size of the cache. There are also issues relating to how you refresh the cache, removing old stale data, and populating new data. Without a pre-populated cache, the first user to run the dashboard will still experience a slower performance.
The global cache is shared by all BI Servers participating in your cluster. In addition, each BI Server maintains its own local query cache for regular queries. The global cache needs to be stored in a shared filesystem. Shared files requirements for the global cache are as follows:
- All BI Servers in the cluster must have read and write access to the global cache directory
- The global cache parameters are configured in the
NQSConfig.INIfile for each BI Server node participating in the cluster. The global cache is controlled centrally - The BI Servers maintain a query cache, which is a local cache of the query results
To enable the cache, carry out the following steps in Fusion Middleware Control:
- Log in to Fusion Middleware Control, Enterprise Manager.
- Expand the Business Intelligence node in the
Farm_domain_namewindow. - Click on biinstance.
- Click on Configuration, and then click on Performance.
- Click on Lock and Edit Configuration.
- Check the box for Cache Enabled.
- Set the amount of cache size to 20 MB.
- In a clustered environment, set the Global Cache section, specify the shared location for the Oracle BI Server Global Cache, and specify 250 MB for the global cache size. In a Windows environment, Enterprise Manager Fusion Middleware Control requires that a UNC path be used to define the shared location of the Oracle BI Server Global Cache.
- Click on Apply.
- Click on Activate Changes.
The cache entries that are made will become stale when new data arrives in the warehouse. It is, therefore, possible to set a length of time that a cache entry is still usable. This can be minutes, hours, days, or unlimited. There is also a method called event polling, which is a way to tell the BI Server that one of the tables in the cache has been updated. For more details about event polling, see the Gerardnico wiki at http://gerardnico.com/wiki/dat/obiee/event_table or the Oracle documentation on Cache Event Processing with an Event Polling Table. Also, see the documentation of the shared folders used in OBIEE.
There are configurations available for an OBIEE system, which include the use of a pure web server on top of the installed application. The use of a web server layer will enable load balancing and high availability. The web servers will also be able to serve up objects such as images quicker than the Weblogic server can. In Chapter 14, Ancillary Installation Options, we demonstrated how to install the proxy plugin, which enables the separate web layer. Go back and give it a try!
The usual suspect when it comes to slow performance is the database, and hopefully you have tuned this as much as possible. If you are expecting very high numbers of users then, in addition to adding web servers (as previously), you will need to deploy more application servers. This will spread the load across different machines and will provide high availability cover.
For an enterprise setup, this will mean installing separate machines with a full application installation, including an admin server and managed servers. This is outside the scope of this book, but you can find information in the Oracle documentation that explains the steps. Find the OBIEE high availability section of the documentation, currently at: