OBIEE12c Integration with Oracle EBS Security

.

Integration of the Oracle Business Intelligence Enterprise Edition and Oracle E-Business Suite provides a seamless controlled flow between the systems, allowing for drill down and drill back from key metrics to underlying detail. Theoretically, this can be done between any OBIEE form and any EBS form with pass-thru to any connected EBS subsystem.

If you are integrating OBIEE with EBS, you are likely to be using OBIA, Oracle Business Intelligence Analytics, although this is certainly not a requirement. OBIA is a pre-built, pre-packaged BI solution that delivers role-based intelligence to the organization. It is a set of OBIEE dashboards and reports that run from a pre-built warehouse previously serviced by Informatica/DAC, while the next generation of the OBIA warehouse utilizes Oracles Data Integrator, ODI, which runs high-volume batch load plans, event-driven load plans, and even SOA data services.

1. OBIEE 12c Configuration

While configuring an initialization block to retrieve data from EBS, make sure that Row-wise initialization is checked, as this allows multiple results to be stored in the variable, regardless of whether the variable is static or dynamic; otherwise you will only be able to retrieve the last item in the result set. Be sure to set the Execution Precedence of the EBS Integration init block that attaches the session through the session cookie, so that it executes before any attempt is made to retrieve security information.

Figure 1: EBS Configuration

Figure 1: EBS Configuration

Two files must be modified in order for WebLogic to find, accept and attach to the EBS session.

• instanceconfig.xml

• authenticationschema.xml

To configure external authentication, you will need to modify instanceconfig.xml as follows:
Path to instanceconfig.xml:

$BI_HOME/config/fmwconfig/biconfig/OBIPS

Note: Take a backup of the file before editing.

Add “EBS-ICX” in the EnabledSchemas xml tag.

<Authentication>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion 
Middleware Control-->
<EnabledSchemas>UidPwd,Impersonate,UidPwd-soap,Impersonate-soap,EBS-ICX</EnabledSchemas>
</Authentication>

Middleware recognizes internal schema name for interface to EBS "EBS-ICX". The Oracle already worked on that. It is only necessary to let the system know that this is to be utilized. Then, let the system know the name of the session cookie that EBS writes.
To configure authenticationschema.xml it is necessary to know the Oracle EBS instance cookie name.
Follow the steps to get the cookie name.

1. Login to Oracle EBS

2. If you are using Google Chrome or Firefox then open Dev Tools ➜ Web Console and write the following command:

javascript:alert(document.cookie)

or

javascript:document.write(document.cookie)

ALERT command will pop up a dialog box as follows, while DOCUMENT.WRITE will display the cookie information in browser window.

Figure 2: Alert command pop up

Figure 2: Alert command pop up

Notice that key value pair of the cookie, ERPU1 is the cookie name of Oracle EBS Test instance and the value dynamically generated for each user after each login. We only required the key from it which is “ERPU1”. Now we will use this value in authenticationschema.xml file.

Path to authenticationschema.xml file:

Obiee/bi/bifoundation/web/display

Note: Take a backup of the file before editing.

Edit the following tags in the file:

<SchemaKeyVariable source="cookie" forceValue="EBS-ICX" nameInSource="ERPU1" />

 

<RequestVariable source="cookie" type="auth" nameInSource="ERPU1" 
biVariableName="NQ_SESSION.ICX_SESSION_COOKIE" />

As per Oracle Doc ID 2141505.1
"Access Prohibited" When Logging In To Analytics In EBS Integrated Environment.
Following tag need to be added in the file under “AuthenticationSchema”.

<RequestVariable source="constant" type="auth" nameInSource="ssi" 
biVariableName="NQ_SESSION.SERVICEINSTANCEKEY" />

That’s it for OBIEE configuration!

 

2. RPD Changes using Administration Tool

Here comes the part that is familiar to every OBIEE administrator, the RPD modifications. If you are following the document, the sample EBS connection pool can be used or create a new one just for the initialization process and retrieving security.
Create database objects and connection pools for Oracle EBS database.

Figure 3: EBS Connection Pool

Figure 3: EBS Connection Pool

Note: APPS user should have all READ and EXECUTE permissions to run PL/SQL queries. If not, grant the privileges to the APPS user.

Now, create an init block which will use this connection pool to retrieve the EBS context and set that into OBIEE session variables. The init block will use the just defined connection pool and will send a data source query to the EBS database:

SELECT
FND_GLOBAL.RESP_ID,
FND_GLOBAL.RESP_APPL_ID,
FND_GLOBAL.SECURITY_GROUP_ID,
FND_GLOBAL.RESP_NAME,
FND_GLOBAL.USER_ID,
FND_GLOBAL.EMPLOYEE_ID,
FND_GLOBAL.USER_NAME,
FND_GLOBAL.RESP_NAME
FROM DUAL

Figure 4: EBS Security Context

Figure 4: EBS Security Context

Referring to another Oracle document, 1539742.1, create these static session variables to hold the context:

EBS_RESP_ID
EBS_RESP_APPL_ID
EBS_SEC_GROUP_ID
EBS_RESP_NAME
EBS_USER_ID
EBS_EMPLOYEE_ID
USER
ROLES

