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 releases PeopleSoft 9.2 – HR, Financials, and SCM

Oracle has released PeopleSoft 9.2 – including HR, Financials and Supply Chain Management. 

You can read the PeopleSoft 9.2 Press Release here, and check out the PeopleSoft 9.2 Data Sheet here.

The release includes over 1000 new features, functions and enhancements.

As for something specifically related to BI/Reporting, here is a clip from the press release:

“PeopleSoft applications take advantage of new real-time, multi-dimensional decision support analysis capabilities enabled by PeopleTools 8.53 with over 60 prebuilt self-service Pivot Grids delivered in release 9.2.”

This is a trend – more reporting/analysis functionality is being built into or integrated into the ERP systems (as opposed to having separate systems that users access separately to get their jobs done).

Enjoy reading.



OBIEE 11g config and log file locations

These are some key log files and config files in OBIEE 11g. These might be frequently accessed for debugging and checking current parameter settings.

bi_server1.log  &  bi_server1-diagnostic.log
(bi_server1.out  &  access.log are also captured in this directory)

nqserver.log    &    nqquery.log


NQSConfig.INI   &  DBFeatures.INI

instanceconfig.xml      &     credentialstore.xml



Oracle Peoplesoft Accounts Payable Data model and dataflow

This is a simplified Peoplesoft Accounts Payable data flow and data model. The purpose is to provide a basic understanding of the key tables and how data flows through the module.  Understanding the key tables and the data flow is helpful when creating metrics and reports in tools such as OBIEE or when working with OBIA Financial Analytics, and particularly, with the Accounts Payable dashboard, reports and metrics.

When a voucher is created, the information is recorded in the VOUCHER, VOUCHER_LINE and DISTRIB_LINE tables. 

When payments are created for the vouchers, the information is recorded in the PAYMENT_TBL and PYMNT_VCHR_XREF tables.  The PYMNT_VCHR_XREF table provides a bridge between voucher and payment tables.

When a voucher is posted and when a payment is posted, accounting entries are generated and recorded in VCHR_ACCTG_LINE.

Payments are of course made to vendors.  Vendor primary information is stored in the VENDOR table, but there are many more vendor related tables.


Accounts Payable key table descriptions

VOUCHER AP Voucher Header Table. This Table provides the Invoice Header Information and the Accounting Information as displayed on the Voucher. Budget Checking Exceptions can also be identified on this table.
VOUCHER_LINE AP Voucher Line Table. This Table provides the individual line information for each invoice including description, quantity, unit of measure, unit amount, and extended amount. You can have multiple VOUCHER_LINE to each VOUCHER.
DISTRIB_LINE AP Voucher Distribution Table. This Table provides the Invoice accounting line distribution detail. Budget Checking Exceptions can also be identified on this table. You can have multiple DISTRIB_LINE to each VOUCHER_LINE.
VCHR_ACCTG_LINE AP Accounting Entries. This Table contains all transactions that are journal generated.
PYMNT_VCHR_XREF Voucher Scheduled Payment Table. This Table is often used as a link between the Voucher and the Payment Tables.
PAYMENT_TBL AP Disbursements Table. The Primary Table that the Payments are recorded against at time of the PayCycle Manager.
PYMNT_XREF_VW AP Payment Voucher Information. The View is designed to link the Voucher ID with the Payment Reference.
VENDOR Vendor Header Table. This Table represents the high-level Header information related to a specific vendor.
VENDOR_ADDR Vendor Address Table
VNDR_ADDR_PHN Vendor Address Phone Table
VENDOR_LOC Vendor Location Table
VENDOR_CNTCT Vendor Contact Detail Table
VENDOR_PAY Vendor Payment Defaults
VENDOR_WTHD Vendor Withholding Table
VENDOR_WTHD_JUR Vendor Withholding Jurisdiction Table