This chapter describes the Data Flow from the fact tables and dimension tables of Oracle Utilities Data Model foundation layer to the target materialize views and cubes of the Analytical Layer to support Oracle Utilities Data Model OLAP.
This chapter includes the following sections:
For more information, see Oracle Utilities Data Model OLAP Model Cubes.
Oracle Data Warehouse for utilities (Oracle Utilities Data Model Relational) contains the lowest level Meter Reading details measuring consumption and detection of events, low level combination of base tables and the summary, average, and so on, of Base and Derived data. Oracle Utilities Data Model was developed in a relational database.
Oracle Utilities Data Model oudm_sys schema does the following:
Directly maps the leaf level data from the relational table/mv into the OLAP cube.
Cube organized materialized views represent the cube to SQL-based applications as materialized views that you can use for both refresh and query rewrite. With Query Re-write enabled, Oracle will automatically re-write SQL queries targeted against relational tables. to use the Cube-Organized Materialized View. To use this feature the OLAP cubes and relational components are in a single schema (oudm_sys).
All cubes are available for the end user SQL based Query Tool access through CUBE_TABLE based SQL Views, which are created and maintained automatically during the cube build/update process.
Cubes are built from level 0 DWA materialized views or DWR tables (which, when a date is present, usually means at the month level).
Using SQL to access the cubes and dimensions is a significant feature of Oracle OLAP because it enables reporting tools that only generate SQL to use all of the powerful features of the analytic workspace. In Oracle Database 11g this is achieved by the use of the CUBE_TABLE function that extracts multidimensional data from a cube in an analytic workspace and presents it to the relational SQL engine in the form of a two dimensional table, such as, a set of rows and columns. It provides a mapping between the cube in the analytic workspace and the rows and columns that the SQL sees.
Oracle Utilities Data Model uses SQL to query the relational base tables and the optimizer transparently translates the SQL to access either the table materialized views or the cube materialized views (and hence the analytic workspace cubes and dimensions) depending upon which provides the better performance. This allows all of the benefits of the analytic workspace to be easily available to any product using regular SQL.
This dimension keeps all the account information.
Table 8-1 Account (ACCT) Levels and Hierarchies
| Level | Description | Account Hierarchy (HACCT) |
|---|---|---|
|
TACCT |
Total Account |
TACCT |
|
ACCT |
Account |
ACCT |
Attribute Name: Long Description(LONG_DESCRIPTION)
Table 8-2 Account Long Description Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TACCT |
'Total Account' |
|
ACCT |
DWR_ACCT.ACCT_DSCR |
Attribute Name: Short Description(SHORT_DESCRIPTION)
Table 8-3 Account Short Description Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TACCT |
'Total Account' |
|
ACCT |
DWR_ACCT.ACCT_CD |
Table 8-4 Customer (CUST) Levels and Hierarchies
| Level | Description | Hierarchy (HCUSTOMER) |
|---|---|---|
|
CUST |
Customer |
CUST |
|
TCUST |
Total customer |
TCUST |
Attribute Name: Long Description(LONG_DESCRIPTION)
Table 8-5 Customer Long Description Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
CUST |
DWR_CUST.LAST_NAME |
|
TCUST |
"Total Customer" |
Attribute Name: Short Description(SHORT_DESCRIPTION)
Table 8-6 Customer Short Description Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
CUST |
DWR_CUST.CUST_CD |
|
TCUST |
"Total Customer" |
This dimension keeps all the usage point information at lower levels with geography information at higher levels, such as city, state, and so on.
Table 8-7 Geography Usage Point (GEOUP) Levels and Attributes
| Level | Description | Geography Usage Point Hierarchy (HGOUP) |
|---|---|---|
|
TGEOUP |
Total Geographical Usage Point |
TGEOUP |
|
STATE |
STATE |
STATE |
|
CITY |
CITY |
CITY |
|
GUP |
GEOGRAPHY USAGE POINT |
GUP |
Attribute Name: Long Description (LONG_DESCRIPTION)
Table 8-8 Geography Usage Point Long Description Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TGEOUP |
'Total Geography UsagePoint' |
|
STATE |
DWV_GEOGRAPHY_ZONES_DIM.GEO_STATE_NAME |
|
CITY |
DWV_GEOGRAPHY_ZONES_DIM.GEO_CITY_NAME |
|
GUP |
DWV_GEOGRAPHY_ZONES_DIM.USG_PNT_CD |
Attribute Name: Short Description(SHORT_DESCRIPTION)
Table 8-9 Geography Usage Point Short Description Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TGEOUP |
'Total Geography UsagePoint' |
|
STATE |
DWV_GEOGRAPHY_ZONES_DIM.GEO_STATE_CD |
|
CITY |
DWV_GEOGRAPHY_ZONES_DIM.GEO_CITY_CD |
|
GUP |
DWV_GEOGRAPHY_ZONES_DIM.USG_PNT_CD |
This dimension keeps the information of product asset model at lower level and manufacturer information at higher level.
Table 8-10 Manufacturer (MNFCTR) Levels and Hierarchies
| Level | Description | Manufacturer Hierarchy (HMNFCTR) |
|---|---|---|
|
TMNFCTR |
Total Manufacturers |
'TMNFCTR |
|
MNFCTR |
Manufacturer |
MNFCTR |
|
PRASTMDL |
Product Asset Model |
PRASTMDL |
Attribute Name: Long Description (LONG_DESCRIPTION)
Table 8-11 Manufacturer Long Description Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TMNFCTR |
'Total Manufacturer' |
|
MNFCTR |
DWR_MNFCTR.NAME |
|
PRASTMDL |
DWR_PROD_ASST_MDL.ASST_MDL_USG_KIND_CD |
Attribute Name: Short Description (SHORT_DESCRIPTION)
Table 8-12 Manufacturer Short Description Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TMNFCTR |
'Total Manufacturer' |
|
MNFCTR |
DWR_MNFCTR.MNFCTR_CD |
|
PRASTMDL |
DWR_PROD_ASST_MDL.ALS_NAME |
This dimension keeps all the meter related information.
Table 8-13 Meter (MTR) levels and Hierarchies
| Level | Description | Meter Hierarchy (HMTR) |
|---|---|---|
|
TMTR |
Total Meters |
TMTR |
|
MTR |
Meter |
MTR |
Attribute Name: Long Description (LONG_DESCRIPTION)
Table 8-14 Meter Long Description Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TMTR |
'Total Meter' |
|
MTR |
DWR_MTR.NAME |
Attribute Name: Short Description (SHORT_DESCRIPTION)
Table 8-15 Meter Short Description Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TMTR |
'Total Meter' |
|
MTR |
DWR_MTR.MTR_CD |
This dimension keeps information of usage point at lower level and electricity power operational information at higher levels. For example, transformer, feeder, and substation.
Table 8-16 Operational Usage Point (OPTUP) Levels and Hierarchies
| Level | Description | Operational Usage Point Hierarchy (HOPTUP) |
|---|---|---|
|
TOPTUP |
Total Operational Usage Point |
TOPTUP |
|
SBSTN |
Substation |
SBSTN |
|
FDR |
Feeder |
FDR |
|
TRTK |
Transformer Tank |
TRTK |
|
OPTUP |
Operation Usage Point |
OPTUP |
Attribute Name: Long Description (LONG_DESCRIPTION)
Table 8-17 Operational Usage Point Long Description Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TOPTUP |
'Total Operational UsagePoint' |
|
SBSTN |
DWV_OPERATIONAL_DIM.SBSTN_NAME |
|
FDR |
DWV_OPERATIONAL_DIM.FEDR_NAME |
|
TRTK |
DWV_OPERATIONAL_DIM.TRNSFRMR_TANK_CD |
|
OPTUP |
DWV_OPERATIONAL_DIM.USG_PNT_CD |
Attribute Name: Short Description (SHORT_DESCRIPTION)
Table 8-18 Operational Usage Point Short Description Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TOPTUP |
'Total Operational UsagePoint' |
|
SBSTN |
DWV_OPERATIONAL_DIM.SBSTN_CD |
|
FDR |
DWV_OPERATIONAL_DIM.FEDR_CD |
|
TRTK |
DWV_OPERATIONAL_DIM.TRNSFRMR_TANK_CD |
|
OPTUP |
DWV_OPERATIONAL_DIM.USG_PNT_CD |
This dimension keeps information of usage point at lower level and regional information at higher levels. For example, sub-region and region.
Table 8-19 Regional Usage Point (RGUP) Levels and Hierarchies
| Level | Description | Regional Usage Point Hierarchy (HRGUP) |
|---|---|---|
|
TRGUP |
Total Regional Usage Point |
TRGUP |
|
RG |
Region |
RG |
|
SUBRG |
Sub Region |
SUBRG |
|
RGUP |
Regional Usage Point |
RGUP |
Attribute Name: Long Description (LONG_DESCRIPTION)
Table 8-20 Regional Usage Point Long Description Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TRGUP |
'Total Regional UsagePoint' |
|
RG |
DWV_REGIONAL_ZONES_DIM.GEO_RGN_NAME |
|
SUBRG |
DWV_REGIONAL_ZONES_DIM.GEO_SB_RGN_NAME |
|
RGUP |
DWV_REGIONAL_ZONES_DIM.USG_PNT_LOC_KEY |
Attribute Name: Short Description (SHORT_DESCRIPTION)
Table 8-21 Regional Usage Point Short Description Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TRGUP |
'Total Regional UsagePoint' |
|
RG |
DWV_REGIONAL_ZONES_DIM.GEO_RGN_NAME |
|
SUBRG |
DWV_REGIONAL_ZONES_DIM.GEO_SB_RGN_NAME |
|
RGUP |
DWV_REGIONAL_ZONES_DIM.USG_PNT_LOC_KEY |
Table 8-22 Time (TIME) Levels and Hierarchies
| Level | Description | Time Business Hierarchy (HTCLNDR) |
|---|---|---|
|
TTIME |
Total Time |
TTIME |
|
CLNDR_YR |
Calendar Year |
CLNDR_YR |
|
CLNDR_HLF_YR |
Calendar Half Year |
CLNDR_HLF_YR |
|
CLNDR_QTR |
Calendar Quarter |
CLNDR_QTR |
|
CLNDR_MO |
Calendar Month |
CLNDR_MO |
Attribute Name: Long Description (LONG_DESCRIPTION)
Table 8-23 Time Long Description Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TTIME |
DWR_TIME_TOT.TOT_DSCR |
|
CLNDR_YR |
DWR_CLNDR_YR.CLNDR_YR_DSCR |
|
CLNDR_HLF_YR |
DWR_CLNDR_HLF_YR.CLNDR_HLF_YR_DSCR |
|
CLNDR_QTR |
DWR_CLNDR_QTR.CLNDR_QTR_DSCR |
|
CLNDR_MO |
DWR_CLNDR_MO.CLNDR_MO_DSCR |
Attribute Name: Short Description(SHORT_DESCRIPTION)
Table 8-24 Time Short Description Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TTIME |
DWR_TIME_TOT.TOT_CD |
|
CLNDR_YR |
DWR_CLNDR_YR.CLNDR_YR_KEY |
|
CLNDR_HLF_YR |
DWR_CLNDR_HLF_YR.CLNDR_HLF_YR_KEY |
|
CLNDR_QTR |
DWR_CLNDR_QTR.CLNDR_QTR_KEY |
|
CLNDR_MO |
DWR_CLNDR_MO.CLNDR_MO_KEY |
Attribute Name: Time Number (TIME_NBR)
Table 8-25 Time Time Number Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TTIME |
DWR_TIME_TOT.TOT_NBR |
|
CLNDR_YR |
DWR_CLNDR_YR.CLNDR_YR_NBR |
|
CLNDR_HLF_YR |
DWR_CLNDR_HLF_YR.CLNDR_HLF_YR_NBR |
|
CLNDR_QTR |
DWR_CLNDR_QTR.CLNDR_QTR_NBR |
|
CLNDR_MO |
DWR_CLNDR_MO.CLNDR_MO_NBR |
Attribute Name: Time Span (TIME_SPAN)
Table 8-26 Time Time Span Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TTIME |
DWR_TIME_TOT.TOT_TIMESPN |
|
CLNDR_YR |
DWR_CLNDR_YR.CLNDR_YR_TIMESPN |
|
CLNDR_HLF_YR |
DWR_CLNDR_HLF_YR.CLNDR_HLF_YR_TIMESPN |
|
CLNDR_QTR |
DWR_CLNDR_QTR.CLNDR_QTR_TIMESPN |
|
CLNDR_MO |
DWR_CLNDR_MO.CLNDR_MO_TIMESPN |
Attribute Name: Start Date(START_DATE)
Table 8-27 Time Start Date Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TTIME |
DWR_TIME_TOT.TOT_STRT_DT |
|
CLNDR_YR |
DWR_CLNDR_YR.CLNDR_YR_STRT_DT |
|
CLNDR_HLF_YR |
DWR_CLNDR_HLF_YR.CLNDR_HLF_YR_STRT_DT |
|
CLNDR_QTR |
DWR_CLNDR_QTR.CLNDR_QTR_STRT_DT |
|
CLNDR_MO |
DWR_CLNDR_MO.CLNDR_MO_STRT_DT |
Attribute Name: End Date (END_DATE)
Table 8-28 Time End Date Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TTIME |
DWR_TIME_TOT.TOT_END_DT |
|
CLNDR_YR |
DWR_CLNDR_YR.CLNDR_YR_END_DT |
|
CLNDR_HLF_YR |
DWR_CLNDR_HLF_YR.CLNDR_HLF_YR_END_DT |
|
CLNDR_QTR |
No value |
|
CLNDR_MO |
DWR_CLNDR_MO.CLNDR_MO_END_DT |
Table 8-29 Usage Point (USGPNT) Levels and Hierarchies
| Level | Description | Usage Point Hierarchy (HUP) |
|---|---|---|
|
TUSGPT |
Total Usage Point |
TUSGPT |
|
USGPT |
Usage Point |
USGPT |
Attribute Name: Long Description(LONG_DESCRIPTION)
Table 8-30 Usage Point Long Description Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TUSGPT |
'Total UsagePoint' |
|
USGPT |
DWR_USG_PNT.USG_PNT_CD |
Attribute Name: Short Description(SHORT_DESCRIPTION)
Table 8-31 Usage Point Short Description Attribute Mapping
| Level | Mapping (Physical Column) |
|---|---|
|
TUSGPT |
'Total UsagePoint' |
|
USGPT |
DWR_USG_PNT.USG_PNT_CD |