Note: You have to create another init block named “EBS Security Context – ROLES – Row wise” only for ROLES as a user will have more than one Role in Oracle EBS and the init block will be set for row-wise initialization.

Figure 5: EBS Security Context - Roles

Figure 5: EBS Security Context - Roles

Figure 6: EBS Security Context - Roles - Row Wise

Figure 6: EBS Security Context - Roles - Row Wise

The following query will be used to fetch all the Responsibilities of log-in user and assign it to the variable ROLES.

SELECT DISTINCT 'ROLES',
RESPONSIBILITY_NAME
FROM FND_USER,
FND_USER_RESP_GROUPS,
FND_RESPONSIBILITY_VL
WHERE FND_USER.USER_ID = FND_USER_RESP_GROUPS.USER_ID
AND FND_USER_RESP_GROUPS.RESPONSIBILITY_ID = FND_RESPONSIBILITY_VL.RESPONSIBILITY_ID
AND FND_USER_RESP_GROUPS.RESPONSIBILITY_APPLICATION_ID = FND_RESPONSIBILITY_VL.APPLICATION_ID
AND FND_USER_RESP_GROUPS.START_DATE < SYSDATE
AND (CASE WHEN FND_USER_RESP_GROUPS.END_DATE IS NULL THEN SYSDATE
ELSE TO_DATE (FND_USER_RESP_GROUPS.END_DATE)
END) >= SYSDATE
AND FND_USER.USER_ID = (SELECT USER_ID
			FROM FND_USER
			WHERE UPPER (USER_NAME) = UPPER('VALUEOF(NQ_SESSION.USER)')
			)

 

3. Oracle EBS Configuration

Now we need to introduce a responsibility for OBIEE through which a user can navigate to OBIEE from Oracle EBS.

1. Create a Function, using Application in EBS:

Figure 7: Oracle EBS Configuration - Form Functions - Description

Figure 7: Oracle EBS Configuration - Form Functions - Description

2. In the Properties Tab, add as follows:

Function: OBIEE
Type: SSWA jsp function
Maintenance Mode Support: None
Context Dependence: Responsibility

Figure 8: Oracle EBS Configuration - Form Functions - Properties

Figure 8: Oracle EBS Configuration - Form Functions - Properties

3. In the Web HTML tab, add the following link:

Function: OBIEE
HTML Call: OracleOasis.jsp?mode=OBIEE&function=Dashboard

Figure 9: Oracle EBS Configuration - Form Functions - Web HTML

Figure 9: Oracle EBS Configuration - Form Functions - Web HTML

4. Create a Menu in Oracle EBS named “OBIEE Dashboard” and add the Function created in step 1:

Figure 10: Oracle EBS Configuration - Menus

Figure 10: Oracle EBS Configuration - Menus

Note: Only create Menu for OBIEE Dashboard

5. Assign Menu to the relevant responsibility:

Figure 11: Oracle EBS Configuration - Users

Figure 11: Oracle EBS Configuration - Users

6. Set Profile

You need to enter the URL of the Oracle BI Server as part of a profile. You can set up a profile for a responsibility, a user, or a site. The following procedure shows how to set profile options for a responsibility:

Figure 12: Oracle EBS Configuration - Find System Profile Values

Figure 12: Oracle EBS Configuration - Find System Profile Values

You should use a fully-qualified host server.domain name rather than an IP address or just a host name. The OBIEE domain must be the same as the Oracle EBS domain, so that the EBS-ICX cookie is visible to OBIEE from the user's browser.

References:
OBIEE 12c: Integrating OBIEE 12c with Oracle E-Business Suite (EBS) Security (Doc ID 2174747.1)
Chapter 9: Oracle® Fusion Middleware Integrator's Guide for Oracle Business Intelligence Enterprise Edition.

Click here if you would like to receive more information about the topic or if you need help with your EBS-OBIEE configuration!

OBIEE 11.1.1.9 UPGRADE

.

How to upgrade your Oracle BI System to 11.1.1.9

2593358

On our previous technical post we looked at what is new in OBIEE 11.1.1.9, below we describe how to upgrade your Oracle BI system from version 11.1.1.7 to 11.1.1.9.

Not sure about the upgrade? Ask us for access to our online testing showcase.

The installation files are available from OTN - Oracle Business Intelligence (11.1.1.9.0) Downloads. As per Oracle advice ensure to download this OBIEE release from the Oracle Software Delivery Cloud for production environments.

Before proceeding with the upgrade always remember to back up your Middleware Home, Domain Home and Oracle Instances, also database schemas and any other additional configuration.

OBIEE upgrade procedure on a Linux x86-64 environment:

  1. Upgrade Oracle WebLogic Server from 10.3.5 to 10.3.6
  2. Upgrade OBIEE from 11.1.1.7 to 11.1.1.9
  3. Upgrade database schemas MDS and BIPLATFORM
  4. Upgrade OPMN system components

Upgrade Oracle WebLogic Server from 10.3.5 to 10.3.6

