Informatica Transformations Frequently used in OBIA

These are some of the Informatica transformations that are frequently used in Oracle Business Intelligence Applications (OBIA).  The OBIA SDE and SIL mappings used to load the Oracle Business Analytics Warehouse (OBAW) are built using these and other transformations.

1. Source Qualifier
The Source Qualifier transformation is used to bring data from one or more tables from the same source into the mapping.  If being used for more than one table, then a join condition needs to be defined between the tables.  The typical naming convention for a Source Qualifier transformation is SQ_* or sq_*.

2. Joiner
The Joiner transformation is used to join tables in different data sources.  The typical naming convention for a Joiner transformation is JNR_* or jnr_*.

3. Expression
The Expression transformation is used to perform simple row-based calculations or derivations.  The typical naming convention for an Expression transformation is EXP_* or exp_*.

4. Filter
The Filter transformation is similar to a where clause in SQL – it adds a conditional filter to the data passing through the mapping.  The typical naming convention for a Filter transformation is FIL_* or fil_*.

5. Aggregator
The Aggregator transformation is used to perform aggregate calculations on the data passing through the mapping, for example, performing a sum or max.  The typical naming convention for an Aggregator transformation is AGG_* or agg_*.

6. Lookup
The Lookup transformation is used to lookup values based on another known/submitted value, and pass the looked up value into the mapping.  There are 2 types of Lookups – connected and unconnected.  The typical naming convention for a Lookup transformation is LKP_* or lkp_*.

7. Update Strategy
The Update Strategy transformation is used to determine and perform the appropriate course of action for data in the mapping.  Based on the determined state of the data, the transformation is used to insert, update, delete or reject records.  The typical naming convention for an Update Strategy transformation is UPD_* or upd_*.


Components of Oracle Business Intelligence Applications (OBIA)

The Oracle Business Intelligence Applications (OBIA) is made up of a number of components that are brought together to create a great prebuilt BI solution.  The components can be categorized into 4 major components.

1. Prebuilt reports and dashboard content + Embedded dashboard/report building tool
This prebuilt content is contained in the Oracle BI Presentation Services Catalog, and some of the content is built on the Oracle BI Repository metadata.
The tools include Dashboard Editor and Answers.

2. Prebuilt metadata content (Oracle BI Server Repository) + Administration Tool      
This metadata content is contained in the Oracle Business Intelligence Applications repository file (EnterpriseBusinessAnalytics.rpd).
This content is built and administered using the BI Administration Tool, and is built from the metadata in the OBAW.

3. Oracle Business Analytics Warehouse
The prebuilt data warehouse that holds data extracted, transformed, and loaded from the transactional sources.  The OBAW contains best-practice star-schemas and conforming dimensions.

4. Prebuilt ETL processes and tools
Prebuilt Informatica content + Embedded Informatica ETL Tool
+ Prebuilt DAC metadata repository files + Embedded DAC Tool
Informatica is a third-party application that performs the extract, transform, and load operations for the Data Warehouse.  The Informatica content includes Extract-Transform-Load (ETL) repository objects, such as mappings, sessions, and workflows, and is contained in the Informatica repository file (Oracle_BI_DW_Base.rep).
The DAC is a tool that is used for setup, configuration, administration, and monitoring of data warehouse processes.  The DAC content includes repository objects such as tables, subject areas, execution plans, and tasks, and is contained in XML files.
These tools and processes together extract data from sources, such as Oracle EBS or PeopleSoft, and load the data into the OBAW.

Oracle PeopleSoft General Ledger data model and data flow

The PeopleSoft General Ledger is a module in the Oracle PeopleSoft Enterprise suite of applications. This post gives a brief overview of the data flow and data model of PeopleSoft General Ledger.  General Ledger applications are generally a central point for key financial reporting and this is also the case for PeopleSoft applications.

Oracle Business Intelligence Applications (OBIA) offers pre-built integration with Oracle Peoplesoft Enterprise, and the tables here are some of the source tables for Oracle Business Intelligence Application Financial Analytics.


Data is brought into the GL Journal Header (JRNL_HEADER) and Journal Lines (JRNL_LN) tables via a number of methods – spreadsheet template import, flat-file import, or via a load process from other PeopleSoft modules.  There are multiple Journal Lines for each Journal Header.

The Journals are edited, and when valid, they can be Posted.  The Posting creates records in the Ledger table (LEDGER).  Ledger data is a common source for reporting – for example, P&L and Balance Sheet reporting will be done against Ledger data.

Some supporting tables for the GL module are:
GL_ACCOUNT_TBL:  contains the value and description of Account values
PRODUCT:   contains the value and description of Product values
DEPT_TBL: contains the value and description of Department values
BUS_UNIT_TBL_GL:  contains the value and description of Business Unit values
ACCT_TYPE_TBL:  contains the descriptions of Account Types – Asset, Liability, Equity, Expense, Revenue

