Thursday Feb 05, 2015

ETL Design for sharing the sequence ID

Author: Archana Dixit


This blog describes an ETL design to share the same sequence ID among several tables. Party Dimension is an exceptional case where Records from different streams merge into one table. Organization, person and group party types are loaded into separate tables W_PARTY_ORG_D, W_PARTY_PER_D & W_PARTY_GROUP_D and later get merged into a master table W_PARTY_D.


These dimensions are Slowly Changing dimension out-of-the-box in the BI Application. They also carry a slowly changing type1 WID named as SCD1_WID. It holds the same value as for the new record also in case of SCD type 2 change. For example, if Organization Name is changed from ‘ABC Software’ to ‘ABCD Software’, the current record would still have the same value for SCD1_WID.


This WID is populated with a sequence generated numeric value. Knowledge modules (KM) use a separate DB sequence for each target table while loading data into W_PARTY_ORG_D, W_PARTY_PER_D & W_PARTY_GROUP_D tables resulting in different sequence numbers for SCD1_WID column in master table W_PARTY_D. The following steps describe an approach to share the same sequence ID.

Step 1:

KM driven option ‘OBI_SCD1_WID’ should be disabled to refrain it from creating separate DB sequences. Set the default value of OBI_SCD1_WID IKM option to ‘false’ as shown in the screen shot below.


Step 2:

Create a mapping to populate the helper table W_PARTY_PER_T1_D table. The source for this mapping should be the corresponding staging table W_PARTY_PER_DS. The mapping expression for SCD1_WID column should read from the sequence created from the previous load stream (in this case W_PARTY_ORG_D load). Set the mapping expression as NEXTVAL (W_PARTY_ORG_S1W) and uncheck ‘Update’ checkbox.


Step 3:

In the flow tab, DETECTION_STRATEGY IKM option should be set to ‘NONE’.


Step 4:

Configure LP components to execute the scenarios loading W_PARTY_PER_T1_D, W_PARTY_PER_D & W_PARTY_D in serial mode in the order as follows.


Monday Apr 21, 2014

How does TYPE2_FLG Work in ETL

Author: Vivian(Weibei) Li


TYPE2_FLG is usually used in slowly changing dimensions in BI Applications. This flag indicates if the dimension is type 2, and it determines the data storing behavior in ETL. This blog is to give you a better understanding on how TYPE2_FLG works in ETL.


Slowly Changing dimension

There are many fundamental dimensions such as Customer, Product, Location and Employee in BI application. The attributes in these dimensions are revised from time to time. Sometimes the revised attributes merely correct an error in the data. But many times the revised attributes represent a true change at a point in time. These changes arrive unexpectedly, sporadically and far less frequently than fact table measurements, so we call this topic slowly changing dimensions (SCDs).

Slowly changing dimensions (SCD) entities like Employee, Customer, Product and others determine how the historical changes in the dimension tables are handled and decide how to respond to the changes. There are three different kinds of responses are needed: slowly changing dimension (SCD) Types 1, 2 and 3.

Type 1: Overwrite the attributes with new changes

Type 2: Add a New Dimension Record

Type 3: Add a New Field

We are talking about type 2 in this blog. In the Type 2 SCD model the whole history is stored in the database. An additional dimension record is created and the segmenting between the old record values and the new (current) value is easy to extract and the history is clear. A minimum of three additional columns should be added to the dimension row with type 2 changes: 1) row effective date or date/time stamp (EFFECTIVE_FROM_DT); 2) row expiration date or date/time stamp (EFFECTIVE_END_DT); and 3) current row indicator (CURRENT_FLG).


The two columns have different concepts though they have similar name. We saw many customers getting confused about the two columns.

SRC_EFF_FROM_DT is extracted from the effective start date of the source (mainly from the main driven source) if the source has the history. If the source doesn’t store history or the history is not extracted, it is hard coded as #LOW_DATE.

EFFECTIVE_FROM_DT is a system column in dimension table to track the history. Remember that we use the knowledge modules (KM) for repeatable logic that can be reused across ETL tasks. Updating the SCD related columns, such as EFFECTIVE_FROM_DT, is usually handled by KM. EFFECTIVE_FROM_DT is modified when inserting a new type 2 record in incremental run, and it is usually modified to the same date as the changed on date from the source. EFFECTIVE_FROM_DT does not always map to the Source Effective Dates.

In type 2 SCD model, EFFECTIVE_FROM_DT is the date used to track the history.

TYPE2_FLG in BI Application

TYPE2_FLG is a flag used to indicate if the dimension is type 2 or not. This flag is used in many dimensions in BI application, such as employee, user, position, and so on. This flag is very important because it determines the history storing behavior.