Oracle BI 11.1.1.7.X and the previous 11.1.1.6 run within Oracle Weblogic Server 10.3.5. However the newly released OBIEE 11.1.1.9 ONLY supports 10.3.6.

To check your current version go to your console page on http://host:7001/console. If you are on 10.3.6 proceed to the next step, otherwise follow the instructions below.

image2

Download Oracle Weblogic 10.3.6 bin files from My Oracle Support. Go to Patches & Updates search for Patch 13529623: PLACEHOLDER BUG FOR WEBLOGIC SERVER 11GR1 (10.3.6) UPGRADE THE INSTALLER corresponding to your platform, this example is based on Linux x86-64.

img0

Unzip the files once downloaded and execute the upgrade. The welcome screen for the upgrade will pop up. Note that you have the command line or the UI options, below is use the latter.

java –jar wls10.36_upgrade_generic.jar

picture 1 picture 2

Click next and select “Use an existing Middleware Home”

img3

On register for security updates screen uncheck the option to receive security updates and confirm.

img4

img5img6

The connection will fail and you have to keep the option to remain uninformed of security issues checked.

img7

On the product and components to upgrade select WebLogic Server, leaving Oracle Coherence unchecked and confirm selection.

img8 img9

After this the upgrade starts and should last a couple of minutes. At the completion click on the Done button.

img10img11

Once the upgrade has completed start the WebLogic Admin Server by command line.

> cd /MW_HOME/user_projects/domains/bifoundation_domain/bin /startWebLogic.sh

Go to http://server:7001/console and make sure you have upgraded to Oracle WebLogic Server 10.3.6 correctly.

image 12

Upgrade OBIEE from 11.1.1.7 to 11.1.1.9

Before proceeding make sure WebLogic Server has completely stopped:

> cd /MW_HOME/user_projects/domains/bifoundation_domain/bin /stopWebLogic.sh

Change directory to Disk1folder inside the unzipped archive folder and start the installer - note that OBIEE installer only has a graphical interface. The installer can be run in Silent Mode.

> ./runInstaller

img13img15

On the software updates select the option “Skip Software Updates”

img17

For the installation type select “Software Only Install” since the system has already been configured and it is only being upgraded.

img18img19

Select your Oracle Middleware Home location and click Next.

img20img23

After the summary click next and the upgrade of the BI components will start, taking less than 10 minutes to complete. Click Finish when it has completed.

img24img26

img21

Upgrade database schemas MDS and BIPLATFORM

The next step is to upgrade the database schemas. Go to MW_HOME/Oracle_BI1/bin and run the Patch Set Assistant (.psa) to upgrade the database repository. In this case you can use graphical interface or command line.

Right after the welcome screen pop ups, click Next.

> cd /MW_HOME/Oracle_BI1/bin/psa

img27

img28

Select “Oracle Business Intelligence” components and check all options from the prerequisites.

img29 img30

Set up the connections for MDS and BIPLATFORM schemas

img31img32

img33 img34

Click on Upgrade button and it will take one minute to complete.

img36

Use the SQL command line to verify that the upgrade of the schema is in VALID status and the version is 11.1.1.9.0.

> SELECT OWNER, VERSION, STATUS, UPGRADED FROM SCHEMA_VERSION_REGISTRY;

UPGRADE OPMN system components

The last post-patching task is to upgrade the system components managed by the OPMN – BI Server, BI Scheduler and BI Presentation Services.

Before starting, make sure that Node Manager and Admin Server are up and running.

> cd /MW_HOME/wlserver_10.3/server/bin/startNodeManager.sh

> cd /MW_HOME/user_projects/domains/bifoundation_domain/bin /startWebLogic.sh

Tip: use NOHUP option to avoid killing the services when closing the command window

img37

Start the Configuration Assistant:

cd /MW_HOME/Oracle_BI1/bin/config.sh

img38img40

Select “Update BI Domain” option and add the credentials of your existing BI Domain.

img41img42

img43img44

Important note: Possibly a process could get stuck during one of the steps without notification. In this event, see the Troubleshooting section on how to fix it.

Start the BI services and start enjoying your OBIEE 11.1.1.9!

img45

For more detailed information you can access the OBIEE documentation - Oracle Fusion Middleware 11g Release 1 (11.1.1.9)

 

Do you need help? Contact us!

Oracle Financial Analytics: General ledger to sub-ledgers navigation

.

When implementing Oracle Financial Analytics, it has been noticed that it is a common requirement to enable navigation from general ledger to sub-ledgers reports to obtain information on specific transactions. One of the main reasons for this is to reconcile general ledger to individual sub-ledgers.

The general ledger is the master set of accounts that summarise the sub-ledger transactions. It is used to add information into the financial statements and journal entries.  A sub-ledger is a detailed record of transactions for an individual account that contains details of transactions for an account.

Sub-ledgers serve as support for amounts posted to the general ledger. It is important to reconcile the general ledger balances to the sub-ledger balances on a regular basis to spot possible discrepancies.

