Category 2 Customizations: Adding Additional Tables

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 to load the 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 the existing table structures. When you create a new table, you must 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 required columns are:

  • 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 — Stores 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 are loaded in the same data warehouse tables from different sources if this column is given a different value for each data source.

Additional Information About Customizing

Learn about miscellaneous information about customization in Oracle BI 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 is designed to load only the changed data (new and modified). If the data is loaded without the incremental process, the loaded data is 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 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 uses 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, register the indices in the ODI Model. See Adding an Index to an Existing Fact or Dimension Table.

Adding New Fact Tables to Oracle Business Analytics Warehouse

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.

Placeholder fact and dimension groups to which you can add your own custom content are provided out-of-the-box. These follow the X_CUSTOM naming convention. You might introduce your own custom fact and dimension groups. See About Creating Custom Fact and Dimension Groups. The following steps assume content is being added to an out-of-the-box X_CUSTOM fact or dimension group. If you create custom groups, use those instead.

To add a new fact table:

  1. Create the custom fact datastores and tasks.
    1. Create a WC_<fact name>_F datastore under the Oracle BI Applications – Fact model.
    2. Create a WC_<fact name>_FS datastore under the Oracle BI Applications – Fact Stage model.
      Use the WC_SAMPLE_FS and WC_SAMPLE_F datastores as templates. These datastores include all required system columns.

    The specific submodel that a table belongs to drives the table maintenance behavior. For example, tables in the Fact Stage submodel are always truncated during each ETL run while tables in the Fact submodel are only truncated during a Full ETL run.

    You can define a fact in ODI either manually, or by generating the DDL to create the table in the database, or by defining the table in the database and importing the definition into ODI using the Reverse-engineering Knowledge Modules (RKM) of Oracle BI Applications. If using the RKM, the imported table is automatically placed in the Other submodel and you must move the imported table into the Fact Staging and Fact submodels as appropriate. Set the OLAP type also for the fact table to Fact Table.

  2. Add a foreign key constraint to all dimension tables associated with this fact. If the custom fact table joins to any custom dimension tables, be sure to complete the steps to introduce the custom dimensions prior to creating the custom fact table.

    The Dimension SDE task is included in the generated load plan because it populates the staging table that is used as a source for the Dimension SIL task.

    1. Drill into the Fact datastore.
    2. Right-click the Constraints subfolder below the Fact datastore and select New Reference.

      The naming convention is FK_<Fact Table>_<Dimension Table>.

      If there are multiple WID columns that need to reference the same dimension table, enumerate each with a numeric suffix. For example, FK_WC_CUSTOM_F_WC_CUSTOM_D1.

    3. Set the Type to User Reference, select the dimension from the Table list and, in the Columns subtab, click the green + button on the top-right to add a new column.
    4. For the Foreign Table column, select the custom WID column in the fact table.
    5. For the Primary Table column, select the ROW_WID column in the dimension table.
  3. Create an SDE and SIL task in the Custom SDE and SIL adaptor folders. Use the SDE_<Product Line Code>_SampleFact and SIL_SampleFact tasks as a template.

    These tasks include the logic required to populate the system columns.

  4. Add Load Plan step to the 3 SDE Facts X_CUSTOM_FG <Product Line Version Code> Load Plan Component.

    Note:

    If you are using a custom fact group, in the following steps, replace references to X_CUSTOM with the name of your custom fact group.
    1. In Designer, navigate to Load Plans and Scenarios, BIAPPS Load Plan, then Load Plan Dev Components.
    2. Navigate to SDE - <Product Line Version Code> and double-click the 3 SDE Facts X_CUSTOM_FG <Product Line Version Code>Load Plan Component.
    3. Select the X_CUSTOM_FG step.
    4. Click the green + symbol near the top right and select the Run Scenario Step option.
    5. Provide the Scenario Name, set the Version as -1, and set the Step Name to match the Task name. Set the Restart Type to Restart from failed step.
  5. Add a Load Plan step to 3 SIL Facts X_CUSTOM_FG Load Plan Component.

    Note:

    If you are using a custom fact group, in the following steps, replace references to X_CUSTOM with the name of your custom fact group.
    1. In Designer, navigate to Load Plans and Scenarios, BIAPPS Load Plan, then Load Plan Dev Components.
    2. Navigate to SIL and double-click the 3 SIL Facts X_CUSTOM_FG Load Plan Component.
    3. Select the X_CUSTOM_FG step.
    4. Click the green + symbol near the top right and select the Run Scenario Step option.
    5. Provide the Scenario Name, set the Version as -1, and set the Step Name to match the Task name. Set the Restart Type to Restart from failed step.

