Category 2 customizations use pre-packaged adapters to add new fact or dimension tables to the Oracle Business Analytics Warehouse.
You can build 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 ETL 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.
About the Main Required Columns
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 plus these:
ROW_WID — A sequence number generated during the ETL process, which is used as a unique identifier for the Oracle Business Analytics Warehouse.
ETL_PROC_WID — Stores the ID of the ETL process information.
About the DATASOURCE_NUM_ID Column
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.
Learn about miscellaneous information about customization in Oracle Business Intelligence Applications.
About the Update Strategy
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.
About Indices and Naming Conventions
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 ETL 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 preconfigured objects for guidance. Name all the newly created tables as WC_. This helps visually isolate the new tables from the preconfigured 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 Adding an Index to an Existing Fact or Dimension Table).
Custom tables should follow the WC_ naming convention to help distinguish from preconfigured tables. Follow this procedure to add a new fact table to the Oracle Business Analytics Warehouse.
Create the custom dimension datastores and tasks.
Create a WC_<dimension name>_D datastore under the 'Oracle BI Applications – Dimension' model. Create a WC_<dimension name>_DS datastore under the 'Oracle BI Applications – Dimension Stage' model. Use the WC_SAMPLE_DS and WC_SAMPLE_D datastores as templates. These datastores include all required system columns. Custom tables should follow the WC_ naming convention to help distinguish from shipped tables.
Note:
The specific submodel that a table belongs to drives the table maintenance behavior. For example, tables in the 'Dimension Stage' submodel will always be truncated at each ETL run while tables in the 'Dimension' submodel are truncated only during a Full ETL run. Do not create a 'Custom' submodel to place your datastores as table maintenance will not be implemented properly for tables in such a submodel.
As described below, a dimension can be defined either in ODI, generating DDL to create the table in the database, or by defining the table in the database and importing the definition into ODI using the BI Applications RKM. If you use the RKM, the imported table is automatically placed in the 'Other' submodel and needs to be moved into the 'Dimension Staging' and 'Dimension' submodels as appropriate. Also, the OLAP type will need to be set for the dimension to 'Dimension' or 'Slowly Changing Dimension' as appropriate.
'X_CUSTOM' placeholder load plan components are provided as templates but should not be used for new custom content. Create a new dimension group in Configuration Manager. For information about how to do this, see About Creating Custom Fact and Dimension Groups.
You can create the dimension and tasks manually using ODI.
Create an ODI sequence for the custom dimension. A database sequence is used to populate the ROW_WID column of the dimension. The Generate DDL procedure is used to generate the DDL required to create the database trigger in the database. Use WC_SAMPLE_D_SEQ as a template.
Once you have completed customization steps to include custom tables and ETL tasks, create custom fact and dimension groups in ODI and Configuration Manager to incorporate your changes into the Load Plans that orchestrate ETL.
Data Warehouse tables are populated by ETL tasks which are orchestrated by a Load Plan. When creating custom content, new data warehouse tables and associated ETL tasks are created and need to be incorporated into a Load Plan so that the custom tables are populated along with the standard tables.
A Load Plan is built by assembling several Load Plan Components into an integrated load plan. Load Plan Components are organized into level 0, 1, 2, and 3 components. Level 3 Load Plan Components execute the actual ETL tasks, while the other Load Plan Components are considered system Load Plan Components, and are used in building up and organizing the Load Plan. These Load Plan Components are assembled into a combined Load Plan by the Load Plan Generator.
In order to have custom content such as custom dimensions or custom facts incorporated into a Load Plan that includes out-of-the-box content, custom Load Plan Components must be created. The Load Plan Generator can then include these components when building a load plan.
For steps on creating custom tables and ETL tasks, refer to Customizing the Oracle Business Analytics Warehouse. The following topics describe how to create new Load Plan Components to execute these ETL tasks. Pre-existing ‘3 Dims X_CUSTOM_DIM’ and ‘3 Dims X_CUSTOM_FG’ Load Plan Components are provided as templates and the steps in the following topics describe copying these X_CUSTOM components and updating the copies to reflect custom content. The steps are essentially the same regardless of which group you are adding, only the template you start with changes.
The first step in creating custom fact and dimension groups is performed in ODI Designer.
The last step in creating custom fact and dimension groups is performed in Configuration Manager.
To create a Custom Dimension Group, you need to associate it to an existing Fact Group, so if the custom Dimension Group is to be associated with a custom Fact Group, be sure to add the Fact Group first.