DAC – Oracle BI By Bakboord

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.

1. If GLOBAL2_CURR_CODE = NULL then populate GLOBAL2_EXCH_RATE = NULL

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 (7.6.9.3), 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.

– $$GLOBAL1_CURR_CODE
– $$GLOBAL2_CURR_CODE
– $$GLOBAL3_CURR_CODE

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)

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.

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

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.

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.

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 .

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.

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).

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.

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.

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).

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?

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.

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

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.

DAC Error Message – Error while executing : INFORMATICA TASK:SILOS:@DAC_SIL_PositionDimension_FULL_TD_CMD

Thanks to Frank Davis, I was able to solve the following error; ‘Error while executing : INFORMATICA TASK:SILOS:@DAC_SIL_PositionDimension_FULL_TD_CMD’

A patch (Patch 12968641: DAC 10.1.3.4.1 CUMULATIVE PATCH FOR BI APPS – SEP 2011) for this error can be downloaded here.

Solving the Informatica TM_6795 ERROR

When running an Execution Plan in the DAC, I ran into an error. Investigating this error led to the Informatica Workflow Monitor. The logfile returned the following error;

“TM_6795 ERROR: Session or its instance is invalidated and the Integration Service is configured not to run impacted sessions.”

The Informatica Message Reference leads to the following;

***

TM_6795 The Repository Service marked the session as impacted, and the Integration Service is not configured to run impacted sessions.
User Response: Validate the session or configure the Integration Service to run impacted sessions.

***

If this is the case check whether all the related Informatica Mappings, Mapplets and Workflows, etc. are validated and checked in. The latter did it for me. Not all objects where properly checked in.

Oracle MOS HTML Update 02012011

