| Oracle® Business Intelligence Applications Installation and Configuration Guide for Oracle Data Integrator Users Version 7.9.5.2 Part Number E13669-01 |
|
|
View PDF |
| Oracle® Business Intelligence Applications Installation and Configuration Guide for Oracle Data Integrator Users Version 7.9.5.2 Part Number E13669-01 |
|
|
View PDF |
This chapter describes concepts and techniques for customizing the E-LT functionality in Oracle Business Intelligence Applications, and contains the following topics:
Section 11.1, "Overview of Customization in Oracle Business Intelligence Applications"
Section 11.2, "Category 1 Customizations: Adding Columns to Existing Fact or Dimension Tables"
Section 11.3, "Category 2 Customizations: Adding Additional Tables"
Section 11.5, "Customizing Stored Lookups, Domain Values, and Adding Indexes"
This section provides an overview of customization in Oracle Business Intelligence Applications, and contains the following topics:
Section 11.1.1, "What is Customization in Oracle Business Intelligence Applications?"
Section 11.1.3, "About the Impact of Patch Installation on Customizations"
In Oracle Business Intelligence Applications, customization is defined as changing the out-of-the-box 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 field HZ_CUST_ACCOUNTS.ATTRIBUTE1 and storing it in the Oracle Business Analytics Warehouse in the X_ACCOUNT_LOG field.
The type of data source that you have determines the type of customization that you can do. Data sources can be one of the following types:
Packaged applications (for example, Oracle EBS), which use prepackaged adapters.
Non-packaged data sources, which use the Universal adapter.
Customizations are grouped into the following categories:
Category 1. In a Category 1 customization, you add additional columns from source systems that have pre-packaged adapters and load the data into existing Oracle Business Analytics Warehouse tables. For more information about performing Category 1 customizations, see Section 11.2, "Category 1 Customizations: Adding Columns to Existing Fact or Dimension Tables".
Category 2. In a Category 2 customization, you use pre-packaged adapters to add new fact or dimension tables to the Oracle Business Analytics Warehouse. Category 2 customizations normally require that you build new SDE and SIL mappings. For more information about performing Category 2 customizations, see Section 11.3, "Category 2 Customizations: Adding Additional Tables".
Category 3. In a Category 3 customization, you use the Universal adapter to load data from sources that do not have pre-packaged adapters. For more information about performing Category 3 customizations, see Section 11.4, "Category 3 Customizations: Adding New Data as a Whole Row into a Standard Dimension Table".
The figure below summarizes the category of customization that you can perform for each type of data source and type of modification.
For detailed information about tables and naming conventions, see Oracle Business Analytics Warehouse Data Model Reference.
When you customize E-LT Packages and Interfaces, you usually work in the \Oracle BI Applications 7.9.5.2\Mappings folder in the Models view in ODI Designer.
Note: When you make customizations to any object, create a version of that object before and after the modifications. These versions will enable you to revert to previous functionality if required, and also to manage customizations after a patch upgrade.
This chapter explains how to customize your E-LT functionality, after you have performed a Business Analysis and Technical Analysis. This chapter does not cover the other typical tasks that you need to perform, as follows:
Business Analysis - before you start customization, you typically analyze your current BI dashboards to determine the changes you need to support your business or organization.
Technical Analysis - when you have agreed your business requirements, you need to determine the technical changes you need to make, by identifying source tables, staging tables, target tables, and ODI Packages and Interfaces that you need to modify.
RPD Modification - having made the customizations in the E-LT functionality, you need to modify your RPD to expose the new data in your dashboards. For more information about RPD modification, refer to the Oracle Business Intelligence Enterprise Edition documentation library.
This section explains what you must do to re-apply a customization that you have made if you apply an Oracle Business Intelligence Applications patch that overwrites that customization. For example, if you install a patch that modifies the Supply Chain and Order Management application, you might need to manually re-apply customizations that you have made to the Supply Chain and Order Management application.
Before you apply a patch, you need to 'version' your existing Work Repository (for example, in ODI Designer so that you can detect and re-apply customizations after the patch installation.
A patch only installs changed repository objects, not the whole Work Repository. Therefore, you only need to re-apply customizations to mappings that have been changed by the patch. For example, if a patch only modifies the Supply Chain and Order Management application, you only need to manually re-apply customizations that you have made to the Supply Chain and Order Management application. Customizations in other applications are not affected by the patch.
To minimize the amount of effort required to re-apply customizations after a patch installation, Oracle recommends that you follow the customization methodology that is described in this chapter.
To maintain customizations after a patch installation:
Before you apply a patch, version your existing Work Repository and customized objects by following the steps in Section 11.1.3.1, "How to Version A Work Repository And Work Repository Objects".
Apply the patch.
After you apply a patch, re-apply customizations that were overwritten during the patch installation by following the steps in Section 11.1.3.2, "How to Re-apply Customizations After a Patch Installation".
Create a version of the customized objects.
Notes
When you customize objects, you must evaluate the options and determine the best approach for your environment. If you find that the custom object approach allows the ELT to run in an acceptable amount of time, then this is the preferred approach. If the custom object causes the ELT process to take too long, you might want to consider incorporating the extension into an existing object package or interface.
When you add custom columns to the Oracle Business Analytics Warehouse, you must make the change in all Oracle Business Analytics Warehouse objects (for example, the W_XXX_D/_F tables as well as the staging tables).
This section explains how to 'version' your Work Repository in ODI Designer so that you can detect and re-apply customizations after a patch installation.
After you apply a patch, you must re-apply customizations by following the steps in Section 11.1.3.2, "How to Re-apply Customizations After a Patch Installation"
After you 'version' an object in ODI Designer, use the Version Browser to manage versions (that is, select File, then Version Browser).
To re-apply customizations after a patch installation:
In ODI Designer, display the Projects view.
Right-click the Oracle BI Applications 7.9.5.2 project and choose Version, then Create to display the Create: <Project Name> dialog.
Use the Create: <Project Name> dialog to specify a unique version number and optional description, and click OK.
Run the package 'Export Changed Objects' in the folder 'Oracle BI Applications 7.9.5.2\Utilities_and_Execution\Utilities\User.
This package will create a file called 'List_Of_Modified_Objects.txt' in the folder location that you specify. This TXT file contains a list of customized objects. This package will also create XML export files for the changed objects in the folder location that you specify, within the date range.
This section explains how to re-apply customizations after you have applied a patch. Initially, any customized objects in the area affected by a patch are over-written with a new version of the object, which overwrites the customization. You use the Version Comparison utility in ODI Designer on the customized objects to compare the new version of an object and the original customized version of that object. When Version Comparison utility has performed the comparison, you can do one of the following:
Manually re-apply the customizations to the object.
Retain the new non-customized version object provided by Oracle (in the patch).
Restore the previous customized version of the object.
For example, before a patch installation, you might have modified the expression of the DUE_DAY_OF_MONTH column in the Interface SDE_ORA_APTermsDimension.SQ_RA_TERMS. After the patch installation, the expression of the DUE_DAY_OF_MONTH column might be reverted to AP_TERMS_LINES.DUE_DAY_OF_MONTH. To reapply the customization, check the expression difference using the comparison dialog, and then replace the current version column expression with the previous version of the column expression.
Before you can re-apply customizations, you must have versioned your Work Repository and customized objects by following the steps in Section 11.1.3.1, "How to Version A Work Repository And Work Repository Objects".
To re-apply a customization after a patch installation:
In ODI Designer, locate the object that you want to compare with the original (customized version).
For example, if you customized the Package SDE_ORA_APTermsDimension in the SDE_ORA_APTermsDimension project folder, locate this object in the Project tab.
Right-click the object (for example, a package), and choose Version, then 'Compare with version' to display the Compare With dialog.
Use the Compare With dialog to select the original version, then click OK to display the Comparison dialog.
Use the Comparison dialog to determine what customizations need to be re-applied.
Manually re-apply the customizations on the respective objects.
For example, to restore a previous (customized) version of an object, right click on the object and select Version, then Restore, and select the version of the object that contains the customization.
Alternatively, you could manually apply a customization to the new version of the object by editing the new object in ODI Designer and making the changes again. For example, you edit an Interface, and change the expression for a column in the Target Datastore by editing the value in the Implementation field.
Repeat steps 1 to 5 for every customization that you need to re-apply.
Create a new version of each customized object.
Category 1 customizations add additional columns from source systems that have pre-packaged adapters and load the data into existing Oracle Business Analytics Warehouse tables.
This section contains the following topics:
Section 11.2.2, "Typical Steps to Extend Mappings in the Oracle Business Analytics Warehouse"
Section 11.2.3, "Example of Extending the Oracle Business Analytics Warehouse"
Section 11.2.4, "Other Types of Customizations Requiring Special Handling"
Category 1 customizations involve extracting additional columns from source systems for which pre-packaged adapters are included (for example, Oracle) and loading the data into existing Oracle Business Analytics Warehouse tables. For Category 1 customizations, data can also come from non-packaged sources, but this section assumes that the sources have already been mapped with a Universal adapter and only need to be extended to capture additional columns. (The initial mapping of a Universal adapter is considered a Category 3 customization. For information, see Section 11.4, "Category 3 Customizations: Adding New Data as a Whole Row into a Standard Dimension Table".)
In order to see additional columns in the Oracle Business Analytics Warehouse, the columns must first be passed through the ELT process. The existing mappings and tables are extensible. Oracle Business Intelligence Applications provides a methodology to extend preconfigured mappings to include these additional columns and load the data into existing tables.
Oracle Business Intelligence Applications recognizes two types of customization: extension and modification. The supported extension logic allows you to add to existing objects. For example, you can extract additional columns from a source, pass them through existing mappings, and populate new columns added to an existing table. Generally, Oracle Business Intelligence Applications does not allow you to modify existing logic or columns. You should not change existing calculations to use different columns, and you should not remap existing columns to be loaded from different sources.
For example, if you want to calculate revenue differently from the existing logic, you should create a new transformation and connect that calculation to a new column, for example, X_REVENUE. You can then remap the Oracle Business Intelligence repository to point to the new X_REVENUE column.
Most mappings have a single placeholder column, named X_CUSTOM, that marks a safe path through the ODI interfaces. If you add transformations to the mapping, they should follow the same route through the mapping as X_CUSTOM.
In the figure below, the preconfigured logic is shaded in gray. You should not modify anything contained within these objects. You should add customizations to existing objects rather than creating new packages and interfaces, which allows them to run parallel to the existing logic.
The most common reason for extending the Oracle Business Analytics Warehouse is to extract existing columns from a source system and map them to an existing Oracle Business Analytics Warehouse table (either fact or dimension). This type of change typically requires you to extend the interfaces within a SIL package. If the data is coming from a packaged source, then you will also need to extend the interfaces within an appropriate SDE adapter package. If the data is coming from a non-packaged source, then you must use a Universal adapter package. If an appropriate package does not already exist, you will need to create a Universal adapter package with interfaces.
To extend an ODI package in the Oracle Business Analytics Warehouse:
Create a version of the folder that contains the ODI Packages and Interfaces that you need to customize.
For example, in ODI Designer, display the Projects view, right-click on a folder, and choose Version, then Create.
Extend the source and target tables by making changes to the tables in the database.
You then can use ODI to reverse-engineer the additive changes on the source and target definitions into models in ODI (which replaces the existing definitions), or manually edit the existing definition.
As a best practice, Oracle recommends that you name custom columns with a X_ prefix to make it easier to distinguish custom columns that have been added to an existing table and to ensure there are no name conflicts with any columns Oracle might add later to that table.
Extend the SDE Package and Interfaces by mapping the additional columns, as follows:
In the ODI Package editor, display the Projects view, and open the package.
For example, you might open the Oracle BI Applications 7.9.5.2\Mappings\SDE_ORA11510_Adaptor\SDE_ORA_OrganizationDimension_Customer folder, and edit the SDE_ORA_OrganizationDimension_Customer Package.
Display the Diagram tab.
Edit either the SQ_BCI_ interface or the LKP_ interface to display the Interface: <Name> dialog.
For example, the SDE_ORA_OrganizationDimension_Customer Package, you might edit the SQ_BCI_CUSTOMERS Interface.
Display the Diagram tab.
Map the new column in the Source table to the Target Datastore.
Click OK to save the changes.
In the ODI Package editor, edit the last interface in the sequence (for example, named RUN <target table name>).
Display the Diagram tab.
Map the new column in the Source table to the Target Datastore.
Click OK to save the changes.
Repeat steps a. to j. for both branches within the package (both full load and incremental load).
For example, in the SDE_ORA_OrganizationDimension_Customer Package, you might also edit the SQ_BCI_CUSTOMERS_FULL Interface.
For a detailed example, see Section 11.2.3.2, "Example of Extracting Data from an Oracle EBS 11.5.10 Data Packaged Source into the ODI Staging Area"
Repeat step 3 for the SIL package and interfaces.
For example, you might open the Oracle BI Applications 7.9.5.2\Mappings\SILOS\SIL_OrganizationDimension folder, and edit the SIL_OrganizationDimension Package.
For a detailed example, see Section 11.2.3.3, "Example of Loading Data from the Staging Area into an Existing Target Table"
Regenerate the scenarios for the packages that you have modified.
You are now ready to perform E-LT with the modified scenarios.
This section contains a worked example of adding additional columns from source systems that have pre-packaged adapters and loading the data into existing Oracle Business Analytics Warehouse tables (known as a Category 1 customization).
This section contains the following topics:
Section 11.2.3.3, "Example of Loading Data from the Staging Area into an Existing Target Table"
Section 11.2.3.4, "Tips for Modifying the SQ_BCI_ Interface"
Section 11.2.3.5, "Including a Source Table for the Change Capture Process"
In this example, a company has identified additional fields in a source system table HZ_CUST_ACCOUNTS that need to be added to the Oracle Business Analytics Warehouse table W_ORG_D. Data is passed from an existing source table to an existing target table, known as a category 1 customization. The company uses an extension field to capture information related to organizations referred to as 'ACCOUNT_LOG.' In addition, the company wants to include the name of the person who last updated the record as an attribute of the organization.
In this example, you want to extract information from the following two fields that are not extracted by the out-of-the-box application:
HZ_CUST_ACCOUNTS.ATTRIBUTE1
ATTRIBUTE1 is currently not extracted from the source table HZ_CUST_ACCOUNTS into the temporary table ODI_SQ_BCI_CUSTOMERS.
HZ_CUST_ACCOUNTS.LAST_UPDATE_LOGIN
LAST_UPDATE_LOGIN is currently extracted from the source table HZ_CUST_ACCOUNTS into the temporary table ODI_SQ_BCI_CUSTOMERS, but is not loaded into the staging table W_ORG_DS.
The diagram below shows the two fields ATTRIBUTE1 and LAST_UPDATE_LOGIN as they pass from the source system table to the target table via the tables: HZ_CUST_ACCOUNTS to ODI_SQ_BCI_CUSTOMERS to W_ORG_DS to ODI_Sq_W_ORG_DS to W_ORG_D.
The customization is done in two parts, as follows:
SDE Processing, which extracts data from the source system and loads it into the staging area (for more information, see Section 11.2.3.2, "Example of Extracting Data from an Oracle EBS 11.5.10 Data Packaged Source into the ODI Staging Area").
SIL Processing, which extracts data from the staging area and loads it into the target table (for more information, see Section 11.2.3.3, "Example of Loading Data from the Staging Area into an Existing Target Table").
This section shows how data is extracted from an existing source table into the staging area.
The diagram below shows the new E-LT mappings that you need to load the new data into the staging area, and the ODI Interfaces that you need to modify.
Note: The diagram above only shows the incremental interfaces.
To customize the E-LT process to load these two fields into the staging area, you need to:
Extract the HZ_CUST_ACCOUNTS.ATTRIBUTE1 value from the source table HZ_CUST_ACCOUNTS into the temporary table ODI_SQ_BCI_CUSTOMERS using the Interfaces SQ_BCI_CUSTOMERS and SQ_BCI_CUSTOMERS_FULL.
Then, load the ODI_SQ_BCI_CUSTOMERS.ATTRIBUTE1 value from the temporary table ODI_SQ_BCI_CUSTOMERS into the X_ACCOUNT_LOG field in the staging table W_ORG_DS using the Interfaces ORG_DS and ORG_DS_FULL.
Load the SQ_BCI_CUSTOMERS.LAST_UPDATE_LOGIN value from the temporary table ODI_SQ_BCI_CUSTOMERS into the X_LAST_LOGIN field in the staging table W_ORG_DS using the Interfaces ORG_DS and ORG_DS_FULL.
Note: Remember that LAST_UPDATE_LOGIN value is already extracted from the source table HZ_CUST_ACCOUNTS into the temporary table ODI_SQ_BCI_CUSTOMERS, but is not loaded into the staging table W_ORG_DS.
To extract data from an Oracle EBS 11.5.10 Data Packaged Source:
In ODI Designer, display the Projects view, expand the 'Oracle BI Applications 7.9.5.2'\Mappings\SDE_ORA11510_Adaptor folder.
Right-click on the SDE_ORA_OrganizationDimension_Customer folder, and choose Version, then Create to display the Create: <Object> dialog, and specify a unique version number and optional version description.
Display the Models view, expand the Dimension Stage folder, and edit the W_ORG_DS data store to display the DataStore: <Name> dialog, and display the Columns tab.
Create the following columns:
X_ACCOUNT_LOG (VARCHAR2(10))
X_LAST_LOGIN (VARCHAR2(10))
In the Models view, right click on the model 'Oracle BI Applications 7.9.5.2' and select Generate DDL to display the Generate DDL dialog.
The Generate DDL option deploys the changes in the database.
Select the check-box in the Synchronize column next for the W_ORG_DS table.
Click the (...) button to the right of the Generation Folder field to display the Select a folder dialog, and select the \Utilities\System folder, and click OK.
When the Procedure: DDL <Name> dialog is displayed, click Execute.
Display ODI Operator and make sure that the procedure executes successfully.
Display the Projects view, expand the Mappings folder, and expand the SDE_ORA_OrganizationDimension_Customer folder.
Edit the Interface SDE_ORA_OrganizationDimension_Customer.SQ_BCI_CUSTOMERS to display the Interface: <Name> dialog, and do the following:
Display the Diagram tab, and select the ATTRIBUTE1 field in the Target Datastore area.
Use the Launch Expression Editor icon to display the Expression Editor dialog, and use this dialog to select HZ_CUST_ACCOUNTS.ATTRIBUTE1 as the value in the Implementation field.
Click OK to save the details.
Repeat Step 10 for the Interface SDE_ORA_OrganizationDimension_Customer.SQ_BCI_CUSTOMERS_FULL.
Edit the Interface SDE_ORA_OrganizationDimension_Customer.ORG_DS to display the Interface: <Name> dialog, and do the following:
Display the Diagram tab, and select the X_ACCOUNT_LOG field in the Target Datastore area.
Use the Launch Expression Editor icon to display the Expression Editor dialog, and use this dialog to select SQ_BCI_CUSTOMERS.ATTRIBUTE1 as the value in the Implementation field.
Select the X_LAST_LOGIN field in the Target Datastore area
Use the Launch Expression Editor icon to display the Expression Editor dialog, and use this dialog to select SQ_BCI_CUSTOMERS.LAST_UPDATE_LOGIN as the value in the Implementation field.
Click OK to save the details.
Repeat Step 12 for the Interface SDE_ORA_OrganizationDimension_Customer.ORG_DS_FULL.
Regenerate scenario SDE_ORA_OrganizationDimension_Customer (that is, right click on the scenario and select Regenerate).
Now that you have set up the E-LT process for extracting and staging the data, you need to load the new data into the data warehouse (for more information, see Section 11.2.3.3, "Example of Loading Data from the Staging Area into an Existing Target Table"),
This section shows how data is loaded from the staging area into an existing target table.
The diagram below shows the new E-LT mappings that you need to load the new data from the staging area into the target table, and the ODI Interfaces that you need to modify.
Note: The diagram above only shows the incremental interfaces.
To customize the E-LT process to load these two fields into the staging area, you need to:
Load the X_ACCOUNT_LOG value and X_LAST_LOGIN value from the staging table W_ORG_DS into the temporary table SQ_W_ORG_DS using the Interfaces Sq_W_ORG_DS and Sq_W_ORG_DS_FULL.
Load the X_ACCOUNT_LOG value and X_LAST_LOGIN value from the temporary table ODI_Sq_W_ORG_DS into the Target table W_ORG_D using the Interfaces ORG_D, ORG_D_FULL, and ORG_D_UNSPC.
To extract data from an Oracle EBS 11.5.10 Data Packaged Source:
In ODI Designer, display the Projects view, expand the 'Oracle BI Applications 7.9.5.2'\Mappings\SILOS folder.
Right-click on the SIL_OrganizationDimension folder, and choose Version, then Create to display the Create: <Object> dialog, and specify a unique version number and optional version description.
Display the Models view, expand the Dimension Stage folder, and edit the W_ORG_DS data store to display the DataStore: <Name> dialog, and display the Columns tab.
Make sure that the following columns are setup:
X_ACCOUNT_LOG (VARCHAR2(10))
X_LAST_LOGIN (VARCHAR2(10))
Repeat steps 3 and 4 for the W_ORG_D data store.
In the Models view, right click on the model 'Oracle BI Applications 7.9.5.2' and select Generate DDL to display the Generate DDL dialog.
The Generate DDL option deploys the changes in the database.
Select the check-box in the Synchronize column next for the W_ORG_DS table.
Click the (...) button to the right of the Generation Folder field to display the Select a folder dialog, and select the \Utilities\System folder, and click OK.
When the Procedure: DDL <Name> dialog is displayed, click Execute.
Display ODI Operator and make sure that the procedure executes successfully.
Display the Projects view, expand the Mappings folder, and expand the SIL_OrganizationDimension folder.
Edit the Interface Sq_W_ORG_DS to display the Interface: <Name> dialog, and do the following:
Display the Diagram tab, and select the X_ACCOUNT_LOG field in the Target Datastore area.
Use the Launch Expression Editor icon to display the Expression Editor dialog, and use this dialog to select W_ORG_DS.X_ACCOUNT_LOG as the value in the Implementation field.
Select the X_LAST_LOGIN field in the Target Datastore area
Use the Launch Expression Editor icon to display the Expression Editor dialog, and use this dialog to select W_ORG_DS.X_LAST_LOGIN as the value in the Implementation field.
Click OK to save the details.
Repeat Step 11 for the Interface Sq_W_ORG_DS_FULL.
Edit the Interface ORG_D to display the Interface: <Name> dialog, and do the following:
Display the Diagram tab, and select the X_ACCOUNT_LOG field in the Target Datastore area.
Use the Launch Expression Editor icon to display the Expression Editor dialog, and use this dialog to select Sq_W_ORG_DS.X_ACCOUNT_LOG as the value in the Implementation field.
Select the X_LAST_LOGIN field in the Target Datastore area
Use the Launch Expression Editor icon to display the Expression Editor dialog, and use this dialog to select Sq_W_ORG_DS.X_LAST_LOGIN as the value in the Implementation field.
Click OK to save the details.
Repeat Step 13 for the Interface ORG_D_FULL and ORG_D_UNSPC.
Regenerate scenario SILOS\SIL_OrganizationDimension (that is, right click on the scenario and select Regenerate).
A new source table should always be defined on right side of a LEFT OUTER join syntax with existing source tables. Using an INNER join or a RIGHT OUTER join can result in loss of records.
Make sure that you define joins to match on a unique set of values. If you do not define a join that ensures a unique relationship, you might get a Cartesian product, which changes the granularity and results in duplicate errors. If it is not possible to define a unique join, then do the following:
Create an inline view interface sourcing from the new table, outputting necessary columns plus a column LKP_ACTIVE.
For example, you might specify the expression for LKP_ACTIVE as:
IS_FIRST( ARG_GROUP(columns to be partitioned by), ARG_GROUP(columns to be ordered by))
Note: In the above example, the IS_FIRST command and the matching filter are only needed if multiple records might be returned.
Bring the inline view interface into an existing interface with a filter LKP_ACTIVE=1, which guarantees that at most one record will be returned.
As a best practice, you should comment custom code that you introduce. Comments should include the developer's name and the date that the code was added.
If you are bringing in data from a new table that was not previously included in an existing SDE package, you might need to create an auxiliary change capture mapping. When a row changes in the new table, the auxiliary change capture mapping marks the corresponding row in the main table as changed. Auxiliary change capture processes can degrade ELT performance. Therefore, auxiliary change capture processes should only be implemented if required.
This section contains the following topics:
Section 11.2.4.2, "How to Add A Dimension to an Existing Fact"
Section 11.2.4.3, "How to Add a Date Dimension to an Existing Fact"
If a dimension is enabled to capture Type-II Change, you can modify the criteria that trigger a Type-II change in a dimension. Most changes in a dimension are treated as Type-I changes in that the existing column is simply overwritten with the new value. Once enabled, there are only a small number of columns that will trigger a Type-II change. You can extend the logic that triggers a Type-II change by adding additional columns to the logic that tracks Type-II changes. In addition, you can remove columns from this logic in case you do not want these types of changes to trigger a Type-II change. The Logic that tracks Type-II changes is contained in the data model.
Note: Modifying the Type-II tracking logic is the only change that you should make to shipped logic.
To modify a Category 2 SCD Trigger:
In ODI Designer, display the Models view, and expand the 'Oracle BI Applications 7.9.5.2' folder.
Expand the Dimension node.
Select the Dimension and column on which you want to implement the Category 2 trigger.
For example, you might select the W_SUPPLIER_D dimension and the CONTACT_CODE column.
Double click the column name to display the Column: <Name> dialog.
Display the Description tab.
Use the Slowly Changing Dimensions Behavior drop down list to specify the behavior that you want, as follows:
If you want to trigger a Category 2 change, select 'Add Row on Change'.
If you want to trigger a Category 1 change, select 'Overwrite on Change'.
Re-generate the Scenario for the SIL Dimension mapping to reflect the data model change.
This is, right-click the SIL Dimension, then select Regenerate.
For more information about customizing Slowly Changing Dimensions, see Section 11.4.4, "Configuring Slowly Changing Dimensions".
This section explains how to add a dimension (pre-existing or custom) to an existing fact. It assumes that you have already built the required process to populate this dimension.
In ODI Designer, define the staging table column as a varchar2(80) field and named with in _ID suffix.
Define the Oracle Business Analytics Warehouse table column as an integer and named with a _WID suffix.
Modify the SDE fact package to pass through the unique identifier of the dimension key.
There must be a relationship between the base table and this unique identifier. It might already be stored in the base table or stored by joining to a related table. This identifier can be based on a single column or derived from multiple columns.
The table below depicts various formats used to derive the INTEGRATION_ID, which is used to identify a dimension key. The INTEGRATION_ID value should be passed to the fact staging table.
Table 11-1 Formats to Derive INTEGRATION_ID
| Dimension | Foreign Key | When Source is Oracle Application |
|---|---|---|
|
W_AP_TERMS_D |
TO_CHAR(TERM_ID) |
|
|
W_BUSN_LOCATION_D |
ASSET_LOC_WID |
ASSET_LOC~' || LOCATION_ID |
|
W_BUSN_LOCATION_D |
EMP_LOC_WID |
EMP_LOC~' || LOCATION_ID |
|
W_BUSN_LOCATION_D |
INVENTORY_LOC_WID |
STORAGE_LOC' || '~' || ORGANIZATION_ID || '~' || SUBINVENTORY_CODE || '~' || INVENTORY_LOCATION_ID |
|
W_BUSN_LOCATION_D |
PLANT_LOC_WID |
'PLANT' || '~' || TO_CHAR(ORGANIZATION_ID) |
|
W_BUSN_LOCATION_D |
RECEIVING_LOC_WID |
'RECIPIENT_LOC' || '~' || TO_CHAR(LOCATION_ID) |
|
W_BUSN_LOCATION_D |
STORAGE_LOC_WID |
'STORAGE_LOC' || '~' || ORGANIZATION_ID || '~' || SECONDARY_INVENTORY_NAME || '~' |
|
W_CUSTOMER_FIN_PROFL_D |
CUSTOMER_FIN_PROFL_WID |
P'||'~'||TO_CHAR(CUSTOMER_ID) ||'~' || TO_CHAR(SITE_USE_ID) ||'~' || CURRENCY_CODE - CUSTOMER_ID is CUST_ACCOUNT_ID from HZ_CUST_ACCOUNTS and CURRENCY_CODE is from HZ_CUST_PROF_CLASS_AMTS |
|
W_CUSTOMER_LOC_D |
To get the customer location key, look up W_CUSTOMER_LOC_USE_D as below |
|
|
W_CUSTOMER_LOC_USE_D |
TO_CHAR(SITE_USE_ID) - Get Site Use Id from HZ_CUST_ACCOUNT_ROLES |
|
|
W_FREIGHT_TERMS_D |
LOOKUP_CODE |
|
|
W_GL_ACCOUNT_D |
to_char(ccid) |
|
|
W_INT_ORG_D |
COMPANY_ORG_KEY |
COMPANY'||'~'||TO_CHAR(SET_OF_BOOKS_ID) |
|
W_INT_ORG_D |
*_ORG_KEY |
Remove any prefixes and use TO_CHAR() |
|
W_ORG_D |
CUSTOMER_WID |
TO_CHAR(CUSTOMER_ID) - CUSTOMER_ID is CUST_ACCOUNT_ID from HZ_CUST_ACCOUNTS |
|
W_PAYMENT_METHOD_D |
LOOKUP_CODE |
|
|
W_PAYMENT_METHOD_D |
TO_CHAR(TERM_ID) |
|
|
W_PERSON_D |
CUST_CONTCT_WID |
TO_CHAR(PARTY_ID) - PARTY_ID from HZ_PARTY_RELATIONS |
|
W_PRODUCT_D |
PRODUCT_WID |
TO_CHAR(INVENTORY_ITEM_ID) |
|
W_SALES_PRODUCT_D |
TO_CHAR(INVENTORY_ITEM_ID) ||'~'||TO_CHAR(ORGANIZATION_ID) |
To resolve the value of the new WID column in the SIL package, do the following:
Create an Inline View Interface X_LKP_W_Dim_D for the dimension table.
In the 'SQ_xxxxx' interfaces, use an outer join to join the Fact table to the Inline View of the dimension table using INTEGRATION_ID, DATASOURCE_NUM_ID.
If the dimension is a slowly changing dimension, the fact table's standard or 'canonical' date should be used as well as the join condition, even if the dimension has not been enabled to capture Category 2 changes.
Use ROW_WID of the dimension table as the WID value of the fact table.
Add logic to default the WID value to 0 if no record is returned from the join.
Save the changes to the SDE and SIL packages.
Make sure that the new dimension extract and load packages are already part of the Master packages, and that it is loaded before the Fact load.
If you want to add a date dimension to a fact table, you pass the date through the SIL Interfaces to the target table.
To add a date dimension to an existing fact:
Add a new field called DT_WID to the fact table, as follows:
In ODI Designer, display the Models view, and open the Oracle BI Applications 7.9.5.2 folder.
Expand the Fact folder, and double-click the fact table to display the DataStore:<Name> dialog.
For example, you might edit the W_GL_REVN_F table.
Click the Add Column icon to add a new column to the list, and change the default column name to DT_WID, and change the Type to DATE.
In the Models view, right click on the model 'Oracle BI Applications 7.9.5.2' and select Generate DDL to display the Generate DDL dialog.
The Generate DDL option deploys the changes in the database.
Select the check-box in the Synchronize column next for the table that you added.
Click the (...) button to the right of the Generation Folder field to display the Select a folder dialog, and select the \Utilities\System folder, and click OK.
When the Procedure: DDL <Name> dialog is displayed, click Execute.
Display ODI Operator and make sure that the procedure executes successfully.
Modify the new DT_WID column in the SIL Interface, as follows:
In ODI Designer, display the Projects view, and open the SIL package.
For example, you added a new date field to the W_GL_REVN_F table, you might want to edit the SIL_GLRevenueFact\SIL_GLRevenueFact Package.
Edit the last Interface located at the end of the flow.
For example, if you edit the SIL_GLRevenueFact\SIL_GLRevenueFact Package, you edit the 'Run GL_REVN_F_FULL' Interface.
Display the Diagram tab.
In the Target Datastore area, select the DT_WID column that you created in step 1.
In the Mapping pane below, add the following expression in the Implementation field:
COALESCE(IIF( ISNULL(Sq_Fact_Table.Newly_added_DT), NULL, TO_INTEGER(TO_CHAR_FORMAT(Sq_Fact_Table.Newly_added_DT,'YYYYMMDD'))),0)
Save the details.
Regenerate the interface.
Category 2 customizations use pre-packaged adapters to add new fact or dimension tables to the Oracle Business Analytics Warehouse.
This section contains the following topics:
Section 11.3.1, "About Creating New Dimension or Fact Tables"
Section 11.3.4, "Adding a New Dimension to the Oracle Business Analytics Warehouse"
Section 11.3.5, "Adding a New Fact Table to the Oracle Business Analytics Warehouse"
This section relates to building entirely new tables that will be loaded with data from a source table that is not already extracted from. For example, you might want to create a new Project dimension table. In this case, you create new dimension and staging tables as well as new extract and load ELT mappings.
When creating a new custom table, use the prefix WC_ to help distinguish custom tables from tables provided by Oracle as well as to avoid naming conflicts in case Oracle later releases a table with a similar name. For example, for your Project dimension you might create a WC_PROJECT_DS and a WC_PROJECT_D table.
When you create a new dimension or fact table, use the required system columns that are part of each of the Oracle Business Analytics Warehouse tables to maintain consistency and enable you to reference existing table structures. When you create a new table, you need to define the table and indices in ODI Designer Models area first. The destination model for the Oracle Business Analytics Warehouse is 'Oracle BI Applications 7.9.5.2'.
For custom staging tables, the following columns are required:
INTEGRATION_ID. Stores the primary key or the unique identifier of a record as in the source table.
DATASOURCE_NUM_ID. Stores the data source from which the data is extracted.
For dimension and fact tables, the required columns are the INTEGRATION_ID and DATASOURCE_NUM_ID columns as well as the following:
ROW_WID. A sequence number generated during the ELT process, which is used as a unique identifier for the Oracle Business Analytics Warehouse.
ELT_PROC_WID. Stores the ID of the ELT process information. The details of the ELT process are stored in the W_ELT_RUN_S table on the Oracle Business Analytics Warehouse side.
The tables in the Oracle Business Analytics Warehouse schema have DATASOURCE_NUM_ID as part of their unique user key. While the transactional application normally ensures that a primary key is unique, it is possible that a primary key is duplicated between transactional systems. To avoid problems when loading this data into the data warehouse, uniqueness is ensured by including the DATASOURCE_NUM_ID as part of the user key. This means that the rows can be loaded in the same data warehouse tables from different sources if this column is given a different value for each data source. The DATASOURCE_NUM_ID is maintained by a Flex Field value that you can specify using ODI Topology Manager. The out-of-the-box Flex Field value is '1', but you change this to '4' (for Oracle EBS 11.5.10 applications) as part of the installation and setup process (for more information, see Section 4.5.5.3, "How to set up the Data Source Number").
Creating a custom ODI Master Package enables you to create a custom Subject Area.
Note: If you customize an existing package or interface, then you do not need to modify the master packages since the modified package is already being executed.
If you create a new customized package, you need to include the customized package in the master packages to be executed during a regular load. To include a package in the master package, it should be called in either a level 3 subject area package or a level 4 task group package. To create one of these level 3 or level 4 packages, you can use of the package templates:
3_Master_PLP_<App>_<Subj>
3_Master_SDE_Facts_<App>_<Subj>
3_Master_SIL_Facts_<App>_<Subj>
4_Master_PLP_TG_<Table>
4_Master_SDE_Dimensions_TG_<Dim>
4_Master_SIL_Dimensions_TG_<Dim>
4_Master_SDE_Facts_TG_<Fact>
4_Master_SIL_Facts_TG_<Fact>
You need to make a copy of the appropriate template that you wish to use for your customized package. If your customized package is for a dimension table and is for an existing category, you just need to append it to their respective level 3 SDE/SIL dimension category package.
If your customized package is for a dimension table and needs a new category, create 3_Master_SDE_Dimensions_Custom and 3_Master_SIL_Dimensions_Custom. There is no template package in the master package folder for these packages. This is because there are no other steps in these packages aside from the package that you need to execute. Use the 'Insert package' option in ODI for creating these packages. Put the step before the 'wait for child session' step in these packages.
If the customized package is a new PLP dimensions package, you need to append this in 3_Master_PLP_Dimensions package. There is no need to create a new level 3 PLP dimension master package.
If your customized package is for a Fact or PLP table and is for an existing subject area, you just need to append this package to their appropriate level 3 packages.
If your customized package is for a Fact or PLP table and is for a new subject area, use the other appropriate level 3 templates. There are steps in this template for refresh and evaluate of the variable OBI_EXEC_PACKAGE. You need to append the steps you need to execute after the evaluate variable step. Use the 'ok' connection from the 'Execute Package?' step to your customized step. Change <App> with CUSTOM and <Subj> with your own customized subject area name. For new subject areas, you need to use Oracle BI Applications Configuration Manager to add the subject area and enable it for execution in its appropriate subject area. Before you can use Oracle BI Applications Configuration Manager to add a new subject area, use the 'Add Custom Subject Area' and 'Refresh Package Module Data' packages located in the Projects folder in ODI Designer (in \Utilities_and_Execution\Utilities\User\Packages\).
After creating the level 3 package, you need to generate the scenario for this package. Add a scenario execution step in their appropriate level 2 master package, as follows.
After creating the level 3 package, you need to generate the scenario for this package. Create the following level 2 master packages to execute these customized packages: 2_Master_PLP_CUSTOM, 2_Master_SDE_Facts_CUSTOM, and 2_Master_SIL_Facts_CUSTOM.
Add a scenario execution step in their appropriate customized level 2 master packages. Set the agent code in these steps to "WORKFLOW". Set the appropriate "Synchronous / Asynchronous" setting for the steps. Add an ÒOdiWaitForChildSessionÓ step if necessary. Generate the scenarios for these customized level 2 packages, add a scenario step to: 1_Master_PLP, 1_Master_SDE_Facts, and 1_Master_SIL_Facts.
For level 4 task group packages, this is required if there are multiple customized package loading the same target table or you are using temporary table.
If your customized package is for an existing task group, just append it to their appropriate level 4 task group package.
If your customized package is for a dimension table and require a new task group package, use either 4_Master_SDE_Dimensions_TG_<Dim> or 4_Master_SIL_Dimensions_TG_<Dim> template.
In this template, there are steps for refresh and evaluate of OBI_START_TASK_GROUP, a refresh of IS_INCREMENTAL variable and an Update Load Date procedure call. These steps are mandatory for this type of task group. The other steps are Drop Indexes, Truncate Table, and Create Indexes. These steps are optional if you want such process to be handled in the task group level. The parameters for these steps should be set as follows:
For drop index step, set the value in additional variable tab for OBI_MANAGE_TABLE_MASK and OBI_INDEX_DROP_ELT.
For truncate table step, set the value in additional variable tab for OBI_MANAGE_TABLE_MASK, OBI_TRUNCATE_TABLE, and OBI_TRUNCATE_TASK_GROUP.
For create index step, set the value in additional variable tab for OBI_MANAGE_TABLE_MASK and OBI_INDEX_CREATE_ELT.
Your customized package should be added after the truncate table step and before create indexes step. Use an OdiWaitForChildSession step if necessary for asynchronous jobs.
If your customized package is for a Fact or PLP table and require a new task group package, then use 4_Master_SDE_Facts_TG_<Fact>, 4_Master_SIL_Facts_<Fact>, or 4_Master_PLP_TG_<Table> template.
The steps in these templates are similar to those in the dimensions task group. The only difference is it has steps for refresh and evaluate of OBI_START_PACKAGE instead of OBI_START_TASK_GROUP variable. Set the other steps the same way as the dimensions task group. These customized level 4 task group packages should be added to their appropriate level 3 master packages.
Note: For all packages modified, you need to regenerate the scenario.
This section contains additional miscellaneous information about customization in Oracle Business Intelligence Applications.
When you import table definitions from external data sources to an ODI work repository, make sure that the 'Technology' is set to Oracle.
To import table definitions into an ODI Work Repository:
In ODI Designer, display the Models view, and double-click the folder that holds the table definitions (for example, 'Oracle eBusiness Suite 11.5.10') to display the Model: <Name> dialog.
Display the Selective Reverse tab.
Select the Objects To Reverse check box.
Select the objects (Table/View/Synonym) that you want to import into the ODI Repository.
Click OK to reverse-engineer the objects.
For loading new fact and dimension tables, design a custom process on the source side to detect the new and modified records. The SDE process should be designed to load only the changed data (new and modified). If the data is loaded without the incremental process, the data that was previously loaded will be erroneously updated again. For example, the logic in the preconfigured SIL mappings looks up the destination tables based on the INTEGRATION_ID and DATASOURCE_NUM_ID and returns the ROW_WID if the combination exists, in which case it updates the record. If the lookup returns null, it inserts the record instead. In some cases, last update date(s) stored in target tables are also compared in addition to the columns specified above to determine insert or update. Look at the similar mappings in the preconfigured folder for more details.
Truncation of Tables should be done using the 'Truncate All Tables' package, which is located in the Projects\Oracle BI Applications 7.9.5.2\Utilities_and_Execution\Utilities\User\Packages folder. This utility truncates all the tables in the data warehouse prior to a forced full load.
Use the ETL_PROC_WID setting in the W_PARAM_G table in custom mappings. ETL_PROC_WID is a reference key to the Run History of the ODI Master Package.
Staging tables typically do not require any indices. Use care to determine if indices are required on staging tables. Create indices on all the columns that the E-LT will use for dimensions and facts (for example, ROW_WIDs of Dimensions and Facts, INTEGRATION_ID and DATASOURCE_NUM_ID and flags). Carefully consider which columns or combination of columns filter conditions should exist, and define indices to improve query performance. Inspect the OTB objects for guidance. Name all the newly created tables as WC_. This helps visually isolate the new tables from the out-of-the-box tables. Keep good documentation of the customizations done; this helps when upgrading your data warehouse. Once the indices are decided upon, they should be registered in the ODI Model (for more information, see Section 11.5.3, "How to add an index to an existing fact or dimension table").
Follow this procedure to add a new dimension table to the Oracle Business Analytics Warehouse.
To add a new dimension and use it with an existing fact table:
In ODI Designer, log in as SUPERVISOR, and display the Models view.
In the Oracle BI Applications 7.9.5.2 folder, create a new dimension table structure (with appropriate system columns),
Use the naming convention W_<Dimension Name>_D.
In the Oracle BI Applications 7.9.5.2 folder, create a new staging table structure (with appropriate system columns), using the naming convention W_<Dimensions Name>_DS.
In the definition of the fact tables related to this new dimension, insert a reference constraint to this dimension table.
The dimension table must be the parent table in the reference constraint definition.
Display the Projects view, and select the Oracle BI Applications 7.9.5.2 folder.
Create a new interface called SDE_<XYZ>.<Interface name>(_Full) to populate the dimension stage.
Create a custom inline view interface if necessary. This inline view interface will be the source of the main interface for loading the dimension stage. Assign the appropriate knowledge module for these interfaces. Set the appropriate value for the options in the assigned knowledge module. If required, create separate interfaces for full load and incremental load. Refer to existing interfaces as examples.
Create a new package called SDE_<Package_name> to contain the interfaces for loading the dimension stage.
Create the steps for refresh and evaluate of variables OBI_START_PACKAGE and IS_INCREMENTAL (if full and incremental is necessary). The 'Refresh OBI_START_PACKAGE' step is always the first step. Add other variables or objects as required by the package. Invoke the interfaces created in the previous step in this package. A package can have two branches, for full or incremental load. Refer to existing SDE packages as a base sample.
Create new custom interfaces and packages for the SILO jobs for loading the dimension table from dimension stage.
To set truncate options for the interfaces, assign a value to the KM option OBI_TRUNCATE_TABLE. A value of F is for truncate on full load, while a value of Y is for truncate always. For SDE it is usually truncate always, while for SIL it is truncate on full load.
Insert the new packages into the master package.
Follow this procedure to add a new fact table to the Oracle Business Analytics Warehouse.
To add a new fact table:
In ODI Designer, log in as SUPERVISOR, and display the Models view.
In the Oracle BI Applications 7.9.5.2 folder, create a new fact table structure (with appropriate system columns), using the naming convention W_<Dimension Name>_F.
In the constraints tree of this new fact table, insert a reference constraint to all dimension tables related to this fact. The dimension table has to be the parent table in the reference constraint definition. In the flexfields tab of this new fact table, change the value of the OBI_MODULE flexfield to the appropriate module of this new fact table such as OM,SCA,HR, FIN or CUSTOM. In addition, set the value of the OBI_SUBJECT_AREA flexfield to your customized subject area name.
In the Oracle BI Applications 7.9.5.2 folder, create a new fact staging table structure (with appropriate system columns), using the naming convention W_<Dimensions Name>_FS.
Display the Projects view, and select the Oracle BI Applications 7.9.5.2 folder.
Create a new interface called SDE_<XYZ>.<Interface name>(_Full) to populate the fact stage.
Create a custom inline view interface if necessary. This inline view interface will be the source of the main interface for loading the dimension stage. Assign the appropriate knowledge module for these interfaces. Set the appropriate value for the options in the assigned knowledge module. If required, create separate interfaces for full load and incremental load. Refer to existing interfaces as examples.
Create a new package called SDE_<Package_name> to contain the interfaces for loading the fact stage.
Create the steps for refresh and evaluate of variables OBI_START_PACKAGE and IS_INCREMENTAL (if full and incremental is necessary). The 'Refresh OBI_START_PACKAGE' step is always the first step. Add other variables or objects as required by the package. Invoke the interfaces created in the previous step in this package. A package can have two branches, for full or incremental load. Refer to existing SDE packages as a base sample.
Create new custom interfaces and packages for the SILO jobs for loading the fact table from the fact stage.
To set truncate options for the interfaces, assign a value to the KM option OBI_TRUNCATE_TABLE. A value of F is for truncate on full load, while a value of Y is for truncate always. For SDE it is usually truncate always, while for SIL it is truncate on full load.
Insert the new packages into the master package.
The steps for creating a new dimension table are similar to the steps for incremental change capture.
To add a new dimension table for a new fact table:
In the new custom fact loading interface, drag and drop the new dimension table into the source pane of the interface.
Create a join between the dimension table and the fact staging table.
Extract the ROW_WID column from the dimension table and assign it to the corresponding column in the target fact table.
Category 3 customizations use the Universal adapter to load data from sources that do not have pre-packaged adapters.
This section contains the following topics:
Follow this procedure to add new data as a whole row into a standard dimension table in the Oracle Business Analytics Warehouse.
To add new data as a whole row into the standard dimension table:
Identify and understand the existing structure of staging tables. Refer to Oracle Business Analytics Warehouse Data Model Reference for the table structures. Non-system columns can include the null value.
Create a custom SDE interface to load the data into the staging table in the custom folder for this purpose. The staging table needs to be populated with incremental data (rows that have been added or changed since the last Refresh ELT process), for performance reasons.
Populate the INTEGRATION_ID column with the unique identifier for the record.
The combination of INTEGRATION_ID and DATASOURCE_NUM_ID is unique. When importing the data, make sure that a unique identifier for the external data source is inserted in the DATASOURCE_NUM_ID column. Populate the INTEGRATION_ID column with the unique identifier for the record. The combination of INTEGRATION_ID and DATASOURCE_NUM_ID is unique. When importing the data, make sure that a unique identifier for the external data source is inserted in the DATASOURCE_NUM_ID column. The DATASOURCE_NUM_ID should be set to 4 for interfaces that source data from the Oracle Applications 11.5.10. This is a reserved value and is used in all standard interfaces.
For example, a value of 2 can be defined for DATASOURCE_NUM_ID in the custom SDE interfaces. The standard SDE interfaces populate the INTEGRATION_ID column of the dimension staging table (used for resolving the dimension's Oracle Applications 11.5.10 ROW_ID value). The custom process must be used to populate the same column with a unique identifier from the external data source.
After the data is populated in the staging table, use the standard SIL interfaces to populate the dimension target tables.
Modify the SDE and SIL interfaces of all the related fact tables (fact tables that need to be linked to this dimension).
The custom fact SDE interfaces must populate the foreign key column of the changed dimension (using a custom map table process to convert from Oracle EBS 11.5.10 ROW IDs to the external data source row IDs). The custom SIL interface should be modified to use the appropriate DATASOURCE_NUM_ID, because the standard SIL interfaces assume that the DATASOURCE_NUM_ID for the dimensions are the same as the fact table's DATASOURCE_NUM_ID.
It is important to decide when the data is going to be loaded. If it is going to be loaded along with the Oracle source data, make sure that failure recovery is configured correctly. The preconfigured Master Packages truncate the target staging table prior to loading. Upon failure, when the Master Package restarts the task, the data is truncated and all the data is loaded again.
If the data from the external source gets loaded into the same staging table, be careful with how you handle this situation, since you cannot use the truncate table functionality. The data migrating into the staging table is not incrementally loaded, and, therefore, should be cleaned up prior to attempting to load this table again. In such a case, it is recommended that you encapsulate the extract part from both the sources inside an Master Package. Note that the data from both the sources should be run at the same time, all the time.
If it is decided that the data is going to be loaded at different time frequencies, then the new SDE packages need not depend on the preconfigured SDE packages and can use the Truncate Table option for failure recovery. Make sure the shared SIL process depends on the SDE processes from both sources.
Each application has prepackaged logic to extract particular data from a particular source. This section discusses how to capture all data relevant to your reports and ad hoc queries by addressing what type of records you want and do not want to load into the Oracle Business Analytics Warehouse, and contains the following topics:
You can configure extract mappings and Interfaces in the Oracle Business Analytics Warehouse to accommodate additional source data. For example, if your business divides customer information into separate tables based on region, then you would have to set up the extract interface to include data from these tables.
Extract interfaces generally consist of source tables, expressions used in the target columns, and a staging table. If you want to extract new data using the existing interface, you have to modify the extract interface to include the new data by performing the following tasks:
To modify an existing interface to include new data:
Modify the existing interface to extract information from the source, and add it to an appropriate extension column.
Modify the Expressions in the target table to perform any necessary transformations.
Save the changes.
Regenerate the scenario.
You have to determine which type of extension column to map the data to in the staging table. After you modified the extract interface, you would also have to modify the corresponding load interfaces (SDE and SIL) to make sure that the extension columns that you added are connected all the way from the staging table to the target data warehouse table.
Extract interfaces (which have the SQ_* naming convention) reside in source-specific folders within the repository. Extract interfaces are used to extract data from the source system. You can configure these extract interfaces to perform the following:
Extract data from a new source table.
Set incremental extraction logic.
When you load data from a Comma Separated Values (CSV) formatted source file, if the data contains a comma character (,), you must enclose the source data with a suitable enclosing character known as a delimiter that does not exist in the source data.
Note: Alternatively, you could configure your data extraction program to enclose the data with a suitable enclosing character automatically.
For example, you might have a CSV source data file with the following data:
Months, Status January, February, March, Active April, May, June, Active
If you loaded this data without modification, ODI would load 'January' as the Months value, and 'February' as the Status value. The remaining data for the first record (that is, March, Active) would not be loaded.
To enable ODI to load this data correctly, you might enclose the data in the Months field within the double-quotation mark enclosing character (" ") as follows:
Months, Status "January, February, March", Active "April, May, June", Active
After modification, ODI would load the data correctly. In this example, for the first record ODI would load 'January, February, March' as the Months value, and 'Active' as the Status value.
To set up the delimiter for a source file:
Open the CSV file containing the source data.
Enclose the data fields with the enclosing character that you have chosen (for example, (").
You must choose an enclosing character that is not present in the source data. Common enclosing characters include single quotation marks (') and double quotation marks (").
Save and close the CSV file.
In ODI Designer, display the Models view, and expand the 'Oracle BI Applications 7.9.5.2' folder.
Identify the data stores that are associated with the modified CSV files. The CSV file that you modified might be associated with one or more data stores.
In ODI Designer, change the properties for each of these data stores to use the enclosing character, as follows:
Double-click the data source, to display the DataStore: <Name> dialog.
Display the Files tab.
Use the Text Delimiter field to specify the enclosing character that you used in step 2 to enclose the data.
Click OK to save the changes.
You can now load data from the modified CSV file.
This section explains how to customize the way that Oracle Business Intelligence Applications loads data into the Oracle Business Analytics Warehouse. For example, you might want to delete records from the Oracle Business Analytics Warehouse that have been deleted in the source system.
In a typical implementation, records that are deleted from your source system are not removed from the Oracle Business Analytics Warehouse. If you want to mark these records as deleted in the Oracle Business Analytics Warehouse, which were removed from the source system's database and archived in a separate database, you must enable the primary extract and delete mappings.
Primary extract mappings flag records that are deleted from the Oracle Business Analytics Warehouse. Delete mappings sets the DELETE_FLG column to 'Y' for these records in the warehouse tables. When enabled, primary extract and delete mappings by default look for any records removed from the source system's database. If these mappings find that the records no longer exist in that database, the mappings mark them as deleted in the data ware
Before you decide to enable primary extract and delete sessions, it is important to understand their function within the Oracle Business Analytics Warehouse. Primary extract and delete mappings allow your analytics system to determine which records are removed from the source system by comparing primary extract staging tables with the most current Oracle Business Analytics Warehouse table.
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.
Figure 11-7 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 mapplet, 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 Figure 11-7. When the extract and load mappings run, the new sales order is added to the warehouse.
The primary extract (*_Primary) and delete mappings (*_IdentifyDelete and *_Softdelete) serve a critical role in identifying which records have been physically deleted from the source system. However, there are some instances when you can disable or remove the primary extract and delete mappings, such as when you want to retain records in the Oracle Business Analytics Warehouse that were removed from the source systems' database and archived in a separate database.
Because delete mappings use Source IDs and Key IDs to identify purged data, if you are using multiple source systems, you must modify the SQL Query statement to verify that the proper Source ID is used in the delete mapping. In addition to the primary extract and delete mappings, the configuration of the delete flag in the load mapping also determines how record deletion is handled.
You can manage the extraction and deletion of data in the following ways:
Deleting the configuration for source-archived records
Deleting records from a particular source
Enabling delete and primary-extract sessions
Configuring the Record Deletion flag
Configuring the Record Reject flag
Some sources archive records in separate databases and retain only the current information in the main database. If you have enabled the delete mappings, you must reconfigure the delete mappings in the Oracle Business Analytics Warehouse to retain the archived data.
To retain source-archived records in the Oracle Business Analytics Warehouse, make sure the LAST_ARCHIVE_DATE parameter value is set properly to reflect your archive date. The delete mappings will not mark the archived records as 'deleted'. For more information about extract and delete mappings, see Section 11.4.3.3, "About Working with Primary Extract and Delete Mappings".
If you want to mark your source-deleted records as deleted in the Oracle Business Analytics Warehouse, you need to enable the delete and primary extract tasks for your application.
To enable delete and primary extract sessions:
In ODI Designer, display the Projects view, and expand the 'Oracle BI Applications 7.9.5.2' folder.
Expand the \Master_Packages\Tools\Delete_Master_Packages folder.
Double-click the package 0_Master_Delete to display the Scenario: <Name> dialog.
Use the scheduling settings to schedule the package.
The jobs for marking the deleted records are already part of the master packages. These jobs will mark the deleted records identified in by the execution of the 0_Master_Delete package.
The Oracle Business Analytics Warehouse provides Type II slowly changing dimension (SCD) functionality, which allows you to track the history of updates to dimension records. When a record in the Oracle Business Analytics Warehouse has an update, the updated information is posted into a new row and the old information is kept for historical reporting purposes.
The Oracle Business Analytics Warehouse identifies and applies the slowly changing dimension logic after data has been extracted and transformed to be source-independent. You can configure Oracle Business Intelligence Applications to support both Type I SCDs, in which data is overwritten with updates, and Type II SCDs, in which the original records are maintained while a new record stores the updated data. Whether you choose to apply Type I or Type II SCD functionality to a column depends whether the column contains historically significant attributes.
The table below shows the dimensions that are required using the SCD Type II update.
Table 11-2 Dimensions that are required using the SCD Type II update
| Dimension | Adaptor |
|---|---|
|
W_EMPLOYEE_D |
Oracle E-Business |
|
W_INVENTORY_PRODUCT_D |
Oracle E-Business |
|
W_POSITION_D |
Oracle E-Business |
|
W_PRODUCT_D |
Oracle E-Business |
By default, all dimensions are using Type I updates.
This behavior of TYPE I or TYPE II is managed by a SIL package level Parameter for each dimension called 'TYPE2_FLG'. By default, the value of the parameter is set to 'N' out-of-the-box (except for the above mentioned tables). To turn a dimension to Type II SCD, update enabled set the value of the parameter for the particular dimension - 'TYPE2_FLG' to 'Y'.
You might want to retain a history of all the updates to a particular dimension so that you can use them in reports. These dimensions are known as historically significant attributes.
For example, if a customer moves to a different region and you assign that customer a new regional salesperson and territory ID, you might want to keep records of that customer's account history with the original salesperson and territory ID. In this case, the salesperson and territory IDs are historically significant attributes.
In contrast, you might have a load that populates the telephone number field. If your business does not perform data analysis on phone number history, then this information might be considered a historically insignificant attribute.
Identifying attributes as significant or insignificant allows you to determine the type of SCD you require. However, before you can select the appropriate type of SCD, you must understand their differences.
The extract view of any given table in the Staging Area consists of four types of records:
New records
Changed records with data that is historically insignificant
Changed records having historical significance
Changed records whose changes have no significance of any kind and are ignored altogether
Of the four kinds of records, only the first three are of interest for the data mart. Of those three, brand new records and records whose changes are tracked as SCDs are both treated as new and become inserts into the Oracle Business Analytics Warehouse. Records with changes that are important but not historically tracked are overwritten in the Oracle Business Analytics Warehouse, based on the primary key.
After you have correctly identified your significant and insignificant attributes, you can configure the Oracle Business Analytics Warehouse based on the type of slowly changing dimension (SCD) that best fits your needs—Type I or Type II.
A Type I SCD overwrites the column's value and is the default SCD for the Oracle Business Analytics Warehouse. Although a Type I does not maintain history, it is the simplest and fastest way to load dimension data. Type I is used when the old value of the changed dimension is not deemed important for tracking or is an historically insignificant attribute. For example, you might want to use Type I when changing incorrect values in a column.
In the figure below, the State Name column for the supplier KMT is changed in the source table Suppliers, because it was incorrectly entered as California. When the data is loaded into the Oracle Business Analytics Warehouse table, no historical data is retained and the value is overwritten. If you look up supplier values for California, records for KMT do not appear; they only appear for Michigan, as they have from the beginning.
A Type II SCD creates another record and leaves the old record intact. Type II is the most common SCD because it allows you to track historically significant attributes. The old records point to all history prior to the latest change, and the new record maintains the most current information.
Slowly changing dimensions work in different parts of a star schema (the fact table and the dimension table). The figure below shows how an extract table (SOURCE_CUSTOMERS) becomes a Oracle Business Analytics Warehouse dimension table (W_ORG_D). Although there are other attributes that are tracked, such as Customer Contact, in this example there is only one historically tracked attribute, Sales Territory. This attribute is of historical importance because businesses frequently compare territory statistics to determine performance and compensation. Then, if a customer changes region, the sales activity is recorded with the region that earned it.
This example deals specifically with a single day's extract, which brings in a new record for each customer. The extracted data from SOURCE_CUSTOMERS is loaded into the target table W_ORG_D, and each record is assigned a unique primary key (ROW_WID).
However, this data is not static; the next time a data extract shows a change for your customers in W_ORG_D, the records must change. This situation occurs when slowly changing dimensions are invoked. The figure below shows that records for the two customers, ABC Co., and XYZ inc. have changed when compared with the figure below. Notice that ABC's Customer Contact has changed from Mary to Jane, and XYZ's Sales Territory has changed from West to North.
As discussed earlier in this example, the Customer Contact column is historically insignificant; therefore a Type I SCD is applied and Mary is overwritten with Jane. Because the change in ABC's record was a Type I SCD, there was no reason to create a new customer record. In contrast, the change in XYZ's record shows a change of sales territory, an attribute that is historically significant. In this example, the Type II slowly changing dimension is required.
As shown in the figure below, instead of overwriting the Sales Territory column in the XYZ's record, a new record is added, assigning a new ROW_WID, 172, to XYZ in W_ORG_D. XYZ's original record, 102, remains and is linked to all the sales that occurred when XYZ was located in the West sales territory. However, new sales records coming in are now attributed to ROW_WID 172 in the North sales territory.
Effective dates specify when a record was effective. For example, if you load a new customer's address on January 10, 2003 and that customer moves locations on January 20, 2003, the address is only effective between these dates. Effective Dates are handled in the following manner:
If the source supplies both effective dates, these dates are used in the warehouse table.
If the source does not supply both the effective to and effective from dates, then the Type II logic creates effective dates.
If the source supplies one of the two effective dates, then you can set up the Oracle Business Analytics Warehouse to populate the missing effective dates using a wrapper mapping. This situation is discussed in this section. By default, these wrapper sessions are disabled and need to be enabled in order to be executed.
For example, in the W_ORG_D table previously discussed, XYZ moved to a new sales territory.
If your source system supplied historical data on the location changes, your table might contain a record for XYZ in the West sales territory with an effective from date of January 1, 2001 and an effective to date of January 1, 3714. If the next year your source indicates XYZ has moved to the North sales territory, then a second record is inserted with an effective from date of January 1, 2002, and an effective to date of January 1, 3714.
This section contains miscellaneous information that applies to all three categories of customization in Oracle Business Intelligence Applications, and contains the following topics:
This section explains codes lookup and dimension keys.
Some source systems use intelligent codes that are intuitively descriptive, such as HD for hard disks, while other systems use non-intuitive codes (like numbers, or other vague descriptors), such as 16 for hard disks. While codes are an important tool with which to analyze information, the variety of codes and code descriptions used poses a problem when performing an analysis across source systems. The lack of uniformity in source system codes must be resolved to integrate data for the Oracle Business Analytics Warehouse.
The code lookup in the load mapping integrates both intelligent and non-intuitive codes by performing a separate extract for codes, and inserting the codes and their description into a codes table. The codes table provides the load mapping with a resource from which it can automatically perform a lookup for code descriptions.
The following components and process are used in a lookup:
The load control table W_CODE_D consolidates all codes for future reference and assigns them a category and a single language for efficient lookup capability.
The Oracle Business Analytics Warehouse uses mappings designed to extract codes from source systems and populate the W_CODE_D table in preparation for use by the load mapping.
To understand how codes mappings function, it is helpful to first understand the columns within W_CODE_D, (see table below).
Table 11-3 Columns in Code Mapplet
| Column | Description |
|---|---|
|
DATASOURCE_NUM_ID |
Unique identifier of the source system from which data was extracted |
|
SOURCE_CODE1 |
The first code in the hierarchy of the various source system codes used to identify a particular code and description combinations |
|
SOURCE_CODE2 |
The second code in the hierarchy of the various source system codes used to identify a particular code and description combinations |
|
SOURCE_CODE3 |
The third code in the hierarchy of the various source system codes used to identify a particular code and description combinations |
|
SOURCE_DESC_1 |
Short description of the source system code |
|
SOURCE_DESC_2 |
Long description for code |
The naming convention for mappings designed for codes lookup is SDE_[SOURCE]_CodeDimension_[CATEGORY]. The figure below shows an example of a code package in ODI Designer.
By default, dimension key resolution is performed by the Oracle Business Analytics Warehouse in the load mapping. The load interface uses prepackaged, reusable lookup transformations to provide pre-packaged dimension key resolution. This section describes how dimension keys are looked up and resolved.
There are two commonly used methods for resolving dimension keys. The first method, which is the primary method used, is to perform a lookup for the dimension key. The second method is to supply the dimension key directly into the fact load mapping.
If the dimension key is not provided to the Load Interface through database joins, the load mapping performs the lookup in the dimension table. The load mapping does this using prepackaged Lookup Interfaces. To look up a dimension key, the Load Interface uses the INTEGRATION_ID, the DATASOURCE_NUM_ID, and the Lookup date, which are described in the table below.
Table 11-4 Columns Used in the load mapping Dimension Key Lookup
| Port | Description |
|---|---|
|
INTEGRATION ID |
Uniquely identifies the dimension entity within its source system. Formed from the transaction in the Source Adapter of the fact table. |
|
DATASOURCE_NUM_ID |
Unique identifier of the source system instance. |
|
Lookup Date |
The primary date of the transaction; for example, receipt date, sales date, and so on. |
If Type II slowly changing dimensions are enabled, the load mapping uses the unique effective dates for each update of the dimension records. When a dimension key is looked up, it uses the fact's primary date to resolve the appropriate dimension key. The effective date range gives the effective period for the dimension record. The same entity can have multiple records in the dimension table with different effective periods due to Type II slowly changing dimensions. This effective date range is used to exactly identify a record in its dimension, representing the information in a historically accurate manner.
In the figure below, the Supplier Product Key Lookup Interface illustrates the four columns needed for the load interface lookup:
In the figure below, the INTEGRATION ID, DATASOURCE_NUM_ID, Lookup Date (EFFECTIVE_FROM_DT and EFFECTIVE_TO_DATE) are highlighted in the Supplier Product Key Lookup.
The transformation then outputs the Supplier Product Key (the dimension key) to the data warehouse table W_SUPPLIER_PRODUCT_D.
The Oracle Business Analytics Warehouse foundation comprises a data model that accommodates data from disparate source systems. Data is sourced from operational systems and systematically molded into a source-independent format. After the data is made source independent, it can then be used to create key metrics for analytic reporting, so that metric calculations are not source dependent. This clear separation allows you to swap source systems or integrate additional source systems without having to reconfigure the metric calculations to accommodate each source system's requirements.
One method for transforming source data into a source-independent format is to convert the source-supplied values to domain values. Domain values are a set of distinct values used to calculate prepackaged metrics. These values are provided by the Oracle Business Analytics Warehouse to allow you to create metric calculations independent of source system values.
To best understand the domain value conversion process, consider an example of two source systems—Source System A and Source System B. Each source system stores two types of employee events—hire and rehire. Source system A uses H to denote a hire event and R to denote a rehire event, whereas source system B uses 1 to denote a hire event and 2 to denote a rehire event. When the Oracle Business Analytics Warehouse extracts data from both systems, it ports those source values through the extract package until the data reaches the W_EVENT_GRP_CODE column in the W_EVENT_TYPE_DS staging table. The load package then ports the extracted source values (H and R from source system A, and 1 and 2 from source system B) into the interface. Within the interface, source values are translated into domain values (HIR and REH) based on a set of rules that are particular to your business practices.
You must define the rules so that the ODI interface knows how to map your specific source values to the given set of domain values. Before you set up the rules you must:
Analyze all of your source values and how they map to the prepackaged domain values. You might find that you need to create additional domain values for particular columns. The result of this preparation work is a list of each source value and how it is mapped to a domain value.
Implement this logic in the ODI Interface. To set up the logic, modify the Expression transformation in the ODI Interface for each affected column.
After the ODI Interface converts the source-specific values to domain values, the domain values are inserted into an Oracle Business Analytics Warehouse table. In this example, the HIR and REH values populate the W_EVENT_TYPES table (Interface SDE_ORA_EventTypeDimension_AdditionalEvents_FromFile.EVENT_TYPE_DS).
Values in the W_EVENT_TYPES table are used to create metrics in the front end. Some metrics are defined using domain values. For example, seven metrics use the HIR and REH event group code in their calculation. The following are the seven metrics, along with their descriptions and calculations:
This metric counts all hires for a specified period. The calculation is:
SUM(CASE WHEN (CMMNEVTP.W_EVENT_GRP_CODE IN ('HIR','REH')) THEN EVNT.EVENT_CNT ELSE 0 END)
This metric determines the ratio of rehires to all employees hired during a specified period. The calculation is:
CASE WHEN SUM(CASE WHEN CMMNEVTP.W_EVENT_GRP_CODE IN ('REH','HIR') THEN EVNT.EVENT_CNT ELSE 0 END) = 0 THEN 0 ELSE SUM(CASE WHEN CMMNEVTP.W_EVENT_GRP_CODE IN ('REH') THEN EVNT.EVENT_CNT ELSE 0 END)/SUM(CASE WHEN CMMNEVTP.W_EVENT_GRP_CODE IN ('REH','HIR') THEN EVNT.EVENT_CNT ELSE 0 END) END
This metric counts the head count hired for regular full-time positions. The calculation is:
SUM(CASE WHEN CMMNEMPT.FULL_TIME_FLAG = 'Y' AND CMMNEMPT.EMP_CAT_CODE = 'R' AND (CMMNEVTP.W_EVENT_GRP_CODE = 'HIR' OR CMMNEVTP.W_EVENT_GRP_CODE = 'REH') AND EVNT.EVENT_DK >= (CMMNDATE.DATE_KEY - 365) AND EVNT.EVENT_DK <= CMMNDATE.DATE_KEY THEN EVNT.EVENT_CNT ELSE 0 END)
This metric counts the regular full-time and part-time employees who belong to this category of veterans and were hired during the previous 12 months. The calculation is:
SUM(CASE WHEN CMMNEMPD.VETERAN_STAT_CODE = '4' AND CMMNEMPT.EMP_CAT_CODE = 'R' AND (CMMNEVTP.W_EVENT_GRP_CODE = 'HIR' OR CMMNEVTP.W_EVENT_GRP_CODE = 'REH') AND EVNT.EVENT_DK >= (CMMNDATE.DATE_KEY - 365) AND EVNT.EVENT_DK <= CMMNDATE.DATE_KEY THEN EVNT.EVENT_CNT ELSE 0 END)
This metric counts regular full-time and part-time employees who belong to this category of veterans. The calculation is:
SUM(CASE WHEN CMMNEMPD.VETERAN_STAT_CODE = '3' AND CMMNEMPT.EMP_CAT_CODE = 'R' AND (CMMNEVTP.W_EVENT_GRP_CODE = 'HIR' OR CMMNEVTP.W_EVENT_GRP_CODE = 'REH') AND EVNT.EVENT_DK >= (CMMNDATE.DATE_KEY - 365) AND EVNT.EVENT_DK <= CMMNDATE.DATE_KEY THEN EVNT.EVENT_CNT ELSE 0 END)
This metric counts regular full-time and part-time employees who belong to this category of veterans and were hired during the previous 12 months. The calculation is:
SUM(CASE WHEN CMMNEMPD.VETERAN_STAT_CODE = '1' AND CMMNEMPT.EMP_CAT_CODE = 'R' AND (CMMNEVTP.W_EVENT_GRP_CODE = 'HIR' OR CMMNEVTP.W_EVENT_GRP_CODE = 'REH') AND EVNT.EVENT_DK >= (CMMNDATE.DATE_KEY - 365) AND EVNT.EVENT_DK <= CMMNDATE.DATE_KEY THEN EVNT.EVENT_CNT ELSE 0 END)
This metric counts regular full-time and part-time employees who belong to this category of veterans and were hired during the previous 12 months. The calculation is:
SUM(CASE WHEN CMMNEMPD.VETERAN_STAT_CODE = '2' AND CMMNEMPT.EMP_CAT_CODE = 'R' AND (CMMNEVTP.W_EVENT_GRP_CODE = 'HIR' OR CMMNEVTP.W_EVENT_GRP_CODE = 'REH') AND EVNT.EVENT_DK >= (CMMNDATE.DATE_KEY - 365) AND EVNT.EVENT_DK <= CMMNDATE.DATE_KEY THEN EVNT.EVENT_CNT ELSE 0 END)
The Oracle Business Analytics Warehouse is also extensible in that you can create additional domain values for those columns that do not fit into the existing domain value definitions. However, before you modify the domain value set for a particular column, you first perform impact analysis on existing metrics. For example, the Oracle Business Analytics Warehouse prepackages the following two events:
New Hire. This event occurs when a new person is hired.
New Position. This event occurs when a position is created, but an existing employee might be hired internally.
If you have an event that represents both a New Hire and a New Position, you might have to create a third event that depicts both. If you create this new event type domain value, you need to include it in the applicable metric definitions so as to account for all hires and positions.
Domain values are a set of distinct values used to calculate prepackaged metrics. These values are provided by Oracle Business Analytics Warehouse to allow you to create metric calculations independent of source system values. Oracle Business Analytics Warehouse provides CSV worksheet files to map source system values to domain values.
You can add to these worksheet files if you need extra source system values and map them to domain values. You can also modify the worksheet files if you need to customize the domain values. You can use an existing domain value if you want to change the preconfigured metrics. Otherwise you can create a new domain value and create new metrics based on this domain value.
The source system values that are not mapped to a domain values in the CSV worksheet files have a question mark (?) as the domain value in the Oracle Business Analytics Warehouse. These values do not affect the domain values metrics.
If there are no worksheet files to map the source system values to the domain values, you need to modify the domain values using ODI (for more information, see Section 11.5.2.5, "Configuring the Domain Value Set Using ODI Designer").
To map source values to domain values using CSV worksheet files:
Identify the Oracle Business Analytics Warehouse table columns that use domain values.
For a list of columns that use domain values, see the Oracle Business Analytics Warehouse Data Model Reference.
List all of your source values that qualify for conversion to one of the domain values.
Map each source value to a domain value.
If any of your source system values do not map to a prepackaged domain value, and you can modify the list of domain values, then create a list of new domain values and map your orphaned source system values to your newly created domain values.
You cannot modify all domain value sets. Also, you must check which metrics are affected by the modified domain value set. For more information, see the Oracle Business Analytics Warehouse Data Model Reference.
Open the CSV worksheet file in the $ODI_HOME\ oracledi\biapps_odi\odifiles\odidatafiles\lkpfiles folder (for example, C:\ODI\oracledi\biapps_odi\odifiles\odidatafiles\lkpfiles).
Edit the file to map your source values to the existing domain values.
Alternately, if you want to add additional domain values, add them in this worksheet file.
Save and close the CSV file.
If there are no worksheet files to map the source system values to the domain values, you need to modify the values using ODI Designer. For more information on configuring the domain value set with CSV worksheet files, see Section 11.5.2.4, "Configuring the Domain Value Set with CSV Worksheet Files".
Configuring the domain value set for a particular column, using ODI, entails one or both of the following activities:
Mapping source-specific values to domain values
Adding more domain values to the prepackaged set of values
Regardless of which activity you choose, the configuration occurs in the Expression transformation of the applicable Source Adapter mapplet. The following procedure shows how to configure the Expression transformation to change the domain values.
To map source values to domain values:
Identify all the Oracle Business Analytics Warehouse table columns that use domain values.
For a list of columns that use domain values, see Oracle Business Analytics Warehouse Data Model Reference.
List all of your source values that qualify for conversion to one of the domain values.
Map each source value to a domain value.
If any of your source system values do not map to a prepackaged domain value, and you might modify the list of domain values, then create a list of new domain values and map your orphaned source system values to your newly created domain values.
You cannot modify all domain value sets. Also, you must check which metrics are affected by the modified domain value set. For more information, see the Oracle Business Analytics Warehouse Data Model Reference.
In ODI Designer, open the applicable extract inline view (inline views follows the SQ_* naming convention).
Open the Expression of the applicable column that uses domain values.
Alternatively, if you want to add domain values, add them to this expression.
Save the changes.
Regenerate the scenario.
Dimension and Fact Tables in the Oracle Business Analytics Warehouse use the following two types of index:
E-LT Index
E-LT Indexes are used for Unique/ Binary Tree index.
Query Index
Query Indexes are used for Non-Unique/ Bit Map Index.
To add an index to an existing fact or dimension table:
In ODI Designer, display the Models view, and expand the 'Oracle BI Applications 7.9.5.2' folder.
Expand the Fact or Dimension node as appropriate.
Expand the Table in which you want to create the index.
Right-click on the Constraints node, and select Insert Key to display the Key: New dialog.
Display the Description tab.
Select the Alternate Key radio button, and update the name of the Index in the Name field.
Display the Column tab.
Select the column on which you want to create the index.
Display the FlexFields tab.
Use the settings to specify the index type, as follows:
For 'Query' type indexes (the default), set the value of the IS_BITMAP parameter to 'Y' and the value of the IS_UNIQUE parameter to 'N'.
For 'E-LT' type indexes, clear the check box for the INDEX_TYPE parameter and set the value to 'ETL'. In addition, set the value of the IS_BITMAP parameter to 'N' and the value of the IS_UNIQUE parameter to 'Y'.
Save the changes.
|
 Copyright © 2009, Oracle. All rights reserved. Legal Notices |
|