Oracle EBS Receivables Data Flow and Data Model

The Accounts Receivable function is responsible for managing outgoing invoices to customers who purchased goods or services, and the collection and application of all payments, including payments for invoices.  The Oracle Receivables module (a part of the Oracle EBS Financials Suite) helps the Accounts Receivable departments to manage this function effectively and efficiently.

This post describes a summary of the Oracle Receivables data model and data flow.  Some of these tables are source tables for the Oracle Business Intelligence Applications – Financial Analytics module, specifically providing information for the Payabales dashboard.


To be in the position where you need to handle and process a payment in Receivables, you need to have a buyer/payer (most times this is a customer but there are exceptions). Customer records are stored in the HZ_CUST_ACCOUNTS and HZ_PARTIES tables.  Each customer needs to have a site (a location/address of business) for which information is stored in HZ_CUST_ACCT_SITES_ALL and HZ_PARTY_SITES_ALL.

When a customer purchases goods or services from your company, an invoice is generated for the customer.  These invoice transactions are recorded in RA_CUSTOMER_TRX_ALL (invoice headers) and RA_CUSTOMER_TRX_LINES_ALL (invoice lines).

When the customer makes a payment, this generates new transactions.  These are recorded in AR_CASH_RECEIPTS_ALL and AR_CASH_RECEIPT_HISTORY.  If there is adjustment to an invoice, this is recorded in AR_ADJUSTMENTS.

Sometimes payments are received in batches, where a single payment is for multiple invoices.  These batch payments have records in AR_BATCHES.

The AR_PAYMENT_SCHEDULE table holds one record per payment.  Therefore, for payments that pay an invoice in full, there will only be one record related to that invoice.  However, if payments for an invoice are broken up into a payment plan, or if a partial payment is received for an invoice, additional records will be generated in this table for each payment.

I mentioned above that “most times payments are from customers, but there are exceptions”. An example of an exception is “payment from a bank for interest earned”.  The payment is not from a customer and it’s not for goods/services provided.  These types of payments are recorded in AR_MISC_CASH_DISTRIBUTIONS.

These transactions affect accounting which will eventually make their way to the GL (when the Receivables Transfer to GL program is run). The accounting transactions are generated in RA_CUST_TRX_LINE_GL_DIST and AR_RECEIVABLE_APPLICATIONS.

Oracle EBS Payables Data Flow and Data Model

This post gives a quick overview of Oracle Payables data flow and data model for some of the most used tables in the module. This is not a complete coverage of the topic but aims to give a general idea of how records are stored in and moves through the module.


In Oracle Payables, before an AP transaction can be generated, a supplier must exist in the system.  When suppliers are created, records are created in AP_SUPPLIERS and AP_SUPPLIER_SITES.  Invoices are saved in AP_INVOICES (invoice header records) and AP_INVOICE_LINES (invoice lines records).

Payments generate records in AP_INVOICE_PAYMENTS and AP_PAYMENTS_SCHEDULE.  These tables will have 1 record for an invoice if the invoice is paid using a single payment, but will have multiple records for an invoice if the invoice is paid in installments or with more than one payment.

When an invoice is approved and when it is paid, accounting transactions are generated.  These transactions make their way to the GL_INTERFACE table via Payables Transfer to GL process.  In addition to interfacing with GL, Payables also interfaces with many other Oracle modules, such as, Purchasing, Assets, Projects, Inventory, and others (but will not be covered in this post).

Invoices can be entered into Oracle Payables from external systems via the AP_INVOICE_INTERFACE and AP_INVOICES_LINES_INTERFACE tables.  When the Payables Open Interface Import program is run, the records are brought into the regular header and lines tables (AP_INVOICE and AP_INVOICE_LINES).

When payments are made, the information about the payment is recorded in AP_CHECKS.

These are some of the sources for the star-schemas (dimension and fact tables) used in Oracle Business Intelligence Applications (OBIA) Financials module, and in particular, the Payables dashboard and analyses.

Oracle EBS General Ledger data flow and data model

In just about any ERP financial accounting system, including Oracle EBS General Ledger, the General Ledger module is integrated with other modules, such as Accounts Payable and Accounts Receivable.  The General Ledger will collect and store financial activity information from the other modules, and will be the module that best reflects the overall financial condition of the company. Because of this, the General Ledger is typically heavily used for financial reporting from OBIEE, OBIA, or other tools.

This post aims to provide a quick overview of the data model of Oracle EBS General Ledger and the data flow within the module.

Oracle General Ledger is a part of the Oracle E-Business Suite Financials Application.  Many of the other Oracle Applications modules are integrated with Oracle General Ledger, and send transactions to the General Ledger in the form of journals.

