The intent of this article is to expose available optimization methods within OBIEE for the OBIEE Community, to then guide the search on the interested methods on the web and Forums, for more details for their deployments. The ultimate goal is to implement an optimized OBIEE environment for our clients and to make report results delivery an effortless activity. There are a number of methods that can be employed in an effort to optimize the BI system. In heavy reporting environments, performance is a major issue as the BI server often times may become over burdened with activities, pulling hundreds of thousands of rows of data mainly in reporting and calculated summary reports. As Oracle Developers and DBAs, we all have quite a few options to optimize the Database, whether it is Partitioning, Materialized Views, Indexing and others. At the same time, the BI Server within OBIEE offers quite a few options, which can work in conjunction with Database optimization techniques, as well as independent in nature. Documentation that is available unfortunately is not cohesive enough or organized in a fashion that is easily accessible. One will have to know what to look for before starting the search.
Content Mapping for LTS
Content Mapping Dimension levels within each Logical Table Source (LTS) controls which table to query by BI Server. For Aggregates and Summary Tables, the content mapping for the Dimension level can be set at a much higher level (depending on the grain of the Aggregate tables), and the transactional or a detailed table LTS can be set at a lower grain. Depending on the measure and the dimension column End-Users choose in the query, BI Server will pick either an Aggregate table or a Detail table. The Content mapping set for each of the LTS determines the table to be queried by the BI Server.
For example for a given Fact, a Detail level Fact$ can be at the Transaction Date level of a Date dimension, and the Aggregate table can be rolled up to the Month or Year level. If the end-user selects Year and the Fact$ from the Fact, BI Server chooses the Aggregate table in the query, therefore avoiding running the query through the detail records, and then rolling them up, therefore providing a significant performance gain. Content mapping will have to be configured at the Business Mapping layer (BMM). NQQuery.log or the Session log provides the query to determine the BI Server execution path.
Fragmentation Concept in OBIEE – History VS Current
The idea is to keep Historical and Transactional data together in one Fact table in BMM, allowing the BI Server to manage querying Historical or the Transactional table depending on end-user dimension filter selection. For example, Historical LTS and Transactional LTS are joined to a Period dimension, and the Fragmentation content is set to query GL_BALANCES table for Year > 1999 from the Period dimension as shown below. So, depending on the filter value the end-user selects in the Answers or the Dashboard query, BI Server will choose either GL_BALANCES or GL_BAL_HISTORY based on the Fragmentation content definition. The concept is similar to Database partitions, however the recommended approach is to continue with Database partitions, as indexes and such can be setup within each local partition, and are easier to manage. Fragmentation has issues as it expects including the common dimension (Period dimension) in the Answers query, otherwise it does a UNION ALL and therefore could be counter-productive. Fragmentation however could be a better option when the two data sources are different and are merged into one Fact in the BMM.
Caching
BI Server has the capability to Cache Answers queries and results set on the server in the form of file structures. However, the challenge is in clearing the Cache and replacing with ever changing data. Although there are several ways to refresh Cache, the recommended approach is to use the Event Polling Table concept within OBIEE to automate Cache Management. Event Polling Table can be used to track Dimension and Fact table refreshes, by inserting a record into the table for every Dimension or Fact data change (via ETL or a SQL script), and the polling frequency can be set in OBIEE as shown, so the BI Cache Manager reads the Event Polling Table, and refreshes Cache of those entries in the table when it polls at the set interval. We have learned from our experience that this is by far the most effective and efficient way to refresh Cache entries.
With that said, the first and foremost place to address performance options is at the Database level. Database must be tuned first to the best. BI Server optimization options discussed above compliment Database optimizations.
Consider the following when modeling the Database and the OBIEE rpd
- Consider database partitions for tables with large data volume
- Usage of bit-map indexes on low-cardinality Key columns of both Facts and Dimensions in a Star-Schema. BI Repository modeling to use joins between bit-mapped columns
- Aggregates and Summary tables
- Materialized Views as the LTS within rpd
- OLAP cubes as LTS both with OBIEE 10g and 11g
Author: Naren Thota, Partner/BI Architect at Infosemantics, Inc., a premier Oracle EBS and Business Intelligence (Oracle, IBM) solutions provider.
Please reach out to the author at naren.thota@infosemantics.com to learn of configuration details or any questions on the above discussion.