Creating Fact Tables

You can create a new fact table using the WC_SAMPLE_FS and WC_SAMPLE_F datastores as templates. You should set the OLAP type to Fact Table and use the Generate DDL procedure to create the tables in the database.

To create a fact table manually:
  1. In Designer, navigate to Models, Oracle BI Applications (Folder), Oracle BI Applications (Model), Fact Stage (Submodel), then right-click the WC_SAMPLE_FS datastore and select Duplicate Selection.
  2. Double-click the new datastore and rename it.

    Name and Resource Name should match the actual table name. Alias is the same or a more user-friendly value.

  3. In the Columns subtab, add all columns.
  4. Repeat the same steps to create the Fact Table by copying the WC_SAMPLE_F datastore under the Facts submodel.
  5. For the fact table, set the OLAP type to Fact Table.
  6. Generate the DDL to create the table in the database.

Importing Fact Tables into ODI

You can import the existing fact tables into ODI using the RKM of Oracle BI Applications. The imported table is automatically placed in the Other submodel and you must move the imported table into the Fact Staging and Fact submodels as appropriate. You should set the OLAP type to Fact Table and use the Generate DDL procedure to generate the required DDL for creating the database trigger in the database.

To import fact tables into ODI:
  1. In Designer, navigate to Models, Oracle BI Applications (Folder), and then double-click the Oracle BI Applications model.
  2. In the Reverse Engineer subtab, indicate the tables to be imported under the LIST_OF_TABLES option.

    To import multiple tables, provide a comma separated list.

  3. Click Reverse Engineer.

    A session is started that imports the table or tables into ODI. The Reverse Engineer process places all tables in the Other submodel.

  4. Drag and drop W_%_FS tables into the Fact Stage submodel and the W_%_F table into the Fact submodel.
  5. For the fact table, set the OLAP type to Fact Table.
  6. Generate the DDL to create the table in the database.

Creating Custom Dimension Datastore and Tasks

You can 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 are always 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 is not implemented properly for tables in such a submodel.

You can define a dimension 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 RKM of Oracle BI Applications. If you use the RKM, the imported table is automatically placed in the Other submodel and you must move the imported table into the Dimension Staging and Dimension submodels as appropriate. Also, set the OLAP type for the dimension to Dimension or Slowly Changing Dimension as appropriate.

The X_CUSTOM placeholder load plan components are provided as templates, but do not use for new custom content. Create a new dimension group in Oracle BI Applications Configuration Manager. For information about how to do this, see About Creating Custom Fact and Dimension Groups.

Creating Dimension Tables in ODI

You can create a new dimension table using the WC_SAMPLE_DS and WC_SAMPLE_D datastores as templates. You should set the OLAP type to Dimension or Slowly Changing Dimension as appropriate.

To create a dimension table in ODI:
  1. In Designer, navigate to Models, Oracle BI Applications (Folder), Oracle BI Applications (Model), and then Dimension Stage (Submodel).
  2. Right-click the WC_SAMPLE_DS datastore and select Duplicate Selection.
  3. Double-click the new datastore and rename it.

    Name and Resource Name should match the actual table name. Alias is the same or a more user-friendly value.

  4. In the Columns subtab, add all columns.
  5. Repeat the same steps to create the Dimension Table by copying the WC_SAMPLE_D datastore under the Dimensions submodel.
  6. For the dimension table, set the OLAP type to either Dimension if this is a Type I dimension or to Slowly Changing Dimension if this is a Type II dimension.

Importing Custom Dimension Tables into ODI

You can import the existing dimension tables into ODI using the RKM of Oracle BI Applications. The imported table is automatically placed in the Other submodel and you must move the imported table into the Dimension Staging and Dimension submodels as appropriate. And, you should set the OLAP type for the dimension to Dimension or Slowly Changing Dimension as appropriate.

To import a dimension table into ODI:
  1. In Designer, navigate to Models, Oracle BI Applications (Folder) and double-click the Oracle BI Applications Model.
  2. In the Reverse Engineer subtab, indicate the tables to be imported under the LIST_OF_TABLES option.

    To import multiple tables, provide a comma-separated list.

  3. Click the Reverse Engineer button to start a session that imports the table(s) into ODI.

    The Reverse Engineer process places all tables in the Other submodel.

  4. Drag and drop W_%_DS tables into the Dimension Stage submodel and the W_%_D table into the Dimension submodel.
  5. Double-click the new dimension datastore and set the OLAP type to either Dimension if this is a Type I dimension, or to Slowly Changing Dimension if this is a Type II dimension.

