Category: Business Intelligence Applications (OBIA)

Oracle Business Intelligence Applications (OBIA)

ETL Tuning in ODI / BI Apps–The #ETL_ANALYZE_WORK_TABLE parameter

Posted on by admin

One of the first things I do when I run into performance issues with ETL loads is to look at the source and target table statistics. Have they been collected before the current select / insert statement was issued?

It turns out that in Oracle BI Apps the #ETL_ANALYZE_WORK_TABLE parameter is turned off by default when a load plan is generated. This can make doing a high level review of your load plan execution tricky since there will be steps that will seem to be gathering statistics, when in reality, the ODI code generator just puts a placeholder instead of the code for statistics. An example of this is shown below:

 

image

 

image

 

SOLUTION:

Once I realized what the issue was with statistics not being gathered for my work tables I was able to zoom into the ETL_ANALYZE_WORK_TABLE variable by looking at my generated load plan as depicted below, and change its default value to Y. The variable is defined globally so once you change the definition this new default value will apply to any newly generated load plans.

 

image  

 

image

Did you run out of time in Oracle Business Intelligence Applications (OBIA)?

Posted on by admin

There’s nothing worse than this right? I’m with you man (sister)!

All right in all due seriousness this can be an awkward situation where you come in Monday morning and your business stakeholders look deeply angry since none of their reports look right and they need to finish the close of the month/year/quarter.

Anyways, in both OBIA 7.9.x and 11g this is most likely due to the fact that the variables that control the generation of calendar tables are set to a date now in the past.

 

OBIA 11.1.1.7 Solution

  1. Log in to OBIA Configuration Manager (BIACM)
  2. Go to Manage Data Load Parameters
  3. Look for Configure Time Dimension > Gregorian Date Range End
  4. Change the END_DATE parameter to a date far far in the future
  5. Put on a contrite face and let your users know this will be fixed next time your load runs

OBIA 7.9.x Solution

The same general steps would need to be applied on your DAC client to fix this issue in versions of OBIA that use Informatica Power Center

  1. Open your DAC console client
  2. Navitate to Design > Tasks
  3. Look up the SIL_DayDimension task
  4. Look up the Parameters tab on the bottom panel
  5. Change the $$END_DATE parameter to a future date
  6. Same deal, contrite face, deep breath and break in the news that this won’t be fixed until tomorrow morning

 

Related:

How To: Download, install, configure and verify you have the latest version of Opatch

Posted on by admin

On this post we will discuss all the steps necessary to ensure you have the correct version of the OPatch patching utility for oracle software running on your system. If you find this post useful please or Share our site!

Environment Variables:

Set your ORACLE_INSTANCE path to a valid OBIEE 11g instance

Set your ORACLE_HOME path to the OBIEE 11g home (Oracle_BI1)

Validating Your Environment:

OPatch is installed by default, locate it go to the ORACLE_HOME path for your OBIEE installation:

\Oracle_BI1\OPatch

To verify your current session is correctly configured verify the installed version by running the following command:

opatch version

Minimum Required Versions:

OBIEE 11.1.1.5.0 -> OPatch version 11.1.0.8.3 or higher (do NOT use OPatch 12.x)

OBIEE 11.1.1.6.0 -> No OPatch packages available as of March 12, 2012

Downloading a Newer Version of OPatch:

To find and download the appropriate version of OPatch for yor system please go to ORacle Support and find the knoledge base article below:

Note 224346.1 – Opatch – Where Can I Find the Latest Version of Opatch?

Installing OPatch:

To install OPatch once you have downloaded the appropriate version follow the steps below:

  1. Rename your current OPatch directory (ORACLE_HOME\OPatch)
  2. Copy the zip file to your ORACLE_HOME
  3. Unzip the zip file
  4. Verify that the upgrade succeeded

    cd OPatch
    opatch version

File System Access:

OPatch will update the local Oracle Inventory so the user account running OPatch must have acces to the location of the OUI Inventory, to verify this you can run the following command:

opatch -lsinventory

Patch Directory Location (PATCH_TOP):

If you have a centralized location where you store code / releases it is a good idea to create a directory called PATCH_TOP to store patches as they are applied to each environment (DEV, QA, STAGE, PRD).

On this post we will discuss all the steps necessary to ensure you have the correct version of the OPatch patching utility for oracle software running on your system.

Configuring the windows host file to correctly use the loopback adapter for Oracle product installations

Posted on by admin

This procedure is required when installing Oracle products such at the Oracle Database or Oracle Business Intelligence Enterprise Edition on a host machine that uses DHCP.  Follow the steps to configure a loopback adapterand then:

  1. Open the host file under c:\windows\system32\drivers\etc using notepad
  2. Identify your computer’s host name by right clicking on My Computer, selecting properties and clicking on the computer name tab Identify your computers host name by right clicking on My Computer, selecting properties and clicking on the computer name tab
  3. On the first line of your host file add a link between the static IP address you assigned your loopback adapter and your computer’s host name, for example: # IP                 hostname.domain        hostname 10.10.10.35    virtualXP.acme.com virtualXP
  4. On the second line associate the same static IP with the localhost default alias, for example: 10.10.10.35 localhost.localdomain    localhost

