Viewing Dynamic Aggregation Results Using Oracle Utilities Analytics Visualization
This topic describes objects used to support viewing dynamic aggregation results using Oracle Utilities Analytics Visualization. Refer to the Oracle Utilities Analytics Visualization User Guide for more information about using Oracle Utilities Analytics Visualization.
Dynamic Aggregation Datasets
The Oracle Utilities Analytics Visualization demonstration database includes predefined datasets that can be used to view dynamic aggregation results using Oracle Utilities Analytics Visualization.
The “Meter — Usage by Network Location” and “Meter — Usage by Customer Class” sample datasets allow access to dynamic aggregation results. Users access these from the Datasets tab in the Oracle Business Intelligence user interface.
These datasets use SQL to retrieve data from the dynamic aggregation results.
Meter — Usage by Network Location
select VW.MEASR_COMP_SET_CD MC_Set,
VW.AGG_MEASR_COMP_ID MC,
VW.AGG_ATTR1 City,
VW.AGG_ATTR2 Postal,
decode(VW.AGG_ATTR3, 'D1CD', 'Commodity', VW.AGG_ATTR3) Relation,
VW.AGG_ATTR4 Facility_ID,
nvl(FI.ID_VALUE, 'N/A') Transformer, /* Facility Identifier */
nvl(NL.FEEDER, 'N/A') FEEDER,
nvl(NL.SUBST, 'N/A') SUBST,
VW.MSRMT_DTTM Date_Time,
VW.AGG_VAL Usage,
VW.AGG_VAL1 Load
from D1_BI_DYN_AGG_VW VW
left join (select * from
(select distinct L.FACILITY_ID, NETWORK_NODE, NETWORK_NODE_TYPE_FLG
from D1_NW_NODE N
inner join D1_NW_LOC L on L.NETWORK_LOCATION_ID = N.NETWORK_LOCATION_ID)
pivot (max(NETWORK_NODE) for NETWORK_NODE_TYPE_FLG
in ('D1-Feeder' as FEEDER, 'D1-Substation' as SUBST))) NL on NL.FACILITY_ID = VW.AGG_ATTR4
left join D1_FACILITY_IDENTIFIER FI on FI.FACILITY_ID = VW.AGG_ATTR4 and FI.FACILITY_ID_TYPE_FLG = 'D1EI'
where MEASR_COMP_SET_CD IN('DA-E-CONS-TRANS')Meter — Usage by Customer Class
select VW.MEASR_COMP_SET_CD MC_Set,
VW.AGG_MEASR_COMP_ID MC,
VW.AGG_ATTR1 City,
VW.AGG_ATTR2 Postal,
VW.AGG_ATTR3 Rate_Class_CD,
decode(VW.AGG_ATTR3, 'E-TOU', 'Electric TOU',
'ER-BASIC', 'Electric Residential Basic',
'ER-DMINU', 'Electric Residential Stepped',
VW.AGG_ATTR3) Rate_Class,
nvl(RS.DESCR, VW.AGG_ATTR3) Rate_Descr,
VW.AGG_ATTR4 Profile_Class_CD,
nvl(PR.DESCR, VW.AGG_ATTR4) Profile_Class,
VW.MSRMT_DTTM Date_Time,
VW.AGG_VAL Usage,
VW.AGG_VAL1 Load
from D1_BI_DYN_AGG_VW VW
left join CI_RS_L RS on RS.RS_CD = VW.AGG_ATTR3
and RS.LANGUAGE_CD = 'ENG'
left join CI_CHAR_VAL_L PR on PR.CHAR_TYPE_CD = 'DM_PROCL'
and trim(PR.CHAR_VAL) = VW.AGG_ATTR4
and PR.LANGUAGE_CD = 'ENG'
where MEASR_COMP_SET_CD IN('DA-E-CONS-PROF')
The SQL used by these datasets must be updated and adjusted based on the dimensions used in your dynamic aggregation configuration, and the Measuring Component Set (MEASR_COMP_SET_CD).
Dynamic Aggregation View — Dynamic Aggregated Measurements
The application includes a “Dynamic Aggregated Measurements” view which can be used when viewing dynamic aggregation results using the “Meter — Usage by Network Location” and “Meter — Usage by Customer Class” sample datasets via Oracle Utilities Analytics Visualization. Details for this view are provided below.
Properties
|
Name |
D1_BI_DYN_AGG_VW |
|
Description |
Dynamic Aggregated Measurements |
|
Date/Time Data Type |
Local Legal Time |
|
Upgrade |
Keep |
|
Key Validation |
Always Check Uniqueness |
Columns
Columns without extra specification are standard existing fields.
|
Seq |
Column |
Nullable |
Key |
Comment |
|---|---|---|---|---|
|
1 |
MEASR_COMP_SET_CD |
No |
PK, FK |
D1_MEASR_COMP_SET |
|
2 |
AGG_MEASR_COMP_ID |
No |
PK |
|
|
3 |
MSRMT_DTTM |
Yes |
PK |
|
|
4 |
AGG_ATTR1 |
Yes |
||
|
5 |
... |
Yes |
||
|
6 |
AGG_ATTR20 |
Yes |
||
|
7 |
AGG_VAL |
Yes |
||
|
8 |
AGG_VAL1 |
Yes |
||
|
9 |
... |
Yes |
||
|
10 |
AGG_VAL10 |
Yes |
Constraints
|
ID |
Column |
Flag |
Table |
Referred |
|---|---|---|---|---|
|
MEASR_COMP_SET_CD |
PK |
|||
|
AGG_MEASR_COMP_ID |
PK |
|
||
|
MSRMT_DTTM |
PK |
|||
|
MEASR_COMP_SET_CD |
FK |
D1_MEASR_COMP_SET |
MEASR_COMP_SET_CD |