Creating ODI Sequences for Custom Dimensions

You can create an ODI sequence for a custom dimension.

You can use a database sequence to populate the ROW_WID column of the dimension. You can use the Generate DDL procedure to generate the required DDL for creating the database trigger in the database. Use WC_SAMPLE_D_SEQ as a template.

To create an ODI sequence for the custom dimension:

  1. In Designer, navigate to Projects, BI Apps Project, then Sequences.
  2. Right-click the Sequence folder and select New Sequence.
  3. Set name to Dimension Name_SEQ.
  4. Select Native sequence.
  5. Set the Schema to DW_BIAPPS11G.

    Generally, the Native sequence name should match the ODI name unless this causes the name length to exceed 30 characters, in which case, you can shorten the name to meet this limit. This database trigger populates the ROW_WID column.

  6. Generate the DDL to create the table in the database.

    Note:

    If you manually create the dimension in ODI, this generates the DDL to create both the table and sequence. If you import the dimension into ODI, this generates the DDL to create the sequence only.

Creating SDE and SIL Tasks

You can create SDE and SIL tasks in the Custom SDE and SIL adaptor folders.

Use the SDE_Product Line Code_SampleDimension and SIL_SampleDimension tasks as a template. These tasks include the logic required to populate the system columns. Finally, generate scenarios for these tasks.

About Creating Custom Fact and Dimension Groups

Once you have completed customization steps to include custom tables and ETL tasks, create custom fact and dimension groups in Configuration Manager and ODI to incorporate your changes into the Load Plans that orchestrate ETL.

The 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 you must incorporate them into a Load Plan to populate the custom tables along with the standard tables.

You can build a Load Plan by assembling several Load Plan Components into an integrated load plan. You can organize Load Plan Components 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 system Load Plan Components, and you can use the other load plan components in building up and organizing the Load Plan. The Load Plan Generator assembles these Load Plan Components into a combined Load Plan.

You must create custom Load Plan Components to have custom content such as custom dimensions or custom facts incorporated into a Load Plan that includes out-of-the-box content. The Load Plan Generator can then include these components when building a load plan.

To create custom tables and ETL tasks, see Customizing the Oracle Business Analytics Warehouse. The following topics describe how to create new Load Plan Components to execute these ETL tasks. Use Pre-existing 3 Dims X_CUSTOM_DIM and 3 Dims X_CUSTOM_FG Load Plan Components 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.

Creating Custom Fact and Dimension Groups in ODI

The first step in creating custom fact and dimension groups is performed in ODI Designer.

