ORACLE BIAPPS – CONFIGURE SCD BEHAVIOR FOR CUSTOM DIMENSION COLUMN

In Oracle Business Intelligence Applications, customization is defined as changing the preconfigured behavior to enable you to analyze new information in your business intelligence dashboards. For example, you might want to add a column to a dashboard by extracting data from the source field and storing it in the Oracle Business Analytics Warehouse in the X_<Column_Name> field.

For added custom dimension column, you must configure SCD behavior after reversing it into ODI model so that the load plan can populate data to that column. The following describes how to configure this:

  1. In ODI Designer, modify the dimension datastore. In the Models view, expand the ‘Oracle BI Applications’ folder, Oracle BI Applications (Model), Dimension (Submodel), and Columns.
  2. Double-click the column whose SCD behavior you want to change.
  3. In the Description subtab’s ‘Slowly Changing Dimensions Behavior’ drop-down list, select the column behavior. To implement Type I behavior, select Overwrite on Change. To implement Type II behavior, select Add Row on Change.

scd1

scd2

Advertisements

ORACLE BIAPPS – HOW TO ENABLE SOFT DELETE PROCESS

What is the issue?

During the OBI Apps implementation and customization projects we were confronted with an uncommon issue. Even though we had run many validation processes and had acquired the agreed approvals within the Development, SIT and UAT environments, some metric issues appeared after going live with the Production environment.

One of the issues is the metric shown within the Oracle BI report was different from (normally greater than) the real number found in the data sources (i.e. EBS). The reason is some records were manually deleted in the EBS but not be reflected in the Data Warehouse.

In case you want to flag these records as deleted (soft delete) in the Data Warehouse, you must enable the related primary extract and delete mappings because this feature is disabled by default.

About Primary Extract and Delete Mappings Process

The primary extract mappings perform a full extract of the primary keys from the source system. Although many rows are generated from this extract, the data only extracts the Key ID and Source ID information from the source table. The primary extract mappings load these two columns into staging tables that are marked with a *_PE suffix.

The figure below provides an example of the beginning of the extract process. It shows the sequence of events over a two day period during which the information in the source table has changed. On day one, the data is extracted from a source table and loaded into the Oracle Business Analytics Warehouse table. On day two, Sales Order number three is deleted and a new sales order is received, creating a disparity between the Sales Order information in the two tables.

1

Above figure shows the primary extract and delete process that occurs when day two’s information is extracted and loaded into the Oracle Business Analytics Warehouse from the source. The initial extract brings record four into the Oracle Business Analytics Warehouse. Then, using a primary extract mapping, the system extracts the Key IDs and the Source IDs from the source table and loads them into a primary extract staging table.

The extract mapping compares the keys in the primary extract staging table with the keys in the most current the Oracle Business Analytics Warehouse table. It looks for records that exist in the Oracle Business Analytics Warehouse but do not exist in the staging table (in the preceding example, record three), and sets the delete flag to Y in the Source Adapter, causing the corresponding record to be marked as deleted.

The extract mapping also looks for any new records that have been added to the source, and which do not already exist in the Oracle Business Analytics Warehouse; in this case, record four. Based on the information in the staging table, Sales Order number three is physically deleted from Oracle Business Analytics Warehouse, as shown in following figure. When the extract and load mappings run, the new sales order is added to the warehouse.

2

The following graph describes how the Primary Extract and Delete mappings interact with the database tables:

3

  • The _Primary mappings perform a full extract of the primary keys from EBS source system and load the result into the primary extract (_F_PE) table.
  • The _IdenfifyDelete mappings identify deleted records in the source by doing comparison between primary extract table (_F_PE) and the target table (_F) and load the results into a staging table (_F_DEL).
  • The _SoftDelete mappings update the delete flag column with a value ‘Y’ on the target table (_F) for all the records that were identified as ‘deleted’, driving from the staging area table (_F_DEL).

Enabling Soft Delete Process in Oracle BI Apps 11.1.1.8.1