For example, when a company receives an invoice, the transaction is entered into Oracle Payables; and then when the invoice is paid, another transaction is generated in Oracle Payables. There could be tens, hundreds or even thousands of these transactions per day.  This activity is recorded in the Oracle General Ledger in the form of journals.

Similarly, when a company makes a sale, and then when payment is received for that sale, those transactions are recorded in the Oracle Receivables module, and the transactions are also passed on to the Oracle General Ledger with the Transfer program.

Below is a diagram of Oracle General Ledger’s data flow and some of the key tables in the data model.


Oracle EBS Modules and External Systems populate the GL_INTERFACE table through the Transfer program and Load programs respectively.  The Journal Import Process takes those records and loads them into the GL_JE_BATCHES, GL_JE_HEADER and GL_JE_LINES tables. The Posting Process calculates and loads balances from those records into the GL_BALANCES table.

The tables covered here are some of the sources for the OBIA star schemas (fact and dimension tables) used in OBIA Financials, and in particular, the General Ledger dashboard and analyses.

GL_INTERFACE This table holds financial transactions   (journals) transferred from other Oracle Applications modules and external   systems.
GL_JE_BATCHES This table identifies a “batch” of   journals that are related and processed together. Each batch contains one or   more journals.
GL_JE_HEADER Each journal has one journal header   and one record this table
GL_JE_LINES Each journal has one or more journal   lines and are tied together by the journal header

Some other important supporting tables in the GL data model include:

GL_CODE_COMBINATIONS This is the Accounting Flexfield table   and it stores the chart of accounts values, and so the table contains the   valid GL account combinations allowed in the system, along with other   relevant information about the accounts.
GL_LEDGERS This table stores all the Ledgers and   Ledger Sets in the Oracle GL system
GL_PERIODS This   table stores information   about the accounting periods defined in the Oracle GL system. Each row contains   information such as, start date, end date of the period, the period type, the   fiscal year, and the period number.
FND_CURRIENCIES This table stores the list of valid   currencies to be used in Oracle Applications

Oracle Business Intelligence Applications (OBIA) Released

Oracle has released OBIA, and it has some significant improvements.  Check out the summary below. You will need an Oracle ID to access the links (if you don’t have one, it’s a free sign up).


Oracle BI Applications, featuring further integration of two new BI Applications, is now available.

The products, which became first available in OBIA Extension Pack release, now have significantly enhanced dashboards, reports and subject areas, and now have been completely integrated to the Oracle BI Applications including a unified data model and conformed RPD, DAC and INFA repositories.  In addition, they now also support an expanded BI Applications Certification Matrix.

Existing customers that have not yet deployed should consider instead of the Extension Pack Release.

  • Oracle Manufacturing Analytics, part of the Oracle BI Applications product family, helps discrete and process manufacturing organizations optimize their supply networks by integrating data from across the enterprise value chain, thereby enabling executives, operations managers, cost accountants and production supervisors to make informed and actionable decisions related to manufacturing execution.  The application includes 21 analytical subject areas that are leveraged to deliver 32 dashboard pages that consist of more than 200 pre-built reports.
  • Oracle Enterprise Asset Management Analytics, part of the Oracle BI Applications product family, offers complete and enhanced visibility to enterprise-wide maintenance information. Pre-built reports covering Maintenance History, Maintenance Cost Analysis and Maintenance Work Orders, provide Maintenance Managers information to maximize performance, identify potential issues much in advance, and address them before they escalate into serious problems. The application includes 11 analytical subject areas that are leveraged to deliver 26 dashboard pages that consist of more than 200 pre-built reports.

Highlights of the Oracle BI Applications Release:

  • New Application – Oracle Manufacturing Analytics with pre-built adapters for EBS Process Manufacturing R12.x and EBS Discrete Manufacturing R12.x and 11.5.10
  • New Application – Oracle Enterprise Asset Management Analytics with pre-built adapters for EBS R12.x, EBS 11.5.10 and IBM Maximo 7.5
  • Universal Adapter to extend the capability to other source systems
  • Certified for OBIEE 11g
  • Certified for Exalytics
  • Certified for DAC 11g including support for Exalytics / Times Ten, Patching Framework, Dual ETL Support and many other performance enhancements
  • Native support for mobile and tablet devices
  • Localized in 28 languages
  • Supported on Oracle, SQL Server, DB2 and Teradata DB

Additional related information is available via:

  • Getting Started With Oracle Business Intelligence Applications [Doc ID 1527634.1]
  • OBIA Bugs Fixed in (Doc ID 1528774.1)
  • Oracle Business Intelligence Applications ETL Data Lineage Guide Release [Doc ID 1527475.1]