Statement of Direction for Oracle Business Intelligence Analytics (OBIA)

Last week Oracle released a Statement of Direction (SOD) for Oracle Business Intelligence Analytics (OBIA). The message from Oracle to their Customers is clear; Move away from OBIA 7.9.6.x and move to OBIA 11g, either in the Cloud or On Premise.

Let us have a look what the actual message is. Find below a picture would find in a lot of the current Oracle presentations regarding Oracle Business Analytics. It’s shows you Oracle’s Business Analytics Strategy

Oracle Business Analytics Strategy - 2014Oracle delivers ‘Any Data’ via a ‘Full Suite of Analytic Tools’, Pre-Configured in ‘Packaged Analytic Applications’, Engineered together on Exalytics (one of Oracle’s ‘Engineered Systems’)

Oracle delivers Packaged Analytic Applications in roughly two different flavors:

  • Informatica 7.9.6.x
  • ODI 11g

There will be no more upgrades on Oracle BIA 7.9.6.x (Informatica release). A few months ago there was a rumor that there would come an Oracle BIA 11g (Informatica release). This is not going to happen.

What are the options?

If you read the SOD for OBIA, existing Oracle BIA 7.9.6.x-Customers are left with three options:

  1. Remain on Oracle BIA 7.9.6.x
  2. Move to ODI 11g
    • New separate instance
    • Migrate to a new instance
  3. Move to the Cloud

Remain on Oracle BIA 7.9.6.x

Remain on Oracle BIA 7.9.6.x

If a client wants to stay on Oracle BIA 7.9.6.x, please check Oracle’s Lifetime Support Policy. You will find the Oracle BIA Lifetime Support Details under the Oracle Business Intelligence EE Releases.

OBIA Lifetime Support - 2014

There are three different Support Levels.

  • Premier
  • Extended
  • Sustained

Check the Oracle Software Technical Support Policies for more details about these Support Levels.

Of course if you want to benefit from new content and new functionality, its better to choose one of the two following options.

Check the Oracle picture below for the OLTP Coverage for OBIA

Oracle BI Applications OLTP Coverage for

Move to ODI 11g

Move to ODI 11g


Move to ODI 11g and benefit from:

  • New Content
  • New Adapters
  • Common Enhancements

Prebuilt Solutions for Orale eBS, PeopleSoft, Siebel, JD Edwards, Fusion Applications, etc.

Move to the Cloud

Move to the Cloud

There will be different Cloud offerings for OBIA.

  • Oracle Transactional Business Intelligence for Oracle SaaS Applications
    • OBIA embedded within every Oracle SaaS Application (CRM, HCM, Finance, Supply Chain, etc.)
  • Oracle Transactional Business Intelligence – Enterprise

Global Currencies in Oracle BIA – The Sequel

I have blogged about Global Currencies in Oracle BIA recently. Global Currencies are used to report in different Currencies like USD, GBP, EUR. If you use the Global Currencies to report the same Currencies (eg. EUR) against different Exchange Rate (Types) you might end up with a challenge, because the Out-of-the-Box Oracle BIA ETL expects different Currency Codes.

If you take eg. the; ‘MPLT_CURCY_CONVERSION_RATES_ToGlobalCurrenciesOnly’-Mapplet, you will notice the;  ‘DOC_TO_GLOBAL2_EXCH_RATE_OUT’-Port. This Port is based on the; ‘DOC_TO_GLOBAL2_EXCH_RATE_VAR’-Port.

Informatica - Mapplet - MPLT_CURCY_CONVERSION_RATES_ToGlobalCurrenciesOnly - DOC_TO_GLOBAL2_EXCH_RATE_VAR

If you further analyze the expression in the; ‘DOC_TO_GLOBAL2_EXCH_RATE_VAR’-Port, you can see that it’s made up of a few different checks.



2. If previous condition(s) not satisfied, then; If GLOBAL2_CURR_CODE = GLOBAL1_CURR_CODE then populate GLOBAL2_EXCH_RATE = GLOBAL1_EXCH_RATE

3. If previous condition(s) not satisfied, then; If GLOBAL2_CURR_CODE = DOC_CURR_CODE, then populate GLOBAL2_EXCH_RATE = 1.0

4. If previous condition(s) not satisfied, then; If DOC_CURR_CODE = ‘STAT’, then populate GLOBAL2_EXCH_RATE = 1.0

