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 | This field is populated with the start date/time of the activity. |
END_DTTM | End Date/Time | D1_ACTIVITY.END_DTTM | This field is populated with the end date/time of the activity. |
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 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 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.
This 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 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. |
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 second 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 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 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. |
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 Warehouse chapter in the Oracle Utilities Analytics Warehouse Installation and Configuration 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 second 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 Warehouse chapter in the Oracle Utilities Analytics Warehouse Installation and Configuration 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 second 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 Warehouse chapter in the Oracle Utilities Analytics Warehouse Installation and Configuration 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 second 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 second 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 Warehouse chapter in the Oracle Utilities Analytics Warehouse Installation and Configuration 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 second 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 second 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. | |