Oracle Utilities Extractors and Schema for Oracle Utilities Meter Data Management

Data Mapping Guide

Release 2.5.2.0.1

E49010-07

June 2016

Oracle Utilities Extractors and Schema for Oracle Utilities Meter Data Management Data Mapping Guide

E49010-07

Copyright © 2016, Oracle and/or its affiliates. All rights reserved.

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

Preface

This guide provides the data mapping information from the Oracle Utilities Meter Data Management source system to the Oracle Utilities Extractors and Schema target product.

Audience

The guide is intended for all implementers of Oracle Utilities Extractors and Schema for Oracle Utilities Meter Data Management.

Related Documents

For more information, see the following documents:

•

Oracle Utilities Analytics Release Notes

•

Oracle Utilities Getting Started Guide

•

Oracle Utilities Analytics Quick Install Guide

•

Oracle Utilities Analytics Installation Guide

•

Oracle Utilities Analytics Administration Guide

•

Oracle Utilities Developer’s Guide

See Also :

•

Oracle Utilities Meter Data Management Documentation Library

Conventions

The following notational conventions are used in this document:

Notation

Indicates

boldface

Graphical user interface elements associated with an action, terms defined in text, or terms defines in the glossary

italic

Book titles, emphasis, or placeholder variables for which you supply particular values

monospace

Commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter

Acronyms

The following acronyms are used in this document:

•

MDM - Oracle Utilities Meter Data Management

•

MWM - Oracle Utilities Mobile Workforce Management

•

CCB - Oracle Utilities Customer Care and Billing

•

NMS - Oracle Utilities Network Management System

•

ETL - Extraction, Transformation, and Loading

•

ODI - Oracle Data Integrator

•

OBIEE - Oracle Business Intelligence Enterprise Edition

•

OUAF - Oracle Utilities Application Framework

Overview

This guide provides the data mapping information from the Oracle Utilities Meter Data Management source system to the Oracle Utilities Extractors and Schema target product, along with the rules of data transformation

Terminologies

This section describes the terminology used for data maps included in the document.

<Table Name>

The Table Name indicates the name of the fact or the dimension in a star schema in the data warehouse.

Properties

The Properties table lists properties of the table independent of each field. The following properties are listed in the table:

Property

Value

Target Table

Name of the table in the target schema (data warehouse) into which data is loaded

Table Type

Fact or Dimension

SCD Type

Type 1 - Existing records are updated directly. The nature of the dimension depends on how it handles changes made in the source system.

Type 2 - When changes occur, a new effective row is inserted. The existing records are deactivated by entering the current date as the effective end date and new records are inserted with an effective start date as today’s date. The new record will have the end date as a high date.

Fact Type

Whether this is a snapshot or accumulation fact table

Snapshot - Captures a snapshot view of the data as available in the source system during that period (monthly or weekly). Each snapshot's data is stored independently.

Accumulation - Data from the source system is accumulated periodically. Changes from source system will be merged with the existing data. Multiple copies of the same data will not be maintained.

Source System Driver Table

Name of the table in source database on which the data is primarily based

Source System Download Table

Name of the download table populated by the source batch program.

Note that this property is applicable only for the snapshot facts for which special batch programs have been designed in the source system for data extraction.

Stage Table

Name of the table in the staging schema (data warehouse) that can be used to query the data records generated by the ETL logic

Oracle Data Integrator Package

Name of the ODI package that needs to be executed to transform data from the replication layer and populate a specific target table in the data warehouse

ETL View

Resides in the replication layer and fetches the base values from the replication tables as a starting point for further transformations to be done by the Oracle Data Integrator interfaces

Materialized View

Names of the materialized views delivered with the product for a specific fact table

These materialized views are designed to support all of the OBIEE answers delivered with the product. Refreshing these materialized views will be taken care by separate ETL jobs.

Source Batch Program

The batch program that needs to be executed in the MDM system to extract the data into download tables. This is available only for the MDM snapshot facts.

Fields

The Fields table lists the individual properties of each field in the fact or dimension table. It includes the following columns:

Property

Value

Target Field

Name of the column in the fact or dimension table present in the data warehouse. The extracted/transformed data is loaded here.

Description

Description of the target field

Source Field

Name of the field from the source application used to load the target field either directly or after transformation

Transformation Logic

Specifies the details of how the data from the source field is transformed before being populated into the target field.

Field Categories

This table lists the categories under which the fields in a particular fact or dimension are grouped. The categories presented are as below:

Category

Details

Surrogate Key

The primary key on the fact/dimension table maintained within the data warehouse.

Natural Keys

The set of columns from the source system define the granularity of the fact or dimension table. The natural key columns from the source along with certain other data load attributes can be used to uniquely identify a record in the table.

The additional columns needed for the identification depend on the type of the table as listed below:

•

SCD Type 1 Dimension - Data Source Indicator

•

SCD Type 2 Dimension - Effective Start Date, Effective End Date, Data Source Indicator

•

Accumulation Fact - Data Source Indicator

•

Snapshot Fact - Snapshot Date, Data Source Indicator

Attributes

Hold descriptive data from the source system and are typically available in dimension tables.

Measures

Hold numerical value based data calculated from the source system data and are typically available on fact tables.

Degenerate Dimensions

Hold descriptive data from the source system and are typically available in fact tables. These cannot be grouped into any of the available dimensions.

Foreign Keys

Foreign key references on the fact tables to the dimension tables.

User Defined Attributes

Additional fields available to customers for extending the star schemas. These fields will not be populated out of the box. Customers need to customize the ETL and populate these fields.

Data Load Attributes

Hold the date/time information, job number details, etc, related to the ETL processes, that are needed for audit purposes.

Data Maps for Oracle Utilities Meter Data Management

This chapter contains data maps for the following Oracle Utilities Extractors and Schema for Oracle Utilities Meter Data Management tables:

•

Dimensions

•

Shared Dimensions

•

Facts

•

MDM Objects Referenced in Oracle Utilities Extractors and Schema

Dimensions

This section lists all the dimension tables that belong to the Oracle Utilities Meter Data Management star schemas only and are populated only from the Oracle Utilities Meter Data Management source product.

Consumption Snapshot Type Dimension

The Consumption Snapshot Type dimension defines the granularity of aggregated consumptions of a service point. It stores the values from the Oracle Utilities Meter Data Management lookup for Consumption Snapshot Type.

Properties

Property

Value

Target Table

CD_CONS_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_EXT_LOOKUP_VAL_L

Stage Table

STG_CD_CONS_TYPE

ODI Package

B1_PKG_CD_CONS_TYPE

ETL View

B1_D_CONS_TYPE _VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

CONS_TYPE_KEY

Consumption Snapshot Type Key

This field is populated from the OUBI_CONS_TYPE_SEQ sequence.

Natural Keys

CONS_SNAPSHOT_TYPE_CD

Consumption Snapshot Type Code

F1_EXT_LOOKUP_VAL_L.F1_EXT_LOOKUP_VALUE

This field is populated with values defined for the D2-ConsumptionSnapshotTypeLkup lookup business object.

Attributes

CONS_SNAPSHOT_TYPE_DESCR

Usage Snapshot Type

F1_EXT_LOOKUP_VAL_L.DESCR_OVRD

F1_EXT_LOOKUP_VAL_L.DESCR

If override description is not available, regular description is extracted.

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

UPDATE_DTTM

Update Date and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Contact Dimension

The Contact dimension includes a record for every contact in the Oracle Utilities Meter Data Management system.

Properties

Property

Value

Target Table

CD_CONTACT

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

D1_CONTACT

Stage Table

STG_CD_CONTACT

ODI Package

B1_PKG_CD_CONTACT

ETL View

B1_D_CONTACT_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

CONTACT_KEY

Contact Dimension Surrogate Key

This field is populated from the OUBI_CONTACT_SEQ sequence.

Natural Keys

SRC_CONTACT_ID

Contact ID

D1_CONTACT.CONTACT_ID

Attributes

CONTACT_INFO

Customer

D1_CONTACT_NAME.NAME_VALUE

D1_CONTACT_PHONE. PHONE_VALUE

This field is a concatenation of contact’s primary name and home/business phone information.

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Days Since Last Normal Measurement Dimension

The Days Since Last Normal Measurement dimension defines the age ranges for days since the last normal measurement was received. There is a record on the dimension for every Days Since Last Normal Measurement bucket defined in the Oracle Utilities Meter Data Management system.

Properties

Property

Value

Target Table

CD_DAYS_LAST_MSRMT

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_EXT_LOOKUP_VAL_L

Stage Table

STG_CD_DAYS_LAST_MSRMT

ODI Package

B1_PKG_CD_DAYS_LAST_MSRMT

ETL View

B1_D_DAYS_LAST_MSRMT_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

DAYS_LAST_MSRMT_KEY

Days Since Last Measurement Dimension Surrogate Key

This field is populated from the OUBI_DAYS_LAST_MSRMT_SEQ sequence.

Natural Keys

DAY_LAST_MSRMT_CD

Days Since Last Measurement Code

F1_EXT_LOOKUP_VAL_L.F1_EXT_LOOKUP_VALUE

This field is populated with values defined for the D1-DaysSinceLastNormalMsrmtLkp lookup business object.

Attributes

DAY_LAST_MSRMT_DESCR

Days Since Last Measurement

F1_EXT_LOOKUP_VAL_L.DESCR_OVRD

F1_EXT_LOOKUP_VAL_L.DESCR

If override description is not available, regular description is extracted.

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

UPDATE_DTTM

Update Date and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Days Since Last Usage Transaction Type Dimension

The Days Since Last Usage Transaction Type dimension defines the bucket definitions for the number of days since the last usage transaction was sent for the service point.

Properties

Property

Value

Target Table

CD_DAYS_LASTUT_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_EXT_LOOKUP_VAL_L

Stage Table

STG_CD_DAYS_LASTUT_TYPE

ODI Package

B1_PKG_CD_DAYS_LASTUT_TYPE

ETL View

B1_D_DAYS_LASTUT_TYPE_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

DAYS_LASTUT_TYPE_KEY

Days Since Last UT Type Dimension Surrogate Key

This field is populated from the OUBI_DAYS_LASTUT_TYPE_SEQ sequence.

Natural Keys

DAYS_LASTUT_TYPE_CD

Days Since Last UT Type Code

F1_EXT_LOOKUP_VAL_L.F1_EXT_LOOKUP_VALUE

This field is populated with values defined for the D2-DaysSinceLastUTLookup lookup business object.

Attributes

DAYS_LASTUT_TYPE_DESCR

Days Since Last UT Type

F1_EXT_LOOKUP_VAL_L.DESCR_OVRD

F1_EXT_LOOKUP_VAL_L.DESCR

If override description is not available, regular description is extracted.

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

UPDATE_DTTM

Update Date and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Meter Device Dimension

The Meter Device dimension stores the details of all the devices in the Oracle Utilities Meter Data Management system.

Properties

Property

Value

Target Table

CD_MTR_DEVICE

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

D1_DVC

Stage Table

STG_CD_MTR_DEVICE

ODI Package

B1_PKG_CD_MTR_DEVICE

ETL View

B1_D_MTR_DEVICE _VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

MTR_DEVICE_KEY

Meter Device Surrogate Key

This field is populated from the OUBI_MTR_DEVICE_SEQ sequence.

Natural Keys

SRC_MTR_DEVICE_ID

Device ID

D1_DVC.D1_DEVICE_ID

Attributes

MTR_DEVICE_TYPE_CD

Device Type Code

D1_DVC.DEVICE_TYPE_CD

MANUFACTURER_CD

Manufacturer Code

D1_DVC.MANUFACTURER_CD

MODEL_CD

Model Code

D1_DVC.D1_MODEL_CD

HEAD_END_CD

Head End System Code

D1_DVC.D1_SPR_CD

D1_DVC.D1_DVC_TYPE

D1_DVC.D1_SPR_CD

This field is populated with the override service provider stored on the device. If not found, it is populated with the fallback service provider on the device type.

DEVICE_CAT_CD

Device Category Code

F1_BUS_OBJ_OPT.BUS_OBJ_OPT_VAL

This field is populated with the device’s category defined on the business object’s option.

MTR_DEVICE_INFO

Device Information

This field is a concatenation of device comprising the device identifier, device type description, installation information, service provider, and device status.

MTR_DEVICE_TYPE_DESCR

Device Type Description

D1_DVC_TYPE_L.DESCR100

MANUFACTURER_DESCR

Manufacturer Description

D1_MANUFACTURER_L.DESCR100

MODEL_DESCR

Model Description

D1_MODEL_L.DESCR100

HEAD_END_DESCR

Head End System Description

D1_SPR_L.DESCR100

DEVICE_CAT_DESCR

Device Category Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

If override description is not available, regular description is extracted.

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_ END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Device Event BO Status Dimension

The Device Event BO Status dimension stores the device event statuses for various device event business objects in the Oracle Utilities Meter Data Management system.

Properties

Property

Value

Target Table

CD_DEVICE_EVT_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BUS_OBJF1_BUS_OBJ_STATUS_RSN

Stage Table

STG_CD_DEVICE_EVT_STATUS

ODI Package

B1_PKG_CD_DEVICE_EVT_STATUS

ETL View

B1_D_DEVICE_EVT_STATUS _VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

DEVICE_EVT_STATUS_KEY

Device Event Status Dimension Surrogate Key

This field is populated from the OUBI_DEVICE_EVT_STATUS_SEQ sequence.

Natural Keys

BUS_OBJ_CD

Business Object Code

F1_BUS_OBJ.BUS_OBJ_CD

All business objects of the device event maintenance object only are extracted.

STATUS_CD

Status Code

F1_BUS_OBJ_STATUS.BO_STATUS_CD

STATUS_RSN_CD

Status Reason Code

F1_BUS_OBJ_STATUS_RSN.BO_STATUS_REASON_CD

Attributes

STATUS_COND_CD

Status Condition Code

F1_BUS_OBJ_STATUS.BO_STATUS_COND_FLG

BUS_OBJ_DESCR

Business Object Description

F1_BUS_OBJ_L.DESCR

STATUS_DESCR

Status Description

F1_BUS_OBJ_STATUS_L.DESCR

STATUS_RSN_DESCR

Status Reason Description

F1_BUS_OBJ_STATUS_RSN_L.DESCR

STATUS_COND_DESCR

Status Condition Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the status condition description from the Lookup Language table for the lookup field BO_STATUS_COND_FLG.

If override description is not available, regular description is extracted.

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

UPDATE_DTTM

Update Date and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Device Activity Type Dimension

The Device Activity Type dimension table includes a record for every activity type in the Oracle Utilities Meter Data Management system.

Properties

Property

Value

Target Table

CD_DEVICE_ACTIVITY_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

D1_ACTIVITY_TYPE

Stage Table

STG_CD_DEVICE_ACTIVITY_TYPE

ODI Package

B1_PKG_CD_DEVICE_ACTIVITY_TYPE

ETL View

B1_D_DEVICE_ACTIVITY_TYPE_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

DEVICE_ACTIVITY_TYPE_KEY

Device Activity Type Dimension Surrogate Key

This field is populated from the OUBI_DEVICE_ACTIVITY_TYPE_SEQ sequence.

Natural Keys

DEVICE_ACTIVITY_TYPE_CD

Activity Type Code

D1_ACTIVITY_TYPE.ACTIVITY_TYPE_CD

Attributes

DEVICE_ACTIVITY_TYPE_INFO

Activity Type Information

D1_ACTIVITY_TYPE_L.DESCR100

DEVICE_ACTIVITY_CAT_CD

Activity Category Code

D1_ACTIVITY_TYPE.ACTIVITY_TYPE_CAT_FLG

EXT_DEVIC_ACT_TYPE_CD

External Device Activity Type Code

D1_ACTIVITY_TYPE.EXT_ACTIVITY_TYPE_FLG

DEVICE_ACTIVITY_CAT_DESCR

Activity Category Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the description from the Lookup Language table for ACTIVITY_TYPE_CAT_FLG lookup field.

If override description is not available, regular description is extracted.

EXT_DEVIC_ACT_TYPE_DESCR

External Device Activity Type Description

F1_EXT_LOOKUP_VAL_L.DESCR_OVRD

F1_EXT_LOOKUP_VAL_L.DESCR

This field is populated with the description from the Lookup Language table for the D1-ExternalActTypeIdentifier lookup business object.

If an override description is available, it will be extracted else the normal description will be extracted.

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

UPDATE_DTTM

Update Date and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Device Activity BO Status Dimension

The Device Activity BO Status dimension holds all business objects defined for activities and their statuses, along with the status reasons for each of them.

Properties

Property

Value

Target Table

CD_DEVICE_ACTIVITY_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BUS_OBJ

F1_BUS_OBJ_STATUS_RSN

Stage Table

STG_CD_DEVICE_ACTIVITY_STATUS

ODI Package

B1_PKG_CD_DEVICE_ACTIVITY_STATUS

ETL View

B1_D_DEVICE_ACTIVITY_STATUS_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

DEVICE_ACTIVITY_STATUS_KEY

Device Activity Status Dimension Surrogate Key