5. If previous condition(s) not satisfied, then; If GLOBAL2_CURR_CODE = LOC_CURR_CODE, then populate GLOBAL2_EXCH_RATE = DOC_TO_LOC_EXCHANGE_RATE_VAR else lookup on W_EXCH_RATE_G on the condition (DOC_CURR_CODE, GLOBAL2_CURR_CODE, EXCH_DT, GLOBAL2_RATE_TYPE_VAR, DATASOURCE_NUM_ID). The output of this Lookup is used to populate the; GLOBAL2_EXCH_RATE.

In the case of the same Global Currencies (eg. EUR) the execution of the above expression will stop at condition 2 and populate the GLOBAL2_EXCH_RATE-column with the value of GLOBAL1_EXCH_RATE. Although this behavior seems logical from an Oracle BIA perspective, it might not be satisfying as you cannot compare GLOBAL1_EXCH_RATE with GLOBAL2_EXCH_RATE.

In this case you will end up with a customization on the; the; ‘MPLT_CURCY_CONVERSION_RATES_ToGlobalCurrenciesOnly’-Mapplet. The easiest way is to copy the logic in the; DOC_TO_GLOBAL1_EXCH_RATE_VAR’-Port and apply it to the DOC_TO_GLOBAL2_EXCH_RATE_VAR’-Port. Of course you have to replace the references to; ‘GLOBAL1’ with; ‘GLOBAL2’.



Global Currencies in Oracle BIA

