OBIEE Tuning - Under the hood

 Monitoring what to tune:

• Monitor performance using Enterprise Manager,
• OBIEE Admin/Session tool
• Usage Tracking
• Fiddler browser based client developer add-on's - see where is most of the time being taken for a page load and plan accordingly.
• Use Oracle Application Testing Suite (OATS) for Load Testing using a test strategy reflecting the load and type of usage.

Understanding Monitoring is very important  because Oracle's documentation is not very clear on how the settings effect the overall performance. They at best provide two values - default, and suggested, without much explanation. So if we want to customize these for the situation at hand, we should be able to monitor the effects of changes and tweak it to the what best applies to our situation.

Apply latest patches

• Upgrade to the latest patch set - follow Support documentation (Upgrade advisory) to plan major upgrades.
• Stay on approved Java version

Delegate work away from OBIEE server

Reverse Proxy/OHS Cache: OBIEE prevents the browsers from caching its content. This is a desired tract to ensure fresh reports data, but overloads the BI Presentation server/OHS as all the static content now needs to be served for each and every request.

This can be mitigated by implementing a Reverse Proxy server or enabling caching on Web server.

Optimized connections

The Connection Pools to the underlying databases should be enough to be able to reasonably service the expected number of concurrent users. Pools also exists for network connection and here, the timeout should be reduced so that they can be freed more often and made available for subsequent calls. There are various parameter based on the underlying OS to achieve this:

• Database Connection Pools for these should be reviewed:
• Database connection in the physical layer:
• Init-Block Connection Pools
• Physical DB Connection Pools
• BIP/MDS Connection Pools (others if Essbase/ODI are installed)
• Inter-Process connection pool:increase the bridge connection pool for Presentation server to sufficient level
• Enable compression at web server to ensure high throughput
• Ensure sufficient search depth within the AD of choice. This can be result in additional complexity if there is a cyclic membership.

Thread Management

•     Manage Stuck thread trigger time: 
• Account for long running queries from the usage tracking parameters 
• Ensure that the system doesn't flag threads as stuck in that time frame.  
•     Increase thread pool for charting engine, job manager

Turn Off Debugging features on production:

•     Log levels
•     Wrap Data Type (JDBC)
•     Lock down production RPD to read only mode
•     Database client libraries - disable signal handling

JVM - Memory

• Increase Heap
• Compressed Reference
• Thread Local sizes based on available physical memory for OBIEE

Cache management:

• Move cache and temp directories(javaHost/BIP/BIPS) to RAM disk (~256 GB)
• Use cache seeding. Automate using SASeedQuery after ETL process.
• Adjust maximum cache size based on frequently used/first page reports
• Increase number of open files limit in OS to allow for multiple concurrent processing of cache/sort/logs etc.

Aggregate data

Reduce Database's processing cycles, and the network bandwidth needs.

Hardware Optimization

• Maximize Memory and Networking capability of each server. 
• OBIEE licensing is core based. Addition servers will result in added licensing cost. So maximize the memory and network capacity on the serve before scaling out.
• Throw in more hardware, vertically scale the system for performance, get high availability as an added benefit.

Sizing of Server

Factors used to determine the capacity of a server:

•     Total Named user vs Concurrent users
•     SSL turned on or not.
•     complexity of the Report formats
•     Underlying database and the connection between the two servers

BI Publisher

•     Ensure appropriate JDK/JVM
•     Optimal memory and disk space allocation
•     Use BI Publisher's XSLT processor (Enable scalable feature of XSLT processor)
•     Divide optimization efforts by
• Online/Concurrent Usage
• Report size - Small(~10 Pages)/Medium(~50 Pages)/Large(> 100 pages)/ Extremely large (> 100,000 rows)
• Bursting - Total number of report vs. time window
•     Run-time optimization
• Enable XSLT Processor, enable scalable features
• Enable Muti-Threading
• Increase Thread Count
• Increase FO Buffer size
• Increase Pages in Caches setting
• Enable Run-Time Cache
• Disable Autorun
•     Use JNDI over JDBC
•     Push database functions like Joins to Database (Use OBIEE models)
•     XML
• Use short names (smaller overall XML)
• Use Unique names across structures
• Avoid XML attributes
• Use Absolute paths for Searches rather than Relative paths
•      Scale out
•     Configure debug settings to minimal for production

