Advertisements

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

Advertisements

“The connection has failed” Error when trying to Import Metadata into OBIEE

If you get the error “The connection has failed” when you try to Import Metadata into the RPD, this post may help you to resolve it.

The solution is to: Create an Environment Variable called TNS_ADMIN and set its value to the directory of your tnsnames.ora file.
The TNS_ADMIN variable tells Oracle Client where to find the tnsnames.ora file which contains your data source details.

In case you need the details:
Click the Windows Start menu –> Right-Click on Computer –> select Properties
Then click on “Advanced system settings” on the left.
Advanced_System_Settings

Click the “Environment Variables” button.
Then in the Environment Variables window, click New.
Enter the details for the TNS_ADMIN variable.  The value needs to be the path to your tnsnames.ora file, typically located at [ORACLE_HOME]\network\admin. The path will look something like the value shown below (it depends on where Oracle is installed on your system).
TNS_ADMIN_Environment_Variable

 Hope this helps.

WebLogic startup failure – BackendRoot cannot cast to BackendStandard

My colleague from a previous company contacted me recently to help with a problem. OBIEE was not starting up.  They had a power failure the night before, and then OBIEE would not start up.  The system is OBIEE 11g on Linux.

This is the error that was generated when trying to start the WebLogic Admin Server…

——-

<Mar 28, 2014 9:11:35 AM EDT> <Critical> <WebLogicServer> <BEA-000362> <Server failed. Reason: There are 1 nested errors: java.lang.ClassCastException: com.octetstring.vde.backend.BackendRoot cannot be cast to com.octetstring.vde.backend.standard.BackendStandard         at weblogic.ldap.EmbeddedLDAP.start(EmbeddedLDAP.java:303)         at weblogic.t3.srvr.SubsystemRequest.run(SubsystemRequest.java:64)         at weblogic.work.ExecuteThread.execute(ExecuteThread.java:209)         at weblogic.work.ExecuteThread.run(ExecuteThread.java:178) > < Mar 28, 2014 9:11:35 AM EDT> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to FAILED> < Mar 28, 2014 9:11:35 AM EDT> <Error> <WebLogicServer> <BEA-000383> <A critical service failed. The server will shut itself down> < Mar 28, 2014 9:11:35 AM EDT> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to FORCE_SHUTTING_DOWN>

——– 

After trying a few things that did not resolve the issue, an online search helped with the solution. This post was very helpful: https://community.oracle.com/thread/2285489?tstart=0

After reading through the post, we went to the below directory on the OBIEE server (Linux) and examined its contents:

$MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/servers/AdminServer/data/ldap/ldapfiles

[oracle@[SERVERNAME]]$ cd /u01/product/middleware/user_projects/domains/bifoundation_domain/servers/AdminServer/data/ldap/ldapfiles [oracle@aeledwpbi ldapfiles]$ ls -l total 11308

-rw-r—– 1 oracle oinstall 10071624 Mar 27 08:40 changelog.data
-rw-r—– 1 oracle oinstall    56940 Mar 27 08:40 changelog.index
-rw-r—– 1 oracle oinstall   804359 Mar 27 08:40 EmbeddedLDAP.data
-rw-r—– 1 oracle oinstall     2028 Jun 25  2013 EmbeddedLDAP.delete
-rw-r—– 1 oracle oinstall     3576 Jun 25  2013 EmbeddedLDAP.index
-rw-r—– 1 oracle oinstall        0 Mar 28 12:36 EmbeddedLDAP.lok
-rw-r—– 1 root   root       615242 Mar 27 08:40 EmbeddedLDAP.tran
-rw-r—– 1 oracle oinstall        8 Mar 27 08:40 EmbeddedLDAP.trpos
-rw-r—– 1 oracle oinstall        8 Mar 27 08:40 EmbeddedLDAP.twpos

Note how one of the files (EmbeddedLDAP.tran) is owned by “root”. It seems the power outage caused something unusual to happen resulting in “root” being assigned ownership of the file.

After having the system administrator change the owner from “root” to “oracle” (the OBIEE admin user), we were able to start the OBIEE system back up.

New OBIEE Tuning Guide (Version 4) – released Jan 2014

A new version of the OBIEE Tuning Guide (Version 4) was released in January, 2014.  It is suitable for OBIEE 11.1.1.6 and 11.1.1.7 versions.

It can be found on Oracle Support under Document ID 1333049.1.   Or here is a direct link to the document (PDF).

—————————————-