This field is populated from the OUBI_DEV_ACT_STATUS_SEQ sequence.

Natural Keys

BUS_OBJ_CD

Business Object Code

F1_BUS_OBJ.BUS_OBJ_CD

All business objects of the Activity maintenance object only are extracted.

STATUS_CD

Status Code

F1_BUS_OBJ_STATUS.BO_STATUS_CD

STATUS_RSN_CD

Status Reason Code

F1_BUS_OBJ_STATUS_RSN.BO_STATUS_REASON_CD

Attributes

BUS_OBJ_DESCR

Business Object Description

F1_BUS_OBJ_L.DESCR

STATUS_COND_CD

Status Condition Code

F1_BUS_OBJ_STATUS.BO_STATUS_COND_FLG

STATUS_DESCR

Status Description

F1_BUS_OBJ_STATUS_L.DESCR

STATUS_RSN_DESCR

Status Reason Description

F1_BUS_OBJ_STATUS_RSN_L.DESCR

STATUS_COND_DESCR

Status Condition Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the status condition description from the Lookup Language table for the BO_STATUS_COND_FLG lookup field.

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

UPDATE_DTTM

Update Date and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Device Event Type Dimension

The Device Event Type dimension stores a record for every device event type in the Oracle Utilities Meter Data Management system.

Properties

Property

Value

Target Table

CD_DEVICE_EVT_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

D1_DVC_EVT_TYPE

Stage Table

STG_CD_DEVICE_EVT_TYPE

ODI Package

B1_PKG_CD_DEVICE_EVT_TYPE

ETL View

B1_D_DEVICE_EVT_TYPE _VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

DEVICE_EVT_TYPE_KEY

Device Event Type Dimension Surrogate Key

This field is populated from the OUBI_DEVICE_EVT_TYPE_SEQ sequence.

Natural Keys

DEVICE_EVT_TYPE_CD

Event Type Code

D1_DVC_EVT_TYPE.DVC_EVT_TYPE_CD

Attributes

DEVICE_EVT_CAT_CD

Event Category Code

D1_DVC_EVT_TYPE.DVC_EVT_CAT_FLG

RPT_CAT_CD

Reporting Category Code

D1_DVC_EVT_TYPE.D1_REPORTING_CAT_FLG

STD_ACT_TYPE_CD

Standard Activity Type Code

D1_DVC_EVT_TYPE.STD_EVT_NAME_FLG

DEVICE_EVT_TYPE_INFO

Event Type Information

D1_DVC_EVT_TYPE_L.DESCR100

DEVICE_EVT_CAT_DESCR

Event Category Description

F1_EXT_LOOKUP_VAL_L.DESCR_OVRD

F1_EXT_LOOKUP_VAL_L.DESCR

This field is populated with the description from Lookup Language table for the D1-DvcEventCategoryLookup lookup business object.

If override description is not available, regular description is extracted.

RPT_CAT_DESCR

Reporting Category Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the description from the Lookup Language table for D1_REPORTING_CAT_FLG lookup field.

If override description is not available, regular description is extracted.

STD_ACT_TYPE_DESCR

Standard Activity Type Description

F1_EXT_LOOKUP_VAL_L.DESCR_OVRD

F1_EXT_LOOKUP_VAL_L.DESCR

This field is populated with the description from the Lookup Language table for the D1-StdEventNameLookup lookup business object.

If override description is not available, regular description is extracted.

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

UPDATE_DTTM

Update Date and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Exception Severity Dimension

The Exception Severity dimension holds the severity levels for exceptions that arise during the VEE processing of raw measurement data.

Properties

Property

Value

Target Table

CD_EXCP_SEV

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_LOOKUP_VAL_L

Stage Table

STG_CD_EXCP_SEV

ODI Package

B1_PKG_CD_EXCP_SEV

ETL View

B1_D_EXCP_SEV _VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

EXCP_SEV_KEY

Exception Severity Dimension Surrogate Key

This field is populated from the OUBI_EXCP_SEV_SEQ sequence.

Natural Keys

EXCP_SEV_CD

Exception Severity Code

CI_LOOKUP_VAL_L.FIELD_VALUE

This field is populated with values defined for the EXCP_SEVERITY_FLG lookup field.

Attributes

EXCP_SEV_DESCR

Exception Severity Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the description from Lookup Language table.

If override description is not available, regular description is extracted.

Data Load Attributes

UPDATE_DTTM

Update Date and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Exception Type Dimension

The Exception Type dimension holds the exception types for the exceptions that arise during the VEE processing of the raw measurement data.

Properties

Property

Value

Target Table

CD_EXCP_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

D1_EXCP_TYPE

Stage Table

STG_CD_EXCP_TYPE

ODI Package

B1_PKG_CD_EXCP_TYPE

ETL View

B1_D__EXCP_TYPE_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

EXCP_TYPE_KEY

Exception Type Dimension Surrogate Key

This field is populated from the OUBI_EXCP_TYPE_SEQ sequence.

Natural Keys

EXCP_TYPE_CD

Exception Type Code

D1_EXCP_TYPE.EXCP_TYPE_CD

Attributes

EXCP_TYPE_INFO

Exception Type Information

D1_EXCP_TYPE_L.DESCR100

RPT_CAT_CD

Reporting Category Code

D1_EXCP_TYPE.D1_REPORTING_CAT_FLG

RPT_CAT_DESCR

Exception Category Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the description from Lookup Language table for the D1_REPORTING_CAT_FLG lookup field.

If override description is not available, regular description is extracted.

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

UPDATE_DTTM

Update Date and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

IMD Type Dimension

The IMD Type dimension holds various types of raw initial measurement data.

Properties

Property

Value

Target Table

CD_IMD_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_LOOKUP_VAL_L

Stage Table

STG_CD_IMD_TYPE

ODI Package

B1_PKG_CD_IMD_TYPE

ETL View

B1_D_IMD_TYPE _VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

IMD_TYPE_KEY

IMD Type Dimension Surrogate Key

This field is populated from the OUBI_IMD_TYPE_SEQ sequence.

Natural Keys

IMD_TYPE_CD

Initial Measurement Type Code

CI_LOOKUP_VAL_L.FIELD_VALUE

This field is populated with values defined for the D1_IMD_TYPE_FLG lookup field.

Attributes

IMD_TYPE_DESCR

Initial Measurement Type Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the description from the Lookup Language table.

If override description is not available, regular description is extracted.

Data Load Attributes

UPDATE_DTTM

Update Date and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Installation Event BO Status Dimension

The Installation Event BO Status dimension holds all business objects defined for the install events and their statuses, along with the status reasons for each of them.

Properties

Property

Value

Target Table

CD_IE_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BUS_OBJF1_BUS_OBJ_STATUS_RSN

Stage Table

STG_CD_IE_STATUS

ODI Package

B1_PKG_CD_IE_STATUS

ETL View

B1_D_IE_STATUS_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

IE_STATUS_KEY

Install Event Status Dimension Surrogate Key

This field is populated from the OUBI_IE_STATUS_SEQ sequence.

Natural Keys

BUS_OBJ_CD

Business Object

F1_BUS_OBJ.BUS_OBJ_CD

All business objects of the Install Event maintenance object only are extracted.

STATUS_CD

Status Code

F1_BUS_OBJ_STATUS.BO_STATUS_CD

STATUS_RSN_CD

Status Reason Code

F1_BUS_OBJ_STATUS_RSN.BO_STATUS_REASON_CD

Attributes

BUS_OBJ_DESCR

Business Object Description

F1_BUS_OBJ_L.DESCR

STATUS_COND_CD

Status Condition Code

F1_BUS_OBJ_STATUS.BO_STATUS_COND_FLG

STATUS_DESCR

Status Description

F1_BUS_OBJ_STATUS_L.DESCR

STATUS_RSN_DESCR

Status Reason Description

F1_BUS_OBJ_STATUS_RSN_L.DESCR

STATUS_COND_DESCR

Status Condition Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the description from Lookup Language table for the BO_STATUS_COND_FLG lookup field.

If override description is not available, regular description is extracted.

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

UPDATE_DTTM

Update Date and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Measurement Condition Code Dimension

The Measurement Condition Code dimension holds all valid values for measurement conditions that can be mapped to consumption.

Properties

Property

Value

Target Table

CD_MSRMT_COND

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_EXT_LOOKUP_VAL

Stage Table

STG_CD_MSRMT_COND

ODI Package

B1_PKG_CD_MSRMT_COND

ETL View

B1_D_MSRMT_COND _VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

MSRMT_COND_KEY

Measurement Condition Dimension Surrogate Key

This field is populated from the OUBI_MSRMT_COND_SEQ sequence.

Natural Keys

MSRMT_COND_CD

Measurement Condition Code

F1_EXT_LOOKUP_VAL.F1_EXT_LOOKUP_VALUE

Attributes

MSRMT_COND_DESCR

Measurement Condition Description

F1_EXT_LOOKUP_VAL_L.DESCR_OVRD

F1_EXT_LOOKUP_VAL_L.DESCR

This field is populated with the description from Lookup Language table for the D1-MeasurementConditionLookup lookup business object.

If override description is not available, regular description is extracted.

MSRMT_COND_CAT_CD

Measurement Condition Category Code

F1_EXT_LOOKUP_VAL.BO_DATA_AREA

MSRMT_COND_CAT_DESCR

Measurement Condition Category Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the description from Lookup Language table for the MSRMT_COND_CAT_FLG lookup field.

If override description is not available, regular description is extracted.

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

UPDATE_DTTM

Update Date and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Measuring Component Dimension

The Measuring Component dimension holds the details of all measuring components in the Oracle Utilities Meter Data Management system.

Properties

Property

Value

Target Table

CD_MC

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

D1_MEASR_COMP

Stage Table

STG_CD_MC

ODI Package

B1_PKG_CD_MC

ETL View

B1_D_MC_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

MC_KEY

Measuring Component Key

This field is populated from the OUBI_MC_SEQ sequence.

Natural Keys

SRC_MC_ID

Measuring Component ID

D1_MEASR_COMP.MEASR_COMP_ID

Attributes

MC_INFO

Measuring Component Information

This is the concatenated information of measuring component, containing the device identifier, measuring component identifier, and measuring component type description from the device configuration, measuring component, and measuring component type tables respectively.

MC_TYPE_CD

Measuring Component Type Code

D1_MEASR_COMP.MEASR_COMP_TYPE_CD

SERVICE_TYPE_CD

Service Type Code

D1_MEASR_COMP_TYPE.D1_SVC_TYPE_CD

DEVICE_CONFIG_TYPE_CD

Device Configuration Type Code

D1_DVC_CFG.DEVICE_CONFIG_TYPE_CD

This field is populated with the device configuration type of the measuring component’s device configuration.

VEE_GRP_CD

VEE Group Code

D1_MEASR_COMP_VEE_GROUP.VEE_GRP_CD

D1_MEASR_COMP_TYP_FBK_VEE_GRP.VEE_GRP_CD

This is the override initial load VEE group of the measuring component.

If not found, it is populated with the fallback initial load VEE group of the measuring component type.

MC_TYPE_DESCR

Measuring Component Type Description

D1_MEASR_COMP_TYPE_L.DESCR100

SERVICE_TYPE_DESCR

Service Type Description

D1_SVC_TYPE_L.DESCR100

DEVICE_CONFIG_TYPE_DESCR

Device Configuration Type Description

D1_DVC_CFG_TYPE_L.DESCR100

VEE_GRP_DESCR

VEE Group Description

D1_VEE_GRP_L.DESCR100

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Service Point BO Status Dimension

The Service Point BO Status dimension holds all business objects defined for service points and their statuses, along with the status reasons for each of them.

Properties

Property

Value

Target Table

CD_SP_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BUS_OBJF1_BUS_OBJ_STATUS_RSN

Stage Table

STG_CD_SP_STATUS

ODI Package

B1_PKG_CD_SP_STATUS

ETL View

B1_D_SP_STATUS_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

SP_STATUS_KEY

Service Point Status Dimension Surrogate Key

This field is populated from the OUBI_SP_STATUS_SEQ sequence.

Natural Keys

BUS_OBJ_CD

Business Object Code

F1_BUS_OBJ.BUS_OBJ_CD

All business objects of the Service Point maintenance object only are extracted.

STATUS_CD

Status Code

F1_BUS_OBJ_STATUS.BO_STATUS_CD

STATUS_RSN_CD

Status Reason Code

F1_BUS_OBJ_STATUS_RSN.BO_STATUS_REASON_CD

Attributes

BUS_OBJ_DESCR

Business Object Description

F1_BUS_OBJ_L.DESCR

STATUS_COND_CD

Status Condition Code

F1_BUS_OBJ_STATUS.BO_STATUS_COND_FLG

STATUS_DESCR

Status Description

F1_BUS_OBJ_STATUS_L.DESCR

STATUS_RSN_DESCR

Status Reason Description

F1_BUS_OBJ_STATUS_RSN_L.DESCR

STATUS_COND_DESCR

Status Condition Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the description from Lookup Language table for the BO_STATUS_COND_FLG lookup field.

If override description is not available, regular description is extracted.

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

UPDATE_DTTM

Update Date and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Service Point Dimension

The Service Point dimension stores the details of all service points in the Oracle Utilities Meter Data Management system.

Properties

Property

Value

Target Table

CD_SP

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

D1_SP

Stage Table

STG_CD_SP

ODI Package

B1_PKG_CD_SP

ETL View

B1_D_SP_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

SP_KEY

Service Point Dimension Surrogate Key

This field is populated from the OUBI_SP_SEQ sequence.

Natural Keys

SRC_SP_ID

Service Point ID

D1_SP.D1_SP_ID

Attributes

SERVICE_TYPE_DESCR

Service Type Description

D1_SVC_TYPE_L.DESCR100

SERVICE_TYPE_CD

Service Type Code

D1_SP_TYPE.D1_SVC_TYPE_CD

SP_TYPE_CD

Service Point Type Code

D1_SP.D1_SP_TYPE_CD

MARKET_CD

Market Code

D1_SP.MKT_CD

FAC_LVL1_CD

Facility Level 1 Code

FAC_LVL2_CD

Facility Level 2 Code

FAC_LVL3_CD

Facility Level 3 Code

SP_INFO

Service Point Information

This field is the concatenation of service point, containing the address, service point type description, measurement cycle description, measurement route description, and service point status.

SP_TYPE_DESCR

Service Point Type Description

D1_SP_TYPE_L.DESCR100

MARKET_DESCR

Market Description

D1_MKT_L.DESCR100

FAC_LVL1_DESCR

Facility Level 1 Description

FAC_LVL2_DESCR

Facility Level 2 Description

FAC_LVL3_DESCR

Facility Level 3 Description

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Service Provider Dimension

The Service Provider dimension holds all the service providers defined in the Oracle Utilities Meter Data Management system.

Properties

Property

Value

Target Table

CD_SPR

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

D1_SPR

Stage Table

STG_CD_SPR

ODI Package

B1_PKG_CD_SPR

ETL View

B1_D_SPR_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

SPR_KEY

Service Provider Dimension Surrogate Key

This field is populated from the OUBI_SPR_SEQ sequence.

Natural Keys

SPR_CD

Service Provider Code

D1_SPR.D1_SPR_CD

Attributes

SPR_INFO

Service Provider Information

D1_SPR_L.DESCR100

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

UPDATE_DTTM

Update Date and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Unreported Usage Analysis Snapshot Type Dimension

The Unreported Usage Analysis Snapshot dimension holds various types of unreported usage snapshots that can be extracted.

Properties

Property

Value

Target Table

CD_SP_UT_AGE_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_EXT_LOOKUP_VAL_L

Stage Table

STG_CD_SP_UT_AGE_TYPE

ODI Package

B1_PKG_CD_SP_UT_AGE_TYPE

ETL View

B1_D_SP_UT_AGE_TYPE_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

SP_UT_AGE_TYPE_KEY

Unreported Usage Aging Type Dimension Surrogate Key

This field is populated from the OUBI_SP_UT_AGE_SEQ sequence.

Natural Keys

SP_UT_AGE_TYPE_CD

Unreported Usage Aging Type Code

F1_EXT_LOOKUP_VAL_L.F1_EXT_LOOKUP_VALUE

This field extracts the values defined for D2-SPUTAgingSnapshotTypeLookup lookup business object.

Attributes

SP_UT_AGE_TYPE_DESC

Unreported Usage Aging Type Description

F1_EXT_LOOKUP_VAL_L.DESCR_OVRD

F1_EXT_LOOKUP_VAL_L.DESCR

This field is populated with the description from the Lookup Language table.

If override description is not available, regular description is extracted.

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

UPDATE_DTTM

Update Date and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

UOM/ TOU Dimension

UOM/ TOU dimension holds all valid combinations of unit of measure and time of use values based on every consumption snapshot type on the service point types from the Oracle Utilities Meter Data Management system.

Properties

Property

Value

Target Table

CD_UOM_TOU

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

D1_SP_TYPE

Stage Table

STG_CD_UOM_TOU

ODI Package

B1_PKG_CD_UOM_TOU

ETL View

B1_D_UOM_TOU _VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

UOM_TOU_KEY