Once you have completed these steps you can proceed with the next steps in your Oracle product installation. If you find this post useful please or Share our site! This procedure is required when installing Oracle server products such at the Oracle Database or Oracle Business Intelligence Enterprise Edition on a host machine that uses DHCP.  Follow the steps to configure a loopback adapter and then the steps outlined on this article…

How to stop / start individual OBIEE 11g System Components from the OPMN

Posted on by admin

obia-tech-components-wd
Sometimes I have a configuration change to make or need to restart a specific system component in one of my servers, here is a list of stop / start commands for each of the system components:

BI Service:

opmnctl stopproc ias-component=coreapplication_obis1

opmnctl startproc ias-component=coreapplication_obis1

Javahost:

opmnctl stopproc ias-component=coreapplication_obijh1

opmnctl startproc ias-component=coreapplication_obijh1

Presentation Service:

opmnctl stopproc ias-component=coreapplication_obips1

opmnctl startproc ias-component=coreapplication_obips1

Scheduler Service:

opmnctl stopproc ias-component=coreapplication_obisch1

opmnctl startproc ias-component=coreapplication_obisch1

Cluster Service:

opmnctl startproc ias-component=coreapplication_obiccs1

opmnctl stopproc ias-component=coreapplication_obiccs1

If you find this post useful please or Share it!

Sometimes I have a configuration change to make or need to restart a specific system component in one of my servers, here is a list of stop / start commands for each of the system components…

How to: Install the Oracle Web Tier / Oracle HTTP Server

Posted on by admin

This post will guide you through the steps to deploy Oracle Web Tier 11.1.1.2.0. This instance will later be upgraded to 11.1.1.5.0 by applying PatchSet 1 (12395155) and configured to serve as a front end for WebLogic as part of an Oracle Business Intelligence environment. If you find this post useful please or Share it!

Pre-Install Tasks

Implementation

  • Run the setup.exe file to start your installation
  • Click next on the welcome screen

    Click next on the welcome screen

  • Select the Install and Configure option and click next

    Select the Install and Configure option and click next

  • Verify that all pre-requisite tests are successfull and click next

    Verify that all pre-requisite tests are successfull and click next

  • Specify the location of your oracle middleware home and the name of your new web tier oracle home directory

    Specify the location of your oracle middleware home and the name of your new web tier oracle home directory

    Note: The WebLogic server must already be installed in the same middleware home you specify.

  • Select to configure instances of Oracle HTTP Server and Oracle Web Cache, also select to associate these components with your weblogic domain

    Select to configure instances of Oracle HTTP Server and Oracle Web Cache, also select to associate these components with your weblogic domain

  • Enter the host name and port of your weblogic domain and then enter the domain’s admnistrator credentials

    Enter the host name and port of your weblogic domain and then enter the domain's admnistrator credentials

  • Confirm the instance name, location and component names and click next

    Confirm the instance name, location

  • Enter a password for the administrator of your new web cache instance

    Enter a password for the administrator of your new web cache instance

  • Allow the installer to perform automatic port configuration using the default port numbers for services

    Allow the installer to perform automatic port configuration using the default port numbers for services

  • Provide your Oracle Support credentials, this will register this instance so that it will be available for support and, if specified, allow you to receive security updates via e-Mail

    Provide your Oracle Support credentials, this will register this instance so that it will be available for support and, if specified, allow you to receive security updates via e-Mail

  • Review theconfiguration presented in the summary screen is correct and click Install to begin the process.

    Review the configuration presented in the summary screen is correct and click Install to begin the process.

  • Review the information on the completed install progress screen, make a note of the install log location and click next.

    Review the information on the final summary screen, make a note of the install log location and click next.

  • Review the information presented in the final summary screen, make a note of component urls and click finish to finalize the initial install and configuration process

    Review the information presented in the final summary screen, make a note of component urls and click finish to finalize the initial install and configuration process

Informatica being replaced by ODI in Oracle BI Apps 7.9.5.2

Posted on by admin
Oracle Data Integrator (ODI) Logo

In a long anticipated move among both Fusion Intelligence and Oracle BI Apps customers, Oracle has released the first versions of its packaged business intelligence solution that replaces the third party tools of previous versions (IBM DataStage or Informatica Power Center) with Oracle Data Integrator as the back end ETL tool. You can find detailed install and configuration instructions in the Oracle Business Intelligence Applications 7.9.5.2 Documentation Library.

 

It is important to note that this is a controlled availability release that mainly means the only data source supported is Oracle E-Business Suite 11.5.10, marts available in this release are:

 

  • Financial Analytics
  • HR Analytics
  • SCM and Order Managment Analytics
  • Procurement and Spend Analytics

Limitations notwithstanding, this is a key move that signals a long term strategy for BI Apps that will require customers and partners to develop additional skills and an important upgrade consideration for EBS customers. I personally hope that we will see Oracle implement Golden Gate at some point down the road in order to provide real time operational intelligence for a limited set of KPIs that lend themselves to be used in BAM applications.