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