UOM TOU Dimension Surrogate Key

This field is populated from the OUBI_UOM_TOU_SEQ sequence.

Natural Keys

UOM_CD

Unit of Measure Code

F1_EXT_LOOKUP_VAL.BO_DATA_AREA

This field is populated with all target unit of measures configured on the consumption snapshot type.

TOU_CD

Time of Use Code

D1_TOU_GRP_TOU.D1_TOU_CD

This field is populated with the time of use.

The time of use codes are from the TOU codes on the TOU map template referenced on the TOU map in the consumption snapshot type.

Attributes

UOM_INFO

Unit of Measure Information

D1_UOM_L.DESCR100

TOU_INFO

Time of Use Information

D1_TOU_L.DESCR100

SERVICE_TYPE_CD

Service Type Code

D1_UOM.D1_SVC_TYPE_CD

SERVICE_TYPE_DESCR

Service Type Description

D1_SVC_TYPE_L.DESCR100

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

UPDATE_DTTM

Update Date and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

UOM/ TOU/ SQI Dimension

UOM/ TOU/ SQI dimension holds all valid combinations of unit of measure, time of use, and service quantity identifier values based on every unreported usage snapshot rules configured on the service point types in the Oracle Utilities Meter Data Management system.

Properties

Property

Value

Target Table

CD_UOM_TOU_SQI

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

D1_SP_TYPE

Stage Table

STG_CD_UOM_TOU_SQI

ODI Package

B1_PKG_CD_UOM_TOU_SQI

ETL View

B1_D_UOM_TOU_SQI_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

UOM_TOU_SQI_KEY

UOM TOU SQI Dimension Surrogate Key

This field is populated from the OUBI_UOM_TOU_SQI_SEQ sequence.

Natural Keys

UOM_CD

Unit of Measure Code

D1_SP_TYPE.BO_DATA_AREA

This field is populated with unit of measure code from the Service Point Type table. The value is configured as part of the Unreported Usage Analysis Snapshot Type configuration on the service point type.

If not found, it is populated with ‘***’.

TOU_CD

Time of Use Code

D1_SP_TYPE.BO_DATA_AREA

This field is populated with time of use code from the Service Point Type table. The value is configured as part of the Unreported Usage Analysis Snapshot Type configuration on the service point type.

If not found, it is populated with ‘***’.

SQI_CD

Service Quantity Identifier Code

D1_SP_TYPE.BO_DATA_AREA

This field is populated with service quantity identifier code from the Service Point Type table. The value is configured as part of the Unreported Usage Analysis Snapshot Type configuration on the service point type.

If not found, it is populated with ‘***’.

Attributes

UOM_INFO

Unit of Measure Information

D1_UOM _L.DESCR100

TOU_INFO

Time of Use Information

D1_TOU _L.DESCR100

This field is populated with the description of the TOU.

SQI_INFO

Service Quantity Identifier Information

D1_SQI_L.DESCR100

SERVICE_TYPE_CD

Service Type Code

D1_UOM.D1_SVC_TYPE_CD

SERVICE_TYPE_DESCR

Service Type Description

D1_SVC_TYPE_L.DESCR100

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

UPDATE_DTTM

Update Date and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Usage Calculation Group Dimension

The Usage Calculation Group dimension holds all usage groups in the Oracle Utilities Meter Data Management system.

Properties

Property

Value

Target Table

CD_USAGE_GROUP

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

D1_USG_GRP_L

Stage Table

STG_CD_USAGE_GROUP

ODI Package

B1_PKG_CD_USAGE_GROUP

ETL View

B1_D_USAGE_GROUP_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

USAGE_GROUP_KEY

Usage Group Dimension Surrogate Key

This field is populated from the OUBI_USAGE_GROUP_SEQ sequence.

Natural Keys

USAGE_GROUP_CD

Usage Group Code

D1_USG_GRP_L.USG_GRP_CD

Attributes

USAGE_GROUP_INFO

Usage Group Description

D1_USG_GRP_L.DESCR100

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

UPDATE_DTTM

Update Date and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Usage Subscription Dimension

The Usage Subscription dimension stores the details of all usage subscriptions in the Oracle Utilities Meter Data Management system.

Properties

Property

Value

Target Table

CD_US

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

D1_US

Stage Table

STG_CD_US

ODI Package

B1_PKG_CD_US

ETL View

B1_D_US _VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

US_KEY

Usage Subscription Dimension Surrogate Key

This field is populated from the OUBI_US_SEQ sequence.

Natural Keys

SRC_US_ID

Usage Subscription ID

D1_US.US_ID

Attributes

US_INFO

Usage Subscription Information

This field contains the concatenated information of the usage subscription, containing the contact name, contact number, usage subscription type description, start date, end date, and status.

US_TYPE_DESCR

Usage Subscription Type Description

D1_US_TYPE_L.DESCR100

US_TYPE_CD

Usage Subscription Type Code

D1_US.US_TYPE_CD

EXT_ID

External ID

D1_US_IDENTIFIER.ID_VALUE

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

VEE Rule Dimension

The VEE Rule dimension stores details of all VEE rules defined in the Oracle Utilities Meter Data Management system.

Properties

Property

Value

Target Table

CD_VEE_RULE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

D1_VEE_RULE

Stage Table

STG_CD_VEE_RULE

ODI Package

B1_PKG_CD_VEE_RULE

ETL View

B1_D_VEE_RULE _VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

VEE_RULE_KEY

VEE Rule Dimension Surrogate Key

This field is populated from the OUBI_VEE_RULE_SEQ sequence.

Natural Keys

VEE_GRP_CD

VEE Group Code

D1_VEE_RULE.VEE_GRP_CD

VEE_RULE_CD

VEE Rule Code

D1_VEE_RULE.VEE_RULE_CD

Attributes

VEE_GRP_INFO

VEE Group Information

VEE_RULE_CD.DESCR100

VEE_RULE_INFO

VEE Rule Information

D1_VEE_RULE_L.DESCR100

D1_VEE_RULE.START_DT

D1_VEE_RULE.END_DT

This field is populated with concatenated information of the VEE rule including its description, start date, and end date.

VEE_RULE_CAT_DESCR

VEE Rule Category Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the description from Lookup Language table for VEE_RULE_CAT_FLG lookup field.

If override description is not available, regular description is extracted.

VEE_RULE_CAT_CD

VEE Rule Category Code

D1_VEE_RULE.VEE_RULE_CAT_FLG

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

UPDATE_DTTM

Update Date and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Shared Dimensions

The dimensions that are used in the star schemas of other source products as well are called the shared dimensions. The other products also populate these shared dimensions with their data. The data source indicator (DSI) column plays a vital role in identifying the records belonging to a specific source product.

Account Dimension

Though Account dimension is referenced in the Oracle Utilities Meter Data Management facts, it is populated only by the Oracle Utilities Customer Care and Billing source product. The ETL processes of the Oracle Utilities Meter Data Management facts map this dimension based on the integration between Oracle Utilities Meter Data Management and Oracle Utilities Customer Care and Billing.

Since this dimension is not populated by Oracle Utilities Meter Data Management, the source mapping details are not provided in the document. See the Oracle Utilities Extractors and Schema for Oracle Utilities Customer Care and Billing for the mapping details.

Address Dimension

The Address dimension includes a record for every service point’s address in the Oracle Utilities Meter Data Management system.

This dimension is also populated by Oracle Utilities Operational Device Management, Oracle Utilities Mobile Workforce Management, Oracle Utilities Customer Care and Billing, Oracle Utilities Network Management System, and Oracle Utilities Work and Asset Management.

Properties

Property

Value

Target Table

CD_ADDR

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

D1_SP

Stage Table

STG_CD_ADDR

ODI Package

B1_PKG_CD_ADDR

ETL View

B1_D_ADDR_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

ADDR_KEY

Address Dimension Surrogate Key

This field is populated from the SPL_ADDR_SEQ sequence.

Natural Keys

SRC_ADDR_ID

Service Point ID

D1_SP.D1_SP_ID

Attributes

ADDR_LINE1

Address Line 1

D1_SP.ADDRESS1

ADDR_LINE2

Address Line 2

D1_SP.ADDRESS2

ADDR_LINE3

Address Line 3

D1_SP.ADDRESS3

ADDR_LINE4

Address Line 4

D1_SP.ADDRESS4

ADDR_INFO

Address

D1_SP. ADDRESS1

D1_SP.CITY

D1_SP.STATE

D1_SP.POSTAL

This field is a concatenation of address, city, state, and postal code, separated by comma.

CITY

City

D1_SP.CITY_UPPER

COUNTY

County

D1_SP.COUNTY

POSTAL

Postal Code

D1_SP. POSTAL

STATE_DESCR

State Description

CI_STATE_L.DESCR

COUNTRY_DESCR

Country Description

CI_COUNTRY_L.DESCR

GEO_CODE

Geographical Code

D1_SP.GEO_CODE

CROSS_STREET

Cross Street

Note : This field is populated only for the Oracle Utilities Mobile Workforce Management source product.

SUBURB

Suburb

Note : This field is populated only for the Oracle Utilities Mobile Workforce Management source product.

User Defined Attributes

UDF1_CD

City Code

D1_SP.CITY_UPPER

UDF1_DESCR

City Description

D1_SP.CITY_UPPER

UDF2_CD

County Code

D1_SP.COUNTY

UDF2_DESCR

County Code

D1_SP.COUNTY

UDF3_CD

Postal Code

D1_SP.POSTAL

UDF3_DESCR

Postal Code

D1_SP.POSTAL

UDF4_CD

State Code

D1_SP.STATE

UDF4_DESCR

State Description

CI_STATE_L.DESCR

UDF5_CD

Country Code

D1_SP.COUNTRY

UDF5_DESCR

Country Description

CI_COUNTRY_L.DESCR

UDF6_CD

Geo Code Value

D1_SP.GEO_CODE

UDF6_DESCR

Geo Code Description

D1_SP.GEO_CODE

UDF7_CD

Premise Char 1 Code

UDF7_DESCR

Premise Char 1 Description

UDF8_CD

Premise Char 2 Code

UDF8_DESCR

Premise Char 2 Description

UDF9_CD

Premise Char 3 Code

UDF9_DESCR

Premise Char 3 Description

UDF10_CD

Premise Char 4 Code

UDF10_DESCR

Premise Char 4 Description

UDF11_CD

User Defined Field 11 Code

UDF11_DESCR

User Defined Field 11 Description

UDF12_CD

User Defined Field 12 Code

UDF12_DESCR

User Defined Field 12 Description

UDF13_CD

User Defined Field 13 Code

UDF13_DESCR

User Defined Field 13 Description

UDF14_CD

User Defined Field 14 Code

UDF14_DESCR

User Defined Field 14 Description

UDF15_CD

User Defined Field 15 Code

UDF15_DESCR

User Defined Field 15 Description

UDF16_CD

User Defined Field 16 Code

UDF16_DESCR

User Defined Field 16 Description

Data Load Attributes

EFF_END_DTTM

Effective End Date/Time

EFF_START_DTTM

Effective Start Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Person Dimension

Though Person dimension is referenced in the Oracle Utilities Meter Data Management facts, it is populated only by the Oracle Utilities Customer Care and Billing source product. The ETL processes of the Oracle Utilities Meter Data Management facts map this dimension based on the integration between Oracle Utilities Meter Data Management and Oracle Utilities Customer Care and Billing.

Since this dimension is not populated by Oracle Utilities Meter Data Management, the source mapping details are not provided in the document. See the Oracle Utilities Extractors and Schema for Oracle Utilities Customer Care and Billing for the mapping details.

Premise Dimension

Though Premise dimension is referenced in the Oracle Utilities Meter Data Management facts, it is populated only by the Oracle Utilities Customer Care and Billing source product. The ETL processes of the Oracle Utilities Meter Data Management facts map this dimension based on the integration between Oracle Utilities Meter Data Management and Oracle Utilities Customer Care and Billing.

Since this dimension is not populated by Oracle Utilities Meter Data Management, the source mapping details are not provided in the document. See the Oracle Utilities Extractors and Schema for Oracle Utilities Customer Care and Billing for the mapping details.

Service Agreement Dimension

Though Service Agreement dimension is referenced in the Oracle Utilities Meter Data Management facts, it is populated only by the Oracle Utilities Customer Care and Billing source product. The ETL processes of the Oracle Utilities Meter Data Management facts map this dimension based on the integration between Oracle Utilities Meter Data Management and Oracle Utilities Customer Care and Billing.

Since this dimension is not populated by Oracle Utilities Meter Data Management, the source mapping details are not provided in the document. See the Oracle Utilities Extractors and Schema for Oracle Utilities Customer Care and Billing for the mapping details.

Facts

Device Activity Fact

An activity is a record of a communication or event related to a device, measuring component, or another entity in the system. The Device Activity fact stores details about all activities in Oracle Utilities Meter Data Management system including their duration information.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_DEVICE_ACTIVITY

Table Type

Fact

Fact Type

Accumulation

Source System Driver Table

D1_ACTIVITY

D1_DVC_CFG

D1_INSTALL_EVT

D1_SP

D1_US

Stage Table Name

STG_CF_DEVICE_ACTIVITY

ODI Package Name

B1_PKG_CF_DEVICE_ACTIVITY

ETL View Name

B1_F_DEVICE_ACTIVITY_VW

Materialized View Name

B1_DEVICE_ACTIVITY_MON_MV1

B1_DEV_ACT_TOPX_MON_MV1

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

DEVICE_ACTIVITY_KEY

Device Activity Key

This field is populated from the OUBI_DEVICE_ACTIVITY_SEQ sequence.

Natural Keys

SRC_DEVICE_ACTIVITY_ID

Device Activity ID

D1_ACTIVITY.D1_ACTIVITY_ID

Only those activities with activity type category in the list of activity type categories configured for inclusion via the extract parameters are extracted.

Measures

DURATION

Duration

D1_ACTIVITY.END_DTTM

D1_ACTIVITY.START_DTTM

This field is populated with the difference between end date/time and start date/time of the activity. For incomplete activities the current date/time is considered as the end date/time. The unit of measure is seconds.

FACT_CNT

Fact Count

This field is populated with the standard value of ‘1’.

Degenerate Dimensions

START_DTTM

Start Date/Time

D1_ACTIVITY.START_DTTM

END_DTTM

End Date/Time

D1_ACTIVITY.END_DTTM

Foreign Keys

DEVICE_ACTIVITY_STATUS_KEY

Device Activity Status Dimension Surrogate Key

D1_ACTIVITY.D1_ACTIVITY.BUS_OBJ_CD

D1_ACTIVITY.D1_ACTIVITY.BO_STATUS_CD

D1_ACTIVITY.BO_STATUS_REASON_CD

This field is populated based on the device activity status.

MTR_DEVICE_KEY

Meter Device Dimension Surrogate Key

D1_ACTIVITY_REL_OBJ.PK_VALUE1

D1_DVC_CFG.D1_DEVICE_ID

This field is populated based on the device ID associated with the activity via its related object collection.

If there is no device ID associated with the activity, then the device ID is derived from the measuring component or service point associated with the activity.

MC_KEY

Measuring Component Dimension Surrogate Key

D1_ACTIVITY_REL_OBJ.PK_VALUE1

This field is populated based on the measuring component ID associated with the activity via its related object collection.

SPR1_KEY

Service Provider 1 Dimension Surrogate Key

D1_SP_MKT_PARTICIPANT.D1_SPR_CD

D1_MKT_FALLBACK_SPR.D1_SPR_CD

This field is populated based on the service point ID associated with the activity.

The service provider is retrieved from the service point or from the fallback service provider on the service point’s market. The market relationship type used is the first one configured via the extract parameters.

SPR2_KEY

Service Provider 2 Dimension Surrogate Key

D1_SP_MKT_PARTICIPANT.D1_SPR_CD

D1_MKT_FALLBACK_SPR.D1_SPR_CD

This field is populated based on the service point ID associated with the activity.

The service provider is retrieved from the service point or from the fallback service provider on the service point’s market. The market relationship type used is the second one configured via the extract parameters.

SP_KEY

Service Point Dimension Surrogate Key

D1_ACTIVITY_REL_OBJ.PK_VALUE1

D1_INSTALL_EVT.D1_SP_ID

This field is populated based on the service point ID associated with the activity via its related object collection.

If there is no service point ID associated with the activity, then the service point ID is derived from the device associated with the activity.

ADDR_KEY

Address Dimension Surrogate Key

D1_ACTIVITY_REL_OBJ.PK_VALUE1

D1_INSTALL_EVT.D1_SP_ID

This field is populated based on the service point ID associated with the activity.

US1_KEY

Usage Subscription 1 Dimension Surrogate Key

D1_US_SP.US_ID

This field is populated based on the service point associated with the activity.

It is populated with the usage subscription linked to the service point, whose usage subscription type’s subscription type matches the first subscription type defined on the extract parameters.

US2_KEY

Usage Subscription 2 Dimension Surrogate Key

D1_US_SP.US_ID

This field is populated based on the service point associated with the activity.

It is populated with the usage subscription linked to the service point, whose usage subscription type’s subscription type matches the first subscription type defined on the extract parameters.

USAGE_GROUP_KEY

Usage Group Dimension Surrogate Key

D1_US_USG_GRP_DL.USG_GRP_CD

This field is populated based on the usage group of the CCB-related usage subscription linked to the service point associated with the activity.

CONTACT_KEY

Contact Dimension Surrogate Key

D1_US_CONTACT.CONTACT_ID

This field is populated with the primary contact Id of the CCB-related usage subscription linked to the SP associated with the activity.

DEVICE_ACTIVITY_TYPE_KEY

Device Activity Type Dimension Surrogate Key

D1_ACTIVITY.ACTIVITY_TYPE_CD

This field is populated based on the device activity type code associated with the activity.

START_DATE_KEY

Start Date (Date Dimension Surrogate Key)

D1_ACTIVITY.START_DTTM

This field is populated based on the activity start date.

START_TIME_KEY

Start Time Dimension Surrogate Key

D1_ACTIVITY.START_DTTM

This field is populated based on the activity start time.

END_DATE_KEY

End Date (Date Dimension Surrogate Key)

D1_ACTIVITY.END_DTTM

This field is populated based on the activity end date.

END_TIME_KEY

End Time (Time Dimension Surrogate Key)

D1_ACTIVITY.END_DTTM

This field is populated based on the activity end time.

PER_KEY

Person Dimension Surrogate Key

D1_CONTACT_IDENTIFIER.ID_VALUE

This field is populated based on the external person ID stored on the activity’s contact identifiers.

ACCT_KEY

Account Dimension Surrogate Key

D1_US_IDENTIFIER.ID_VALUE

This field is populated based on the external account ID stored on the activity’s CCB-related usage subscription’s identifiers.

SA_KEY

Service Agreement Dimension Surrogate Key

D1_US_IDENTIFIER.ID_VALUE

This field is populated based on the external service agreement ID stored on the activity’s CCB-related usage subscription’s identifiers.

PREM_KEY

Premise Dimension Surrogate Key

D1_SP_IDENTIFIER.ID_VALUE

This field is populated based on the external premise ID stored on the activity’s service point’s identifiers.

DEVICE_ACTIVITY_UDD1_KEY

Device Activity User Defined Dimension 1 Surrogate Key

DEVICE_ACTIVITY_UDD2_KEY

Device Activity User Defined Dimension 2 Surrogate Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDGEN4

User Defined Degenerate Dimension 4

UDDGEN5

User Defined Degenerate Dimension 5

UDDGENL1

User Defined Long Degenerate Dimension 1

UDDGENL2

User Defined Long Degenerate Dimension 2

UDDGENL3

User Defined Long Degenerate Dimension 3

UDDGENL4

User Defined Long Degenerate Dimension 4

UDDGENL5

User Defined Long Degenerate Dimension 5

UDDFK1_KEY

User Defined Dimension Foreign Key 1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Device Event Fact

Device events are events of some sort that have taken place relative to a device, and can include power outages, power restorations, tampering alerts, command completions, and other events. The Device Event fact stores details about all device events in the Oracle Utilities Meter Data Management system including their duration information.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_DEVICE_EVT

Table Type

Fact

Fact Type

Accumulation

Source System Driver Table

D1_DVC_EVT

D1_DVC_CFG

D1_INSTALL_EVT

D1_SP

D1_US

Stage Table Name

STG_CF_DEVICE_EVT

ODI Package Name

B1_PKG_CF_DEVICE_EVT

ETL View Name

B1_F_DEVICE_EVT_VW

Materialized View Name

B1_DEVICE_EVT_MON_MV1

B1_DEVICE_EVT_MON_TOPX_MV1

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

DEVICE_EVT_KEY

Device Event Key

This field is populated from the OUBI_DEVICE_EVT_SEQ sequence.

Natural Keys

SRC_DEVICE_EVT_ID

Device Event ID

D1_DVC_EVT.DVC_EVT_ID

This field is populated with the primary keys of all device events from the Device Event table.

Only those device events whose business object codes are not present in the list of device event business objects to exclude as configured in the extract parameters are extracted.

Measures

DURATION

Duration

D1_DVC_EVT.DVC_EVT_END_DTTM

D1_DVC_EVT.DVC_EVT_DTTM

This field is populated with the difference between end date/time and start date/time of the device event.

For incomplete events the current date/time is considered as the end date/time. Unit of measure is seconds.

FACT_CNT

Fact Count

This field is populated with the standard value of ‘1’.

Degenerate Dimensions

START_DTTM

Start Date/Time

D1_DVC_EVT.DVC_EVT_DTTM

END_DTTM

End Date/Time

D1_DVC_EVT.DVC_EVT_END_DTTM

Foreign Keys

DEVICE_EVT_STATUS_KEY

Device Event Status Dimension Surrogate Key

D1_DVC_EVT.D1_DVC_EVT.BUS_OBJ_CD

D1_DVC_EVT.D1_DVC_EVT.BO_STATUS_CD

D1_DVC_EVT.BO_STATUS_REASON_CD

This field is populated based on the current device event status.

MTR_DEVICE_KEY

Meter Device Dimension Surrogate Key

D1_DVC_EVT.D1_DEVICE_ID

D1_DVC_EVT_REL_OBJ.PK_VALUE1

D1_DVC_CFG.D1_DEVICE_ID

This field is populated based on the device ID associated with the device event (on the device event itself or via its related object collection.

If there is no device ID on the device event, the device ID is derived from the measuring component or service point associated with the device event.

MC_KEY

Measuring Component Dimension Surrogate Key

D1_DVC_EVT_REL_OBJ.PK_VALUE1

This field is populated based on the measuring component associated with the device event via its related object collection.

SPR_KEY

Service Provider Dimension Surrogate Key

D1_SP_MKT_PARTICIPANT.D1_SPR_CD

D1_MKT_FALLBACK_SPR.D1_SPR_CD

This field is populated based on the service point ID associated with the device event. The service provider is retrieved from the service point or from the fallback service provider on the service point’s market. The market relationship type used is the first one configured via extract parameters.

SP_KEY

Service Point Dimension Surrogate Key

D1_DVC_EVT_REL_OBJ.PK_VALUE1

D1_INSTALL_EVT.D1_SP_ID

This field is populated based on the service point associated with the device event via its related object collection.

If there is no service point ID associated with the device event, then the service point ID is derived from the device associated with the device event.

ADDR_KEY

Address Dimension Surrogate Key

D1_DVC_EVT_REL_OBJ.PK_VALUE1

D1_INSTALL_EVT.D1_SP_ID

This field is populated based on the service point associated with the device event.

US1_KEY

Usage Subscription 1 Dimension Surrogate Key

D1_US_SP.US_ID

This field is populated based on the usage subscription linked to the service point associated with the device event. It is populated with the usage subscription linked to the service point, whose usage subscription type’s subscription type matches the first subscription type defined on the extract parameters.

US2_KEY

Usage Subscription 2 Dimension Surrogate Key

D1_US_SP.US_ID

This field is populated based on the usage subscription linked to the service point associated with the device event. It is populated with the usage subscription linked to the service point, whose usage subscription type’s subscription type matches the first subscription type defined on the extract parameters.

USAGE_GROUP_KEY

Usage Group Dimension Surrogate Key

D1_US_USG_GRP_DL.USG_GRP_CD

This field is populated based on the usage group of the CCB-related usage subscription linked to the service point associated with the activity.

CONTACT_KEY

Contact Dimension Surrogate Key

D1_US_CONTACT.CONTACT_ID

This field is populated with the primary contact Id of the CCB-related usage subscription linked to the service point associated with the activity.

DEVICE_EVT_TYPE_KEY

Device Event Type Dimension Surrogate Key

D1_DVC_EVT.DVC_EVT_TYPE_CD

This field is populated based on the device event type code associated with the activity.

START_DATE_KEY

Start Date (Date Dimension Surrogate Key)

D1_DVC_EVT.DVC_EVT_DTTM

This field is populated based on the activity start date.

START_TIME_KEY

Start Time (Time Dimension Surrogate Key)

D1_DVC_EVT.DVC_EVT_DTTM

This field is populated based on the device event start time.

END_DATE_KEY

End Date (Date Dimension Surrogate Key)

D1_DVC_EVT.DVC_EVT_END_DTTM

This field is populated based on the device event end date.

END_TIME_KEY

End Time (Time Dimension Surrogate Key)

D1_DVC_EVT.DVC_EVT_END_DTTM

This field is populated based on the device event end time.

PER_KEY

Person Dimension Surrogate Key

D1_CONTACT_IDENTIFIER.ID_VALUE

This field is populated based on the External Person ID stored on the activity’s contact identifiers.

ACCT_KEY

Account Dimension Surrogate Key

D1_US_IDENTIFIER.ID_VALUE

This field is populated based on the External Account ID stored on the activity’s CCB-related usage subscription’s identifiers

SA_KEY

Service Agreement Dimension Surrogate Key

D1_US_IDENTIFIER.ID_VALUE

This field is populated based on the External Service Agreement ID stored on the activity's CCB-related usage subscription's identifiers.

PREM_KEY

Premise Dimension Surrogate Key

D1_SP_IDENTIFIER.ID_VALUE

This field is populated based on the External Premise ID stored on the activity's service point's identifiers.

DEVICE_EVT_UDD1_KEY

Device Event User Defined Dimension 1 Surrogate Key

DEVICE_EVT_UDD2_KEY

Device Event User Defined Dimension 2 Surrogate Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDGEN4

User Defined Degenerate Dimension 4

UDDGEN5

User Defined Degenerate Dimension 5

UDDGENL1

User Defined Long Degenerate Dimension 1

UDDGENL2

User Defined Long Degenerate Dimension 2

UDDGENL3

User Defined Long Degenerate Dimension 3

UDDGENL4

User Defined Long Degenerate Dimension 4

UDDGENL5

User Defined Long Degenerate Dimension 5

UDDFK1_KEY

User Defined Dimension Foreign Key 1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Installation Event Fact

The Installation Event fact holds all the installation details of devices on service points in the Oracle Utilities Meter Data Management system. It holds the current installation status and the duration of installation.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_INSTALL_EVT

Table Type

Fact

Fact Type

Accumulation

Source System Driver Table

D1_INSTALL_EVT

D1_SP

D1_US

Stage Table Name

STG_CF_INSTALL_EVT

ODI Package Name

B1_PKG_CF_INSTALL_EVT

ETL View Name

B1_F_INSTALL_EVT_VW

Materialized View Name

B1_INSTALL_EVT_MON_MV1

B1_INSTALL_EVT_MON_MV2

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

INSTALL_EVT_KEY

Install Event Fact Key

This field is populated from the OUBI_INSTALL_EVT_SEQ sequence.

Natural Keys

SRC_INSTALL_EVT_ID

Install Event ID

D1_INSTALL_EVT.INSTALL_EVT_ID

This field is populated based on all the install events from the Install Event table.

Measures

DURATION

Duration

D1_INSTALL_EVT.D1_REMOVAL_DTTM,SYSDATE)-D1_INSTALL_EVT.D1_INSTALL_DTTM

This represents the time between installation and removal. If the removal date/time is blank, the time between the system date/time and installation date/time. Unit of measure is seconds.

FACT_CNT

Fact Count

This field is populated with the standard value of ‘1’.

Degenerate Dimensions

INSTALL_DTTM

Install Date

D1_INSTALL_EVT.D1_INSTALL_DTTM

REMOVAL_DTTM

Removal Date

D1_INSTALL_EVT.D1_REMOVAL_DTTM

Foreign Keys

IE_STATUS_KEY

Install Event Status Dimension Surrogate Key

D1_INSTALL_EVT.D1_INSTALL_EVT.BUS_OBJ_CD

D1_INSTALL_EVT.D1_INSTALL_EVT.BO_STATUS_CD

D1_INSTALL_EVT.BO_STATUS_REASON_CD

This field is populated based on the installation event's current status.

MTR_DEVICE_KEY

Meter Device Dimension Surrogate Key

D1_DVC_CFG.D1_DEVICE_ID

This is populated with the device associated with the installation event's device configuration.

SPR1_KEY

Service Provider 1 Dimension Surrogate Key

D1_SP_MKT_PARTICIPANT.D1_SPR_CD

D1_MKT_FALLBACK_SPR.D1_SPR_CD

This field is populated based on the service point ID associated with the activity.

The service provider is retrieved from the service point or from the fallback service provider on the service point’s market. The market relationship type used is the first one configured via extract parameters.

SPR2_KEY

Service Provider 2 Dimension Surrogate Key

D1_SP_MKT_PARTICIPANT.D1_SPR_CD

D1_MKT_FALLBACK_SPR.D1_SPR_CD

This field is populated based on the service point ID associated with the activity.

The service provider is retrieved from the service point or from the fallback service provider on the service point’s market. The market relationship type used is the first one configured via extract parameters.

SP1_KEY

Service Point 1 Dimension Surrogate Key

D1_INSTALL_EVT.D1_SP_ID

This field is populated with the service point where the installation event is.

SP2_KEY

Service Point 2 Dimension Surrogate Key

D1_SP_REL.REL_SP_ID

This field is populated based on the parent service point of the installation event's service point.

ADDR_KEY

Address Dimension Surrogate Key

D1_INSTALL_EVT.D1_SP_ID

This field is populated based on the installation event's service point.

US1_KEY

Usage Subscription 1 Dimension Surrogate Key

D1_US_SP.US_ID

This field is populated based on the service point associated with the activity. It is populated with the usage subscription linked to the service point, whose usage subscription type’s subscription type matches the first subscription type defined on the extract parameters.

US2_KEY

Usage Subscription 2 Dimension Surrogate Key

D1_US_SP.US_ID

This field is populated based on the service point associated with the activity. It is populated with the usage subscription linked to the service point, whose usage subscription type's subscription type matches the second subscription type defined on the extract parameters.

USAGE_GROUP_KEY

Usage Group Dimension Surrogate Key

D1_US_USG_GRP_DL.USG_GRP_CD

This field is populated based on the usage group of the CCB-related usage subscription linked to the service point associated with the activity.

CONTACT_KEY

Contact Dimension Surrogate Key

D1_US_CONTACT.CONTACT_ID

This field is populated with the primary contact ID of the CCB-related usage subscription linked to the service point associated with the activity.

INSTALL_DATE_KEY

Install Event Date Dimension Surrogate Key

D1_INSTALL_EVT.D1_INSTALL_DTTM

INSTALL_TIME_KEY

Install Event Time Dimension Surrogate Key

D1_INSTALL_EVT.D1_INSTALL_DTTM

REMOVAL_DATE_KEY

Removal Date Dimension Surrogate Key

D1_INSTALL_EVT.D1_REMOVAL_DTTM

REMOVAL_TIME_KEY

Removal Time Dimension Surrogate Key

D1_INSTALL_EVT.D1_REMOVAL_DTTM

PER_KEY

Person Dimension Surrogate Key

D1_CONTACT_IDENTIFIER.ID_VALUE

This field is populated based on the External Person ID stored on the install event’s contact identifiers.

ACCT_KEY

Account Dimension Surrogate Key

D1_US_IDENTIFIER.ID_VALUE

This field is populated based on the External Account ID stored on the install event’s CCB-related usage subscription's identifiers.

SA_KEY

Service Agreement Dimension Surrogate Key

D1_US_IDENTIFIER.ID_VALUE

This field is populated based on the External Service Agreement ID stored on the install event’s CCB-related usage subscription's identifiers.

PREM_KEY

Premise Dimension Surrogate Key

D1_SP_IDENTIFIER.ID_VALUE

This field is populated based on the External Premise ID stored on the install event’s service point's identifiers.

INSTALL_EVT_UDD1_KEY

Install Event User Defined Dimension 1 Surrogate Key

INSTALL_EVT_UDD2_KEY

Install Event User Defined Dimension 2 Surrogate Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDGEN4

User Defined Degenerate Dimension 4

UDDGEN5

User Defined Degenerate Dimension 5

UDDGENL1

User Defined Long Degenerate Dimension 1

UDDGENL2

User Defined Long Degenerate Dimension 2

UDDGENL3

User Defined Long Degenerate Dimension 3

UDDGENL4

User Defined Long Degenerate Dimension 4

UDDGENL5

User Defined Long Degenerate Dimension 5

UDDFK1_KEY

User Defined Dimension Foreign Key 1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Service Point Accumulation Fact

The Service Point Accumulation fact holds the details of all service points defined in the Oracle Utilities Meter Data Management system along with its current installation status.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_SP

Table Type

Fact

Fact Type

Accumulation

Source System Driver Table

D1_SP

D1_INSTALL_EVT

D1_US

Stage Table Name

STG_CF_SP

ODI Package Name

B1_PKG_CF_SP

ETL View Name

B1_F_SP_VW

Materialized View Name

B1_SP_MV1

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

SP_KEY

Service Point Dimension Surrogate Key

This field is populated from the OUBI_SP_SEQ sequence.

Natural Keys

SRC_SP_ID

Service Point ID

D1_SP.D1_SP_ID

Measures

FACT_CNT

Fact Count

This field is populated with the standard value of ‘1’.

Degenerate Dimensions

SP_CONN_STATUS

Service Point Connection Status

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the description of the service point’s connection status.

If the override description is not available, the regular description is extracted.

DEVICE_STATUS

Device Status

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the description of the status of the device currently installed on the service point.

If the override description is not available, the regular description is extracted.

Foreign Keys

SP_STATUS_KEY

Service Point Status Dimension Surrogate Key

D1_SP.D1_SP.BUS_OBJ_CD

D1_SP.D1_SP.BO_STATUS_CD

D1_SP.BO_STATUS_REASON_CD

This field is populated based on the service point’s current status.

IE_STATUS_KEY

Install Event Status Dimension Surrogate Key

D1_INSTALL_EVT.D1_INSTALL_EVT.BUS_OBJ_CD

D1_INSTALL_EVT.D1_INSTALL_EVT.BO_STATUS_CD

D1_INSTALL_EVT.BO_STATUS_REASON_CD

This field is populated based on the installation event's current status.

MTR_DEVICE_KEY

Meter Device Dimension Surrogate Key

D1_DVC_CFG.D1_DEVICE_ID

This field is populated with the device associated with installation event's device configuration.

SPR1_KEY

Service Provider 1 Dimension Surrogate Key

D1_SP_MKT_PARTICIPANT.D1_SPR_CD

D1_MKT_FALLBACK_SPR.D1_SPR_CD

This field is populated based on the service point ID associated with the activity. The service provider is retrieved from the service point or from the fallback service provider on the service point’s market. The market relationship type used is the first one configured via the extract parameters.

SPR2_KEY

Service Provider 2 Dimension Surrogate Key

D1_SP_MKT_PARTICIPANT.D1_SPR_CD

D1_MKT_FALLBACK_SPR.D1_SPR_CD

This field is populated based on the service point ID associated with the activity. The service provider is retrieved from the service point or from the fallback service provider on the service point’s market. The market relationship type used is the second one configured via the extract parameters.

SP1_KEY

Service Point 1 Dimension Surrogate Key

D1_SP.D1_SP_ID

This field is populated with the service point where the installation event is.

SP2_KEY

Service Point 2 Dimension Surrogate Key

D1_SP_REL.REL_SP_ID

This field is populated based on the parent service point of the installation event's service point.

ADDR_KEY

Address Dimension Surrogate Key

D1_SP.D1_SP_ID

This field is populated based on the installation event's service point.

US1_KEY

Usage Subscription 1 Dimension Surrogate Key

D1_US_SP.US_ID

This field is populated based on the service point associated with the activity. It is populated with the usage subscription linked to the service point, whose usage subscription type's subscription type matches the first subscription type defined on the extract parameters.

US2_KEY

Usage Subscription 2 Dimension Surrogate Key

D1_US_SP.US_ID

This field is populated based on the service point associated with the activity. It is populated with the usage subscription linked to the service point, whose usage subscription type's subscription type matches the first subscription type defined on the extract parameters.

USAGE_GROUP_KEY

Usage Group Dimension Surrogate Key

D1_US_USG_GRP_DL.USG_GRP_CD

This field is populated based on the usage group of the CCB-related usage subscription linked to the service point associated with the activity.

CONTACT_KEY

Contact Dimension Surrogate Key

D1_US_CONTACT.CONTACT_ID

This field is populated with the primary contact ID of the CCB-related usage subscription linked to the service point associated with the activity.

PER_KEY

Person Dimension Surrogate Key

D1_CONTACT_IDENTIFIER.ID_VALUE

This field is populated based on the External Person ID stored on the service point’s contact identifiers.

ACCT_KEY

Account Dimension Surrogate Key

D1_US_IDENTIFIER.ID_VALUE

This field is populated based on the External Account ID stored on the service point’s CCB-related usage subscription's identifiers.

SA_KEY

Service Agreement Dimension Surrogate Key

D1_US_IDENTIFIER.ID_VALUE

This field is populated based on the External Service Agreement ID stored on the service point’s CCB-related usage subscription's identifiers.

PREM_KEY

Person Dimension Surrogate Key

D1_SP_IDENTIFIER.ID_VALUE

This field is populated based on the External Premise ID stored on the service point’s service point's identifiers.

SP_UDD1_KEY

Service Point User Defined Dimension 1 Surrogate Key

SP_UDD2_KEY

Service Point User Defined Dimension 2 Surrogate Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDGEN4

User Defined Degenerate Dimension 4

UDDGEN5

User Defined Degenerate Dimension 5

UDDGENL1

User Defined Long Degenerate Dimension 1

UDDGENL2

User Defined Long Degenerate Dimension 2

UDDGENL3

User Defined Long Degenerate Dimension 3

UDDGENL4

User Defined Long Degenerate Dimension 4

UDDGENL5

User Defined Long Degenerate Dimension 5

UDDFK1_KEY

User Defined Dimension Foreign Key 1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Service Point Snapshot Fact

The Service Point Snapshot fact stores periodic snapshot of all service points defined in the Oracle Utilities Meter Data Management system along with important metrics, such as device status, connection status, and measurement indicators.

Taking the Service Point Snapshot fact involves determining how long it has been since a normal measurement was received for the service point. Because of the potential huge volume of measurements, these data are not replicated on to the data warehouse. Instead, a download table for Service Point snapshot is populated in Oracle Utilities Meter Data Management through a specialized batch job. This job is responsible for taking the weekly or monthly service point snapshot, and inserting the results onto the download table. This download table is the one replicated and loaded by the ODI ETL process on to the data warehouse.

Note : There is no transformation logic to populate the target fields in the fact since they directly consume the data from the download table (populated by the batch programs in Oracle Utilities Meter Data Management).

For details about setting up snapshot fact extracts, see the Configuring Oracle Utilities Analytics chapter in the Oracle Utilities Analytics Administration Guide.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_SP_SNAP

Table Type

Fact

Fact Type

Snapshot

Source System Download Table

D1_SP_SNAP_DL

Stage Table Name

STG_CF_SP_SNAP

ODI Package Name

B1_PKG_CF_SP_SNAP

ETL View Name

B1_F_SP_SNAP_VW

Materialized View Name

B1_SP_SNAP_MON_MV1

B1_SP_SNAP_MON_TOPX_MV1

Source Batch Program

D1-SPSNAP-DL

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

SP_SNAP_KEY

Service Point Snapshot Key

This field is populated from the OUBI_SP_SEQ sequence.

Natural Keys

SRC_SP_ID

Service Point ID

D1_SP_SNAP_DL.D1_SP_ID

SNAP_TYPE_CD

Snapshot Type Code

D1_SP_SNAP_DL.SNAPSHOT_TYPE_FLG

Measures

DEVICE_STATUS

Device Status

D1_SP_SNAP_DL.ONOFF_HIST_DESCR

This field is populated with the status of the device installed at the service point at the time of the snapshot.

SP_CONN_STATUS

Service Point Connection Status

D1_SP_SNAP_DL.SP_SRC_STAT_DESCR

DAYS_LAST_MSRMT

Days without Measurements

D1_SP_SNAP_DL.DAYS_LAST_NORMAL_MSRMT

This field is populated with the number of days since the last normal measurement was received for the service point.

If there are no normal measurements at all, the number of days is calculated based on the start date of the service point's installation event as of the snapshot end date.

If there is no installation event linked to the service point, the number of days is ‘0’.

NVR_RECD_MSRMT_IND

Never Received Measurements

D1_SP_SNAP_DL.NEVER_RCVD_MSRMT_IND

If the service point never received measurements, this indicator is set to ‘1’.

Foreign Keys

DATE_KEY

Date Dimension Surrogate Key

D1_SP_SNAP_DL.SNAPSHOT_DTTM

This field is populated from the Date table based on the snapshot date/time.

SP_STATUS_KEY

Service Point Status Dimension Surrogate Key

D1_SP_SNAP_DL.SP_BUS_OBJ_CD

D1_SP_SNAP_DL.SP_BO_STATUS_CD

D1_SP_SNAP_DL.SP_BO_STATUS_REASON_CD

This field is populated based on the status of the service point at the end of the snapshot period.

IE_STATUS_KEY

Install Event Status Dimension Surrogate Key

D1_SP_SNAP_DL.IE_BUS_OBJ_CD

D1_SP_SNAP_DL.IE_BO_STATUS_CD

D1_SP_SNAP_DL.IE_BO_STATUS_REASON_CD

This field is populated based on the status of the installation event at the end of the snapshot period.

MTR_DEVICE_KEY

Meter Device Dimension Surrogate Key

D1_SP_SNAP_DL.D1_DEVICE_ID

This is populated with the device that is installed at the service point at the end of the snapshot period.

SPR1_KEY

Service Provider 1 Dimension Surrogate Key

D1_SP_SNAP_DL.SPR1_CD

This field is populated based on the service point ID associated with the activity. The service provider is retrieved from the service point or from the fallback service provider on the service point’s market. The market relationship type used is the first one configured via the extract parameters.

SPR2_KEY

Service Provider 2 Dimension Surrogate Key

D1_SP_SNAP_DL.SPR2_CD

This field is populated based on the service point ID associated with the activity. The service provider is retrieved from the service point or from the fallback service provider on the service point’s market. The market relationship type used is the second one configured via the extract parameters.

SP1_KEY

Service Point 1 Dimension Surrogate Key

D1_SP_SNAP_DL.D1_SP_ID

This field is populated with the service point where the installation event is.

SP2_KEY

Service Point 2 Dimension Surrogate Key

D1_SP_SNAP_DL.PARENT_SP_ID

This field is populated based on the parent service point of the installation event's service point.

ADDR_KEY

Address Dimension Surrogate Key

D1_SP_SNAP_DL.ADDRESS_ID

This field is populated based on the installation event's service point.

US1_KEY

Usage Subscription 1 Dimension Surrogate Key

D1_SP_SNAP_DL.US1_ID

This field is populated based on the service point associated with the activity. It is populated with the usage subscription linked to the service point, whose usage subscription type's subscription type matches the first subscription type defined on the extract parameters.

US2_KEY

Usage Subscription 2 Dimension Surrogate Key

D1_SP_SNAP_DL.US2_ID

This field is populated based on the service point associated with the activity. It is populated with the usage subscription linked to the service point, whose usage subscription type's subscription type matches the first subscription type defined on the extract parameters.

USAGE_GROUP_KEY

Usage Group Dimension Surrogate Key

D1_SP_SNAP_DL.USG_GRP_C

This field is populated based on the usage group of the CCB-related usage subscription linked to the service point associated with the activity.

CONTACT_KEY

Contact Dimension Surrogate Key

D1_SP_SNAP_DL.CONTACT_ID

This field is populated with the primary contact ID of the CCB-related usage subscription linked to the service point associated with the activity.

DAYS_LAST_MSRMT_KEY

Days Since Last Measurement Dimension Surrogate Key

D1_SP_SNAP_DL.DAYS_LAST_MSRMT_BKT_CD

This field is populated based on the External Person ID stored on the service point’s contact identifiers.

PER_KEY

Person Dimension Surrogate Key

D1_SP_SNAP_DL.CIS_EXT_PERSON_ID

This field is populated based on the External Account ID stored on the service point’s CCB-related usage subscription's identifiers.

ACCT_KEY

Account Dimension Surrogate Key

D1_SP_SNAP_DL.CIS_EXT_ACCT_ID

This field is populated based on the External Service Agreement ID stored on the service point’s CCB-related usage subscription's identifiers.

SA_KEY

Service Agreement Dimension Surrogate Key

D1_SP_SNAP_DL.CIS_EXT_SA_ID

This field is populated based on the External Premise ID stored on the service point’s service point's identifiers.

PREM_KEY

Person Dimension Surrogate Key

D1_SP_SNAP_DL.CIS_EXT_PREM_ID

This field is populated from the Premise table based on external premise identifier associated with the service point.

SP_SNAP_UDD1_KEY

SP Snapshot User Defined Dimension 1 Surrogate Key

D1_SP_SNAP_DL.UDD1_CD

SP_SNAP_UDD2_KEY

SP Snapshot User Defined Dimension 2 Surrogate Key

D1_SP_SNAP_DL.UDD2_CD

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDGEN4

User Defined Degenerate Dimension 4

UDDGEN5

User Defined Degenerate Dimension 5

UDDGENL1

User Defined Long Degenerate Dimension 1

UDDGENL2

User Defined Long Degenerate Dimension 2

UDDGENL3

User Defined Long Degenerate Dimension 3

UDDGENL4

User Defined Long Degenerate Dimension 4

UDDGENL5

User Defined Long Degenerate Dimension 5

UDDFK1_KEY

User Defined Dimension Foreign Key 1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

Data Load Attributes

SNAPSHOT_DT

Snapshot Date

D1_SP_SNAP_DL.SNAPSHOT_DTTM

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Unreported Usage Analysis Snapshot Fact

The Unreported Usage Analysis snapshot fact stores the amount of consumption that has taken place since the last usage transaction was sent. The consumption data is captured for all active service points on a periodic basis.

Taking the Unreported Usage Analysis Snapshot involves determining the amount of consumption that has taken place since the most recent usage transaction was sent. Because of the potential huge volume of measurements, the data is not replicated on to the data warehouse. Instead, a download table for the Unreported Usage Analysis Snapshot is populated in Oracle Utilities Meter Data Management through a specialized batch job. This job is responsible for taking the weekly or monthly Unreported Usage Analysis Snapshot, and inserting the results onto the download table. This download table is the one replicated and loaded by the ODI ETL process on to the data warehouse.

Note : There is no transformation logic to populate the target fields in the fact since they directly consume the data from the download table (populated by the batch programs in Oracle Utilities Meter Data Management).

For details about setting up snapshot fact extracts, see the Configuring Oracle Utilities Analytics chapter in the Oracle Utilities Analytics Administration Guide.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_SP_UT_AGE

Table Type

Fact

Fact Type

Snapshot

Source System Download Table

D1_SP_UNR_USG_SNAP_DL

Stage Table Name

STG_CF_SP_UT_AGE

ODI Package Name

B1_PKG_CF_SP_UT_AGE

ETL View Name

B1_F_SP_UT_AGE_VW

Materialized View Name

B1_SP_UT_AGE_MON_MV1

B1_SP_UT_AGE_MON_TOPX_MV1

Source Batch Program

D2-SPUT-DL

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

SP_UT_AGE_KEY

Unreported Usage Analysis Snapshot Key

This field is populated from the OUBI_SP_UT_AGE_SEQ sequence.

Natural Keys

SRC_SP_ID

Service Point ID

D1_SP_UNR_USG_SNAP_DL.D1_SP_ID

SRC_SP_UT_AGE_TYPE_CD

Unreported Usage Aging Type Key

D1_SP_UNR_USG_SNAP_DL.UNR_USG_SNAPSHOT_TYPE_FLG

Measures

FACT_CNT

Count

This field is populated with the standard value of ‘1’.

CONS_AFT_LAST_UT

Total Quantity

D1_SP_UNR_USG_SNAP_DL.TOT_CONS_SINCE_LAST_UT

This is the total consumption since the last usage transaction was sent.

CONS_AFT_LAST_UT_BUCKET1

Consumption After Last UT - Bucket 1

D1_SP_UNR_USG_SNAP_DL.CONS_SINCE_LAST_UT_BKT1

This is the total consumption, since the last usage transaction was sent, that falls within the first configurable bucket range.

CONS_AFT_LAST_UT_BUCKET1_DESCR

Consumption After Last UT - Bucket 1 Description

D1_SP_UNR_USG_SNAP_DL.CONS_SINCE_LAST_UT_BKT1_DESCR

CONS_AFT_LAST_UT_BUCKET2

Consumption After Last UT - Bucket 2

D1_SP_UNR_USG_SNAP_DL.CONS_SINCE_LAST_UT_BKT2

This is the total consumption, since the last usage transaction was sent, that falls within the second configurable bucket range.

CONS_AFT_LAST_UT_BUCKET2_DESCR

Consumption After Last UT - Bucket 2 Description

D1_SP_UNR_USG_SNAP_DL.CONS_SINCE_LAST_UT_BKT2_DESCR

CONS_AFT_LAST_UT_BUCKET3

Consumption After Last UT - Bucket 3

D1_SP_UNR_USG_SNAP_DL.CONS_SINCE_LAST_UT_BKT3

This is the total consumption, since the last usage transaction was sent, that falls within the third configurable bucket range.

CONS_AFT_LAST_UT_BUCKET3_DESCR

Consumption After Last UT - Bucket 3 Description

D1_SP_UNR_USG_SNAP_DL.CONS_SINCE_LAST_UT_BKT3_DESCR

CONS_AFT_LAST_UT_BUCKET4

Consumption After Last UT - Bucket 4

D1_SP_UNR_USG_SNAP_DL.CONS_SINCE_LAST_UT_BKT4

This is the total consumption, since the last usage transaction was sent, that falls within the last configurable bucket range.

CONS_AFT_LAST_UT_BUCKET4_DESCR

Consumption After Last UT - Bucket 4 Description

D1_SP_UNR_USG_SNAP_DL.CONS_SINCE_LAST_UT_BKT4_DESCR

DAYS_LAST_UT

Days Since Last Usage Transaction

D1_SP_UNR_USG_SNAP_DL.TOT_DAYS_SINCE_LAST_UT

This is the number of days since the last usage transaction was sent.

Degenerate Dimensions

SNAP_TYPE_CD

Snapshot Type Code

D1_SP_UNR_USG_SNAP_DL.SNAPSHOT_TYPE_FLG

Foreign Keys

DATE_KEY

Date Dimension Surrogate Key

D1_SP_UNR_USG_SNAP_DL.SNAPSHOT_DTTM

DAYS_LASTUT_TYPE_KEY

Days Since Last UT Type Dimension Surrogate Key

D1_SP_UNR_USG_SNAP_DL.TOT_DAYS_SINCE_LAST_UT_BKT_CD

This field is populated based on the total number of days since last usage transaction bucket code associated with the service point.

SP_UT_AGE_TYPE_KEY

Unreported Usage Aging Type Dimension Surrogate Key

D1_SP_UNR_USG_SNAP_DL.UNR_USG_SNAPSHOT_TYPE_FLG

This field is populated based on the unreported usage snapshot type associated with the service point.

MTR_DEVICE_KEY

Meter Device Dimension Surrogate Key

D1_SP_UNR_USG_SNAP_DL.D1_DEVICE_ID

This field is populated with the device ID based on the unreported usage snapshot type associated with the service point.

SPR1_KEY

Service Provider 1 Dimension Surrogate Key

D1_SP_UNR_USG_SNAP_DL.SPR1_CD

This field is populated based on the service point ID associated with the unreported usage transaction. The service provider is retrieved from the service point or from the fallback service provider on the service point’s market. The market relationship type used is the first one configured via the extract parameters.

SPR2_KEY

Service Provider 2 Dimension Surrogate Key

D1_SP_UNR_USG_SNAP_DL.SPR2_CD

This field is populated based on the service point ID associated with the unreported usage transaction. The service provider is retrieved from the service point or from the fallback service provider on the service point’s market. The market relationship type used is the first one configured via the extract parameters.

SP1_KEY

Service Point 1 Dimension Surrogate Key

D1_SP_UNR_USG_SNAP_DL.D1_SP_ID

This field is populated based on the service point ID associated with the unreported usage transaction via its related object collection.

If there is no service point ID associated, then the service point ID is derived from the device associated with the activity.

SP2_KEY

Service Point 2 Dimension Surrogate Key

D1_SP_UNR_USG_SNAP_DL.PARENT_SP_ID

This field is populated based on the service point ID associated with the unreported usage transaction via its related object collection.

If there is no service point ID associated, then the service point ID is derived from the device associated with the activity.

ADDR_KEY

Address Dimension Surrogate Key

D1_SP_UNR_USG_SNAP_DL.ADDRESS_ID

This field is populated based on the service point ID associated with the unreported usage transaction.

US_KEY

Usage Subscription Dimension Surrogate Key

D1_SP_UNR_USG_SNAP_DL.US_ID

This field is populated based on the service point associated with the unreported usage transaction. It is populated with the usage subscription linked to the service point, whose usage subscription type’s subscription type matches the first subscription type defined on the extract parameters.

USAGE_GROUP_KEY

Usage Group Dimension Surrogate Key

D1_SP_UNR_USG_SNAP_DL.USG_GRP_CD

This field is populated based on the usage group of the CCB-related usage subscription linked to the service point associated with the unreported usage.

CONTACT_KEY

Contact Dimension Surrogate Key

D1_SP_UNR_USG_SNAP_DL.CONTACT_ID

This field is populated with the primary contact ID of the CCB-related usage subscription linked to the service point associated with the unreported usage.

UOM_TOU_SQI_KEY

UOM TOU SQI Dimension Surrogate Key

D1_SP_UNR_USG_SNAP_DL.D1_UOM_CD

D1_SP_UNR_USG_SNAP_DL.D1_TOU_CD

D1_SP_UNR_USG_SNAP_DL.D1_SQI_CD

This field is populated based on the UOM/TOU/SQI used for identifying the source measuring component and value identifier that is used to calculate the unbilled consumption.

PER_KEY

Person Dimension Surrogate Key

D1_SP_UNR_USG_SNAP_DL.CIS_EXT_PERSON_ID

This field is populated based on the External Person ID stored on the activity's contact identifiers.

ACCT_KEY

Account Dimension Surrogate Key

D1_SP_UNR_USG_SNAP_DL.CIS_EXT_ACCT_ID

This field is populated based on the External Account ID stored on the activity’s CCB-related usage subscription’s identifiers.

SA_KEY

Service Agreement Dimension Surrogate Key

D1_SP_UNR_USG_SNAP_DL.CIS_EXT_SA_ID

This field is populated based on the External Service Agreement ID stored on the activity’s CCB-related usage subscription’s identifiers.

PREM_KEY

Premise Dimension Surrogate Key

D1_SP_UNR_USG_SNAP_DL.CIS_EXT_PREM_ID

This field is populated based on the External Premise ID stored on the activity’s service point’s identifiers.

SP_UT_AGE_UDD1_KEY

Unreported Usage Analysis User Defined Dimension 1 Surrogate Key

D1_SP_UNR_USG_SNAP_DL.UDD1_CD

SP_UT_AGE_UDD2_KEY

Unreported Usage Analysis User Defined Dimension 2 Surrogate Key

D1_SP_UNR_USG_SNAP_D.UDD2_CD

User Defined Attributes

UDM1

User Defined Measure 1

D1_SP_UNR_USG_SNAP_DL.D1_UDM1

UDM2

User Defined Measure 2

D1_SP_UNR_USG_SNAP_DL.D1_UDM2

UDM3

User Defined Measure 3

D1_SP_UNR_USG_SNAP_DL.D1_UDM3

UDM4

User Defined Measure 4

D1_SP_UNR_USG_SNAP_DL.D1_UDM4

UDM5

User Defined Measure 5

D1_SP_UNR_USG_SNAP_DL.D1_UDM5

UDM6

User Defined Measure 6

D1_SP_UNR_USG_SNAP_DL.D1_UDM6

UDM7

User Defined Measure 7

D1_SP_UNR_USG_SNAP_DL.D1_UDM7

UDM8

User Defined Measure 8

D1_SP_UNR_USG_SNAP_DL.D1_UDM8

UDM9

User Defined Measure 9

D1_SP_UNR_USG_SNAP_DL.D1_UDM9

UDM10

User Defined Measure 10

D1_SP_UNR_USG_SNAP_DL.D1_UDM10

UDDGEN1

User Defined Degenerate Dimension 1

D1_SP_UNR_USG_SNAP_DL.D1_UDDGEN1

UDDGEN2

User Defined Degenerate Dimension 2

D1_SP_UNR_USG_SNAP_DL.D1_UDDGEN2

UDDGEN3

User Defined Degenerate Dimension 3

D1_SP_UNR_USG_SNAP_DL.D1_UDDGEN3

UDDGEN4

User Defined Degenerate Dimension 4

D1_SP_UNR_USG_SNAP_DL.D1_UDDGEN4

UDDGEN5

User Defined Degenerate Dimension 5

D1_SP_UNR_USG_SNAP_DL.D1_UDDGEN5

UDDGENL1

User Defined Long Degenerate Dimension 1

D1_SP_UNR_USG_SNAP_DL.UDDGENLONG1

UDDGENL2

User Defined Long Degenerate Dimension 2

D1_SP_UNR_USG_SNAP_DL.UDDGENLONG2

UDDGENL3

User Defined Long Degenerate Dimension 3

D1_SP_UNR_USG_SNAP_DL.UDDGENLONG3

UDDGENL4

User Defined Long Degenerate Dimension 4

D1_SP_UNR_USG_SNAP_DL.UDDGENLONG4

UDDGENL5

User Defined Long Degenerate Dimension 5

D1_SP_UNR_USG_SNAP_DL.UDDGENLONG5

UDDFK1_KEY

User Defined Dimension Foreign Key 1

D1_SP_UNR_USG_SNAP_DL.D1_UDDFK1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

D1_SP_UNR_USG_SNAP_DL.D1_UDDFK2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

D1_SP_UNR_USG_SNAP_DL.D1_UDDFK3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

D1_SP_UNR_USG_SNAP_DL.D1_UDDFK4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

D1_SP_UNR_USG_SNAP_DL.D1_UDDFK5

Data Load Attributes

SNAPSHOT_DT

Snapshot Date

D1_SP_UNR_USG_SNAP_DL.SNAPSHOT_DTTM

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Usage Snapshot Fact

The Usage Snapshot fact stores the summarized consumption details, such as total usage and number of measurements. The data is stored for every active service point in the system on a periodical basis. Once a month (or once a week), a TOU map is applied to every active service point’s consumption; every resultant TOU, measurement condition and quantity results in a row on the Usage Snapshot fact.

Taking the Usage snapshot involves transforming the consumption by applying a few core Oracle Meter Data Management functionalities, such as converting scalar consumption to intervals, axis conversion and TOU-mapping, These functionalities cannot be replicated in ODI. Instead, a download table for the Usage Snapshot is populated in Oracle Utilities Meter Data Management through a specialized batch job. This job is responsible for taking the weekly or monthly Usage Snapshot, and inserting the results onto the download table. This download table is the one replicated and loaded by the ODI ETL process on to the data warehouse

Note : There is no transformation logic to populate the target fields in the fact since they directly consume the data from the download table (populated by the batch programs in Oracle Utilities Meter Data Management).

For details about setting up snapshot fact extracts, see the Configuring Oracle Utilities Analytics chapter in the Oracle Utilities Analytics Administration Guide.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_CONSUMPTION

Table Type

Fact

Fact Type

Snapshot

Source System Download Table

D1_SP_USG_SNAP_DL

Stage Table Name

STG_CF_CONSUMPTION

ODI Package Name

B1_PKG_CF_CONSUMPTION

ETL View Name

B1_F_CONSUMPTION_VW

Materialized View Name

B1_CONSUMPTION_MON_MV1

B1_CONSUMPTION_MON_TOPX_MV1

Source Batch Program

D2-SP-USG-DL

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

CONSUMPTION_KEY

Consumption Dimension Surrogate Key

This field is populated from the OUBI_CONSUMPTION_SEQ sequence.

Natural Keys

SRC_SP_ID

Service Point ID

D1_SP_USG_SNAP_DL.D1_SP_ID

SRC_MC_ID

Measuring Component ID

D1_SP_USG_SNAP_DL.MEASR_COMP_ID

SRC_CONS_SNAPSHOT_TYPE_CD

Usage Snapshot Type

D1_SP_USG_SNAP_DL.USG_SNAPSHOT_TYPE_FLG

SRC_TOU_CD

TOU Code

D1_SP_USG_SNAP_DL.D1_TOU_CD

SRC_MSRMT_COND_CD

Measurement Condition Code

D1_SP_USG_SNAP_DL.MSRMT_COND_FLG

SNAP_TYPE_CD

Snapshot Type Code

D1_SP_USG_SNAP_DL.SNAPSHOT_TYPE_FLG

Measures

QUANTITY

Quantity

D1_SP_USG_SNAP_DL.MSRMT_VAL

NUM_MSRMT

Number of Measurements

D1_SP_USG_SNAP_DL.NBR_OF_MSRMTS

This field is populated with the number of measurements included in the summarized consumption of the service point.

NUM_MIN

Number of Minutes

D1_SP_USG_SNAP_DL.NBR_OF_MINS

This field is populated with the number of minutes per measurement.

FACT_CNT

Fact Count

This field is populated with the standard value of ‘1’.

Foreign Keys

DATE_KEY

Date Dimension Surrogate Key

D1_SP_USG_SNAP_DL.SNAPSHOT_DTTM

This field is populated based on the snapshot date/time.

CONS_SNAPSHOT_TYPE_KEY

Consumption Snapshot Type Dimension Surrogate Key

D1_SP_USG_SNAP_DL.USG_SNAPSHOT_TYPE_FLG

This field is populated from the Consumption Snapshot Type table based on the usage subscription type associated with the usage subscription.

MC_KEY

Measuring Component Dimension Surrogate Key

D1_SP_USG_SNAP_DL.MEASR_COMP_ID

This field is populated based on the Measuring Component ID associated with the usage subscription via its related object collection.

SPR1_KEY

Service Provider 1 Dimension Surrogate Key

D1_SP_USG_SNAP_DL.SPR1_CD

This field is populated based on the service point ID associated with the usage subscription. The service provider is retrieved from the service point or from the fallback service provider on the service point’s market. The market relationship type used is the first one configured via the extract parameters.

SPR2_KEY

Service Provider 2 Dimension Surrogate Key

D1_SP_USG_SNAP_DL.SPR2_CD

This field is populated based on the service point ID associated with the usage subscription. The service provider is retrieved from the service point or from the fallback service provider on the service point’s market. The market relationship type used is the first one configured via the extract parameters.

SP1_KEY

Service Point 1 Dimension Surrogate Key

D1_SP_USG_SNAP_DL.D1_SP_ID

This field is populated based on the service point ID associated with the usage subscription via its related object collection.

If there is no service point ID associated with the activity, then the service point ID is derived from the device associated with the activity.

SP2_KEY

Service Point 2 Dimension Surrogate Key

D1_SP_USG_SNAP_DL.PARENT_SP_ID

This field is populated based on the service point ID associated with the usage subscription via its related object collection.

If there is no service point ID associated with the activity, then the service point ID is derived from the device associated with the activity.

MTR_DEVICE_KEY

Meter Device Dimension Surrogate Key

D1_SP_USG_SNAP_DL.D1_DEVICE_ID

This field is populated based on the device ID associated with the usage subscription via its related object collection.

If there is no device ID associated with the usage subscription, then the device ID is derived from the measuring component or service point associated with the usage subscription.

ADDR_KEY

Address Dimension Surrogate Key

D1_SP_USG_SNAP_DL.ADDRESS_ID

This field is populated based on the service point ID associated with the usage subscription.

US1_KEY

Usage Subscription 1 Dimension Surrogate Key

D1_SP_USG_SNAP_DL.US1_ID

This field is populated based on the service point associated with the usage subscription. It is populated with the usage subscription linked to the service point, whose usage subscription type’s subscription type matches the first subscription type defined on the extract parameters.

US2_KEY

Usage Subscription 2 Dimension Surrogate Key

D1_SP_USG_SNAP_DL.US2_ID

This field is populated based on the service point associated with the usage subscription. It is populated with the usage subscription linked to the service point, whose usage subscription type’s subscription type matches the first subscription type defined on the extract parameters.

USAGE_GROUP_KEY

Usage Group Dimension Surrogate Key

D1_SP_USG_SNAP_DL.USG_GRP_CD

This field is populated based on the usage group of the CCB-related usage subscription linked to the service point associated with the usage subscription.

CONTACT_KEY

Contact Dimension Surrogate Key

D1_SP_USG_SNAP_DL.CONTACT_ID

This field is populated with the primary contact ID of the CCB-related usage subscription linked to the service point associated with the usage subscription.

MSRMT_COND_KEY

Measurement Condition Dimension Surrogate Key

D1_SP_USG_SNAP_DL.MSRMT_COND_FLG

This field is populated with the measurement condition associated with the current usage subscription.

UOM_TOU_KEY

UOM TOU Dimension Surrogate Key

D1_SP_USG_SNAP_DL.

D1_UOM_CD

D1_SP_USG_SNAP_DL.D1_TOU_CD

This field is populated based on the UOM code and TOU code associated with the usage subscription.

PER_KEY

Person Dimension Surrogate Key

D1_SP_USG_SNAP_DL.CIS_EXT_PERSON_ID

This field is populated based on the external person ID stored on the activity’s contact identifiers.

ACCT_KEY

Account Dimension Surrogate Key

D1_SP_USG_SNAP_DL.CIS_EXT_ACCT_ID

This field is populated based on the external account ID stored on the activity's CCB-related usage subscription's identifiers.

SA_KEY

Service Agreement Dimension Surrogate Key

D1_SP_USG_SNAP_DL.CIS_EXT_SA_ID

This field is populated based on the external service agreement ID stored on the activity's CCB-related usage subscription's identifiers.

PREM_KEY

Premise Dimension Surrogate Key

D1_SP_USG_SNAP_DL.CIS_EXT_PREM_ID

This field is populated based on the external premise ID stored on the activity's service point's identifiers.

CONSUMPTION_UDD1_KEY

Consumption User Defined Dimension 1 Surrogate Key

D1_SP_USG_SNAP_DL.UDD1_CD

CONSUMPTION_UDD2_KEY

Consumption User Defined Dimension 2 Surrogate Key

D1_SP_USG_SNAP_DL.UDD2_CD

User Defined Attributes

UDM1

User Defined Measure 1

D1_SP_USG_SNAP_DL.D1_UDM1

UDM2

User Defined Measure 2

D1_SP_USG_SNAP_DL.D1_UDM2

UDM3

User Defined Measure 3

D1_SP_USG_SNAP_DL.D1_UDM3

UDM4

User Defined Measure 4

D1_SP_USG_SNAP_DL.D1_UDM4

UDM5

User Defined Measure 5

D1_SP_USG_SNAP_DL.D1_UDM5

UDM6

User Defined Measure 6

D1_SP_USG_SNAP_DL.D1_UDM6

UDM7

User Defined Measure 7

D1_SP_USG_SNAP_DL.D1_UDM7

UDM8

User Defined Measure 8

D1_SP_USG_SNAP_DL.D1_UDM8

UDM9

User Defined Measure 9

D1_SP_USG_SNAP_DL.D1_UDM9

UDM10

User Defined Measure 10

D1_SP_USG_SNAP_DL.D1_UDM10

UDDGEN1

User Defined Degenerate Dimension 1

D1_SP_USG_SNAP_DL.D1_UDDGEN1

UDDGEN2

User Defined Degenerate Dimension 2

D1_SP_USG_SNAP_DL.D1_UDDGEN2

UDDGEN3

User Defined Degenerate Dimension 3

D1_SP_USG_SNAP_DL.D1_UDDGEN3

UDDGEN4

User Defined Degenerate Dimension 4

D1_SP_USG_SNAP_DL.D1_UDDGEN4

UDDGEN5

User Defined Degenerate Dimension 5

D1_SP_USG_SNAP_DL.D1_UDDGEN5

UDDGENL1

User Defined Long Degenerate Dimension 1

D1_SP_USG_SNAP_DL.UDDGENLONG1

UDDGENL2

User Defined Long Degenerate Dimension 2

D1_SP_USG_SNAP_DL.UDDGENLONG2

UDDGENL3

User Defined Long Degenerate Dimension 3

D1_SP_USG_SNAP_DL.UDDGENLONG3

UDDGENL4

User Defined Long Degenerate Dimension 4

D1_SP_USG_SNAP_DL.UDDGENLONG4

UDDGENL5

User Defined Long Degenerate Dimension 5

D1_SP_USG_SNAP_DL.UDDGENLONG5

UDDFK1_KEY

User Defined Dimension Foreign Key 1

D1_SP_USG_SNAP_DL.D1_UDDFK1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

D1_SP_USG_SNAP_DL.D1_UDDFK2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

D1_SP_USG_SNAP_DL.D1_UDDFK3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

D1_SP_USG_SNAP_DL.D1_UDDFK4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

D1_SP_USG_SNAP_DL.D1_UDDFK5

Data Load Attributes

SNAPSHOT_DT

Snapshot Date

D1_SP_USG_SNAP_DL.SNAPSHOT_DTTM

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

VEE Exception Snapshot Fact

The VEE Exception Snapshot fact stores the details of the exceptions encountered during VEE processing of the initial measurement data. It captures metrics, such as number of IMDs that result into exceptions, as well as those that do not result into any exception. The data is stored periodically.

Taking the VEE Exception snapshot involves determining the number of IMDs with and without exceptions. For IMDS with exceptions, we further subdivide the counts by exception type, IMD type, severity, VEE group and rules. Due to this complicated logic that looks at the presence and absence of data, as well as the volume of IMDs, these data are not replicated on the data warehouse. Instead, a download table for the VEE Exception Snapshot is populated in Oracle Utilities Meter Data Management through a specialized batch job. This job is responsible for taking the weekly or monthly VEE Exception Snapshot, and inserting the results onto the download table. This download table is the one replicated and loaded by the ODI ETL process on to the data warehouse

Note : There is no transformation logic to populate the target fields in the fact since they directly consume the data from the download table (populated by the batch programs in Oracle Utilities Meter Data Management).

For details about setting up snapshot fact extracts, see the Configuring Oracle Utilities Analytics chapter in the Oracle Utilities Analytics Administration Guide.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_VEE_EXCP

Table Type

Fact

Fact Type

Snapshot

Source System Download Table

D1_SP_VEE_EXCP_SNAP_DL

Stage Table Name

STG_CF_VEE_EXCP

ODI Package Name

B1_PKG_CF_VEE_EXCP

ETL View Name

B1_F_VEE_EXCP_VW

Materialized View Name

B1_VEE_EXCP_MON_MV1

B1_VEE_EXCP_TOPX_MON_MV1

Source Batch Program

D2-SPVEE-DL

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

VEE_EXCP_KEY

VEE Exception Key

This field is populated from the OUBI_VEE_EXCP_SEQ sequence.

Natural Keys

SRC_SP_ID

Service Point ID

D1_SP_VEE_EXCP_SNAP_DL.D1_SP_ID

SRC_MC_ID

Measuring Component ID

D1_SP_VEE_EXCP_SNAP_DL.MEASR_COMP_ID

SRC_EXCP_TYPE_CD

Exception Type Code

D1_SP_VEE_EXCP_SNAP_DL.EXCP_TYPE_CD

This field populates the source column from the download table. If the source column is not found in the download table, then the field is populated with ‘***’.

SRC_IMD_TYPE_CD

IMD Type Code

D1_SP_VEE_EXCP_SNAP_DL.D1_IMD_TYPE_FLG

This field populates the source column from the download table. If the source column is not found in the download table, then the field is populated with ‘***’.

SRC_EXCP_SEV_CD

Exception Severity Code

D1_SP_VEE_EXCP_SNAP_DL.EXCP_SEVERITY_FLG

This field populates the source column from the download table. If the source column is not found in the download table, then the field is populated with ‘***’.

SRC_VEE_GRP_CD

VEE Group Code

D1_SP_VEE_EXCP_SNAP_DL.VEE_GRP_CD

This field populates the source column from the download table. If the source column is not found in the download table, then the field is populated with ‘***’.

SRC_VEE_RULE_CD

VEE Rule Code

D1_SP_VEE_EXCP_SNAP_DL.VEE_RULE_CD

This field populates the source column from the download table. If the source column is not found in the download table, then the field is populated with ‘***’.

SNAP_TYPE_CD

Snapshot Type Code

D1_SP_VEE_EXCP_SNAP_DL.SNAPSHOT_TYPE_FLG

Measures

FACT_CNT

Count

This field is populated with the standard value of ‘1’.

IMD_COUNT_NO_EXCP

IMD Count without Exceptions

D1_SP_VEE_EXCP_SNAP_DL.NBR_IMDS_WITH_NO_EXCP

This field is the count of IMDs without exceptions, populated on the summary row.

IMD_COUNT_DISTINCT

IMD Count with at Least One Exception

D1_SP_VEE_EXCP_SNAP_DL.NBR_OF_IMDS_MIN_ONE_EXCP

This field is the number of IMDs that has at least one exception, only populated on the summary row.

IMD_COUNT

IMD Count

D1_SP_VEE_EXCP_SNAP_DL.NBR_IMDS_WITH_EXCP

This field is number of IMDs with exception for the exception type, severity, IMD type and VEE group/rule combination.

Foreign Keys

DATE_KEY

Date Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.SNAPSHOT_DTTM

This field is populated based on the snapshot date/time.

MTR_DEVICE_KEY

Meter Device Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.D1_DEVICE_ID

This field is populated based on the device ID associated with the VEE exception via its related object collection.

If there is no device ID associated with the exception, then the device ID is derived from the measuring component or service point associated with the activity.

MC_KEY

Measuring Component Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.MEASR_COMP_ID

This field is populated based on the measuring component ID associated with the VEE exception via its related object collection.

SPR1_KEY

Service Provider 1 Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.SPR1_CD

This field is populated based on the service point ID associated with the VEE exception. The service provider is retrieved from the service point or from the fallback service provider on the service point’s market. The market relationship type used is the first one configured via the extract parameters.

SPR2_KEY

Service Provider 2 Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.SPR2_CD

This field is populated based on the service point ID associated with the VEE exception. The service provider is retrieved from the service point or from the fallback service provider on the service point’s market. The market relationship type used is the first one configured via the extract parameters.

SP1_KEY

Service Point 1 Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.D1_SP_ID

This field is populated based on the service point ID associated with the VEE exception via its related object collection.

If there is no service point ID associated with the exception, then the service point ID is derived from the device associated with the activity.

SP2_KEY

Service Point 2 Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.PARENT_SP_ID

This field is populated based on the service point ID associated with the VEE exception via its related object collection.

If there is no service point ID associated with the exception, then the service point ID is derived from the device associated with the activity.

ADDR_KEY

Address Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.ADDRESS_ID

This field is populated based on the service point ID associated with the VEE exception.

US1_KEY

Usage Subscription 1 Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.US1_ID

This field is populated based on the service point associated with the VEE exception. It is populated with the usage subscription linked to the service point, whose usage subscription type's subscription type matches the first subscription type defined on the extract parameters.

US2_KEY

Usage Subscription 2 Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.US2_ID

This field is populated based on the service point associated with the VEE exception. It is populated with the usage subscription linked to the service point, whose usage subscription type's subscription type matches the first subscription type defined on the extract parameters.

USAGE_GROUP_KEY

Usage Group Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.USG_GRP_CD

This field is populated based on the usage group of the CCB-related usage subscription linked to the service point associated with the VEE exception.

CONTACT_KEY

Contact Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.CONTACT_ID

This field is populated with the primary contact ID of the CCB-related usage subscription linked to the service point associated with the VEE exception.

EXCP_TYPE_KEY

Exception Type Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.EXCP_TYPE_CD

This field is populated based on the exception type code associated with the VEE exception.

IMD_TYPE_KEY

IMD Type Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.D1_IMD_TYPE_FLG

This field is populated based on the IMD type code associated with the VEE exception.

EXCP_SEV_KEY

Exception Severity Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.EXCP_SEVERITY_FLG

This field is populated based on the exception severity code associated with the service point.

VEE_RULE_KEY

VEE Rule Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.VEE_RULE_CD

D1_SP_VEE_EXCP_SNAP_DL.VEE_GRP_CD

This field is populated based on the exception rule code associated with the service point.

PER_KEY

Person Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.CIS_EXT_PERSON_ID

This field is populated based on the external person ID stored on the exception’s contact identifiers.

ACCT_KEY

Account Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.CIS_EXT_ACCT_ID

This field is populated based on the external account ID stored on the exception’s CCB-related usage subscription's identifiers.

SA_KEY

Service Agreement Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.CIS_EXT_SA_ID

This field is populated based on the external service agreement ID stored on the exception’s CCB-related usage subscription's identifiers.

PREM_KEY

Premise Dimension Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.CIS_EXT_PREM_ID

This field is populated based on the external premise ID stored on the exception’s service point’s identifiers.

VEE_EXCP_UDD1_KEY

VEE Exception User Defined Dimension 1 Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.UDD1_CD

VEE_EXCP_UDD2_KEY

VEE Exception User Defined Dimension 2 Surrogate Key

D1_SP_VEE_EXCP_SNAP_DL.UDD2_CD

User Defined Attributes

UDM1

User Defined Measure 1

D1_SP_VEE_EXCP_SNAP_DL.D1_UDM1

UDM2

User Defined Measure 2

D1_SP_VEE_EXCP_SNAP_DL.D1_UDM2

UDM3

User Defined Measure 3

D1_SP_VEE_EXCP_SNAP_DL.D1_UDM3

UDM4

User Defined Measure 4

D1_SP_VEE_EXCP_SNAP_DL.D1_UDM4

UDM5

User Defined Measure 5

D1_SP_VEE_EXCP_SNAP_DL.D1_UDM5

UDM6

User Defined Measure 6

D1_SP_VEE_EXCP_SNAP_DL.D1_UDM6

UDM7

User Defined Measure 7

D1_SP_VEE_EXCP_SNAP_DL.D1_UDM7

UDM8

User Defined Measure 8

D1_SP_VEE_EXCP_SNAP_DL.D1_UDM8

UDM9

User Defined Measure 9

D1_SP_VEE_EXCP_SNAP_DL.D1_UDM9

UDM10

User Defined Measure 10

D1_SP_VEE_EXCP_SNAP_DL.D1_UDM10

UDDGEN1

User Defined Degenerate Dimension 1

D1_SP_VEE_EXCP_SNAP_DL.D1_UDDGEN1

UDDGEN2

User Defined Degenerate Dimension 2

D1_SP_VEE_EXCP_SNAP_DL.D1_UDDGEN2

UDDGEN3

User Defined Degenerate Dimension 3

D1_SP_VEE_EXCP_SNAP_DL.D1_UDDGEN3

UDDGEN4

User Defined Degenerate Dimension 4

D1_SP_VEE_EXCP_SNAP_DL.D1_UDDGEN4

UDDGEN5

User Defined Degenerate Dimension 5

D1_SP_VEE_EXCP_SNAP_DL.D1_UDDGEN5

UDDGENL1

User Defined Long Degenerate Dimension 1

D1_SP_VEE_EXCP_SNAP_DLUDDGENLONG1

UDDGENL2

User Defined Long Degenerate Dimension 2

D1_SP_VEE_EXCP_SNAP_DLUDDGENLONG2

UDDGENL3

User Defined Long Degenerate Dimension 3

D1_SP_VEE_EXCP_SNAP_DLUDDGENLONG3

UDDGENL4

User Defined Long Degenerate Dimension 4

D1_SP_VEE_EXCP_SNAP_DLUDDGENLONG4

UDDGENL5

User Defined Long Degenerate Dimension 5

D1_SP_VEE_EXCP_SNAP_DLUDDGENLONG5

UDDFK1_KEY

User Defined Dimension Foreign Key 1

D1_SP_VEE_EXCP_SNAP_DL.D1_UDDFK1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

D1_SP_VEE_EXCP_SNAP_DL.D1_UDDFK2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

D1_SP_VEE_EXCP_SNAP_DL.D1_UDDFK3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

D1_SP_VEE_EXCP_SNAP_DL.D1_UDDFK4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

D1_SP_VEE_EXCP_SNAP_DL.D1_UDDFK5

Data Load Attributes

SNAPSHOT_DT

Snapshot Date

D1_SP_VEE_EXCP_SNAP_DL.SNAPSHOT_DTTM

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

MDM Objects Referenced in Oracle Utilities Extractors and Schema

This section provides details about the pseudo star schemas that are based on objects available in the Oracle Utilities Meter Data Management (MDM) application.

These star schemas are not based out of tables residing in the BI data warehouse, but are sourced out of specially designed materialized views residing directly in the Oracle Utilities Meter Data Management database.

Oracle Utilities Meter Data Management supports a type of aggregation that calculates the totals of measurements, by interval, for every combination of dimensional values. Because of the sheer volume of measurement data, the implementation of these aggregated measurements that are accessed via the materialized views can save a great deal of processing and allow implementation of KPIs much faster than if we have to extract, load, and transform the measurement data into star schemas.

These pseudo star schemas support the analyses in the Usage and Performance dashboards in Oracle Utilities Meter Data Analytics. These dashboards showcase the measurement data (quantity and count) at the interval level, as well as the timeliness of these interval data.

Each of the views described below are pseudo facts, each with a single pseudo dimension.

Measured Quantity Dimension Views

The Measured Quantity dimension view is used to flatten all the dimensional attributes (such as postal, head end, and usage calculation group) and aggregator measuring component IDs related to the Measured Quantity fact.

Properties

Property

Value

Name

D2_MEASR_QTY_MV

Table Type

Materialized View

Mapped Alias for Dimension

CD_MEASURED_QTY

Fields

Extract Field

Type

Description

MEASR_COMP_ID

CHAR(12)

Measuring Component ID

MEASR_COMP_TYPE_CD

VARCHAR2(30)

Measuring Component Type

MC_TYPE_DESCR

VARCHAR2(100)

Measuring Component Type Description

POSTAL

VARCHAR2(50)

Postal Code

CITY

VARCHAR2(50)

City

DEVICE_TYPE_CD

VARCHAR2(50)

Device Type

DEVICE_TYPE_DESCR

VARCHAR2(100)

Device Type Description

HEAD_END_SYSTEM_CD

VARCHAR2(50)

Head End System

HEAD_END_SYSTEM_DESCR

VARCHAR2(100)

Head End System Description

USG_CALC_GRP_CD

VARCHAR2(50)

Usage Calculation Group

USG_CALC_GRP_DESCR

VARCHAR2(100)

Usage Calculation Group Description

MKT_CD

VARCHAR2(50)

Market

MKT_DESCR

VARCHAR2(100)

Market Description

SPR_CD

VARCHAR2(50)

Service Provider

SPR_DESCR

VARCHAR2(100)

Service Provider Description

D1_SVC_TPE_CD

VARCHAR2(50)

Service Type

SVC_TYPE_DESCR

VARCHAR2(100)

Service Type Description

MKT_REL_TYPE_FLG

VARCHAR2(50)

Market Relationship Type

MKT_REL_TYPE_DESCR

VARCHAR2(60)

Market Relationship Type Description

MANUFACTURER_CD

VARCHAR2(50)

Manufacturer

MANUFACTURER_DESCR

VARCHAR2(100)

Manufacturer Description

D1_MODEL_CD

VARCHAR2(50)

Model

MODEL_DESCR

VARCHAR2(100)

Model Description

GEO_CODE

VARCHAR2(50)

Geographic Code

BUS_OBJ_CD

CHAR(30)

Business Object

Quality Count Dimension View

The Quality Count dimension view is used to flatten all the dimensional attributes (such as postal, head end, and usage calculation group) and aggregator measuring component IDs related to the Quality Count fact.

Properties

Property

Value

Name

D2_QUALITY_CNT_MV

Table Type

Materialized View

Mapped Alias for Dimension

CD_ QUALITY_CNT

Fields

Extract Field

Type

Description

MEASR_COMP_ID

CHAR(12)

Measuring Component ID

MEASR_COMP_TYPE_CD

VARCHAR2(30)

Measuring Component Type Code

MC_TYPE_DESCR

VARCHAR2(100)

Measuring Type Code Description

POSTAL

VARCHAR2(50)

Postal Code

CITY

VARCHAR2(50)

City

DEVICE_TYPE_CD

VARCHAR2(50)

Device Type

DEVICE_TYPE_DESCR

VARCHAR2(100)

Device Type Description

HEAD_END_SYSTEM_CD

VARCHAR2(50)

Head End System

HEAD_END_SYSTEM_DESCR

VARCHAR2(100)

Head End System Description

USG_CALC_GRP_CD

VARCHAR2(50)

Usage Calculation Group

USG_CALC_GRP_DESCR

VARCHAR2(100)

Usage Calculation Group Description

MKT_CD

VARCHAR2(50)

Market

MKT_DESCR

VARCHAR2(100)

Market Description

SPR_CD

VARCHAR2(50)

Service Provider

SPR_DESCR

VARCHAR2(100)

Service Provider Description

D1_SVC_TPE_CD

VARCHAR2(50)

Service Type

SVC_TYPE_DESCR

VARCHAR2(100)

Service Type Description

MKT_REL_TYPE_FLG

VARCHAR2(50)

Market Relationship Type

MKT_REL_TYPE_DESCR

VARCHAR2(60)

Market Relationship Type Description

MANUFACTURER_CD

VARCHAR2(50)

Manufacturer

MANUFACTURER_DESCR

VARCHAR2(100)

Manufacturer Description

D1_MODEL_CD

VARCHAR2(50)

Model

MODEL_DESCR

VARCHAR2(100)

Model Description

GEO_CODE

VARCHAR2(50)

Geographic Code

BUS_OBJ_CD

CHAR(30)

Business Object

Timeliness Count Dimension View

The Timeliness Count dimension view is used to flatten all the dimensional attributes (such as postal, head end, and usage calculation group) and aggregator measuring component IDs related to the Timeliness Count fact.

Properties

Property

Value

Name

D2_TIMELINESS_CNT_MV

Table Type

Materialized View

Mapped Alias for Dimension

CD_ TIMELINESS_CNT

Fields

Extract Field

Type

Description

MEASR_COMP_ID

CHAR(12)

Measuring Component ID

MEASR_COMP_TYPE_CD

VARCHAR2(30)

Measuring Component Type Code

MC_TYPE_DESCR

VARCHAR2(100)

Measuring Component Type Description

POSTAL

VARCHAR2(50)

Postal Code

CITY

VARCHAR2(50)

City

DEVICE_TYPE_CD

VARCHAR2(50)

Device Type

DEVICE_TYPE_DESCR

VARCHAR2(100)

Device Type Description

HEAD_END_SYSTEM_CD

VARCHAR2(50)

Head End System

HEAD_END_SYSTEM_DESCR

VARCHAR2(100)

Head End System Description

USG_CALC_GRP_CD

VARCHAR2(50)

Usage Calculation Group

USG_CALC_GRP_DESCR

VARCHAR2(100)

Usage Calculation Group Description

MKT_CD

VARCHAR2(50)

Market

MKT_DESCR

VARCHAR2(100)

Market Description

SPR_CD

VARCHAR2(50)

Service Provider

SPR_DESCR

VARCHAR2(100)

Service Provider Description

D1_SVC_TPE_CD

VARCHAR2(50)

Service Type

SVC_TYPE_DESCR

VARCHAR2(100)

Service Type Description

MKT_REL_TYPE_FLG

VARCHAR2(50)

Market Relationship Type

MKT_REL_TYPE_DESCR

VARCHAR2(60)

Market Relationship Type Description

MANUFACTURER_CD

VARCHAR2(50)

Manufacturer

MANUFACTURER_DESCR

VARCHAR2(100)

Manufacturer Description

D1_MODEL_CD

VARCHAR2(50)

Model

MODEL_DESCR

VARCHAR2(100)

Model Description

GEO_CODE

VARCHAR2(50)

Geographic Code

BUS_OBJ_CD

CHAR(30)

Business Object

Timeliness Quantity Dimension View

The Timeliness Quantity dimension view is used to flatten all the dimensional attributes (such as postal, head end, and usage calculation group) and aggregator measuring component IDs related to the Timeliness Quantity fact.

Properties

Property

Value

Name

D2_TIMELINESS_QTY_MV

Table Type

Materialized View

Mapped Alias for Dimension

CD_ TIMELINESS_QTY

Fields

Extract Field

Type

Description

MEASR_COMP_ID

CHAR(12)

Measuring Component ID

MEASR_COMP_TYPE_CD

VARCHAR2(30)

Measuring Component Type Code

MC_TYPE_DESCR

VARCHAR2(100)

Measuring Component Type Description

POSTAL

VARCHAR2(50)

Postal code

CITY

VARCHAR2(50)

City

DEVICE_TYPE_CD

VARCHAR2(50)

Device Type ID

DEVICE_TYPE_DESCR

VARCHAR2(100)

Device Type Description

HEAD_END_SYSTEM_CD

VARCHAR2(50)

Head End System

HEAD_END_SYSTEM_DESCR

VARCHAR2(100)

Head End System Description

USG_CALC_GRP_CD

VARCHAR2(50)

Usage Calculation Group

USG_CALC_GRP_DESCR

VARCHAR2(100)

Usage Calculation Group Description

MKT_CD

VARCHAR2(50)

Market

MKT_DESCR

VARCHAR2(100)

Market Description

SPR_CD

VARCHAR2(50)

Service Provider

SPR_DESCR

VARCHAR2(100)

Service Provider Description

D1_SVC_TPE_CD

VARCHAR2(50)

Service Type

SVC_TYPE_DESCR

VARCHAR2(100)

Service Type Description

MKT_REL_TYPE_FLG

VARCHAR2(50)

Market Relationship Type

MKT_REL_TYPE_DESCR

VARCHAR2(60)

Market Relationship Type Description

MANUFACTURER_CD

VARCHAR2(50)

Manufacturer

MANUFACTURER_DESCR

VARCHAR2(100)

Manufacturer Description

D1_MODEL_CD

VARCHAR2(50)

Model

MODEL_DESCR

VARCHAR2(100)

Model Description

GEO_CODE

VARCHAR2(50)

Geographic Code

BUS_OBJ_CD

CHAR(30)

Business Object

Measuring Component Dimension View

The Measuring Component dimension view is used to flatten all the dimensional attributes (such as postal, head end, and usage calculation group) and aggregator measuring component IDs related to the Measuring Component dimension.

Properties

Property

Value

Name

D2_MEASR_COMP_VW#1

Table Type

Materialized View

Mapped Alias for Dimension

CD_MEASR_COMP

Default Initialization String

SELECT * from D2_TIMELINESS_QTY_MV

UNION

SELECT * from D2_MEASR_QTY_MV

UNION

SELECT * from D2_QUALITY_CNT_MV

UNION

SELECT * from D2_TIMELINESS_CNT_MV

Fields

Extract Field

Type

Description

MEASR_COMP_ID

CHAR(12)

Measuring Component ID

MEASR_COMP_TYPE_CD

VARCHAR2(30)

Measuring Component Type Code

MC_TYPE_DESCR

VARCHAR2(100)

Measuring Component Type Description

POSTAL

VARCHAR2(50)

Postal code

CITY

VARCHAR2(50)

City

DEVICE_TYPE_CD

VARCHAR2(50)

Device Type ID

DEVICE_TYPE_DESCR

VARCHAR2(100)

Device Type Description

HEAD_END_SYSTEM_CD

VARCHAR2(50)

Head End System

HEAD_END_SYSTEM_DESCR

VARCHAR2(100)

Head End System Description

USG_CALC_GRP_CD

VARCHAR2(50)

Usage Calculation Group

USG_CALC_GRP_DESCR

VARCHAR2(100)

Usage Calculation Group Description

MKT_CD

VARCHAR2(50)

Market

MKT_DESCR

VARCHAR2(100)

Market Description

SPR_CD

VARCHAR2(50)

Service Provider

SPR_DESCR

VARCHAR2(100)

Service Provider Description

D1_SVC_TPE_CD

VARCHAR2(50)

Service Type

SVC_TYPE_DESCR

VARCHAR2(100)

Service Type Description

MKT_REL_TYPE_FLG

VARCHAR2(50)

Market Relationship Type

MKT_REL_TYPE_DESCR

VARCHAR2(60)

Market Relationship Type Description

MANUFACTURER_CD

VARCHAR2(50)

Manufacturer

MANUFACTURER_DESCR

VARCHAR2(100)

Manufacturer Description

D1_MODEL_CD

VARCHAR2(50)

Model

MODEL_DESCR

VARCHAR2(100)

Model Description

GEO_CODE

VARCHAR2(50)

Geographic Code

BUS_OBJ_CD

CHAR(30)

Business Object

Time of Use Map Dimension Table

The Time of Use Map dimension is based upon the Oracle Utilities Meter Data Management Time of Use Map table directly.

Properties

Property

Value

Name

CD_ TOU_MAP

Table Type

Physical Table

Default Initialization String

SELECT

A.D1_TOU_MAP_ID, D.DESCR100 TOU_MAP_DESCR, A.D1_TOU_CD, B.DESCR100 TOU_DESCR, A.TOU_MAP_DATA_DTTM

FROM

D1_TOU_MAP_DATA A, D1_TOU_L B, F1_INSTALLATION C, D1_TOU_MAP_L D

WHERE

B.D1_TOU_CD = A.D1_TOU_CD

AND B.LANGUAGE_CD = C.LANGUAGE_CD

AND A.D1_TOU_MAP_ID = D.D1_TOU_MAP_ID

AND D.LANGUAGE_CD = C.LANGUAGE_CD

Fields

Extract Field

Type

Description

D1_TOU_CD

VARCHAR(30)

Time of Use Code

D1_TOU_MAP_ID

CHAR(12)

Time of Use Map ID

TOU_MAP_DATA_DTTM

DATETIME

Time of Use Map Data Date Time

TOU_MAP_DESCR

VARCHAR2(100)

Time of Use Map Description

TOU_DESCR

VARCHAR2(100)

Geographical Code

Time of Use Language Dimension Table

The Time of Use Language dimension table will be mapped directly in the reports to retrieve the description of the Time of Use. Note that this mapping fetches the same description as CD_ TOU_MAP.TOU_DESCR, but this has been introduced to improve performance in certain reports.

Properties

Property

Value

Name

D1_TOU_L

Table Type

Physical Table

Fields

Extract Field

Type

Description

D1_TOU_CD

VARCHAR(30)

Time of Use Code

LANGUAGE_CD

CHAR(3)

Language Code

DESCR100

VARCHAR2(100)

Description

VERSION

NUMBER(5)

Version

Time of Use Map Language Dimension Table

The Time of Use Map Language dimension table will be mapped directly in the reports to retrieve the description of the Time of Use Map.

Note that this mapping fetches the same description as CD_ TOU_MAP.TOU_MAP_DESCR, but this has been introduced to improve performance in certain reports.

Properties

Property

Value

Name

D1_TOU_MAP_L

Table Type

Physical Table

Fields

Extract Field

Type

Description

D1_TOU_MAP_ID

CHAR(12)

Time of Use Map ID

LANGUAGE_CD

CHAR(3)

Language Code

DESCR100

VARCHAR2(100)

Description

VERSION

NUMBER(5)

Version

Measured Quantity Fact View

The Measured Quantity Fact contains the aggregated measurements of the dimensional attributes spread across the measurement condition.

Properties

Property

Value

Name

D2_MEASR_QTY_AGR_MV

Table Type

Materialized View

Mapped Alias for Fact

CF_MEASURED_QTY

Fields

Extract Field

Type

Description

MEASR_COMP_ID

CHAR(12)

Measuring Component ID

MSRMT_DT

DATE

Measurement Date

MSRMT_DTTM

DATE

Measurement Date Time

MSRMT_LOCAL_DTTM

DATE

Measurement Local Date Time

MSRMT_VAL

NUMBER(16,6)

Measured Quantity

MSRMT_VAL1

NUMBER(16,6)

Measurement Value1

MSRMT_VAL2

NUMBER(16,6)

Measurement Value2

MSRMT_VAL3

NUMBER(16,6)

Measurement Value3

MSRMT_VAL4

NUMBER(16,6)

Measurement Value4

MSRMT_VAL5

NUMBER(16,6)

Measurement Value5

MSRMT_VAL6

NUMBER(16,6)

Measurement Value6

MSRMT_VAL7

NUMBER(16,6)

Measurement Value7

MSRMT_VAL8

NUMBER(16,6)

Measurement Value8

MSRMT_VAL9

NUMBER(16,6)

Measurement Value9

Quality Count Fact View

The Quality Count fact contains the aggregated count for each interval of the dimensional attributes spread across measurement conditions.

Properties

Property

Value

Name

D2_QUALITY_CNT_AGR_MV

Table Type

Materialized View

Mapped Alias for Fact

CF_QUALITY_CNT

Fields

Extract Field

Type

Description

MEASR_COMP_ID

CHAR(12)

Measuring Component ID

MSRMT_DT

DATE

Measurement Date

MSRMT_DTTM

DATE

Measurement Date Time

MSRMT_LOCAL_DTTM

DATE

Measurement Local Date Time

MSRMT_VAL

NUMBER(16,6)

Measured Quantity

MSRMT_VAL1

NUMBER(16,6)

Measurement Value1

MSRMT_VAL2

NUMBER(16,6)

Measurement Value2

MSRMT_VAL3

NUMBER(16,6)

Measurement Value3

MSRMT_VAL4

NUMBER(16,6)

Measurement Value4

MSRMT_VAL5

NUMBER(16,6)

Measurement Value5

MSRMT_VAL6

NUMBER(16,6)

Measurement Value6

MSRMT_VAL7

NUMBER(16,6)

Measurement Value7

MSRMT_VAL8

NUMBER(16,6)

Measurement Value8

MSRMT_VAL9

NUMBER(16,6)

Measurement Value9

Timeliness Count Fact View

The Timeliness Count fact contains the aggregated count per interval spread across timeliness buckets.

Properties

Property

Value

Name

D2_TIMELINESS_CNT_AGR_MV

Table Type

Materialized View

Mapped Alias for Fact

CF_TIMELINESS_CNT

Fields

Extract Field

Type

Description

MEASR_COMP_ID

CHAR(12)

Measuring Component ID

MSRMT_DT

DATE

Measurement Date

MSRMT_DTTM

DATE

Measurement Date Time

MSRMT_LOCAL_DTTM

DATE

Measurement Local Date Time

MSRMT_VAL

NUMBER(16,6)

Measured Quantity

MSRMT_VAL1

NUMBER(16,6)

Measurement Value1

MSRMT_VAL2

NUMBER(16,6)

Measurement Value2

MSRMT_VAL3

NUMBER(16,6)

Measurement Value3

MSRMT_VAL4

NUMBER(16,6)

Measurement Value4

MSRMT_VAL5

NUMBER(16,6)

Measurement Value5

MSRMT_VAL6

NUMBER(16,6)

Measurement Value6

MSRMT_VAL7

NUMBER(16,6)

Measurement Value7

MSRMT_VAL8

NUMBER(16,6)

Measurement Value8

MSRMT_VAL9

NUMBER(16,6)

Measurement Value9

Timeliness Quantity Fact View

The Timeliness Quantity fact contains the aggregated measurements per interval spread across the timeliness buckets.

Properties

Property

Value

Name

D2_TIMELINESS_QTY_AGR_MV

Table Type

Materialized View

Mapped Alias for Fact

CF_TIMELINESS_QTY

Fields

Extract Field

Type

Description

MEASR_COMP_ID

CHAR(12)

Measuring Component ID

MSRMT_DT

DATE

Measurement Date

MSRMT_DTTM

DATE

Measurement Date Time

MSRMT_LOCAL_DTTM

DATE

Measurement Local Date Time

MSRMT_VAL

NUMBER(16,6)

Measured Quantity

MSRMT_VAL1

NUMBER(16,6)

Measurement Value1

MSRMT_VAL2

NUMBER(16,6)

Measurement Value2

MSRMT_VAL3

NUMBER(16,6)

Measurement Value3

MSRMT_VAL4

NUMBER(16,6)

Measurement Value4

MSRMT_VAL5

NUMBER(16,6)

Measurement Value5

MSRMT_VAL6

NUMBER(16,6)

Measurement Value6

MSRMT_VAL7

NUMBER(16,6)

Measurement Value7

MSRMT_VAL8

NUMBER(16,6)

Measurement Value8

MSRMT_VAL9

NUMBER(16,6)

Measurement Value9