In this blog article, I will describe how this process can be approached in OBI Apps. I will describe the OBI Apps data model and the configuration required. Also, I will describe the creation of sample navigation from general ledger to sub-ledger by the creation of an intermediate analysis.

The animation below shows the expected navigation from GL Account balance to Payables details.

Financial Analytics

Note: This information applies when the data source is Oracle EBS.

OBI Apps allows this navigation by default using the ‘GL Journal ID’ column as a link between subject areas. It is available in general ledger transaction subject area and in the following sub-ledgers (In some cases, ‘GL Journal ID’ has to be exposed to the presentation layer):

  • Accounts Payable
  • Accounts Receivable
  • Revenue
  • Purchase Orders
  • Fixed Assets (Available in OBIA 11g)

Understanding the OBIA model

‘GL Journal ID’ is a unique identifier of an EBS journal entry line and it is available in the linkage table W_GL_LINKAGE_INFORMATION_G.

Sub-ledger transaction tables are linked to W_GL_LINKAGE_INFORMATION_G at source distribution level.

Finantial Analytics

For each sub-ledger transaction fact, there is a logical “transaction detail” dimension comprising the transaction fact and the W_GL_LINKAGE_INFORMATION_G table, ‘GL Journal ID’ is available in this dimension. The figure below shows the physical source “Fact_W_AP_XACT_F_GLJournalID” of the logical dimension “Dim – AP Transaction Details”.

Finantial Analytics

With this configuration, it is possible to have ‘GL Journal ID’ available in the sub-ledger transaction subject areas. And since ‘GL Journal ID’ is also available in “Financial - GL Detail Transactions” subject area, it is possible to configure the navigation from general ledger to sub-ledger transactions by using the OBIEE Navigation functionality.

Creating GL to sub-ledger navigation

‘GL Journal ID’ is available in the two subject areas that we want to link, but the level of detail between the source and the target reports are not the same, which is why it is necessary to create an intermediate analysis to link the two reports. This process is described in this section.

  1. Create the GL report and enable navigation.

Using the subject area “Financial - GL Detail Transactions”, create a new analysis that shows the Balance by Accounting Document Source of a selected account for a selected period. Accounting Document Source is used to identify the sub-ledger source.

Finantial Analytics

  1. Create an Intermediate analysis that contains the ‘GL Journal ID’.

Using the subject area “Financial - GL Detail Transactions”, create an analysis that contains the ‘GL Journal ID’ column. Account and period filters need to be created as well. This report will be used to filter the sub-ledger Transactions.

Finantial Analytics

  1. Create a detailed sub-ledger report

Using one of the sub-ledger subject areas, create a new analysis and filter the results by existing report. Use the analysis created in step 2 as the “Saved Analysis”.

 

image 6 Finantial Analytics

  1. Enable navigation

In the source report (created in step 1), using Action Links, enable navigation to the different sub-ledger reports. The example below shows the navigation to AP Transaction report when Account Document source is Payables.

Finantial Analytics

In conclusion, the OBI Apps data model is prepared to enable the link between General ledger and sub-ledger reports, but it has to be configured at analysis level. This feature can be used for the reconciliation process between General ledger and sub-ledgers.

If you want to go further, create a report that shows summaries sourced by general ledger, a second column that shows sub-ledger totals and a third column that shows the difference between these two amounts, if the value of this column is not ‘0’ navigate to details to discover the discrepancies.

 

OBIEE 11g Installation in Silent Mode

.

At ClearPeaks we recently received a request to perform an OBIEE installation on an Oracle Enterprise Linux (OEL) server without Graphical User Interface (GUI).

The Repository Creation Utility (RCU) and the Oracle Universal Installer (OUI) offer the capability of being executed without a graphical assistant. It will be necessary to run them in SILENT MODE.

Since a database was already installed, only the RCU and OBIEE silent installation process is described in this post.

1. Schema Creation with RCU

1.1 Prerequisites

Make sure that Database and listener are running

1.2  Schema creation

1.2.1  Passwords file creation

As it is a silent installation, the RCU installer will require a text file containing the following passwords (with this sorting):

  • Database password
  • Component 1 schema password (BIPLATFORM)
  • Component 2 schema password (MDS)

vi rcu_passwords.txt


OBIEE Silent Mode

Ensure that the file belongs to Oracle before running the rcu command.

1.2.2 Execution in silent mode

As in every schema creation through RCU, it will be necessary to obtain the software from the Oracle site and extract it. The executable is located at rcuHome/bin/

Execute the following command to start the installer in silent mode:


./rcu -silent -createRepository -connectString localhost:1521:orcl -dbUser SYS -dbRole SYSDBA -schemaPrefix DEV -component BIPLATFORM -component MDS -f < ../../rcu_passwords.txt


After a while, this should be the result:

OBIEE Silent Mode

2. OBIEE Installation

2.1  Prerequisites

2.1.1   Database and listener

As in the RCU execution, the database and the listener need to be started and working before starting the OUI.

2.1.2  Schemas created through RCU

BIPLATFORM and MDS schemas must be created during the RCU installation.

2.1.3  Unset ORACLE HOME variable

