OBIEE Performance Tuning

This post describes a few tips and things to keep in mind for OBIEE Performance Tuning.
Be Proactive when possible
The need to performance tune can be proactive (tune before a major issue arises) or reactive (tune after a problem is reported by users for example).  It is best to be proactive – so performance tuning should be built into your OBIEE maintenance schedule. For example, OBIEE’s Usage Tracking functionality should be used regularly to identify reports whose performance can be improved and then performance steps should be carried out on the worst performers.
Iterative Process – change one thing or set of things at a time
One of the first things to keep in mind is that performance tuning is an iterative process.  And there is typically no one silver bullet that will resolve all your performance problems.  You may need to analyze and make changes to multiple parts of the system, but you want to make the changes methodically.  It is best to change one parameter or setting at the same time (or one related set of parameters).  Adjust and test the settings for that one parameter/setting (or set of parameters) before moving on to another.  If you change too much at one time, you may have a difficulty determining what is helping from what is hurting your efforts.
Fix user complaints first, worst performers next, and then the next bad performers down the list
Another thing to keep in mind, tune what users are reporting first, then tune the worst problems second, then move on to the next.
Team Effort – problem could be anywhere along the technology stack
Performance problems could be anywhere along the technology stack:
• OBIEE
• Database
• Server
• Network
Due to that span of technology, performance tuning is a team effort.  OBIEE Admins and Developers, DBAs, and ETL Developers can all be key to solving performance issues.
Logs from all components may need to be reviewed depending on the scenario.
Try to isolate or narrow-down the source of the problem
For example, run the report SQL directly on the database and see if you have the same problem. If there is no issue when run directly on that the database, then you have eliminated the database as the problem.
Determine if other applications have been also been experiencing slowness which could indicate the possibility of a network problem.
If your users have reported an issue, then you need to get as much details as possible about the performance problems they are experiencing.  When did this start happening?  Is it just one report or many?  Is it localized to one business area or multiple?  Is it all the time or sometimes?  Knowing this will help you to know where to focus.
Other questions to ask as you try to identify the source of the problem include but not limited to:
Has anything changed?  If reports were running fine, but are now slow, the first thing to ask is …
When the issue start?  Determining exactly when it started might be helpful when correlating with other system or company activity)
What has changed recently?  Has there been any system changes, data changes, database updates, network changes, etc. (even if they seem unrelated)?  For example, rolling into a new calendar year will cause new “Year” value(s) to be included in the data and can impact performance if statistics are not gathered.
Is there a possibility that an index was dropped and not recreated as expected?
Use OBIEE’s Usage Tracking information to analyze specific reports, analyze long running reports, or frequently run reports.  You will want to capture and analyze the SQL from these reports to determine what can be done to improve their performance.
Database
DBAs can monitor the system in real-time, use various tools, or review logs for information that can be helpful in the tuning effort.  Tools such as Oracle Enterprise Manager (EM) or SQL Tuning Advisor can be used to identify, analyze and tune high-load SQL.
OBIEE Usage Tracking can also be used to identify high-load SQL.
Without getting into much detail, these are some database features that could be used to help improve performance:
• Gather Statistics
• Results Cache database feature
• Partitioning
Servers
The System Admins can monitor the server resources to determine if there is an issue there.
• Use fast disk for the OBIEE cache and/or temporary files.

OBIEE-specific performance tuning tips
• OBIEE Caching
Are the tables being used set to cacheable?
Is caching turned on at the application level?
You may consider seeding the cache daily.
CACHE Settings:
o MAX_ROWS_PER_CACHE_ENTRY
o MAX_CACHE_ENTRY_SIZE
o MAX_CACHE_ENTRIES
o ——————-
o USE_ADVANCED_HIT_DETECTION
• Use Aggregation: Aggregate data when applicable
o You can use Aggregate tables or materialized views to realize this benefit.
o Aggregate Fact tables and corresponding Aggregate Dimensions.
o Make sure aggregation rules are applied to Fact table measures.
o Don’t necessarily merge all measures into a single fact.
• Joins and Indexes
o Do not create unnecessary joins.
o Verify that the joins on the tables being investigated are appropriate.
o Performance Indexing could be helpful.  Again, this is an iterative process.
• Prompts and Filters
o Use LOV tables to drive prompt values when possible, instead of building prompts from large transactional data tables.
o Force filter selection / entry by making prompt values required.  Do not allow open ended run of reports.
• Filter out unneeded data.  If there is a significant amount of data that is not being used in one or more tables (especially if they are frequently used), then that data should be filtered out by the ETL before it gets joined in SQL, and then has to be filtered out in the RPD or at the report level.
• Enter the “Number of Elements at this level” value in the logical level in hierarchies.
• Also ensure that all logical level keys are unique.
• Avoid function in the where clause when possible.
• Be careful of sub-queries.
• Check out the features of the OBIEE Performance Monitor
http://server:port/analytics/saw.dll?Perfmon  (enter your OBI server and port)
• When possible, do comparison analysis to determine for example, why is this report running fine, but this other seemingly similar report is not.
• Use fast disk for the OBIEE cache and/or temporary files.
Sometimes a complete overhaul might be required
Review the users’ workflow and determine if new and improved queries can be written or if the number of queries can be reduced.
Present information from a summary level first, and then provide increasing levels of details as requested by users through drill down or navigation.  Basically, present detailed information only when necessary, and minimize the amount of detail provided at a time by filtering on user selections.
Oracle’s OBIEE Performance Tuning Guide
Apply recommendations from the “Best Practices Guide for Infrastructure Tuning Oracle® Business Intelligence Enterprise Edition 11g Release”.  I would recommend applying 1 – 3 changes or set of changes at a time; don’t apply everything at the same time because if there is a problem, it will be more difficult to determine which change caused it.
https://blogs.oracle.com/proactivesupportEPM/entry/wp_obiee_tuning_guide

OBIEE 11G Create Logical Table and Logical Columns

1 .	In the BMM layer, right-click the Sample Sales business model and select New Object > Logical Table to open the Logical Table dialog box.
2 .	On the General tab, name the logical table D5 Sales Rep.
3 .	Click OK to add the logical table to the business model. Notice that the D5 Sales Rep icon has a # sign. This is because you have not yet defined the logical join relationship. When you define the logical join later in this tutorial the icon will change accordingly.
4 .	Drag all six columns from D50 Sales Rep in the Physical layer to D5 Sales Rep in the BMM layer. This action creates logical columns and adds a D50 Sales Rep logical table source to D5 Sales Rep.
5 .	Rename the D50 Sales Rep logical table source to LTS1 Sales Rep.
6 .	In the Physical layer, expand D52 Sales Rep Position.
7 .	Drag POSTN_DESC and POSTN_LEVEL from D52 Sales Rep Position to LTS1 Sales Rep. Note that you are dragging the columns to the logical table source, not the logical table. Dragging to the logical table would create a second logical table source.
8 .	Drag DISTANCE from D51 Sales Rep Parent Child to LTS1 Sales Rep. Again, you drag the column to the logical table source, not the logical table.
9 .	Rename the logical columns: Old Name New Name POSTN_KEY Position Key TYPE Sales Rep Type EMPL_NAME Sales Rep Name EMPLOYEE_KEY Sales Rep Number HIRE_DT Hire Date MGR_ID Manager Number POSTN_DESC Position POSTN_LEVEL Position Level DISTANCE Closure Distance