Administration

This page describes the administrative tasks that can be performed using the Oracle Utilities Administration Warehouse (OUAW) tool to configure the out-of-the-box and custom ELT jobs, and to view the configurations made during the installation and source configuration processes.

On this page:

Product Instance Configuration

The product instance represents a specific instance of a source application that can be configured as a source for the Oracle Utilities Analytics Warehouse (OUAW) solution. A record is created for every product instance where ELT is set up to extract data. OUAW delivers a record for the product that uses Oracle Data Integrator for the ELT.

Note: You may have multiple instances of the same source application to integrate with OUAW.

The Product Instance page, accessible through the ETL Configuration tab, displays information about the product instances configured with OUAW.

Several Product Instances are listed in a seven columns table that details each instance's name, number, context code, change data capture, data source indicator, language code, and product version.

The following attributes are available on the product instance metadata table.

Image of two columns table showing in the left-hand column the attributes listed in the product instance metadata table, and their purposes in the right-hand column.

To add a new product instance, use the script delivered as a part of the installation package. For more details, see 'Configuring the Source' under the 'Upgrading Oracle Utilities Analytics Based ELT Component' section in Chapter 5: Upgrading Oracle Utilities Analytics.

Maintain Product Instance page showing the Main and the Information Retrieved from Source sections, where the user is to set and save the product instance details. The Save button is the first one from right to left on the Main section header.

Back to Top

Server Configuration View

The Server Configuration page, accessible through the ETL Configuration tab, displays the details of the source and target servers used for configuring Oracle GoldenGate for OUAW.

As part of the OUAW installation process, Oracle GoldenGate, Oracle Database, and JAgent configuration records are generated for the configured source application and the target OUAW application, and are stored in the B1_SERVER_CFG table in MDADM schema. The data from this table are displayed on the Server Configuration page, and its content cannot be edited from the Oracle Utilities Analytics Administration tool.

Server Configuration page showing an eight columns table that lists each server configuration detailing its context, host name, port, db service name, home path, user name, db client home, and server type. Find this page under ETL Configuration.

The following attributes are displayed:

Image of two columns table showing in the left-hand column the attributes listed for the server configuration, and their purposes in the right-hand column.

Back to Top

Storage Configuration

The Storage Configuration page, accessible through the ETL Configuration tab, enables you to manage the tablespaces that are used for the database objects created during the execution of the Oracle Data Integrator jobs, which allows you to maintain existing records related to configuration and to add new records.

Note: Base product-owned records are delivered out-of-the-box for the star schemas tables that come with OUAW, and they cannot be deleted. If you want to add any entity to the ELT, you can create a new record, which can be later edited or deleted.

Storage Configuration page showing a search bar and a seven columns table that contains the following information: Storage configuration ID, context code, logical schema, object type, sequence, tablespace name, and owner. Find this page under ETL Configuration.

The following are the attributes available on the page:

Image of a two columns table showing in the left-hand column the attributes listed for the storage configuration, and their purposes in the right-hand column.

Back to Top

Global Settings Configuration

The Global Configuration page, accessible through the ETL Configuration tab, is used for setting up the global parameters needed for OUAW, allowing you to enter or edit values for the configuration settings. As a part of the OUAW installation process, the global configuration records are generated for both the configured source application and the target OUAW application. The configuration values must be entered in the specified data format. Entering values in any other format causes errors during ELT processing.

Global Configuration page showing a search bar and a four columns table, which contains the following details: Product, instance number, description, and value. Find this page under ETL Configuration.

The following are the attributes displayed on the page:

Image of a two columns table showing in the left-hand column the attributes listed for the global configuration, and their purposes in the right-hand column.

Back to Top

Target Entities Configuration

The Target Entity page, accessible through the ETL Configuration tab, displays the configuration information for the viz. dimensions, facts, and materialized views target entities, which can be loaded for multiple source instances. The configuration that is common across multiple instances is stored in this table. You can add, edit, or delete records on this page.

Note: Base product-owned records are delivered out-of-the-box for the star schemas tables that come with OUAW, and they cannot be deleted. If you want to add any entity to the ELT, you can create a new record, which can be later edited or deleted.