If you have already installed an ORACLE database within the same server where you are going to install the OBIEE server, the ORACLE_HOME environment variable must be disabled. Bear in mind that the variable remains disabled only in the terminal session.

Execute the following command (as root):


unset ORACLE_HOME


OBIEE Silent Mode

2.1.4  Set Kernel Parameters

The last step is to modify the Kernel Parameters (as root):

The next lines must be added in the limits.conf file

  • oracle hard nofile 4096
  • oracle soft nofile 4096

vi /etc/security/limits.conf


OBIEE Silent Mode

2.2  Silent validation

2.2.1  Response file creation

If you don’t have GUI in your server, you can edit the response file I used for this installation:

response_file

It will be necessary to replace the <SECURE_VALUE> for your actual passwords.

2.2.2  Silent validation execution

Before installing OBIEE, a silent validation is required. OUI needs the response file to be executed in silent mode.

Ensure that the response file belongs to Oracle before running the installer.

Execute the following command as an Oracle user (the full path of the response file is required).


./runInstaller -silentvalidate -response /home/oracle/Desktop/bi_binaries/obiee_binaries/bishiphome/Disk1/response_file.rsp


You can ignore the following error:

OBIEE Silent Mode

2.3  Silent installation

2.3.1  Location file

If you already have an oraInst.loc file in your system, you can use it:


vi /home/oracle/app/oracle/product/11.2.0/dbhome_1/oraInst.loc


OBIEE Silent Mode

If this file does not exist on the system, the installation program creates it automatically.

2.3.2  Silent installation execution

This is the last and most critical step of the installation. Please make sure that all the previous steps have been performed successfully.

Execute the OUI in silent mode (as an Oracle user):


./runInstaller -silent -response /home/oracle/Desktop/bi_binaries/obiee_binaries/bishiphome/Disk1/response_file.rsp -invPtrLoc /home/oracle/app/oracle/product/11.2.0/dbhome_1/oraInst.loc


This step will take several minutes. If the previous steps have been performed correctly, the installation should end successfully.

Conclusion

This post outlines how to fully install OBIEE 11g on a Linux server without GUI.

Advantages of the silent mode installation include:

  • No need to consume extra resources with the graphical user interface.
  • The whole installation could be automatically executed by a script.
  • Possibility to perform equal installations if the response files don’t change.
  • No need to spend more time executing the graphical wizard manually.

For more information, consult the official Oracle documentation:

OBIEE 11g tuning and performance monitoring

.

OBIEE 11g tuning and performance monitoring is often an overlooked yet very important activity after an OBIEE 11g solution has been deployed. For your BI project to be successful is not enough to deliver clear and accurate information. You also have to deliver it fast. Understanding the working mechanisms of OBIEE and its relationship with the Fusion MiddleWare components (FMW) is key to tune your system to get maximum performance out of your existing hardware.

In this article I want to approach the OBIEE performance and tuning process by addressing the most important points that you should review in your current project. We will then have a look to how you can monitor day-to-day activities using the available performance tools that will allow you to identify performance issues and take corrective actions.

The content of this article is not by any means an extensive guide with all the answers, as something like that is away of the scope of a blog article, but I hope this information will be enough to put the interested reader in the right mindset to start improving and monitoring their OBIEE environments.

Why is it slow?

Usually the performance problems in an OBIEE environment are first detected because users complain that the application reporting is too slow, or at least slower than before.

In this situation, as administrator of the system, you should not guess about what might be the reason. Your experience will be a great asset for you to quickly determine the most probable cause of the problem, but it’s important to test empirically your assumptions.

There are four broad areas where the culprit might be.

  • OBIEE layer
  • Database layer
  • Hardware layer
  • Network layer

Let’s assume that you are a new administrator to the system and therefore you don’t have any clue on where the problem for this particular case might be. In this case you should follow a formal process to check all the areas until you find the root cause and fix it.

This scenario is differs from the one where you might not have any particular complaint from the users but as a proactive measure you are continuously trying to improve your system performance against a preexisting baseline. In this case we would be talking about of optimizing the system. More about this later.

So, let’s start by surveying the above four areas to see what are the most likely places where things can go sour.

Improving Performance

OBIEE & Database Layer

If your environment is stable, meaning that these performance complaints are not happening often, chances are that the culprit for this problem is within the OBIEE boundaries. These are some of the operations to try in the same order as below.

1. Reproduce the problem

The first thing to do is to reproduce the scenario to confirm that the performance issue report is accurate. It is important to reproduce it at the same time as reported and also at different times through the day to discard (or confirm) that the resources accessed by that report have some sort of bottleneck access at specific time frames.

Also is important to replicate the scenario with the same user who reported the problem and with another users. You can achieve that thanks to the impersonation feature of OBIEE. What we are looking here is for identity filters (row level security) applied to this user that might be causing a bad query generation.

These two easy test will give you a better idea of when and for who the problem exist.

2. Check the database query

Once you confirm the problem, the next step is to get the physical query that the report is sending to the back end databases. In the case that we are talking about a dashboard page, you’ll have to go analysis by analysis, though almost always is visually evident which analysis is taking most time, so you can prioritize your efforts.