In order to enable the Primary Extract and Delete mappings you will have to apply changes to the SOFT_DELETE_PREPROCESS data load parameter using Oracle BI Applications Configuration Manager (BIACM).

Here is the list of steps required:

  1. Log in to BIACM as the BI Applications Administrator user.
  2. Select the Manage Data Load Parameters link to display the Manage Data Load Parameter dialog.
  3. Select the Source Instance need to configure and search SOFT_DELETE_PREPROCESS parameter as shown below. Click Search.4
  4. Select the SOFT_DELETE_PREPROCESS Parameter Code and you will see all the OOTB Dimensions/ Facts that you can enable soft delete process.
  5. Change the Parameter Value to ‘Yes’ in which Dimensions/ Facts you want to enable Primary Extract and Delete mapping, as shown below:5
  6. By default, OBI Apps include the filter with the DELETE_FLG field (DELETE_FLG=’N’) but it would be good to double check.6

You can check whether the filter is added in the Logical Table Source as screenshot below:

Soft Delete feature for new facts

The above instruction is just used for the pre-build facts in the Data Warehouse. For the new facts you need to see the OOTB Primary Extract and Delete mappings for reference and build the similar custom mappings for these new facts.

Below is the sample sql code generated by the Primary Extract and Delete Mappings process of the main process which populate the Purchase Cost data from EBS source to W_PURCH_COST_F table:

_Primary:

select
TO_CHAR(PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID)       C1_INTEGRATION_ID
from    BI_ACCNT.PO_DISTRIBUTIONS_ALL   PO_DISTRIBUTIONS_ALL
where    (1=1)
And (PO_DISTRIBUTIONS_ALL.CREATION_DATE>=TO_DATE(SUBSTR(‘#BIAPPS.INITIAL_EXTRACT_DATE’,0,19),’YYYY-MM-DD HH24:MI:SS’))

_IdenfifyDelete:

insert into W_PURCH_COST_F_DEL
(DATASOURCE_NUM_ID, INTEGRATION_ID )
select
T.DATASOURCE_NUM_ID, T.INTEGRATION_ID
from W_PURCH_COST_F  T
left outer join W_PURCH_COST_F_PE  S
on T.DATASOURCE_NUM_ID =S.DATASOURCE_NUM_ID and
T.INTEGRATION_ID =S.INTEGRATION_ID
where S.DATASOURCE_NUM_ID IS NULL
and S.INTEGRATION_ID IS NULL
and T.DELETE_FLG = ‘N’
and T.CREATED_ON_DT > TO_DATE(SUBSTR(‘#BIAPPS.LAST_ARCHIVE_DATE’,0,19),’YYYY-MM-DD HH24:MI:SS’)
and exists
(select 1
from W_PURCH_COST_F_PE DS
where T.DATASOURCE_NUM_ID = DS.DATASOURCE_NUM_ID
)           

_SoftDelete:

update W_PURCH_COST_F  T
set
T.DELETE_FLG = ‘Y’
,T.W_UPDATE_DT = SYSDATE
,T.ETL_PROC_WID = #BIAPPS.ETL_PROC_WID
where (T.DATASOURCE_NUM_ID, T.INTEGRATION_ID) IN
(select D.DATASOURCE_NUM_ID, D.INTEGRATION_ID
from W_PURCH_COST_F_DEL  D
)

After you finish building Primary Extract and Delete mappings, remember that you should apply the filter (DELETE_FLG=’N’) in the Logical Table Source.

ORACLE BIAPPS – TRIMMING RPD

Follow this procedure to extract projects from the full RPD. The end result of this process is a trimmed RPD.

To extract from the RPD the projects for the products you have purchased:

  1. Open a Command window on the computer where the BI Administration Tool is installed.
  2. If you installed Oracle BI EE on Windows, then run bi-init.cmd to launch a Command prompt that is initialized to your Oracle instance. This utility is located in:

<MiddlewareHome>\instances\instance<n>\bifoundation\OracleBIApplication\coreapplication\setup

If you installed the BI Administration Tool using the BI Client installer, then run bi_init.bat to launch a Command prompt that is initialized your Oracle instance. This file is located in:

<Oracle BI Home>\oraclebi\orahome\bifoundation\server\bin

3. In a Command prompt window, run ExtractProjects, as described below:

  • If you installed Oracle BI EE on Windows, ExtractProjects.exe is located in <Oracle Home for BI>\bifoundation\server\bin.
  • If you installed BI Administration Tool using the BI Client installer, ExtractProjects.exe is located in<Oracle BI Home>\oraclebi\orahome\bifoundation\server\bin.

Run one of the following commands:

For extracting a single project:

ExtractProjects -B input_rpd -O output_rpd -I “project_name”

For extracting multiple projects:

ExtractProjects -B input_rpd -O output_rpd -I “project_name1” -I “project_name2”-I “project_name3” (and so on)

where:

input_rpd is the name and path of the full (delivered) release 11.1.1.8.1 RPD and from which you want to extract the project or projects (for example, OracleBIApps.rpd).

output_rpd is the name and path of the RPD you want to create with the extracted projects (for example, OracleBIAppsTrimmed.rpd).

project_name is the name of the RPD project you want to extract.

You will be prompted to enter the encryption password for the RPD (input_rpd ).

The list of projects in the 11.1.1.8.1 RPD includes the following:

  • Financial Analytics Fusion Edition
  • Human Resources Analytics Fusion Edition
  • Marketing Analytics Fusion Edition
  • Partner Analytics Fusion Edition
  • Project Analytics Fusion Edition
  • Sales Analytics Fusion Edition
  • Supply Chain and Order Management Analytics Fusion Edition
  • Student Information Analytics
  • Service Analytics
  • Price Analytics
  • Manufacturing Analytics
  • DataLineage_Project

Note: The RPD contains projects in addition to those listed above. These projects are included for future content delivery and upgrade support. To determine the BI Applications available in this release, see “System Requirements and Supported Platforms,” for Oracle BI Applications release 11.1.1.8.1 at http://www.oracle.com/technetwork/middleware/ias/downloads/fusion-certification-100350.html.

4. Save and rename the trimmed RPD. Make sure the name identifies this RPD as one that has been trimmed, for example, OracleBIAppsTrimmed.rpd.

ORACLE BIAPPS – NOTES WHEN SETTING UP DATA LINEAGE

dl

When setting up Data Lineage on Oracle Business Intelligence Applications 11.1.1.8.1 as document (http://docs.oracle.com/cd/E51479_01/doc.111181/e51483/datalineage.htm#CDEDDIGA), you may get some errors like below:

ODI-1217: Session DATALINEAGE_ETL_SDE_DL_OBIEE_BMM_HIERARCHY (44440500) fails with return code 7000.
IOError: (2, ‘ENOENT’, ‘E:\\software\\obiee\\Oracle_BI1\x08iapps\\DataLineage/rpd_text.txt’)

ODI-1217: Session DATALINEAGE_ETL_SDE_DL_OBIEE_SQL_PARSER (44447500) fails with return code 7000.
IOError: (2, ‘ENOENT’, ‘E:\\software\\obiee\\Oracle_BI1\x08iapps\\DataLineage/sql_query_list.txt’)

ODI-1217: Session DATALINEAGE_ETL_SDE_DL_FUSION_METADATA_EXTRACT (44543500) fails with return code 7000.
OSError: [Errno 0] No such directory: ‘E:\\software\\obiee\\Oracle_BI1\x08iapps\\DataLineage’

ODI-1217: Session DATALINEAGE_ETL_SDE_DL_FUSION_TEMPORARY_LOAD (44703500) fails with return code 7000.
ODI-1226: Step SDE_DL_FUSION_AM_Extract_Temporary.W_FUSION_PILLAR_AM_TMP fails after 1 attempt(s).
ODI-1240: Flow SDE_DL_FUSION_AM_Extract_Temporary.W_FUSION_PILLAR_AM_TMP fails while performing a Loading operation. This flow loads target table W_FUSION_PILLAR_AM_TMP.
ODI-1227: Task SrcSet0 (Loading) fails on the source FILE connection BIAPPS_DW_FILE.
Caused By: java.sql.SQLException: ODI-40438: File not found: E:\software\obiee\Oracle_BI1\biapps\etl\data_files\src_files\BIA_11/AM_List.dsv

ODI-1217: Session DATALINEAGE_ETL_SDE_DL_ODI_INTERFACE_HIERARCHY_DERIVE (44798500) fails with return code 7000.
ODI-1226: Step INTERFACE_HIERARCHY fails after 1 attempt(s).
ODI-1232: Procedure INTERFACE_HIERARCHY execution fails.
Caused By: org.apache.bsf.BSFException: exception from Jython:
Traceback (most recent call last):
File “<string>”, line 1, in <module>
File “<string>”, line 158, in process_interface_hierarchy
IOError: (2, ‘ENOENT’, ‘$ORACLE_BI_HOME/biapps/DataLineage/adaptor_list.txt’)

ODI-1217: Session DATALINEAGE_ETL_SIL_DL_COMMON_INTERFACE_HIERARCHY_FACT (44799500) fails with return code 7000.
ODI-1226: Step SIL_DL_COMMON_Interface_Hierarchy_Fact.W_INTERFACE_HIERARCHY_F fails after 1 attempt(s).
ODI-1240: Flow SIL_DL_COMMON_Interface_Hierarchy_Fact.W_INTERFACE_HIERARCHY_F fails while performing a Loading operation. This flow loads target table W_INTERFACE_HIERARCHY_F.
ODI-1227: Task SrcSet0 (Loading) fails on the source FILE connection BIAPPS_DW_FILE.
Caused By: java.sql.SQLException: ODI-40438: File not found: E:\software\obiee\Oracle_BI1\biapps\etl\data_files\src_files\BIA_11/interface_hierarchy_output.txt

ODI-1217: Session DATALINEAGE_ETL_SIL_DL_COMMON_ETL_SUMMARY (44804500) fails with return code 7000.
ODI-1226: Step SIL_DL_COMMON_ETL_SUMMARY.W_ETL_SUMMARY_F fails after 1 attempt(s).
ODI-1240: Flow SIL_DL_COMMON_ETL_SUMMARY.W_ETL_SUMMARY_F fails while performing a Loading operation. This flow loads target table W_ETL_SUMMARY_F.
ODI-1227: Task SrcSet0 (Loading) fails on the source FILE connection BIAPPS_DW_FILE.
Caused By: java.sql.SQLException: ODI-40438: File not found: E:\software\obiee\Oracle_BI1\biapps\etl\data_files\src_files\BIA_11/lineage_summary.txt

If you have got one of those, please ensure that some following configurations had been done (I tried and the ‘Data Lineage Extract and Load’ load plan ran successfully):

– Use / instead of \ when setting DL_HOME variable on the Data Lineage Exract and Load load plan
– Copy APP-INF from $ORACLE_BI_HOME/fsm/modules/oracle.setup/SetupLite.ear to $ORACLE_BI_HOME/biapps/DataLineage
– Update config of BIAPPS_DW_FILE, change directory from $ORACLE_BI_HOME/biapps/etl/data_files/src_files/BIA_11 to $ORACLE_BI_HOME/biapps/DataLineage (same with the value set for the DL_HOME variable on the Data Lineage Exract and Load load plan.
– Copy all source files from $ORACLE_BI_HOME/biapps/etl/data_files/src_files/BIA_11 to $ORACLE_BI_HOME/biapps/DataLineage
– Enable SDE_DL_OBIEE_BMM_HIERARCHY & SDE_DL_FUSION_METADATA_EXTRACT step in the Data Lineage Exract and Load load plan (they are disabled by default). Disable SDE_DL_ODI_MAPPING_LIST_WIDS step if it is enabled (disabled by default, enabling it will caused SDE_DL_ODI_INTERFACE_HIERARCHY_DERIVE step runs for a long time)