Target Entity page showing a search bar and a six columns table which contains the following data: Target entity ID, entity name, entity type, ODI package name, staging retention days, and owner. Fin this page under ETL Configuration.

The following are the attributes displayed on this page:

Image of a two columns table showing in the left-hand column the attributes listed for the target entity, and their purposes in the right-hand column.

Back to Top

Job Configuration

The Job Configuration page, accessible through the ETL Configuration tab, is used to provide configuration for the package (the executable logic) to populate the target entities for each instance. This page allows you to add, edit, and delete records.

Note: Base product-owned records are delivered out-of-the-box for the star schemas tables that come with OUAW, and they cannot be deleted. If you want to add any entity to the ELT, you can create a new record, which can be later edited or deleted.

Job Configuration page showing a search bar and a seven columns table containing the following details: Job configuration ID, source product, instance number, target entity name, entity active flag, user exit procedure, and last sync data time.

The following are the attributes displayed on this page:

Image of a two columns table showing in the left-hand column the attributes for the job configuration, and their purposes in the right-hand column.

Back to Top

Maintain Job Configuration

To specify configuration for a job, open the Maintain Job Configuration page by clicking the Add (+) or the Edit buttons for a selected job record row on the Job Configuration page.

Maintain Job Configuration page showing the following sections, where the user is to enter various details: Main, Scheduling Parameters, Customization Attributes, and Record Information.

Back to Top

Jobs Enablement 

A job can be enabled from the Job Configuration page by clicking its respective Edit button. Alternately, a group of jobs can be enabled together using the Enable Jobs button. There are three job type options:

  • Dimension Jobs
  • Facts
  • Facts and Materialized Views

When the selected job type is either Dimension Jobs or Facts, the replication objects to be loaded with dimensions and facts are also enabled. 

Enable Jobs dialog where the user is to select from three dropdown menus the source product, instance number, and job type.

Back to Top

Schedule Configuration

The Schedule Configuration page, accessible through the ETL Configuration tab, is used to configure a job’s scheduled start and interval. Multiple schedule types can be created with different schedule periods and jobs can be assigned to them; the various job schedules may be distributed so multiple jobs do not run simultaneously. The schedule period has to be created first and then it has to be updated in the Job Configuration page. See Job Configuration for details.

Schedule Configuration page showing a search bar and a six columns table that contains the following details: Schedule ID, schedule type, owner, start time, interval, and end time. Find this page in the ETL Configuration tab.

The following are the attributes displayed on the page:

Image of a two columns table showing in the left-hand column the attributes for the schedule configuration, and their purposes in the right-hand column.

Back to Top

Source Table Configuration

The Source Table page, accessible through the ETL Configuration tab, displays the list of tables from the source applications that are available for replication in Oracle Utilities Analytics Warehouse (OUAW) and their mode of replication. For those tables that are not replicated in the base product, this page allows you to enable custom replication. 

Source Table page showing a search bar and a seven columns table containing the following details: Source table ID, source product name, table name, history type, base replication, custom replication, and owner. Find this page in the ETL Configuration tab.

To enable custom replication for a table:

  1. Click on the pencil icon on the first column to the left of the table.

  2. Set the Custom Replication flag value to Y for the table in the Manage Replication Details section.

  3. You may also change the History Type of the table to be replicated.

Note: Changing the history type of a table already configured and replicated may result in error in the processing of ELT jobs and in data processing by GoldenGate.

The following are the attributes of the table:

Image of a two columns table showing in the left-hand column the attributes for the source table, and their purposes in the right-hand column.

Back to Top

ETL Replication View

Every ETL job in the data warehouse depends on one or more Oracle Data Integrator interfaces to populate the target fact or dimension tables. The starting point is a view defined for the ETL job in the replication layer of the specific source product. This ETL replication view fetches the base values from the replication tables as a starting point for further transformations to be done by the Oracle Data Integrator interfaces.

In certain business scenarios, it might be necessary for you to override the definition of the provided ETL replication view. For example, in Oracle Utilities Network Management System, a particular type of data may be stored in different tables based on the network model. The base view definitions supplied for Oracle Utilities Network Management System ETL are designed based on the OPAL network model. When you use a different model, you might have to change the definition of some of these views in the replication layer for your network model.