To get the physical queries you’ll need to check the logs (nqquery.log) assuming that your system is configured with a LOGLEVEL of at least 2. If this is not the case and you are in a hurry, you can grab the logical query of the report from the advance tab on the answers editor and then execute the query through the “Issue logical query” from the administration page. In here you can select the log level that you want to apply for that particular query (and don’t forget to un-check the use BI Presentation cache!)

Once you have the physical query, if you have a database access you can start your normal query tuning process, like checking explain plans, Tk*Prof, missing indexes, etc…

3. Review your RPD model

As a result of the previous step you might conclude that there is something funny going on with the query generator. This might be for example the inclusion of an extra fact table that explodes the granularity level of the intended analysis resulting on many more rows of the necessary being fetched from the DB and then aggregated by BI server, resulting on the right results but delivered using a costly strategy.

In this regard make sure that you follow desirable practices like using aggregate tables, defining properly the content level of your logical table sources and using properly the vertical and horizontal fragmentation.

Remember that if you are on OBIEE 11.1.1.7+ you can use the “Check Logical Model” feature that will give you hints about these things. You can execute it for individual business models to save some time.

Picture 1

4. Review your OBIEE configuration

If everything is looking ok, still is worth to check some of the OBIEE configurations that might give you some troubles over the time, basically the connection pool configuration and the cache settings.

Regarding the OBIEE connection pool configuration, you should double check that your RPD has dedicated connection pools for variable initialization blocks. This is quite important as it will save connection slots for your other data connection pools. For those connection pools it is good to revisit the maximum connections from time to time as your user base grows over time.

The rule of thumb says that you can derive a baseline figure for your connection pool by multiplying the average concurrent users of your environment by the average physical queries of your dashboard pages. So for example, let’s imagine that your dashboards have an average of 5 analysis per page, and each analysis executes one logical query that turns into two physical queries (due to federated sources, for example). Assuming a number of 20 concurrent users your maximum connection pool value for could be 20 * 5 * 2 = 200.

The consequence of having fewer number of connections will result on a higher physical queries response times.

Picture 2

 

Hardware and Network Layer

Either as discarding OBIEE as the root cause of the problem or as a side effect of it, you should check that the available hardware is enough for the demands of the OBIEE application. This should have been assessed during the infrastructure sizing phase, but as your environment grows in users and complexity is usually a good idea to have a look to make sure that it stills holds on.

Memory

The memory of the system is going to be used basically for the server’s JVM and also for the OBIEE components such the BI Server or the Presentation Server. Depending on the total memory of your system, you can configure the JVM parameters to try to get the most of your memory. We will see how to do this in the next section.

When the system runs out of memory, it will start moving the pages to disk, causing performance problems. The easiest way to check if you still have enough free memory is to execute a top and check that the free + cached is still enough to avoid pagination.

 

Picture 3

 

As you can see in the above screenshot, the both Java processes are the two JVM machines (Admin server and Managed server bi_server1, while the sawserver is the process for the presentation server).

One way to make sure if your system is doing heavy use of pagination is to use the vmstat command and check for the swap in and out parameters.

Picture 4

 

If these two are close to 0 values means that so far you still have some memory to use.

CPU

Normally the CPU consumption for an OBIEE environment should be pretty low. If you see your consolidated CPU cores working at >95% then probably the system is trying to page out to disk as a result of insufficient memory.  You can check again using the Top command that will default order the process by CPU consumption and look for the processes that are using the most of your CPU cores.

Another documented reason for abnormal CPU consumption are faulty devices. It might be worthy to check the output of the dmesg command and forward it to your systems team if you find repeating messages about faults on some devices.

Network

Often overlooked, your problem might be within the network layer itself. At the end, no matter how fast your database or server is behaving if the network can’t transmit those results fast enough to the users.

As with the CPUs the network interfaces should be quite relaxed, so one quick check is to run some network monitor program, like iftop to check what processes are using the most of the network interfaces.

Picture 5

Also you can use IPTraf to monitor a particular network interface to see if the total traffic is the expected one. In this case we can’t see information about the individual connections but we have more statistics about the traffic that goes across the interface.

Picture 6

 

So hopefully after reviewing the above areas you’ll have identified the root cause of your problem and eventually fix it. While this is a good approach that allow you to react to specific complaints or issues from your users, you should embark into the perilous journey of continuous tuning and improvement of your system.

OBIEE tuning, review areas

Oracle maintains updated a document that lists and describes all the parameters that affect the performance of OBIEE across five different areas:

  • Operative System Parameters
  • Weblogic server
  • JVM (oracle, sun or IBM)
  • HTTP Server
  • Web browser
  • Database

You’ll find all the information in the document (Doc ID 1333049.1). You need a valid oracle account to access this document. The latest version available at the time of this writing is January 2014.

Of course, you should have a look at the above document, but when talking about performance and tuning, I like to follow the saying “If it ain’t broke, don´t fix it”. Keep in mind that when experimenting when these parameters, you should always test the changes and follow their impact closely after the change. So in my opinion you should play with the below settings just if you are experiencing some sort of performance issue.