Document ID	Title	Doc Type	Modified Date
605803.1	PROBLEM TO FILTER A COLUMN WITH A PARAMETER OF TYPE DATE	HOWTO	29-DEC-10
1276905.1	How to customize the OBIEE 11g UI with Custom Skin and Style?	HOWTO	28-DEC-10
1276636.1	After upgrade pivot view charts are not displaying the symbols and also displaying the column	HOWTO	27-DEC-10
1277027.1	Does OBIEE 11g support Itanium chipset ?	HOWTO	29-DEC-10
1277089.1	DefaultImageType Setting In Instanceconfig.xml Still Uses Flash in the Graphs	PROBLEM	29-DEC-10
1277275.1	Does OBI EE 11g support Non SQL VO ADF objects?	HOWTO	30-DEC-10
1274601.1	Browser freezes when creating Bins in Dashboard Prompt based on Results of another Request	PROBLEM	30-DEC-10
1212677.1	DAC 10.1.3.4.1 No More Data To Read From Socket	HOWTO	28-DEC-10
1276672.1	How to Customize the DAC Change Capture SQL for Siebel Sources	HOWTO	28-DEC-10
1274166.1	Inventory Turns Metric Calculation	PROBLEM	28-DEC-10
1268905.1	EBF239342 required on Informatica PowerCenter 8.6.1 HotFix6 for SQL SERVER target Data Warehouse	HOWTO	28-DEC-10
1268769.1	Issue with composite join condition between W_GL_SEGMENT_D and W_GL_BALANCE_A	PROBLEM	28-DEC-10
1055324.1	Certification of the Oracle E-Business Suite R12.1.2 with Oracle Business Intelligence Applications Version 7.9.6.1	HOWTO	28-DEC-10
973777.1	DAC INSTALLATION ERROR with 64 bit.	PROBLEM	28-DEC-10
1131267.1	SDE_PSFT_EmployeeDimension_Biography2 Source Qualifier SQL does not work as its intended	HOWTO	28-DEC-10
1155586.1	Position Dimension Hierarchy Unique Constraint Error	HOWTO	28-DEC-10
1276983.1	How can I ensure my custom Statistics Script gets executed after every ETL?	HOWTO	29-DEC-10
739926.1	SQL1042C error in DAC while running change capture	PROBLEM	29-DEC-10
1277470.1	Dac Email Notification	HOWTO	30-DEC-10
1172884.1	Updated Exchange Rates in the Source System are not Propagated to W_EXCH_RATE_G	PROBLEM	28-DEC-10
1160828.1	W_GL_COGS_FS is populated with Wrong CUSTOMER_ID	PROBLEM	28-DEC-10
1276588.1	Check Global Consistency on the BI Applications 7.9.6.2 Repository throws Warnings	HOWTO	28-DEC-10
1268892.1	‘Enable Project Dimenisons’ Config Tag in DAC should have SCM Subject Areas as Inactive	HOWTO	28-DEC-10
782844.1	where find/download sample data sources OBI 10.1.3.2 OracleBIAnalyticsApps.rpd	HOWTO	29-DEC-10
762612.1	Siebel 8.x Sales Stage old value Audit Trail in Warehouse for Teradata	PROBLEM	29-DEC-10
1096893.1	Persons and Organizations deleted in the Source System are duplicated in the Datawarehouse	PROBLEM	30-DEC-10
1277724.1	Oracle E-Business Suite Applications has been upgraded from R12.0.6 to R12.1.1. What steps need to be perform in OBI Applications 7.9.6.1?	HOWTO	01-JAN-11
1168123.1	Mapping SIL_CustomerLocationDimension Errors “Target table [W_CUSTOMER_LOC_D] has no keys specified”	PROBLEM	28-DEC-10
1166698.1	Ambigious Fact Folders Under Procurement And Spend ? Invoice Lines	HOWTO	28-DEC-10
1164023.1	Missing Tasks tab information after DAC Upgrade from 7.8.4 to 10.1.3.4.1	PROBLEM	28-DEC-10
1230074.1	HR-Vacancies has Null Recruiter Name if recruiter is hired later to the vacancy start date	PROBLEM	28-DEC-10
1231078.1	MISSING LTS STANDARD FILTER IN CORE “DIM – PAYMENT METHOD”	PROBLEM	28-DEC-10
1277348.1	7961 SDE_ORA_SALESCYCLINESFACT_HOLDDURATIONEXTRACT is getting more records	PROBLEM	30-DEC-10
1182993.1	Installation of Informatica 8.6.1 on DB2 9.7 Throws”No more data available to read” Error	PROBLEM	28-DEC-10
1246365.1	How to Access / Use MOVING_AVG_PRICE in W_STANDARD_COST_G	HOWTO	28-DEC-10
1276230.1	Rejected Records In SDE_SBL_VERT_80_Adaptor SDE_GEOGRAPHYDIMENSION_PERSONAL	PROBLEM	28-DEC-10
1253014.1	Dac: 10.1.3.4.1 :MESSAGE:::C:\oracle\10g\client_1\BIN\ocijdbc10.dll: The specified procedure could not be found	HOWTO	28-DEC-10
1085175.1	OBIA 7.9.6 – W_GL_REVN_F INCORRECT SOFT DELETE LOGIC	PROBLEM	28-DEC-10
1210234.1	OBI7961:Performance issue with Sil_PartyPersonDimension_SCDUpdate task	PROBLEM	28-DEC-10
1214953.1	7.9.6 W_ORG_D, W_PERSON_D Replaced by W_PARTY_PER_D, W_PARTY_ORG_D	HOWTO	28-DEC-10
1230093.1	SDE_JDE_CUSTOMERLOCATIONDIMENSIONS Incremental Workflows ran over an hour	PROBLEM	28-DEC-10
1244724.1	PLP_WorkforceEventFact_Month Incremental Running Extremely Slow	PROBLEM	28-DEC-10
1069193.1	DAC:OBIA-7.9.5 SYNCHRONIZE TASK IS FAILING FOR ONE TASK IF SORUCE SQL HAS DBLINK	PROBLEM	28-DEC-10
1145603.1	Fact – HR Recruitment Event Information for Openings Logical Column	PROBLEM	28-DEC-10
1213184.1	Sil_PositionHierarchyDimension_Asisupdate_Full Taking Too Long.	HOWTO	28-DEC-10
1247504.1	W_PARTY_ORG_DS Minority and Women Owned columns are not populated for Peoplesoft source	PROBLEM	28-DEC-10
1275175.1	Create Unique Index W_GEO_D_U1 on W_GEO_D Fails Due to Duplicate Keys	PROBLEM	28-DEC-10
839442.1	DAC – email recipients – only for failures	HOWTO	29-DEC-10
1084366.1	$$ETL_PROC_WID =1 FOR SIL_PARTYORGANIZATIONDIMENSION & SIL_PARTYPERSONDIMENSION	PROBLEM	28-DEC-10
1165353.1	ETL throws ORA-39778: The Parallel Load Option Is Not Allowed When Loading Lob Columns	PROBLEM	28-DEC-10
1132973.1	SDE_PSFT_CostCenterDimension_Hierarchy is Missing from the PSFT 9.0 Adaptors	PROBLEM	28-DEC-10
737066.1	No Informatica SDE mapping for OLTP Table S_MKTGOFFR_PROD	HOWTO	28-DEC-10
1247464.1	Cannot Run Initial Data Load Through Dac From Oracle Applications	PROBLEM	28-DEC-10
1275059.1	X_CUSTOM column unmapped in SIL_EmployeeDimension mapping	HOWTO	28-DEC-10
1268856.1	SIL_FINANCIALRESOURCEDIMENSION failure on MS SQL SERVER 2005 as target Data Warehouse	PROBLEM	28-DEC-10
1268885.1	SIL_PRODUCTMULTIPLECATEGORIES failure on MS SQL Server 2005 as target Datawarehouse	PROBLEM	28-DEC-10
859724.1	SDE_ORA_Reverse_GLJournals parameter default to null	PROBLEM	29-DEC-10
1277351.1	Inventory Metrics at Year and Quarter Level (OBI Applications 7.9.6, 7.9.6.1, 7.9.6.2)	PROBLEM	30-DEC-10

Spread the word:

Like this:

Spread the word:

Like this:

Spread the word:

Like this:

Spread the word:

Like this:

Spread the word:

Like this:

Spread the word:

Like this: