Pre-Data Warehouse Load Configuration

Oracle Utilities Analytics Warehouse ETL architecture is a metadata driven framework that allows you to configure out-of-the-box ETL jobs. The out-of-the-box ETL jobs are set up during the integration of the source system with the Oracle Utilities Analytics Warehouse. However, some additional configuration is required before starting the loading of data into the target data warehouse. This section contains only required metadata configurations before the data warehouse load, and these configurations augment those default configurations at the time of integration of the source system with the Oracle Utilities Analytics Warehouse.

Review Default Metadata Configuration

Some default metadata configurations can also be modified using the Oracle Utilities Analytics Warehouse Administration Tool. It is also mandatory to modify some default metadata configuration for the user customization. To know about default metadata configuration and how to modify them, follow the usage of Oracle Utilities Analytics Warehouse Administration Tool in the Administration topic.

Configure ETL Jobs

OUAW contains pre-built analytics for Oracle Utilities Analytics Framework (OUAF) and non-OUAF source applications. These source applications are listed below.

OUAF Source

  • Oracle Utilities Analytics Customer Care and Billing
  • Oracle Utilities Analytics Customer to Meter
  • Oracle Utilities Analytics Meter Data Management
  • Oracle Utilities Analytics Work and Asset Management
  • Oracle Utilities Analytics Mobile Workforce Management

Non-OUAF Source

  • Oracle Utilities Network Management System

If you have integrated one of these source system with OUAW, then perform the tasks below before the initial load of data into the data warehouse from each respective source.

Configure ETL Jobs

Oracle Data Integrator ETL jobs are configured at the time of integrating the source application with OUAW. These jobs are used to transform and load target dimensions, facts, and materialized views data from the replication layer into the target layer. Some ETL jobs are dependent on others. The dependency structure of target entities, and the order in which target entities should be loaded, are explained below.
  1. Make sure that all required ETL parameters and buckets are configured.
    • For an OUAF source application, the respective parameters and buckets must be configured in source application before the integration of source application. If not configured, then configure all required ETL parameters and buckets, and regenerate all replication views.
    • For a non-OUAF source application (specifically Oracle Utilities Network Management System), the respective parameters and buckets must be configured using the Oracle Utilities Analytics Warehouse Administration Tool after the integration of the source application. If not configured, then configure all required ETL parameters and buckets, and regenerate all replication views.
  2. All dimensions are independent. Therefore, all dimensions can be loaded simultaneously.
  3. Facts are dependent on dimensions. Therefore, facts should be loaded only after their dependent dimensions are loaded completely at the time of initial loading. Dependency is managed by the ETL framework. During incremental load, all facts will be loaded until the time their dependent dimensions are loaded.
  4. Accumulating fact contains only the latest version of a transaction or event. These types of facts are usually small and should be loaded fully, from the extract start date provided at the time of integration of the source application. The default slice duration is Yearly, but depending on the volume of data, the slice duration type can be modified from Yearly to Quarterly or Monthly, and so on.
  5. Periodic snapshot facts contain the latest version of a transaction or event within a snapshot period. If there are no modifications for a transaction or event after it is loaded into the target fact, then the same record will be loaded for all subsequent snapshot periods. The default slice duration type for most of the periodic snapshot facts are Monthly, but this can be modified to Quarterly or Weekly and so on, depending on the business need.
  6. Periodic snapshot fact used to report trends. If business users are only interested in analyzing trends for the last two years, then this fact should be loaded from the last two years. This will take less time to complete the loading, and it also reduces the storage cost. In this case, the slice start date should be modified to load data from the last two years.
  7. The slice duration type and slice start date can be modified using the Administration Tool. How to modify these metadata configurations is explain in the Administration topic.

Set Up the Scheduler

  1. Open Oracle Data Integrator Studio.
  2. Navigate to Designer > Load Plans and Scenarios > Framework > Scheduler > B1_RUN_ALL Version 001 > Scheduling.
  3. Right-click scheduling and select New Scheduling.
  4. Select the source context as global, the agent as WLS_AGENT, and the log level as 5.
  5. To specify how often the scheduler should run, navigate to the Execution Cycle tab and select Many Times.

  6. Set the interval between repetitions. For example, to set up the scheduler to run every five minutes, follow the screenshot below:

    Scheduler screen with the Execution Cyclo tab expanded. There are two sections in this tab. In the first section, Repetition, Many times option is selected, and the Interval between Repetitions field is set to five.

  7. Click Save.
  8. Navigate to Topology > Agents > OracleDIAgent.
  9. Right-click OracleDIAgent and click Update Schedule.

Run B1_INITIAL_SETUP_PKG Scenario

This scenario is used to populate the date and time dimension. This will insert two records into every dimension in the target schema. These two default records are necessary to handle missing and unassigned or unavailable dimension references in the fact.
  1. Open Oracle Data Integrator Studio.
  2. Navigate to Designer > Load Plans and Scenarios > Framework > Scheduler.
  3. Right-click B1_INITIAL_SETUP_PKG Version 001, and select Run.
  4. Select global as context, WLS_AGENT as logical agent, and then click OK.
  5. Provide the minimum date range to populate the CD_DATE dimension in YYYYMMDD format. Uncheck the option and provide the values in the option. Make sure that no transaction should be present before this date.
  6. Provide the maximum date range to populate the CD_DATE dimension in YYYYMMDD format. Uncheck the option and provide the values in the option. Make sure that you should populate the date dimension with advance entries for future dates. By default, this scenario will add date entries till December 31, 2050.
  7. Navigate to the Operator tab and make sure that this scenario is executed successfully.