New highlights from the previous document include:

  •     Optimized JVM switches for JRockit / Sun JVM / IBM JVM
  •     New tuning parameters settings / values for JavaHost / OPIS / OBIS components.
  •     Improved performance monitoring techniques.
  •     IBM WebSphere tuning parameters.
  •     More WebLogic Server tuning parameters.
  •     Windows Server 2012 tuning parameter.
  •     New optimized Linux / AIX tuning parameters.
  •     Additional Essbase ASO tuning parameters.
  •     libOVD authenticator search tuning

—————————————-

As always, this document provides us with recommended baselines or starting points, but the appropriate settings for each environment will vary.

How to clear the BI Server cache using command line script or via the Issue SQL page

This post will explain how to clear the BI Server cache using either (1) a script, or (2) the user interface.
You may need to clear your cache to allow the data in the cache to get refreshed.

(1) Clearing the cache using Command Line script …

This example is for Linux/Unix, but can be easily adjusted for Windows.

Create a file called purgecache.txt … and place it at … [FMW_HOME]/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

In the file, enter the code “call SAPurgeAllCache()” – (without the quotes) – which is a special BI Server command for clearing the entire cache.

Then create a shell script called purgecache.sh, located in a directory where you store your custom scripts … which includes the following 2 commands …

 (Note: there is a space after the dot in the below command)
. /[FMW_HOME]/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup/bi-init.sh  

(Note: the below is ONE long command; not 2 commands)
[FMW_HOME]/Oracle_BI1/bifoundation/server/bin/nqcmd -d AnalyticsWeb -u administrator -p pswd –s [FMW_HOME]/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup/purgecache.txt

Then you may execute the shell script just as you would any other script (for example  ./purgecache.sh ) -or- you may add this new script to your crontab if desired – for example, to run each night before your cache seeding reports run.

(2) Clearing the cache using the Issue SQL page …

Log into OBIEE Analytics using an ID that has the “Administration” link privilege.
http://server:9704/analytics

Click on the “Administration” link located at the top right of the page.

Administration_link

Click on the “Issue SQL” link at the bottom left of the page.

Issue_SQL_link

In the Issue SQL window, enter the command:  “call SAPurgeAllCache()” (without the quotes)
Click the “Issue SQL” button to execute the command.

Issue_SQL_Window_and_command

You should see a message indicating that the command was executed successfully (assuming you have caching turned on).

Your cache directory located at …
[FMW_HOME]/instances/instance1/bifoundation/OracleBIServerComponent/ coreapplication_obis1/cache
… should now be empty.

Creating a new Security Realm in OBIEE 11g

When setting up security in OBIEE 11g, you may modify the default security realm (myrealm) that installs with OBIEE.   But even better, you may create a new security realm and leave the default realm untouched.

My preference is to leave the default realm untouched and create a new realm – this is a best practice in my opinion.  I think it is helpful to always be able to go back and look at the features and settings of the default security realm.  And you can name your new security realm more appropriately, such as, ABCIncSecurityRealm.

The link below (Paul Cannon’s Blog) brings you to a blog post about configuring OBIEE to use LDAP authentication, and the first part of the post covers creating the new security realm.  It is very detailed. I used it the first time I created a new security realm.

http://paulcannon-bi.blogspot.com/2012/07/configuring-ldap-authentication-for.html

Good luck creating your new security realm.

How to run Catalog Manager in GUI mode from the Linux Server

OBIEE Catalog Manager can be accessed from a client tool on Windows or from a server side process.  There are times when it is required that you run the Catalog Manager in offline mode, and at those times you will be required to run it on the server.

Although there is a command line option for Catalog Manager, it is easier to work with it in a Graphical User Interface (GUI) mode and at times its necessary to use that mode.

Start Xming (or another X-Windows type tool) on your local PC or laptop. [This will allow the Linux screens to show up on your desktop when you run the shell script on the Linux server]

For more on Xming, visit http://en.wikipedia.org/wiki/Xming

Connect to your Linux server from your desktop/laptop using Putty (or other tool).   Then go to the catalog manager directory:

cd [$FMW_HOME]/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/catalogmanager

runcat-start-catalogmanager

Start up Catalog Manager by running:    ./runcat.sh

This will start Catalog Manager, and it will be running on the Linux server, but the screens will be showing on your desktop/laptop.

From the main Catalog Manager window, you can then click on File –>Open Catalog

catalogmanager_interface

And then select the Type based on how you want to connect – Online or Offline mode.  Accessing Catalog Manager in this way is most likely needed when you need to connect in Online mode.

And then of course, you need to enter the URL – which is the full path to the catalog, for example:[$FMW_HOME]/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/catalog/default

And enter the User and Password.

catalogmanager_onlineoffline_login

Assuming everything was entered correctly, you will be connected to the catalog.  You can then use the interface to browse the catalog and perform other tasks on catalog objects.

catalogmanager_browsing

Good luck!