TYPE2_FLG has two values: ‘Y’ and ‘N’. ‘Y’ means the dimension is a type 2, and ‘N’ means the dimension is type 1. Type 2 dimensions store the history, while type 1 dimensions only store the current record.

For example, if the supervisor is changed from Peter to Susan for an employee on 01/02/2012:

Type 1







Type 2
















As shown above, type 1 dimension overwrites the supervisor with the new supervisor, and only stores the current record. Type 2 dimension inserts a new record with the new supervisor name and keeps the old record as a history. The EFFECTIVE_FROM_DT, EFFECTIVE_TO_DT and CURRENT_FLG are modified accordingly: EFFECTIVE_TO_DT is changed to 01/02/2012 and CURRENT_FLG is set as ‘N’ for the old record. The ‘CURRENT_FLG’ is set as ‘Y’ for the new record with the new EFFECTIVE_FROM_DT.

How to Setup TYPE2_FLG

The out of the box code in BI application should have setup the default values. For the type 2 dimensions, it is usually set as ‘Y’.

The TYPE2_FLG can be configured in BIACM. This variable is configured by different dimension groups.

The screenshot above shows that you can configure the value of this flag for difference dimension groups by clicking the parameter value and overwriting it to a different value.

Note: You can only configure the TYPE2_FLG for the dimension groups that are in this BIACM list. The dimension groups that are not in the list cannot be configured.

You should set the value of TYPE2_FLG carefully. If you override the TYPE2_FLG to ‘N’ for a type 2 dimension, you may meet some issues. I will describe more details in the next session.

Possible Issues Related to TYPE2_FLG

As mentioned earlier, sometimes for some reason, the value of TYPE2_FLG may be set to ‘N’ for the type 2 dimension. This may cause some issues.

In BI application, SDE mapping brings the history from the source in the initial full load in some adapters, such as EBS. TYPE2_FLG affects the storing behavior for these historic records. Here compares the different behaviors when setting TYPE2_FLG to ‘Y’ and ‘N’ for a type 2 dimension.

Case 1-TYPE2_FLG = ‘Y’

Let’s take employee dimension (type 2 dimension) as an example