To create custom fact and dimension groups in ODI:

  1. Add the custom load plan component.
    1. In ODI Designer, navigate to the Designer tab and under Load Plans and Scenarios, expand the BIAPPS Load Plan, and then Load Plan Dev Components.
    2. Expand the appropriate subfolder for the custom Load Plan Component you are adding.
      If you are adding a SIL Load Plan Component, expand the SIL subfolder. If you are creating a SDE Load Plan Component, expand the SDE subfolder, then expand the appropriate product line subfolder.
  2. Duplicate an existing X_CUSTOM Load Plan Component.
    The following Load Plan Components are generally preconfigured with all required variables and other settings, so copying these components saves you the trouble of adding variables and configuring required settings:

    • 3_SDE Dims X_CUSTOM_DIM <PLV_CODE>

    • 3 SDE Fact X_CUSTOM_FG <PLV_CODE>

    • 3 SIL Dims X_CUSTOM_DIM

    • 3 SIL Fact X_CUSTOM_FG

  3. Rename the Load Plan Component.
    In the properties for the copied component, delete the Copy of prefix. For an SDE Load Plan Component, it is very important to retain the Product Line Version code (for example, EBS_11_5_10 and so on) as the suffix, because the suffix is used by Load Plan Generator to determine whether a Load Plan Component is included in a load plan that extracts from a particular data source. Rename the X_CUSTOM_DIM or X_CUSTOM_FG portion of the Load Plan Component name to match the code of the Dimension Group or Fact Group that is registered in Configuration Manager. Load Plan Generator uses this portion of the component’s name to coordinate with Configuration Manager to include Load Plan Components for those Groups specified in Configuration Manager.
  4. In the Steps subtab, rename the first, or Root, step to match the custom Group Code. Variables are assigned values at the Fact and Dimension Group level, and are refreshed at the root level. ETL refresh logic takes the name of the root step and passes it to Configuration Manager to get the value for the variable.
  5. Add the Custom Level 3 Load Plan Component to the Level 2 System Load Plan Component.
    1. Navigate to the Designer tab and under Load Plans and Scenarios, expand the Load Plan System Components.
    2. If you are adding a SDE Load Plan Component, expand the SDE subfolder. If you are adding a SIL Load Plan Component, expand the SIL subfolder.
    3. If you are adding a Dimension Group, double-click the Level 2 Dimension Group Load Plan Component.  If you are adding a Fact Group, double-click the Level 2 Fact Group Load Plan Component.
    4. Navigate to the Steps subtab of the Load Plan Component, right-click the $${3 X_CUSTOM_DIM} Load Plan Step, and select Duplicate Selection.
    5. Rename the Load Plan Step, replacing the X_CUSTOM_DIM or X_CUSTOM_FG suffix with the Group Code value used in the custom Load Plan Component and root step names. The portion within the brackets must match the custom Load Plan Component name exactly (ignoring the Product Line Version Code suffix in the case of SDE load plan components).  Load Plan Generator uses this value to incorporate the Level 3 Load Plan Component into the generated load plan.
  6. Add Load Plan steps to the 3 SDE Dims X_CUSTOM_DIM <Product Line Version Code> and 3 SIL Dims X_CUSTOM_DIM Load Plan Components you created.
    The following steps use X_CUSTOM in describing the changes to the Load Plan Components. Replace this with the actual dimension name used when you created your custom Load Plan Components.
    1. In Designer, navigate to Load Plans and Scenarios, BIAPPS Load Plan, Load Plan Dev Components, SDE - Product Line Version Code and double-click the 3 SDE Dims X_CUSTOM_DIM Product Line Version Code Load Plan Component.
    2. In the Steps subtab, select the X_CUSTOM_DIM step.
    3. Click the green +symbol near the top right and select Run Scenario Step.
    4. Provide the Scenario Name, set the Version as -1, and set the Step Name to match the Task name. Set the Restart Type to Restart from failed step.
    5. In Designer, navigate to Load Plans and Scenarios, BIAPPS Load Plan, Load Plan Dev Components, SIL and double-click the 3 SIL Dims X_CUSTOM_DIM Load Plan Component.
    6. In the Steps subtab, select the X_CUSTOM_DIM step.
    7. Click the green +symbol near the top-right and select Run Scenario Step.
    8. Provide the Scenario Name, set the Version as -1, and set the Step Name to match the Task name. Set the Restart Type to Restart from failed step.
To associate your custom dimension with a fact table, see Adding a Dimension to an Existing Fact.

Creating Custom Fact and Dimension Groups in Configuration Manager

The last step in creating custom fact and dimension groups is performed in Configuration Manager.

To create custom fact and dimension groups:

Note:

If you encounter an error while creating a fact or dimension group with CUSTOM_FG or CUSTOM_DIM code, then create with a different code and rename the code to CUSTOM_FG/DIM later.
  1. In order to add custom Fact or Dimension Groups, the user, if not the Administrative User, must be granted the following roles:

    • BIA_CONFIGURE_CREATE_EDIT_DIMGROUP_PRIV

    • BIA_CONFIGURE_CREATE_EDIT_FACTGROUP_PRIV

  2. In Configuration Manager, click Manage Business Intelligence Applications.
  3. Select an offering where the custom Fact or Dimension Group most closely fits.
  4. In the Associated Fact and Dimension Groups table, click Actions and select either Create Fact Group or Create Dimension Group.
  5. Enter X_CUSTOM_FG if you are creating a fact group and X_CUSTOM_DIM if you are creating a dim group.

    Note:

    The Group Code value must exactly match the value used when creating the corresponding ODI Load Plan Component. Dimension groups use _DIM as a suffix while Fact groups use _FG as a suffix. As a best practice, use X_ as a prefix to easily identify custom groups.
  6. Add a meaningful group description.
The remaining steps for creating Fact Groups and Dimension Groups are different. 

Creating Custom Dimension Groups

To create a Custom Dimension Group, you must 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.

To create a Custom Dimension Group:

  1. In the Associated Fact and Dimension Groups list for the offering you added a new group for, click Actions and select Create Dimension Group.
  2. In the Create Dimension Group dialog, select a Fact Group from the Associate Fact Group list, and add it to the right-hand list of associated Fact Groups.

    Note:

    The Create Dimension Group dialog doesn’t filter the Fact Groups by the offering you selected when creating the new group. Be sure to only select those Fact Groups that are actually associated with the offering you are adding the custom Dimension Group to.
  3. Click OK.
The custom Dimension Group is now added to the offering and associated with a Fact Group.