Category: Performance Tuning

Performance Tuning October 11, 2013

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.

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

In the Issue SQL window, enter the command: “call SAPurgeAllCache()” (without the quotes)
Click the “Issue SQL” button to execute the 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.

Data Integration, Performance Tuning September 30, 2013

Informatica Unconnected Transformations

There are 3 Informatica transformations (External Procedure, Lookup, and Stored Procedure) that can be unconnected in a valid mapping. An unconnected transformation is one that is not connected to the pipeline – that is, it is not connected to any other transformation via a link. Unconnected transformations, especially unconnected lookup transformations, are widely in OBIA mappings.

Unconnected transformations are defined in a mapping and are called / invoked from another transformation in the mapping. They can be called from any transformation that supports the transformation expression language.

Below is a list of the 3 Informatica Unconnected Transformations and how they are called or invoked from another transformation:

Transformation	How its called / invoked
External Procedure	:EXT.external_procedure_transformation(argument1, argument2, …)
Lookup	:LKP.lookup_transformation(argument1, argument2, …)
Stored Procedure	:SP.stored_procedure_transformation( argument1, argument2, [, PROC_RESULT])

Any mapping with an unconnected transformation must also include another transformation that calls the unconnected transformation.

All of the 3 Unconnected Transformations can also be used in connected mode. However, when you want to execute the transformation logic for a subset of the rows passing through the mapping (especially when it’s a small percentage of the rows), you will want to consider using the transformations in unconnected mode and with conditional logic. The conditional logic will cause the unconnected transformation to execute only when necessary, and therefore, may improve perfiormance.

The example below shows a call to an unconnected lookup transformation (lkp_item_id) that is invoked only when the desired item_id value is NULL. In a case where only a small percentage of the rows have an item_id that is NULL, then the lookup would only be invoked for a small number of rows, as opposed to all rows if the transformation was connected.

IIF ( ISNULL(item_id), :LKP.lkp_item_id ( item_name ), item_id )

As a side note, the Sequence Generator transformation can also be invoked from a function call, but it is a connected transformation.

Transformation	How its called / invoked
Sequence Generator	:SEQ.sequence_generator_transformation.CURRVAL

Data Warehousing, Performance Tuning, Reporting March 7, 2013

OBIEE Tuning Whitepaper from Oracle (has been updated)

Oracle has released an updated version of their OBIEE Tuning Whitepaper.

You can find the document here …

https://blogs.oracle.com/pa/entry/test

… or here …

https://support.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?_afrLoop=212370301476321&id=1333049.1&_afrWindowMode=0&_adf.ctrl-state=w65avp7pa_30

You will need to have an Oracle ID to access it (which is a free sign up).

In addition to all the great information that was in the original document, the updates to the document include:

New improved HTTP Server Caching algorithm
Oracle iPlanet Web Server tuning parameters
New tuning parameters settings / values for OPIS/OBIS components

The topics included in the document are:

1.0 Performance Overview

1.1 Introduction to Oracle Business Intelligence EE Performance
1.2 Performance Terminology
1.3 Understanding Key Performance Drivers

2.0 Top Tuning Recommendations for OBIEE

2.1 Tune Operating Systems parameters.
2.2 Tune Oracle WebLogic Server (WLS) Parameters
2.3 Tune 64bit Java Virtual Machines (JVM)
2.4 Tune 32bit Java Virtual Machines (JVM)
2.5 Tune HTTP Server Parameters
2.6 Tune HTTP Server Compression / Caching
2.7 Tune Oracle Database Parameters

3.0 Performance Monitoring OBIEE