When loading the data into data warehouse in the initial full run, both the rows (including the historical record #1) will be loaded. TYPE2_FLG is ‘Y’ in this case, KM, which will handle the loading behavior, uses this value to determine the type of employee dimension, and accordingly the storing method.

KM will modify EFFECTIVE_TO_DT and CURRENT_FLG for the two records as TYPE2_FLG=’Y’ in this case.
















Case 2 - TYPE2_FLG =’N’

This time, the TYPE2_FLG is set as ‘N’ for employee dimension (type 2 dimension), which is incorrect. KM will treat it as type 1 rather than type 2.














When loading the data into data warehouse, both the rows will be loaded because the history from the source is stored. However, because TYPE2_FLG is ‘N’, KM won’t modify EFFECTIVE_TO_DT and CURRENT_FLG accordingly, and this will cause issues.

Employee Table in Data warehouse
















As shown above, the two records are in an overlapping time range, and both have CURRENT_FLG as ‘Y’. It may give duplicates when resolving the employee from the facts. For example, the transaction date 02/04/2013 will fall into the time range of the two records, so both will be extracted, thus causing the duplicates in the facts.

How to Debug TYPE2_FLG Issues

As discussed in the previous session, in order to avoid this kind of issues, you should set the value of TYPE2_FLG carefully, and set it as ‘Y’ for out of the box TYPE2 dimensions.

In addition, when you get the duplicates in the fact, you can do the following checks.

  • Check where the duplicates come from in the fact, and find out the problematic dimension if they are from the dimension.
  • Check the data in the dimension for the duplicates to see if you see the similar loading behavior as the one in use case 2 of the previous session. You can first simply see if multiple records having CURRENT_FLG=’Y’.
  • Check the value of the TYPE2_FLG in ODI repository.

1. Open the session log of the task

2. Open ‘Definition’

3. Expand ‘Variable and Sequence Values’

4. Find TYPE2_FLG and check the value

5. If the value is ‘N’ but the dimension is type 2, you may hit the issue described in the previous session.

I also would like to provide you some tips to find out the type of a dimension here. You can find out this information in ODI repository.

  • For one dimension, such as employee dimension, you should first know the dimension table name, for example, W_EMPLOYEE_D
  • Go to ODI repository->’Designer’->’Models’
  • Find out the dimension table and open it by double clicking it
  • Go to ‘Definition’ and check the OLAP type. The type of slowly changing dimension tells you that this dimension is type 2

  • You can also find out which attributes are type 2 by checking the column attribute

1. Expand the dimension table, for example, W_EMPLOYEE_D and then expand Columns

2. Open the attribute of a column by double clicking it

3. Go to ‘Description’ and check ‘Slowly Changing Dimension Behavior’

As shown above, ‘Add Rows on Change’ option tells you that this attribute is type 2.


This blog helps you understand how TYPE2_FLG works in ETL and recognize the importance of this flag. It also gives you a way to debug the possible TYPE2_FLG issue.

Wednesday Mar 05, 2014

BI apps Cumulative Patch 1 is available now

BI applications cumulative patch  is available now.

Patch 17546336 - BIAPPS ODI CUMULATIVE PATCH 1 (Patch) can be downloaded from My Oracle Support.

 "To download patch , navigate to My Oracle Support > Patches & Update. Search with the patch number (17546336) on the Patch Name or Number field. Follow the instructions in the Readme to apply the patch."

Monday Feb 17, 2014

How to Compare RPDs

Author: Vivian(Weibei) Li


Comparing RPD is a very necessary and useful process in RPD development. It is good to consider doing it in the following use cases.

  • You have a customized RPD and want to find out the differences between the customized RPD and the OOTB RPD.
  • You did some customization on the RPD and want to check if the customization is done as expected.
  • The administrator wants to check the differences in the later RPD with the older RPDs.
  • You want to upgrade your RPD and you are not sure if there are any upgrade issues from one release to anther release. You can compare the RPDs for the two releases to catch issues before upgrading.

There are many more cases to compare RPDs. This blog describes how to compare the RPDs.

There are two ways to compare RPDs, one is to use the compare utility and another is to use the compare dialogue in the admin tool.

You can choose either way to compare your RPDs. Here are the comparison of the two methods and some tips.

  • RPD compare utility is executed by the command line, so it doesn’t need the admin tool open.
  • RPD compare dialogue can be directly used in admin tool.
  • When two local RPDs are compared, especially when they require different OBIEE versions, RPD compare utility is recommended.
  • When modifications are performed in the RPD with admin tool and it is to be compared with benchmark RPDs(for example, the original RPD), RPD compare dialogue is recommended. See the details in the next sessions.

Compare RPDs with Utility

There are utilities used for rpd comparison in OBIEE. You can call the utilities with a few commands in the command prompt of Windows. Here lists the steps to compare RPDs with this option.

Step 1 - Choose your OBIEE version

Use the later version of OBIEE to do the comparison if you compare the two releases, and the two releases need different versions of OBIEE.
For example, there are two releases, let’s say release 1 and release 2. release 2 uses the later version of OBIEE. To compare the two releases, you need to use the OBIEE version for release 2.

Step 2 – Check out the subset of RPDs

It is recommended to check out the subset of RPDs that you want to compare with the projects you are interested in. Comparing the entire RPDs will be very inefficient and time-consuming.

Step 3 – Equalize the RPDs

Before comparing the RPDs, you should equalize the RPDs first with equalizerpds utility. The equalizerpds utility will equalize the Upgrade ID of objects in two separate repositories. If objects have the same Upgrade ID, they are considered to be the same object. The utility compares Upgrade IDs from the first repository (typically the original repository) with Upgrade IDs from the second repository (typically the modified repository). Then, the utility equalizes the Upgrade IDs of objects with the same name, using the Upgrade ID from the original repository.

equalizerpds.exe can be found under <ORA_HOME>\Oracle_BI1\bifoundation\server\bin.

· Syntax

The equalizerpds utility takes the following parameters:

equalizerpds [-B original_repository_password] -C original_repository_name

[-E modified_repository_password] -F modified_repository_name [-J rename_map_file]

[-O output_repository_name] [-Y equalStringSet]

· Explanation

Original and modified repository – Use the base repository as the ‘original’ repository and use repository you want to compare as ‘modified’ repository.

For example, if you want to compare release 1 and release 2 RPDs to find out upgrading issues to upgrade from release 1 to release 2, put release 1 RPD as the original and release 2 RPD as the modified repository.


When you equalize objects, you can lose track of object renames because legitimate object renames become different objects. In other words, intentional renames you did in the repository might be changed to different Upgrade IDs, so subsequent merges erroneously treat the renamed object as a new object. To avoid this situation, enter the before and after names of intentionally renamed objects in a rename map file that you then pass to the utility. The equalizerpds utility uses the information in the file to ensure that the original IDs are used in the renamed current objects.

rename_map_file is a text file containing a list of objects that were renamed and that you want to equalize. The format is a tab-separated file with the following columns:

                    TypeName     Name1     Name2
       For example, logical column "Core"."Dim - Customer"."Address4" is re-named as 
       "Core"."Dim - Customer"."Address 4" from Release 1 to Release 2. The file can be written as
                    Logical Column "Core"."Dim - Customer"."Address4" "Core"."Dim - Customer"."Address 4"

       Tip: How to find out the TypeName value?
Query your object with Query Repository tool in the admin tool, and you will find the TypeName value in the result. 
1.       Open the admin tool. Go to Tools->Query Repository


2.       In the popup dialogue, query your object.
3.       You will find the Type value in the result.


You can put this file in any folder in your machine, and give the absolute path in rename_map_file parameter. See the example below.

· An equalization command example

equalizerpds -B pwd123 -C C:\rpdcomparison\release1\release1.rpd -E pwd123 -F C:\rpdcomparison\release2\release2.rpd -J C:\rpdcomparison\rename-map-file.txt -O C:\rpdcomparison\release2\equalizedrpd.rpd

Step 4 – Compare the RPDs

Now you can compare the RPDs with the comparerpd utility. comparerpd.exe can be found under <ORA_HOME>\Oracle_BI1\bifoundation\server\bin.

· Syntax

The comparerpd utility takes the following parameters:

comparerpd [-P modified_rpd_password] -C modified_rpd_pathname

[-W original_rpd_password] -G original_rpd_pathname {-O output_csv_file_name |

-D output_patch_file_name | -M output_mds_xml_directory_name} -E -8

· Explanation

Original and modified repository – Use the base repository as the ‘original’ repository and use repository you want to compare as ‘modified’ repository. The ‘modified’ repository should be the equalized RPD got from step 3.

-O output_csv_file_name is the name and location of a csv file where you want to store the repository object comparison output.

-D output_patch_file_name is the name and location of an XML patch file where you want to store the differences between the two repositories.

-M output_mds_xml_directory_name is the top-level directory where you want to store diff information in MDS XML format.

Note: You can specify an output CSV file using -O, an XML patch file using -D, or an MDS XML directory tree using -M. You cannot specify more than one output type at the same time.

· A comparison command example

comparerpd -P pwd123 -C C:\rpdcomparison\release2\equalizedrpd.rpd -W pwd123 -G C:\rpdcomparison\release1\release1.rpd -O C:\rpdcomparison\results.csv

Compare RPDs with Compare Dialogue

In this session, I will describe how to compare RPDs with the compare dialogue. The compare dialogue must be used with admin tool opened.

Compare the MUD RPD Before and After the Modification

· Open your fixed MUD RPD.

· Go to ‘File’->‘Multiuser’->’Compare with Original’.

It will compare the current modified rpd with the local copy of the original rpd.

Compare the local RPDs

You can also use compare dialogue to do the comparison for the local RPDs.

Note: It is recommended to use the compare utility to compare the local RPDs. Remember to extract the subset of RPDs before comparing. Comparing the entire RPDs will be time-consuming.

· Open your local RPD, let’s say RPD1 with admin tool. This RPD is the base RPD in your comparison.

· Go to ‘File’->’Compare’.

· Select the repository or XML file in the popup compare dialogue.

· Enter the password for the repository which you want to compare to, let’s say RPD2. RPD2 will be the modified repository.

· The compare dialogue will open the RPD2, equalize RPD2, then compare the RPD2 with RPD1, and finally show the results.

· You can see what the object looks like in RPD1 and RPD2 by clicking ‘View 1’ and ‘Edit 2’.

View 1 – the object in RPD1

Edit 2 – the object in RPD2

· Save the diff file as .CSV file in your local machine.

In summary, RPD comparison is a good tool for RPD development.  Using it appropriately will build more confidence for your RPD modification, fix and upgrade. It makes the RPD development more smooth and less error-prone if adding RPD comparison as a necessary process.

Tuesday Nov 26, 2013

Introduction and Tips of Oracle BI Apps Variable Refresh

Author: Chuan Shi


The ETL logic in BI Apps uses parameters in packages, interfaces, load plans, and knowledge modules (KM) to control the ETL behaviors. Parameters can be configured by customers, pre-seeded in the ETL code, or maintained internally:

  • Data Load parameters that can be configured by customers are maintained at product line (PLV) and fact/dimension group levels in the BI Apps Configuration Manager (BIACM).
  • Some parameters are pre-seeded in the ETL code OOTB, e.g. DATASOURCE_NUM_ID.
  • Other parameters are used internally by the load plan to control the execution of the load plan, e.g. EXECUTION_ID.

ETL parameters are handled by using ODI variables. The purpose of this blog is to explain ODI variable refreshing to ensure that correct values of variables are used in Oracle BI Apps ETL.

ODI Variable Classification

To ensure correct values to be used in ETL, variables are refreshed at run time. A variable can be refreshed in either a package or a load plan. Variables can be classified into four categories based on whether and where they are refreshed in ODI (and if a variable needs to be refreshed, then there must be a refreshing logic specified for it, which will be discussed later).

The four categories are:

  1. Not refreshed

Definition: Generally, internal parameters used by the load plan to control the ETL execution, and therefore they shall not be refreshed


  1. Refreshed in the package

Definition: Variables that are not feasible to be refreshed in load plans. (The refreshing logic of such a variable depends on ETL run facts. It sources from DW tables and uses the QUALIFY user define function (UDF). ODI fails to interpret QUALIFY at load plan level in some cases.)


  1. Hardcoded in the load plan

Definition: Variables whose values are hardcoded by overwriting in load plans. The variables will take the hardcoded values in ETL.


  1. Refreshed in the load plan

Definition: Variables whose values are configured in BIACM by customers. (In other words, the values of these variables come from BIACM. The refreshing logic of such a variable uses a UDF to extract its value from BIACM.)


For a complete list of ODI variables, please refer to this post.

Refreshing Variables in a Package

Refreshing variables in a package is straightforward. One needs to create Refresh Variable steps for the variables to be refreshed. The screenshot below shows examples of refreshing IS_INCREMENTAL.

Hard-coding Variables in a Load Plan

Some variables, such as DOMAIN_CODE and LOOKUP_TYPE are hardcoded in load plan components. To do that, go to the load plan component to which the variables are to be hardcoded, select the Overwrite checkbox and provide the hardcode values for the variables. The screenshot below shows examples of hardcoding DOMAIN_CODE and LOOKUP_TYPE.

Refreshing Variables from BIACM

BIACM is a central UI where customers can define the values of data load parameters (i.e., ODI variables), among with many other features offered. ODI variables, which are refreshed in load plans, have their values extracted from BIACM. We also refer to such variables as BIACM variables.

BIACM Variables are classified into truly global variables, PLV specific variables, and fact/dimension group level variables.

  • A truly global variable (e.g., 13P_CALENDAR_ID) is a variable that has the same value in all product lines (i.e., EBS11510, PSFT90, etc) and for all fact/dimension groups. Truly global variables are refreshed centrally in the load plan system components.
  • A PLV specific variable (e.g., LANGUAGE_BASE) is a variable that takes the same value for all fact/dimension groups within a product line, but different values of the variable can be used in different production lines. They are refreshed individually in consuming load plan dev components.
  • A fact/dimension group level variable (e.g., UPDATE_ALL_HISTORY) is group specific. It can take different values in different fact/dimension groups within the same PLV and across different PLVs. They are refreshed individually in consuming load plan dev components.

From a variable value overriding perspective:

  • A truly global variable has a unique value. PLVs and fact/dimension groups cannot override its value.
  • A PLV variable has product line specific values (e.g., LANGUAGE_BASE takes the value of US in EBS product lines but ENG in PSFT product lines). The value is the same for all fact/dimension groups within that product line.
  • A fact/dimension group level variable has group specific values (e.g., TYPE2_FLG has the value of Yes in Position Dimension Hierarchy, while it has the value of No in Asset Dimension). Also, such a variable has a global default value. If a fact/dimension group does not specify the value of such a variable for its use, then the global default value will be used whenever this variable is called by that group (e.g., the global default value of TYPE2_FLG is No).

These variables are defined in ODI. To ensure that variable refreshing works correctly, there are some rules on the definitions of ODI variables:

  • Set the ‘Keep History’ option to ‘No History’;
  • Always provide a default value (the default value will be picked if refreshing from BIACM does not return a value for some reason. Otherwise the ETL will fail.). As a good practice, the ODI default value of the variable can be set the same as the global value of the variable in BIACM.

(Whenever the Keep History option or the Default Value of a variable is changed, the scenarios that use this variable need to be regenerated.)

Once ODI variables are defined, a refreshing logic is needed to refresh them from BIACM. In this regard,

  • The ODI UDF GET_CM_PARAM is used
  • To return the correct value for a variable, we need to specify the following in the refreshing logic:
  • variable name;
  • product line;
  • fact/dimension group.
  • Syntax: getcmparam($(param_code),$(DATASOURCE_NUM_ID))
  • $(param_code) is the name of the variable (e.g., TYPE2_FLG)
  • $(DATASOURCE_NUM_ID) is used to specify the product line.

For PLV/group level variables, we pass #DATASOURCE_NUM_ID as $(DATASOURCE_NUM_ID);

e.g., getcmparam('TYPE2_FLG','#DATASOURCE_NUM_ID')

For truly global variable, we pass #WH_DATASOURCE_NUM_ID as a pseudo-PLV ID.

e.g., getcmparam('13P_CALENDAR_ID','#WH_DATASOURCE_NUM_ID')

  • Do not pass fact/dimension group directly into the syntax. They are determined by where the variable is refreshed.

BIACM variables are refreshed in load plans. To refresh a variable in a load plan, the following three steps are required (they have been done OOTB):

Step 1: Specify the correct logical schema and refreshing logic in the refreshing tab of the variable definition.

The logical schema has to be CM_BIAPPS11G.

The refreshing logic should be getcmparam() with appropriate inputs, e.g.,


Step 2: Update the variable definition in the variables tab of the load plan.

Go to the load plan component where you want to refresh the variables. In the Variables tab, right click on the variables and select ‘Refresh Variable Definition’ so that the variable definition in the LP is synchronized with its real definition. Once this is done, verify that the logical schema is showing CM_BIAPPS11G, and the select statement is showing the embedded SQL in the getcmparam() function.

Step 3: Check the refreshing checkbox at the appropriate LP step.

For truly global variables, Step 3 becomes:

The logic behind getcmparam() guarantees that appropriate value of the variable is returned from BIACM given the name of the variable, the DATASOURCE_NUM_ID passed in, and the LPC step where it is refreshed.

Values stored in BIACM are strings. Therefore all ODI variables refreshed from BIACM will come in as strings. Each of the consuming codes (where the variables are used) should make sure it converts the data type accordingly. For example, dates are returned as a string in format yyyy-mm-dd hh:mi:ss. TO_DATE_VAR UDF is used to convert the returned string to DATE format. Number values are returned as strings as well.

Checklist when Things Go Wrong

What can go wrong?

  • The value of a variable used in ETL is not in line with expectation.
  • A variable refreshed has no value returned, and it fails ETL run.

Overriding Rule (1)

  • In a load plan, when a variable is refreshed in the parent step (e.g., the root step), its value will be inherited by all its child steps, unless this variable is refreshed/overwritten in a child step.

· However, if a variable is refreshed and/or overwritten in a child step, the value refreshed from this step will override the value refreshed from the parent step. Other child steps of the same level will NOT be affected. They will still inherit the value refreshed in the parent step.

Overriding Rule (2) (unlikely to happen but it exists)

If a variable is refreshed both in a package and in a load plan, then the value refreshed from the package will override the value refreshed from the load plan.

When the value of a variable returned from BIACM is not in line with expectation:

  • Confirm where the variable is refreshed, e.g., BIACM? ETL tables in DW? etc.
  • For BIACM PLV or group level variables:
  • Check its value(s) in BIACM UI. For PLV variables, check its value in each product line; for group level variables, check its group specific values as well as global default value.
  • Check if the variable is refreshed in a root step of a load plan (refresh checkbox checked). In the meanwhile, check if the root step is named after a fact/dim group.
  • Check if the variable is incorrectly refreshed or hardcoded in a child step belonging to the root step (avoid overriding rule 1).
  • Check the ODI default value of this variable. If BIACM returns (null), i.e., nothing, for this variable, its ODI default value will be used. Also, if we check the overwrite box (but not the refresh checkbox) of a variable in a load plan step, but forget to provide the value, then the ODI default value will be used.
  • Note: overriding rule (2) is unlikely to happen to BIACM variables.
  • For variables refreshed from ETL tables in DW, an incorrect value likely to indicate run issue. Check the run of that specific task.

When variable has no value:

  • Confirm where the variable should be refreshed, e.g., BIACM? ETL tables in DW? etc.
  • In rare cases, a variable may not have a value returned when it is refreshed, and this leads to ETL failures.
  • ODI behaves like this: it first refreshes the variable from its designated source (e.g., BIACM). If its source returns (null), i.e., nothing, for this variable, the ODI default value of this variable will be used in ETL. However, if the ODI default value is not provided, then this variable will not have a value.

Tuesday May 14, 2013

Load Plan Generator – An Inside Look

Authored By : Don Co Seng

Oracle BI Applications (BIAPPS) was recently released and is now available for download. One of the biggest features in this release is the introduction of Oracle Data Integrator (ODI) as the embedded ETL tool. With the introduction of ODI, a new method of orchestrating the task of loading your data warehouse is now required. This requirement brings about the introduction of Load Plan Generator (LPG).

LPG is a utility for generating ODI load plans for a desired subset of fact tables to be populated into BIAPPS Data Warehouse against one or more source systems. An ODI load plan is an executable object in ODI allowing you to organize tasks based on pre-defined order on the basis of the fact tables being loaded. LPG is invoked from Configuration Manager (CM) and makes use of metadata stored in CM and ODI repository. There is no separate repository required for LPG. This results in significantly lower metadata development and maintenance costs as LPG uses same metadata as in the ETL tool repository.

Key Concepts

Before we get to the inner workings of LPG, let’s take a quick look at a few key concepts that drive the LPG behavior.

BI Apps taxonomy

At the top of the BIAPPS taxonomy are the different offerings available such as Financial Analytics, HR, CRM, etc. Under the offerings are the functional areas such as accounts payable in finance or payroll in HR. Below the functional areas are the different fact groups like “AP Transactions and Balance” or “Payroll Balance”. For each fact group, there are dimension groups associated with it. A dimension group can be specific to a particular fact group or shared across different fact group. The BI apps taxonomy drives both load plan generation as well as the setup flows in Functional Setup manager.

Offering (contains) -> Functional Areas (contains) -> Fact Groups (associated to) -> Dimension Groups


As for BIAPPS load phases, it is similar to that of previous BIAPPS releases. There are 3 main phases: Source Data Extract (SDE), Source Independent Load (SIL), and Post Load Process (PLP). The SDE phase consists of tasks extracting data from your different source systems. This phase loads all your staging tables and requires source system downtime. The SIL phase loads data from your staging tables into their respective dimension or fact tables. The PLP phase loads data into aggregate tables or some other facts requiring additional data processing. The SIL and PLP phase requires data warehouse downtime to complete the load process.

With BI apps, due to clear separation of SDE and SIL/PLP phases it’s possible to control and optimize the source system / warehouse downtime to the corresponding phases.

Design Time vs. Runtime dependencies

The LPG algorithm deals with the design time dependencies and run time dependencies separately. Any intra-entity specific design time dependencies are seeded in the repository. For example if a dimension has multiple steps to load the target table in SIL phase, these steps are seeded once in the ODI repository as they are known at design time and never change. The run time dependencies i.e. association of dimension to fact, or association of a dimension or fact to corresponding staging tables on the basis of sources are calculated by the LPG engine.

This approach reduces the number of iterations and the metadata required to get task ordering correct at the entire graph level as the design time dependencies are always consistently honored. LPG behavior is deterministic and does not require multiple iterations to get your entire graph behave correctly.

Load Plan Generator

Now, let’s take a closer look at how LPG works. At the core of LPG are load plan components. The load plan components capture the design time dependencies. There is a load plan component per dimension or fact per phase in general. Load plan components are used as building blocks by LPG to generate a complete load plan. Load plan components are further classified into two categories:

Development components are defined at the grain of fact groups or dimension groups as described earlier. Each component contains one or more references to actual ODI scenarios. Each development component requires all steps for loading a particular dimension or fact group. The order of scenarios is pre-defined during development. Most of the development components are for a specific dimension or fact group. However, there are also those defined for supporting tables like persistent staging or general tables.

System components are defined to capture the phases and ordering of phases. The system components are mostly static, since BIAPPS load phases rarely change. For special cases, the dependencies across dimension groups and fact groups are defined here. Also, inclusion of support tables such as persistent staging table is controlled here. System components contain references to development components.

Generating a Load Plan

As mentioned earlier, LPG is invoked from CM. Generating a load plan is a two step process. First, a user creates a load plan definition using CM. A load plan definition is mainly a list of one or more fact groups per source system which the user desires to load. The user then invokes LPG to generate a load plan based on this load plan definition. When complete, the generated load plan is stored back in a load plan folder in ODI repository.

Load Plan generation logic

So, how does LPG figure out which dimension or fact group component to include in the load plan to be generated? LPG generates a load plan utilizing the following information:

1. Fact tables belonging to selected fact groups

o LPG determines the fact tables and fact group association via an ODI flex field at the fact table level in the ODI data store.

2. Dimensions dependencies to Facts

o The main source of dependency information between dimensions and facts are the foreign key constraints between them. Dimension to dimension dependency (snow-flake) is also supported up to the 2nd level.

3. Staging tables related to Facts and Dimensions

o Staging and warehouse tables are resolved via BIAPPS data model naming standard. For example XXX_DS is the staging table for XXX_D dimension table.

4. Related PS or TMP tables used as source in scenarios

o PS and TMP dependencies are resolved based on which are used as source or lookup table in scenarios required for the dimension or fact group.

5. Keywords in load plan steps for domains and class dimensions

o An additional resolution is through the use of keywords in load plan steps which are used mainly for resolving steps within class dimensions or domains. These are generic tables used across facts / dimensions and the keywords help associate as subset task for these tables to specific facts or dimensions.

Table Maintenance

For table maintenance, additional steps are added in the generated load plan to perform truncate, index management, and table analysis based on pre-defined settings and table types. We will cover this in more detail in a follow up post.

Multisource Support

For multi-source support, the user can create a load plan definition containing one or more source system for a fact particular fact group. There are 3 sequencing options in this release for multi-source support:

1. Extract-Load, Extract-Load, Extract-Load (EL, EL, EL) – LPG generates separate load plans containing all phases for each source system. Each load plan is executed serially of each other.

2. Extract, Extract, Extract, Load (E,E,E,L) – LPG generates multiple SDE only load plan for each source and a single SIL/PLP load plan. Each SDE only load plan will be sourcing from a particular adaptor. The SIL/PLP load plan should be executed after all of the SDE load plans. Each load plan is executed serially of each other.

3. Extract-Extract-Extract-Load (E-E-E-L) – LPG generates a single load plan containing multiple SDE phases and one SIL phase. This load plan will simultaneously extract data from different source systems. After the SDE phase completes, the SIL followed by PLP phase ensues.

 For more details and information on LPG, please refer to documentation available in OTN.

Thursday May 09, 2013

Oracle BI apps (BI apps on ODI)

From the announcement today

Oracle Business Intelligence (BI) Applications (also known as 11g PS1) is now available on the Oracle Software Delivery Cloud (eDelivery) and on the Oracle Technology Network (OTN). This is the first major release on the 11g code line leveraging the power of ODI, and certified with the latest version of Oracle BI Foundation

What’s New

This major new release of Oracle Business Intelligence Applications 11g adds:

1. New in-memory analytic applications

2. Significantly expand functional content across existing CRM and ERP Oracle BI Applications

3. Re-design to leverage the power of Oracle Data Integrator and, optionally, GoldenGate

4. New out-of-the-box utilities to increase productivity and further drive down Total Cost of Ownership (TCO).

New Oracle BI Applications:

· Oracle Student Information Analytics enables academic institutions to improve recruitment, lower dropout rate by tracking student attendance in real-time, monitor graduation rate, manage student financial transactions, and match up school resources with student needs more effectively.

· Oracle Indirect Spend Planning – a prebuilt planning application that teams with Oracle Procurement and Spend Analytics to enable businesses to optimize spend through what-if modeling

New Content Enhancements: Significant expansion of content improves existing front and back-office Oracle BI Applications including:

· Oracle Human Resources Analytics expands analysis on HR programs and workforce performance with new

Time and Labor Analysis, global Payroll Analysis and Headcount Gain and Loss.

· Oracle Financial Analytics improves company Financial Performance with new Fixed Assets Analysis, providing complete lifecycle of assets from acquisitions, capitalization to retirements. Budgetary Control Analysis, ideal for public sector, delivers insight into expense & revenue budgets including analysis of budgeted amounts, encumbrances, expenditures, funds available, overspending, and recognized revenues.

· Oracle Project Analytics expands insight into project-based data with new Resource Management Analysis, providing visibility into project requirements, trending, utilization, competencies and alignment. Reconciliation of project sub-ledger and General Ledger assist project accountants in reconciling differences between the two ledgers for cost and revenue transactions. Perspective based dashboards provide an optimized Project Analytics user interface that orients the content around specific user perspectives.

· Oracle Supply Chain & Order Management Analytics introduces new Enhanced Inventory Analysis, providing deep insight into Inventory Cycle Count, Inventory Aging, Inventory Expiration/Obsolescence tracking and Inventory balances and transaction details at lot level. Item Cost History Analysis allows insight into historical cost of Supply Chain related items.

· Oracle Procurement & Spend Analytics introduces new Sourcing Analysis, providing a new comprehensive Sourcing Business Process that includes negotiations, awards, savings and performance. Award decisions will help identify trends and check if there is a margin for any savings opportunity.

· Oracle CRM Analytics expands insight into front office Analytics with Indirect Forecasting and Service Contracts support. Forecasting Analysis is now possible when submissions are made up a forecast hierarchy that is outside of the reporting hierarchy. Oracle Price Analytics has added E-Business Suite to the list of supported sources. Customers now have a ready means to assess pricing performance and arrest revenue and margin leakage.  Robust price waterfall analysis possible with the new Waterfall view.

Using New Oracle BI Foundation Suite Features:

· Visualization Enhancements, and User Interaction Improvements

· BI Mobile HD Enhancements: BI Applications available on mobile without additional development

New Oracle Data Integration: BI Apps are completely re-architected to leverage Oracle Data Integrator and Oracle GoldenGate. 

· BI Applications is now available with next-generation Data Integration. Oracle Data Integrator (ODI) delivers unique next-generation, Extract Load and Transform (E-LT) technology that improves performance and reduces data integration costs—even across heterogeneous systems, and improves productivity.

· Functional Setup Manager provides a Web based Administrative User Interface to track and manage implementation projects and their required functional setup steps

· Configuration Manager provides a centralized Web based administrative user interface to simplify the setup and maintenance of Oracle Business Intelligence Applications.

· A new optional pre-packaged solution, that leverages GoldenGate, eliminates the need for an ETL batch window and minimizes the impact on OLTP systems, while providing near real-time reporting and enabling more reliable change data capture and delete support.

· Customers may continue alternatively to use Oracle BI Apps 7.9.6.x and Informatica ETL.


Oracle Blogs Admin-Oracle