Below you’ll find an extract of the Oracle Tuning guide with the parameters that in my opinion might yield have the most impact.

Operating System Parameters - Linux
ParameterDefault ValueSuggested Value
tcp_fin_timeout (in seconds)
This parameter controls for how long to keep sockets in FIN-WAIT-2. Reducing the value will result in abnormal terminated connections (client not sending FIN packet) to be released faster, thus providing more resources for new connections.
6030
tcp_max_syn_backlog (# of connections)
This parameter tells us how many SYN requests to keep in memory that we have yet to get the third packet in a 3-way handshake from. Depending of the quality or latency of our client connections we would like to increase it to avoid overloads at peak time.
10244096

There are OS settings for other systems such Windows, AIX or Solaris. Refer to the Oracle Tuning Guide mentioned above for a complete list of OS and their suggested parameters.

Weblogic Server

There are many different areas that might require a review when tuning the Weblogic Server. However, we found that one that yields quick performance improvements (for large OBIEE implementations) is to tune the maximum connections for JDBC data sources. We found this especially important if you are heavily using BI Publisher.

Note however that increasing the maximum connection pool setting will impact directly the corresponding database layers of each data source, so make sure that you have enough resources in your database tier.

Data Source NameDefault ValueSuggested Value
bip_datasourceInitial Capacity: 0
Maximum Capacity: 50
Initial Capacity: 0
Maximum Capacity: 150
mds-owsmInitial Capacity: 0
Maximum Capacity: 50
Initial Capacity: 0
Maximum Capacity: 150
EPMSystemRegistryInitial Capacity: 0
Maximum Capacity: 50
Initial Capacity: 0
Maximum Capacity: 150
aps_datasourceInitial Capacity: 0
Maximum Capacity: 50
Initial Capacity: 0
Maximum Capacity: 150
calc_datasourceInitial Capacity: 0
Maximum Capacity: 50
Initial Capacity: 0
Maximum Capacity: 150
raframework_datasourceInitial Capacity: 0
Maximum Capacity: 50
Initial Capacity: 0
Maximum Capacity: 150

You should determine the optimum capacity in your pre-production environment and once the results are good, change also the initial capacity equal to the value of the maximum capacity.

In addition to that, if your environment requires more than average long running sessions (because of long running complex reports) it might be a good idea to increase the stuck thread configuration. A thread that has been working for more than Stuck Thread Max Time will be tagged as Stuck Thread by the WebLogic server. If the server gets too many Stuck Threads it will transition to critical status.

So it might be better to increase this threshold to avoid (legit) long running threads to be considered Stuck.

The recommended values by Oracle are:

ServerDefault ValueSuggested Value
bi_server1Stuck Thread Max Time=600
Stuck Thread Timer Interval=60
Stuck Thread Max Time=2400
Stuck Thread Timer Interval=2400
AdminServerStuck Thread Max Time=600
Stuck Thread Timer Interval=60
Stuck Thread Max Time=2400
Stuck Thread Timer Interval=2400

To modify these values use the Weblogic Administration Console and go to the Tuning section of your managed and admin servers.

Picture 7

Java Virtual Machines (Sun - 64 bits)

This is one of the first things that we do for a new enterprise OBIEE installation. Depending of the memory available on the server is important to increase the amount of available memory to JVMs that the WebLogic servers will use.

By default, the OBIEE configuration for 64 Sun JVM will allocate a minimum heap of 256MB and a maximum heap of 1024MB. If you have a server with enough memory, you should allocate 4096MB both as minimum and maximum heap size.  As per the tuning guide:

SettingSuggested ValueJava Argument
Minimum heap4096 MB-Xms4096m
Maximum heap4096 MB-Xms4096m
Maximum permanent generation heap512 MB-XX:MaxPermSize=512m

To change this Java arguments modify the file:

$MWHOME/user_projects/domains/<your_bi_domain>/bin/setOBIDomainEnv.sh

And make sure that you modify the arguments under your JVM vendor and bit word size.

echo "SVR_GRP = ${SVR_GRP}"
if [ "${SVR_GRP}" = "obi" ] ; then
     if [ "${JAVA_VENDOR}" = "Sun" ] ; then
          if [ "${JAVA_USE_64BIT}" = "true" ] ; then
               SERVER_MEM_ARGS="-Xms4096m -Xmx4096m -XX:MaxPermSize=512m"
               export SERVER_MEM_ARGS
          fi
     if [ "${JAVA_VENDOR}" = "Oracle" ] ; then
          if [ "${JAVA_USE_64BIT}" = "true" ] ; then
               SERVER_MEM_ARGS="-Xms256m -Xmx2048m"
               export SERVER_MEM_ARGS
     else ...

Increasing these values will result on a higher memory consumption right away, but it will get rid of any out-of-memory errors that your JVMs might have. So in general it’s a good idea to increase these values if your server has a good amount of memory, like 16+ GB.

HTTP server compression/caching

If you are using a HTTP server such as OHS (Oracle HTTP server), IIS or Apache, you might want to enable the http compression settings. The basic idea behind this is that the contents (data, images, http code…) that the WebLogic server has to send to the users will be first compressed by OHS prior to sending and then de-compressed by the web browser of the user.

To enable this feature you need to do some modifications to the http.conf file in the OHS installation. For detailed instructions for both OHS and ISS please refer to the Oracle tuning guide mentioned at the beginning of the section.

Picture 8

Performance monitoring of WebLogic/OBIEE

All the changes described above should be applied one at a time and in a very controlled way. As a matter of fact, you need to monitor the performance and the stability of your system after any change.

There are many ways to monitor OBIEE performance, but normally you will use a combination of log files and built in monitoring tools available in Enterprise Manager like the Performance Summary monitor.

To access this tool you have to log in to the Enterprise Manager and right click your bi_serverX under the WebLogic domain section. Once you are there, some metrics will be selected by default but you should go straight ahead to the Metric Selector to spice things a little bit.

Picture 9

The combination that I should use normally to have an eye to the performance and stability of the system is the following:

CPU Usage (%)

It should be pretty low at all times, but if you see spikes or a continuous high usage of the CPU is a good idea to go to your OS and check what is going on.

Heap Usage (%)

This metric is useful to check that we are not running out of memory on our JVMs. If you see that the heap usage is touching your maximum heap consistently this is an indicator that you should increase the memory of the JVM you still have memory available in your system. If you don’t have, you might think about scaling out your environment horizontally.

Active Sessions

The active sessions is there to provide context for the rest of the metrics. As the number of sessions goes up, you should expect more utilization of the whole system

Request Processing (ms)

This is an interesting metric that gives an overall idea of how good is the performance of your environment. As per my experience, I have noticed that when users complains about the system being “slow” this metric is usually pretty high, the order of several thousand ms.

It’s important here to notice that this metric is averaged for all the applications deployed in that server, and this might be misleading.

For example, you might have your analytics working smoothly under <100 ms but maybe some crazy BI Publisher reports going on and generating thousand pages PDF files. In this case, you response time for BI Publisher will be way higher than for analytics, but the average of both will still be quite low, at least nothing that will turn your head. My recommendation in environments where there are more than one application used heavily is to add the response time metric of each application (or at least have a look at the table available below the graphs).

Picture 10

If your analytics application is consistently having a very high response time (>1000ms) you should check if there long queries running by checking the Manage Sessions link in the administration section of OBIEE. If this is the case, you can cancel the queries from there.

In any case, stopping and starting the application from the deployments section of the WebLogic console will usually solve the problem, but this should be considered as your last resort because it will end the sessions and running reports of your users.

Request (per minute)

The request per minute is somehow related to the processing time. Basically when the response time is high, the number of requests per minute will decrease as the system will be able to process less request in the same time.

What we are looking here is for high values. A sudden plunge on this metric normally is associated with a higher system load at that particular time. If it does not recover, will mean a performance decrease.

For this metric we should also do the same as for the Processing Time above, and in case of having not only analytics application running in our server, add the related target based metrics for each application.

In addition to the performance summary monitor it can be useful in some case to check the status of the server threads.

All the work that is processed by the WebLogic server is carried out by threads, these threads are created and disposed depending of the current system load and the configuration of our server. Once a thread is created it will be in standby status until a work manager assigns some work to it. At this point of time the thread will be considered active until it finishes the job.

A thread that is been running for long time will be tagged as a Hogging thread, and eventually (after the StuckThreadTime that we discussed above) it will marked as Stuck. The server failsafe configuration can be set up to restart automatically the WebLogic if there are many stuck threads, or you can bypass this behavior by changing the configuration of your Work Manager with the ignore stuck threads parameter.

So, before we reach this point, is normally a good idea to check the threads status when we are experiencing performance issues.

You can monitor the threads of the system from the WebLogic Console.  Navigate to your server and then select the Threads tab under the Monitoring Tab.

Picture 11

In the above screenshot I highlighted the areas that should get your attention. The main indicator of troubles is the “Health” column, which is related to the overall server Health indicator. If you see warning here (or worse) and this is related to the threads, then you’ll see how several of your threads are in the Stuck and Hogger status.

Normally in these case there is no much to do other than restart the server. But the edge of coming here and check it first is that you can click that “Dump Thread Stacks” button at the top of the screen to get a stack trace of all your threads, so you’ll see what they were doing to become stuck.

Another way to look at the thread information on graphical way is to use the Monitoring Dashboard available out of the box with the WebLogic console.

To access point your browser to http://server:port/console/dashboard

The interface is quite powerful, allowing you to create your own views with a rich set of metrics, which expose for example the Thread pool information, JVM metrics and even host OS metrics.

Picture 12

(Just remember to press the play button on top left or you won’t get any results! :P)

Conclusion

In this blog article we have explored some of the concepts related to solving, tuning and monitoring performance in an OBIEE instance.

Please remember that any change to the performance configuration settings should be always done in a controlled test environment first and monitored closely for some time before applying it to a productive environment.

 

privacy policy - Copyright © 2000-2010 ClearPeaks

topnav