ETL Replication View page showing a search field and a six columns table with the following details: Object ID, source product, view name, view type, custom ODI scenario name, and target entity. Find this page under the ETL Configuration tab.

To take a more specific example, the B1_D_ACCT_VW for the account dimension in the Oracle Utilities Network Management System star schema tries to fetch the account-related attributes from the CES_CUSTOMERS_HISTORY table. If your model stores the account attributes in a different table, then the view definition needs to be changed. The Maintain ETL Replication Views page in the Oracle Utilities Analytics Administration Tool allows you to do this by defining a custom Oracle Data Integrator interface to generate the view. The view name and the column names in the view should be exactly as it was delivered. However, the view definition can be changed in accordance with your requirements.

Maintain ETL Replication Views page showing the Maintain ETL Replication Views section, where the user can enter a custom ODI scenario name, along with two sections with details: Maintain ETL Replication View, and Record Information.

A scenario should be generated for the custom Oracle Data Integrator interface and should be shown in the Oracle Utilities Analytics Administration tool page against the appropriate view name with a definition that needs to be overridden. When views are then generated through the Run View Generator script, the ETL framework creates the view-based Oracle Data Integrator scenario that you entered.

Back to Top

SCD Type 2 Dimension Column Properties of Type 2 Slowly Changing Dimension 

A type 2 slowly changing dimension has the following column types:

  • Natural Key
  • SCD Start
  • SCD End
  • SCD Flag

Any change in any of the remaining columns is treated as a new history item for the same natural key. You can decide which of the columns should not be considered for creating a new history item. Using Oracle Data Integrator, this is done by changing the corresponding property of the column in the model of the target dimension. However, this change requires regeneration of the dimension load scenario. The customization that you do will get overridden by any future upgrades provided by Oracle. You can control the columns where a change causes a history to be created.

You can change the properties by accessing the administration tool.

SCD Type 2 Dimension Hybrid Behavior page showing a search field and a four columns table that contains: dimension ID, Source Product, Dimension Table Name, and Owner. Find this page under ETL Configuration tab.

Modify SCD Type 2 Dimension Behavior page showing a seven columns table with the dimension's details: ID, column ID, column name, data type, column behavior, updated date time, updated by, and owner. Look for this page in the ETL Configuration tab.

Once the configurations have been changed, a script needs to be executed to ensure the changes have been applied to the appropriate scenarios. For Unix, perform the following steps:

  1. Navigate to the /bin directory.
  2. Add Java 8 to the following path variable:
    export PATH=/bin:$PATH
  3. Run ksh ./updateODIMetadata.sh. 

Note: After the SCD2 dimension behavior is customized, execute the script after every upgrade to retain the customized behavior. 

Back to Top

NMS Configuration

Before attaching a specific source system to Oracle Utilities Analytics Administration, there are some configurations that need to be done for every source system for its ETL to function properly. These configurations include setting up certain parameters to be referred to during the extraction or setting up bucket ranges to classify the measures in a fact.

You can set up the configurations needed for the Oracle Utilities Network Management System ETL processes via the Oracle Utilities Analytics Administration Tool using the two pages grouped under the NMS Configuration.

Screenshot showing the links to use to set up the configurations for the Network Management System ETL processes.

Note: Details are covered in the Configuring Source > Oracle Utilities Network Management System (NMS) section in Chapter 5: Configuring Oracle Utilities Analytics, where the configurations needed for every source system are discussed at length with all necessary details.

Back to Top

ETL Job Execution

The Job Execution page, accessible through the ETL Job Execution tab, is used for tracking the execution of ETL processes. An entry is created for each execution.

The following image shows an example of the job execution log table.

Job Execution page showing a nine columns table that details source product, instance number, entity name, session, status, slice start date, slice end date, session start date, and session end date. Look for this page under ETL Job Execution tab.

The following are the attributes on this table:

Image of a two columns table showing in the left-hand column the attributes for the job execution, and their purposes in the right-hand column.

Back to Top