Category: Data Integration (ETL)

Data Integration (ETL)

Some workflows not showing in Informatica Workflow Monitor workflow run

If you run a lot of workflows and have noticed that you are not seeing some of the workflows in the Workflow Run window, this post may help.

Your workflows might be running, but you are not seeing some of them because you have exceeded the maximum number of workflow runs that will be stored in each of the folders.

In Informatica Workflow Monitor, change the “Maximum workflow runs per folder” property.  From Informatica Workflow Monitor, select the Tools -> Options menu item.

Informatica_Workflow_Monitor_Options

Then, change the “Maximum workflow runs per folder” to a number that is higher than the highest number of workflows that are run in your cycle for a given folder.

Informatica_Workflow_Monitor_Option_Maximum_workflow_runs_per_folder

Save changes.  You will now see more workflows in the output after your next job cycle.

 

Data Integration (ETL)

Maximum number of characters allowed in the Informatica SQL Qualifier & Lookup transformations’ SQL Query

The Informatica SQL Qualifier and Lookup transformations’ SQL Query parameter has a limitation on the number of characters that your SQL can contain. There is no limitation on the number of lines, the limitation is on the number of characters.

Your SQL is allowed a maximum of of 32,767 characters. If you copied and pasted SQL into the field, after 32767 characters the query will be truncated.  If you are typing into the field, after 32767 characters, no more characters can be entered.

To reduce your SQL character count, removing unnecessary spaces used for formatting will help. You may also examine your aliases to see if they can be shortened.

Data Integration (ETL)

Unable to see the SQL Override SQL in Informatica SQL Qualifier Transformation

I had an issue in Informatica where I was not able to see the SQL Override SQL code in the SQL Qualifier.

It turns out this was caused by switching the application interface between double monitors or disconnecting from double monitors while one of the Informatica application windows was on the secondary monitor screen, which led to changed settings in the registry.

I found a resolution on the Oracle site and this post explains how to resolve in detail.

To correct this, you need to update the appropriate registry settings. As a precautionary measure, you should backup your registry before making changes to it.

Follow these steps to resolve.
1. Close out all Informatica applications.
2. Click your Windows start icon, and type in regedit.   Then double-click “regedit.exe” to run it.

regedit

3. From the Registry Editor window, navigate as follows …

HKEY_CURRENT_USER -> Software -> Informatica -> PowerMart Client Tools -> 9.5.1 -> Designer -> Options -> Global -> Editor -> SQL
Note: The path will vary based on your installed version. So, instead of “9.5.1”, you may have another version number.

regedit_before

4. Locate these 4 values, which you will update, on the right-hand side of the Registry Editor window (highlighted in the image above):

  • Expression Editor Position
  • Expression Editor Splitter Position
  • SQL Editor Position
  • SQL Editor Splitter Position

5. Edit each of the 4 values one at a time, by double-clicking on them.

regedit_update_valuesAnd you will be changing the 16 values highlighted in the image above.

This is an example of what the values will look like before you make any changes.
regedit_values_before

In the Edit Binary Value window, update the 16 double character values to all be ‘00’

For each key value, the Edit Binary Value window should look like this after the update.
regedit_values_after

6. Click ok. Then repeat for each of the other values.

When you have updated all 4 keys, your Registry Editor window should look like this. Note that the values are now all ’00’.

regedit_after

7. Close the Registry Editor and open your Informatica application and check if it is resolved.  Hopefully, it will be.

I haven’t done any exhaustive testing on this to determine all scenarios under which this happens, but I think the way to prevent this is, if you are using multiple monitors, use only your primary monitor for Informatica applications, or bring your Informatica application windows back to your primary monitor before closing them and before disconnecting your computer from the secondary monitor.

Business Intelligence Platform, Data Integration (ETL)

DAC Execution Plan Failure – “No active subject areas for execution plan”

When your Oracle Business Intelligence Applications (OBIA) DAC Execution Plan fails with a message that looks like this …

“Retrieving execution plan informationMESSAGE:::com.siebel.analytics.etl.execution.exceptions.EmptyExecutionPlanException: No active subject areas for execution plan were found
EXCEPTION CLASS::: com.siebel.analytics.etl.execution.ExecutionPlanInitializationException

com.siebel.analytics.etl.execution.ExecutionParameters.(ExecutionParameters.java:111)
com.siebel.analytics.etl.execution.ExecutionParameterHelper.(ExecutionParameterHelpe”

It is likely that you have a missing Subject Area in your Execution Plan in DAC.  You need to attach the Subject Area to the Execution Plan and rebuild the Plan.

After doing this your job should run successfully.

 

Data Integration (ETL)

Informatica Command-line Programs

Frequently used Informatica Programs are:

  • pmcmd – used to manage workflows, such as starting, stopping and scheduling
  • pmrep – used to perform PowerCenter Repository administration tasks, such as update repository information and perform repository functions
  • infacmd – used to administer Informatica application services
  • infasetup – used to administer Informatica domain and nodes
  • pmpasswd – used to encrypt passwords for use in parameter files or environment variables

Command-line and Interactive Execution
All 5 programs (pmcmd, pmrep, infacmd, infasetup, and pmpasswd) can be executed in Command-line mode.
Three of them (pmcmd, pmrep and infacmd) can be executed in Interactive mode.

Program Locations
All programs except infasetup are located in [InformaticaInstallDirectory]/server/bin.  infasetup is located in [InformaticaInstallDirectory]/server.

Summary
Below is a table that summarizes the features/usage of each of these programs into one location:

InformaticaCommandLinePrograms

Business Intelligence (BI) Development, Business Intelligence Platform, Data Integration (ETL)

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_*.

Business Intelligence Platform, Data Analysis, Data Integration (ETL)

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.

OracleReceivablesDataflowDataModel

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.