Finally some functional optimization

• Rearrange dashboards so that the smaller/faster reports are rendered first
• Show summary reports (from aggregates) first
• Force users to put appropriate filters in prompts
• Partition database
• Index appropriately
• Run Statistics and keep them current

Moniter, Adjust, Repeat!

Sachin
Architect - Oracle Engineered Systems
Exalytics/Exalogic/Exadata
BuzzClan LLC

BuzzClan is a business consulting company collaborating to provide Oracle software advisory services & implementation services. BuzzClan LLC is committed to providing substantive business value on each and every client engagement. We do this through a combination of industry-specific business expertise, technical skills, proven project management methods and our “onsite - off site - offshore” delivery model. We strive to work in partnership with our customers to build high-performance teams and create business solutions that will last.

External table based authentication and autorization in OBIEE

Sachin
JainSys Inc. The basic out of box security available with OBIEE is via the administration tool. To administer the users/groups one logs in to the RPD and open Security Manager (Manage/Security). Groups, users and their relations are managed via this.

The drawback to this method is that every time we need to add users or manage groups, this becomes an admin task involving IT. In an enterprise application, there are other means of maintaining user access like SSO or custom admin screens which allow for the designate admin roles in the user community to manage access to users. This generally runs off of a custom profile implementation.
A typical layout for this type of security implementation is done via a set of three tables:

• USER
• ROLE
• UserInRole

This allows a many to many association between users and roles.

To take advantage of this in OBIEE we have to configure the system to query the database on login. The steps required to do so are as follows:

1. Setup database connection.

Start by importing from the database which has the security/profile tables.This Connection pool will be used later in the process.

2. Once we have the connection pool ready we now need to set up an Initialization Block. An IB is executed every time a user logs in and a session is being created. The idea is to get teh User name and password passed on through the login page and use that information to query the database and authenticate the user.

3. Open the RPD and Create an new IB by clicking the menu
and then on the Variable Manager :
We need to do this on a "Session" IB not a "Repository" as this will be done as each user logs in.
Select the Data Source Type as "Database".
(If you do not see Database as an option then you may not have a valid ODBC or Oracle client on the server. If you have been able to complete the import step above then this shold not be an issue. Else this is about time you install a client to connect to the data store.)

4. Initialization string: We now need to define a query which will take the UserId and Password passed on to the login screen and check the tables to see if this user exists and have access to this application or not. This is also our chance to grab items from the user profile like Display Name, groups they are assigned to etc.
Typical sql at this point is:

select username, grp_name, SalesRep, 2 from securitylogons where username = ':USER' and pwd = ':PASSWORD'

:USER is a way to indicate that the value of that variable will be substituted on run time.

5. Variable Target: This section allows us to define some variables to contain the results. There are some predefined session level variables:
USER ; PASSWORD; DISPLAYNAME; LOGLEVEL; EMAIL etc
See this for details

The variables should be lined up in the order they are being returned from the query to allow proper assignment.

Check the "Required for authentication" check box to indicate that this IB is used for authenticating users.

Save.

6. Testing : The logs for any issues with this test can be found at {OBIEE Dir}\OracleBI\server\Log\NQServerLogs.log.

I kept getting :
[nQSError: 13011] Query for Initialization Block 'Authentication' has failed.
[nQSError: 17001] Oracle Error code: 911, message: ORA-00911: invalid character at OCI call OCIStmtExecute.
[nQSError: 17010] SQL statement preparation failed.

because I had the semicolon at the end of the query. Apparently statement preparation did not like it.

Once there is data in underlying tables, this should be a easy!

7. Next tasks: setting up groups and managing RPD level and Report level security. That's calls for another blog i guess!! Sachin
JainSys Inc.