If you read the Oracle Documentation for Oracle BIA (, you could find the following; “Currency conversions are required because your business might have transactions involving multiple currencies. To create a meaningful report, you have to use a common currency.”

Oracle BIA stores amounts in the following currencies;

– Document Currency –> Currency of the actual Transaction (Can vary in a multinational organization)
– Local Currency –> Currency defined in the Ledger
– Global Currency –> Defined in the DAC

You can use a Global Currency if you want to report all the different Currencies in one Global Currency (eg. ‘EUR’, or ‘USD’). Oracle BIA for Oracle eBS is able to report in 3 different Global Currencies. If you use Oracle BIA for CRM, you are able to store an additional two Global Currencies. You define the Global Currencies in the DAC.


You will have to define a Global Rate Type for each Global Currency as well.

– $$GLOBAL1_RATE_TYPE (for the first global currency)
– $$GLOBAL2_RATE_TYPE (for the second global currency)
– $$GLOBAL3_RATE_TYPE (for the third global currency)

DAC - Currency - Rate - Parameters

You would use the above DAC Parameters for a Document Currency to Global Currency Conversion ($$GLOBALn_CURR_CODE & $$GLOBALn_RATE_TYPE). The $$DEFAULT_LOC_RATE_TYPE-Parameter is used for the Document Currency to Local Currency Conversion.

To get a better understanding about how these Conversions work, you should try to understand the; ‘MPLT_CURCY_CONVERSION_RATES’-Mapplet. The purpose of this Mapplet is to; “Find the Conversion Rate for a given Currency Code, Rate Type and Exchange date. You should find this mapping in the Out-of-the-Box SILOS-Folder.

Informatica - Mapplet - MPLT_CURCY_CONVERSION_RATES - Diagram

In the ‘MPLT_CURCY_CONVERSION_RATES’-Mapplet you will find an Expression; ‘EXPT_CALC_EXCH_RATES.’ This ‘EXPT_CALC_EXCH_RATES’-Expression is responsible for the actual Conversion (Calculation).


There are a few other Mapplets with similar name and functionality compared to the ‘MPLT_CURCY_CONVERSION_RATES’-Mapplet.

The Conversion for eg. the GLOBAL1_EXCHANGE_RATE basically consists of three components:

– Input from a Mapping (eg. SIL_APInvoiceDistributionFact – W_AP_INV_DIST_F)
– Lookup to the W_GLOBAL_CURR_G-Table
– Lookup to the W_EXCH_RATE_G-Table

Informatica - Mapplet - MPLT_CURCY_CONVERSION_RATES - EXPT_CALC_EXCH_RATES - Calculate Rate

Input from a Mapping

The ‘MPLT_CURCY_CONVERSION_RATES’-Mapplet is part of a Mapping. Let’s take the ‘SIL_APInvoiceDistributionFact’-Mapping which populates the ‘W_AP_INV_DIST_F’-Table as an example.

Informatica Mapping - SIL_APInvoiceDistributionFact

Lookup to the W_GLOBAL_CURR_G-Table

The ‘SILGlobalCurrencyGeneral_Update’-Mapping in the ‘SILOS’-folder stores the values of the  to the $$GLOBALn_CURR_CODE-, and $$GLOBALn_RATE_TYPE-DAC-Parameters in the W_GLOBAL_CURR_G-Table.

W_GLOBAL_CURR_G - Currency - Rate

As you can see the values in the W_GLOBAL_CURR_G-Table, match with the values in the DAC-Parameters. Of course you can setup these Parameters anyway you like.

Lookup to the W_EXCH_RATE_G-Table

The W_EXCH_RATE_G is a Table which resembles the GL_DAILY_RATE-Table in Oracle eBS. If the contents of this Table are incomplete or incorrect, the whole setup of the different Currencies is useless. The ‘LKP_W_EXCH_RATE’-Lookup is used to find the Conversion Rate for a given Currency Code (‘From’ and ‘To’) and Exchange Date .

Informatica - Mapplet - MPLT_CURCY_CONVERSION_RATES - LKP_W_EXCH_RATE - Complete


If you query the  ‘W_AP_INV_DIST_F’-Table an you find the ‘GLOBAL1_EXCHANGE_RATE’-Column empty for a certain Transaction, the above should help you find out the reason.

Good Luck.

Oracle BIA – Making the Full / Incremental Load work

It’s possible to configure either a Full- or an Incremental Load in Oracle BIA. If you look at the Informatica version of Oracle BIA, there are a few areas you will have to configure.

First you start with the Informatica Mapping. This will be one Mapping. It does not matter whether you run this Mapping Full or Incremental.

Lets take the ‘SDE_ORA_GLJournals’-Mapping as an example. In the Source Qualifier of the Mapping (or Mapplet), you will see a reference to to the $$LAST_EXTRACT_DATE. If you would run the Mapping with these settings, you will run an Incremental Mapping. This means that you only select the data which is created / updated since the last ETL-run.

Informatica - Source Qualifier - $$LAST_EXTRACT_DATE

The $$LAST_EXTRACT_DATE is a Parameter which you configure in the Datawarehouse Administration Console (DAC) and reference in Informatica.


According to the Oracle documentation, the “@DAC_SOURCE_PRUNED_REFRESH_TIMESTAMP. Returns the minimum of the task’s primary or auxiliary source tables last refresh timestamp, minus the prune minutes.”

Make sure this Parameter is available in both the DAC (see above) as well as in the Mapping (or Mapplet).

Informatica - Variables and Parameters - $$LAST_EXTRACT_DATE

This way the Parameter can be used in the Extraction Mapping. If you reference a Parameter in the Extraction Mapping Query which isn’t declared, the Workflow will return an error and won’t complete.

So the steps are easy;

1. Declare the $$LAST_EXTRACT_DATE-Parameter in the DAC
2. Declare the $$LAST_EXTRACT_DATE-Parameter in Informatica
3. Reference the $$LAST_EXTRACT_DATE-Parameter in the Source Qualifier

As I said before, the same Mapping is used for the the Incremental- as well as the Full-Load. If you want to run the two different loads, make sure there ar two different Workflows which run the same mapping. The difference is in the mapping of the Workflow. The Full-Workflow uses the $$INITIAL_EXTRACT_DATE whereas the Incremental-Workflow uses the $$LAST_EXTRACT_DATE.

Informatica - Workflow - SDE_ORA_GLJournals

If you edit the task which belongs to the Incremental-Workflow (‘SDE_ORA_GLJournals’), you will find the Source Qualifier with the extraction query and a reference to the $$LAST_EXTRACT_DATE-Parameter.

As you can see, the LAST_UPDATE_DATE is compared to the $$LAST_EXTRACT_DATE-Parameter.

After each ETL-run, the LAST_EXTRACT_DATES (Refresh Date) per table are stored. You can check, update or delete these values as per requirement (see picture below). If you decide to delete the Refresh Date, a Full Load ill be performed the next time.

DAC - Refresh Dates

As stated earlier, the Full-Workflow is almost identical. The only thing is that there is a reference to the $$INITIAL_EXTRACT_DATE. The $$INITIAL_EXTRACT_DATE-Parameter is defined in the DAC. You define a date in the past. Just make sure that this date captures all the data you need.


Make sure this Parameter is available in both the DAC (see above) as well as in the Mapping (or Mapplet).

Informatica - Variables and Parameters - $$INITIAL_EXTRACT_DATE

This way the Parameter can be used in the Extraction Mapping. If you reference a parameter in the Extraction Mapping Query which isn’t declared, the Workflow will return an error and won’t complete.

How do you make sure that the $$INITIAL_EXTRACT_DATE-Parameter will be used when running a Full-Load?

Informatica - Workflow - SDE_ORA_GLJournals_Full

If you edit the task which belongs to the Incremental-Workflow (‘SDE_ORA_GLJournals_Full’), you will find the Source Qualifier with the extraction query and a reference to the $$INITIAL_EXTRACT_DATE-Parameter.

As you can see, the LAST_UPDATE_DATE is compared to the $$INITIAL_EXTRACT_DATE-Parameter.

At this point everything is in place to either run a Full-, or an Incremental Load.

Informatica - Workflows

You just have to tell the DAC to either run the ‘SDE_ORA_GLJournals_Full’-Workflow or the ‘SDE_ORA_GLJournals’-Workflow (incremental)

DAC - Task - SDE_ORA_GL_Journals

Check the Informatica Session Log when the ETL has a another result than expected. It could be that the Workflows are incorrectly defined. You will see in the Session Log which Parameter is used and what the value of that Parameter is.

Good Luck.

The Fit of Oracle Analytical Applications

My colleague Ronald Kok wrote a blogpost yesterday. Next to Oracle BI Applications for ERP / CRM (OBIA), Oracle offers BI Applications for the industry as well. I agree with Ronald, that there is nothing wrong with the technology. Still there are (a lot of) Oracle Analytical Applications (OAA) projects which struggle to succeed.

In my experience one of the major reasons of these struggles is the fact that a lot of Implementation Partners refuse to implement OAA like it is meant to be. They just start customizing and before you know it, you are no longer looking at OBIA, but you have built your own custom solution. I don’t have anything against custom solutions, but if you have spent a large amount of money on buying OBIA,  then it’s sin if you do not use it.

I do not have any experience with the Industry BI Applications. From my on-hands OBIA implementations, I know for a fact that you should always start with a Vanilla Implementation. Take your time to follow the Configuration Guide. Include Functional expertise in the process of configuring OBIA. If you do the configuration properly and run your first load, you will see the out-of-the-box dashboards and they will show data. Not just data, but the data of the source system OBIA extracts it’s data from.

From this point on, you will be able to assess what OBIA can do for your organization. Perform a Fit/Gap analyses, but focus on the Fit and not solely on the Gap. Oracle BIA - IcebergIn a lot of Oracle BIA presentations you will see the picture on the left side. What you see is definitely not only what  you get. The top of the iceberg could be compared with the dashboards and reports of OBIA. The OBIEE metadata and the ETL (DAC & Informatica or ODI and the Web Configuration Tools) represent the remainder of the iceberg.

A Gap does not necessarily mean that you have to build a whole new customization or dig your way through the existing ETL. It’s quite possible that just changing an out-of-the-box analysis would be sufficient. I have mentioned it earlier, the out-of-the-box Balance Sheet, can easily be customized by using GL Segments instead of the Group Accounts. The GL Segments are out-of-the-box functionality (just some configuration) as well. It’s ‘just’ a matter of replacing fields.

Therefore I recommend everybody to take the time to investigate what’s actually in OBIA. Fit/Gap analyses will take time, but building a customized solution will take time too.

Subledger Accounting (Oracle eBS R12) for Oracle BI Applications

I am currently reading the following book; ‘Oracle Business Intelligence Applications: Deliver Value Through Rapid Implementations’. As far as I know it’s the first and only book on Oracle BI Applications. In Chapter 5: Financial Analytics there is a paragraph about analyzing on Subledger Accounting (SLA) Transactions. This functionality is not in the Out-of-the-Box version of Oracle BIA, but Oracle provides a patch to make SLA Transaction analyzing possible.

If you want more details about this patch, have look on My Oracle Support. Search for; Patch 13697336 – PROVISION FOR LOADING SUB-LEDGER SLA ENTRIES FOR EXCHANGE GAIN/LOSS (Patch)

It could be of added value to my current project, so I will check it out. More on this later.

Exploring Oracle BI Applications

Last May I blogged about a new Oracle BI Applications version which had become GA.Thanks to my colleague Rob Chou we are able to explore this new OBIA version here at Scamander.

First stop; Oracle BI Applications Configuration Manager (http://<hostname>:<port>/biacm)