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:

• Administration
• • Product Instance Configuration
  • Server Configuration View
  • Storage Configuration
  • Global Settings Configuration
  • Target Entities Configuration
  • Job Configuration
  • • Maintain Job Configuration
    • Jobs Enablement 
  • Schedule Configuration
  • Source Table Configuration
  • ETL Replication View
  • SCD Type 2 Dimension Column Properties of Type 2 Slowly Changing Dimension 
  • NMS Configuration
  • ETL Job Execution

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.

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

Attribute	Purpose
Product Code	A reference to the product code that identifies the product for this instance.
Instance Number	A unique number starting with 1 to uniquely identify the instance.
Context Code	A unique code comprised of the product code and instance number that is used to identify the connections in Oracle Data Integrator. Due to limitations imposed by Oracle GoldenGate (used for replication), the context code cannot be more than five characters in length.
Change Data Capture	A flag identifying the methodology to be used for replicating source tables. The default is GoldenGate.
Drill Back URL	A URL to be used to allow you to drill back to the source system from the analytics.
Time Zone Code	The time zone of the source product instance. This allows the interfaces to be built so that dependencies on job execution for multiple time zones are handled correctly.
Currency Code	The currency used in the source product instance. Multiple currencies are not supported.
Language Code	The primary language supported by the data warehouse. This is used to filter language specific data in the data warehouse.
Data Source Indicator	A unique value representing the instance. For Oracle Utilities Application Framework products, this is the environment ID of the source instance.
Source Instance Version	The current version number of the source product instance. This can be used to validate whether the version is supported by the data warehouse or provide suggestions if upgrades or patches need to be applied to enable support for the source product.

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.

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.

The following attributes are displayed:

Attribute	Purpose
Context	The code is either the context code identifying the instance uniquely or B1 for the target data warehouse.
Host Name	The host name of the data server based on the server type.
Port	The port that the data server utilizes.
DB Service Name	The service name of the database.
Home Path	The home folder of the data server as defined by the server type.
User Name	The username to connect to the data server. For example The database user if the server type is the database. The server user that has access to the software if the server type is GoldenGate or JAgent.
Server Type	The type of server to identify the different configurations. For example: Database: Details about the database. Golden Gate: Details about the GoldenGate JAgent: Details about the JAgent configuration

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.

The following are the attributes available on the page:

Attribute	Purpose
Context Code	Identifies the context for which this is applicable. A value of "GLOBAL" is allowable across all the contexts unless overridden by an entry for the specific context.
Logical Schema	The logical schema for which the configuration is applicable. An empty value indicates that this is applicable to all logical schemas.
Object Type	The type of object for which the configuration is applicable. This can be Table, Index, or Partition.
Sequence	The default is 0. This is applicable only to partitions. This can be used to map multiple tablespaces in round-robin scheduling while creating the new partitions within the same table.
Tablespace Name	The name of the tablespace to be associated with the object.

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.

The following are the attributes displayed on the page:

Attribute	Purpose
Product	A reference to the product code that identifies the product for this instance. This could be one of the configured source products or the base Oracle Utilities Analytics Warehouse product itself.
Instance Number	The unique number of the configured source product instance. For the base Oracle Utilities Analytics Warehouse product configuration settings, this value is set to null.
Description	This column describes the purpose of the specific global configuration setting.
Value	The configuration value that you can update based on their setup.
Data Type	The data type of the configuration value that a user is expected to enter.
Data Format	The specific format of the data that a user is expected to enter the configuration value in.

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.

The following are the attributes displayed on this page:

Attribute	Purpose
Entity Name	The name of the entity that needs to be scheduled for loading into the target.
Entity Type	The type of entity supported by Oracle Utilities Analytics Warehouse: Slowly changing dimension Type 1 Slowly changing dimension Type 2 Accumulation facts Snapshot facts Materialized views
Maximum Parallel Executions	It may be necessary to execute multiple instances to efficiently load data with each instance working on a different data set. This attribute controls how many parallel executions can be spawned for a single entity load.
Maximum Retries	This attribute controls the maximum number of retry attempts in a day. If the load is still failing when this limit is reached, it is retried the next day.
Retry Interval	The base architecture has been designed for automatic retries. In case of failures, job are retried and this attribute controls the interval been successive retries. Default is 30 minutes, but it can be configured according to your requirements.
Scheduling Type	Three different modes of schedules are supported: Daily Incremental Load: Loads are executed as soon as data is available to load. Near Real Time Load: Loads are executed within a configured interval. This is for future use. One Time Load: A load is executed only once.
Slice Duration Type	A slice is a volume of data bound within a time span (duration). Different objects have differing data distribution and load processing requirements. This attribute controls the duration between two slices. The following slicing intervals are supported: Day(s) Week(s) Month(s) Quarter(s) Year(s) Hour(s) Minute(s)
Slice Duration	The number identifying the slice duration based on the slice duration type.
ODI Package Name	The name of the Oracle Data Integrator scenario that should be executed.
Staging Retention Days	The number of days to retain data in the staging tables.
Owner Flag	Indicates whether the record is owned by the base product (BI) or you (CM)

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.

The following are the attributes displayed on this page:

Attribute	Purpose
Source Product	A reference to the product code, which identifies the product for this instance.
Instance Number	To be populated for instance specific deviations. Ideally most data should be reusable across instances of the same product.
Target Entity	A reference to the entity.
Entity Active Flag	Indicates whether the ELT job should be picked up for execution by the Oracle Data Integrator Scheduler.
Slice Date/Time	That starting timestamp for the next execution.
Initialize Flag	Controls the initialization of the entity for slicing.
Execution Sequence	Sequence of execution within the same group.
Last Sync Timestamp	Stores the timestamp of the most recent successful slice.
Override Slice Duration Type/Override Slice Duration	The override slice duration fields act as the overriding value for the ones in the target entity: Type: The override duration unit of measure (for example, Year) Duration: The value for the override slice duration
User Exit Procedure	The procedure name, which can be used to load the user-defined columns in the entity.

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.

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. 

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.

The following are the attributes displayed on the page:

Attribute	Purpose
Schedule Type	There are four schedule types: Incremental Update: Load the data when the data is changed in the source. Near Real Time Load: Loads are executed within a configured interval. Daily Refresh: Loads the data once daily (full load). One Time Load: A load is executed only once.
Schedule Start Time	The time in a day when the load can be started.
Schedule Interval	The time interval between the consecutive execution of jobs.
Schedule End Time	The time in a day after which the load cannot be started.

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. 

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:

Attribute	Purpose
Source Product	A reference to the product code that identifies the product for this instance.
Table Name	The name of the source table. There is also a corresponding entry in the objects table.
History Type	The following scenarios exist: Source system tracks history using an effective date column. No history in source, but history needed for the Type 2 dimensions. No history required.
Effective Date Column Name	The column name used for storing the effective dates in the source.
Characteristic Entity	Identifies the Char Entity flag in the source Oracle Utilities Application Framework application.
Base Replication	Controls whether the table is required to be replicated for target entity load.
Group Number	Defines the grouping of various tables for a source product. The group numbers are used to define the various GoldenGate models that are set up to extract data from the source system.
Custom Replication	Extension for customizations. The additional tables to be marked for replication.
Purge Enabled	Controls whether the replicated table should be purged or not.
Replication Retention Days	The number of days the data should be retained in the replication layer.
Owner Flag	Indicates whether the record is owned by the base product (B1) or you (CM).

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.

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.

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.

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.

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.

The following are the attributes on this table:

Attribute	Purpose
Job Configuration	A reference to the job configuration.
Session Number	Reference to the Oracle Data Integrator session number.
Scheduled Start Time	Start time as set in the schedule.
Slice Start	Starting timestamp of the slice.
Slice End	Ending timestamp of the slice.
Status	A composite status of the job. The status is primarily derived from SNP_SESSION; however additional statuses are tracked in job executions: Pending Submitted Reprocessed Error Done Running
Actual Start Time	The timestamp when the job actually started.
Actual End Time	The timestamp when the job actually ended.
Duration	The total execution time in seconds.
Insert Count	The number of records inserted.
Update Count	The number of records updated.
Delete Count	The number of deletions.
Error Count	The number of rows identified as error.
Total Count	The number of rows processed. Sum of all the counts mentioned above.

Back to Top