1 Introducing Oracle Utilities Data Model

This chapter introduces the Oracle Utilities Data Model, which is a standards-based, pre-built approach to utilities data warehousing.

This chapter includes the following sections:

Overview of Oracle Utilities Data Model

Oracle Utilities Data Model is a pre-built, standards-based data warehouse solution designed and optimized for Oracle database and hardware. Oracle Utilities Data Model can be used in any applications environment and is easily extensible. Oracle Utilities Data Model enables utilities to establish a foundation for business intelligence and analytics across the enterprise, allowing each business domain to leverage a common analytics infrastructure and pre-defined cross-domain relationships, driving unprecedented levels of intelligence and discovery.

Oracle Utilities Data Model offers a single-vendor solution package that is tightly integrated with the business intelligence platform. With pre-built data mining, Oracle Online Analytical Processing (Oracle OLAP) and dimensional models, Oracle Utilities Data Model provides you with industry-specific metrics and insights that you can act on immediately to improve your bottom line. These business intelligence solution offerings take advantage of Oracle's scalability and reliability, using Oracle's familiar optimization, parallelism, and performance engineering within the database.

Oracle Utilities Data Model includes an exhaustive set of embedded advanced analytics, using Oracle's OLAP and data mining technology. You can take advantage of pre-built and pre-tested solution sets designed by industry experts that deliver relevant insights, are actionable, and aimed at improving both top-line and bottom-line results. You can see summarized, aggregated information or quickly navigate to drill-down transaction details to better understand business issues.

Oracle Utilities Data Model, combined with Oracle technology, provides all of the components required for a complete and extendable utilities Data Warehouse and Business Intelligence framework to eliminate complex and costly integration requirements, all designed to reduce your total cost of ownership.

What Are the Benefits of Using Oracle Utilities Data Model?

With Oracle Utilities Data Model, you can jump-start the design and implementation of a data warehouse to quickly achieve a positive ROI for your data warehousing and business intelligence project with a predictable implementation effort.

Oracle Utilities Data Model provides the following features:

Query and Reporting for information: provides extraction of detailed and summary data.
OLAP for data analysis: provides summaries, trends, and forecasts.
Data Mining for insight and prediction: provides knowledge discovery of hidden patterns and insights.
Oracle Utilities Data Model is aligned with the CIM Users Group. For more information, see "About Common Information Model (CIM) Users Group".

Oracle Utilities Data Model provides an off-the-shelf data warehouse framework that is both adaptable and extendable. Alignment with utilities industry standards ensures interoperability with other systems. The pre-built, pretuned data model with intelligent insight into detailed utilities and market data, allows you to quickly gain value from your data warehousing effort, supports diverse analytical requirements, and assists in building future analytical applications. Fast, easy and predictable implementation reduces risks and enables you to achieve strategic value more rapidly by eliminating deployment delays and expenses associated with built-from-scratch or proprietary data warehouse solutions.

Benefit	Description
Delivers Compelling Business Improvements	Accelerate business value with configurable, pre-built data model and analytics samples Provide better service and decision making by enabling the cross-domain data and business analysis Increase operational efficiency with streamlined and enterprise scale Analytics/DW solution
Provides Common, Accurate Data Definition Across All Applications	Improve quality and information accuracy between applications and establish a single source of truth Join new, existing or 3rd party customer and operational information on a robust, open industry standards-based platform
Provides Common, Accurate Data Definition Across All Applications	Reduce need for costly custom BI/DW development with pre-built model and architecture based on best practices Accelerate user adoption of Analytics/DW strategy and drive holistic analytics use as strategic business differentiator Simplify model extensions as future analytical needs change

What Are the Components of Oracle Utilities Data Model?

Oracle Utilities Data Model includes the following components:

Logical Data Model Foundation
Logical Data Model Dimensions
Physical Model
Intra-ETL database packages and SQL scripts to extract, transform, and load (ETL) data from one layer of Oracle Utilities Data Model to another.
OLAP Models for Oracle Utilities Data Model
Pre-defined Data Mining Model
Utility Scripts
Reports and dashboards
Installation scripts

What Oracle Technologies are in Oracle Utilities Data Model

Several Oracle technologies are involved in building the infrastructure:

Oracle Database with OLAP, Data Mining and Partitioning Option

Oracle Utilities Data Model utilizes a complete Oracle technical stack. It leverages the following data warehousing features of the Oracle database: SQL model, compression, partitioning, advanced statistical functions, materialized views, data mining, and online analytical processing (OLAP).

Tip:

To achieve cost-effective scalability, availability, and reliability, you can consider using Oracle Real Application Clusters (Oracle RAC) and commodity hardware.

Oracle Development Tools

Use the Oracle tools shown in Table 1-1 to customize the predefined logical and physical models provided with Oracle Utilities Data Model, or to populate the target relational tables, materialized views, or OLAP cubes.

Table 1-1 Oracle Development Tools Used with Oracle Utilities Data Model

Name	Use
Oracle SQL Data Modeler	To create the logical model
SQL Developer or SQL*Plus	To create or modify database objects
Analytic Workspace Manager	To populate the target OLAP cubes

Oracle Business Intelligence Suite Enterprise Edition Presentation Tools

Oracle Business Intelligence Suite Enterprise Edition is a comprehensive suite of enterprise Business Intelligence products that delivers a full range of analysis and reporting capabilities. You can use Oracle Business Intelligence Suite Enterprise Edition Answers and Dashboard presentation tools to customize the predefined dashboard reports that are provided with Oracle Utilities Data Model.

What is Oracle Utilities Data Model

Oracle Utilities Data Model leverages several Oracle Database data warehouse and Business Intelligence concepts that need to be clarified to understand the structure and use of Oracle Utilities Data Model.

Oracle Utilities Data Model provides "One Single True Vision of the Business". This unique architecture provides the utilities Service Provider (CSP) Flexibility, Agility, Scalability and Accuracy to obtain a real competitive advantage.

A typical enterprise data warehouse architecture, as shown in Figure 1-1, is composed of several layers ordered by the growing actionable value of the information in the warehouse:

The Data Source layer (operational systems, Commercial-Off-The shelf solution, unstructured and syndicated data, with possibly a Master Data Management system).
The Staging layer: Typically used for transformation and data cleansing. It is also sometimes used as Operational Data Store, in particular for real-time operational reporting.
The Foundation layer: It is typically used to store all transactions and reference data at the most atomic level. Best practices require that this level is 3rd normal form, to avoid data redundancy.
The Access and Performance or Analytical layer: this is the layer optimized for the business end-users. It usually contains the star schema to answer business questions, as well as OLAP tools and mining models.
The Information (or Information Access) layer: This is the metadata layer and above, accessed by end-users through their Business Intelligence and/or reporting tools, or even external analytical tools (other OLAP or Mining tools). This layer is usually changeable by normal end-users (within their roles and responsibility). This is where the performance management applications provide their reports, where user roles, alerts, guided analytics, dashboards and reports are defined (usually by a specific BI administrator).
The data movement from one layer to the other is run through ETL / ELT tools. One distinguishes the standard ETL/ELT (from data sources to foundation layer) from the intra-ETLs (from foundation layer up to the reporting).

Figure 1-1 Data Warehouse Reference Architecture with Oracle Utilities Data Model

Description of "Figure 1-1 Data Warehouse Reference Architecture with Oracle Utilities Data Model "

Within a standard enterprise data warehouse architecture, as shown in Figure 1-1, if an adapter is used, for example DataRaker, the Staging area is also provided. Oracle Utilities Data Model covers Foundation Layer, plus the intra-ETL part, and includes parts of the reporting area if OBIEE is used (Oracle Utilities Data Model also includes the pre-built OBIEE repository).

To summarize Oracle Utilities Data Model includes the parts shown in Figure 1-2.

Figure 1-2 Oracle Utilities Data Model Inner Structure

Description of "Figure 1-2 Oracle Utilities Data Model Inner Structure"

The Oracle Utilities Data Model Foundation Layer is composed of the components shown in Table 1-2.

Table 1-2 Oracle Utilities Data Model Foundation Layer Components

Component	Usage
Reference entities and tables	Used to store master reference entities required by a utility operation Non-changing infrequently-changing data These entities translate into dimensions and hierarchies Physically, table names start with "DWR_"
Base entities and tables	Store transactions from systems of record. It contains data at atomic level with the lowest level of granularity possible Required to perform detailed analysis, and uncovering causal effects and associations Physically, table names start with "DWB_"
Lookup entities and tables	Hold descriptions for common code lookups. Their goal is to save space since one does not have to store long descriptions in each transaction record. Physically, table names start with "DWL_"
Control tables	These are only used and filled by the intra-ETLs. Physically, tables names start with "DWC_"

The Analytic Layer serves as an abstraction layer to simplify analytical access; this layer is a subject oriented representation of data ("shellfish" model). The analytic layer is easily understood by end-users and is simpler to navigate. This layer consists of aggregates, summaries, hierarchical relationships, and so on. The analytic layer is composed of star schemas, materialized views, OLAP cubes, and so on and is populated using intra-ETL processes from data in the Foundation Data Layer (FDL). The Oracle Utilities Data Model Analytic layer is composed of the components shown in Table 1-3.

Table 1-3 Oracle Utilities Data Model Analytic Layer Components

Component Usage

Component	Usage
DERIVED entities and tables	Provide a transition level to STARs. This layer is denormalized and is typically used for operational reporting and data mining, to uncover new insights and predict the future and: Provides information that can only be derived from base data, usually at day level. Leverages data mining, advanced statistics, and complex queries. Physically, tables start with "`DWD_`". Examples of derived tables include: `DWD_MTR_RDNG_DAY`, for meter reading day, `DWD_OUTG_DAY`, for outage by day, and `DWD_RLBLTY_IND_CITY_MO` for reliability indicies by city by month, and so on. There is also a mining model at this level:.
AGGREGATE entities and tables	Provide information to analyze and summarize, usually at the monthly level and: Leverages base and derived data models to provide aggregated data such as summaries, averages, and so on. Enables dimensional analysis on wide variety of subject areas. Leverages Oracle OLAP cubes (pre-built OLAP cubes are available. For more information, see Oracle Utilities Data Model OLAP Model Cubes. Contains tables starting with "DWA_"; usually materialized views. Represents the information access layer: It covers all the metadata.

DERIVED entities and tables

Provide a transition level to STARs. This layer is denormalized and is typically used for operational reporting and data mining, to uncover new insights and predict the future and:

Provides information that can only be derived from base data, usually at day level.
Leverages data mining, advanced statistics, and complex queries.
Physically, tables start with "DWD_".

Examples of derived tables include: DWD_MTR_RDNG_DAY, for meter reading day, DWD_OUTG_DAY, for outage by day, and DWD_RLBLTY_IND_CITY_MO for reliability indicies by city by month, and so on.

There is also a mining model at this level:.

AGGREGATE entities and tables

Provide information to analyze and summarize, usually at the monthly level and:

Leverages base and derived data models to provide aggregated data such as summaries, averages, and so on.
Enables dimensional analysis on wide variety of subject areas.
Leverages Oracle OLAP cubes (pre-built OLAP cubes are available. For more information, see Oracle Utilities Data Model OLAP Model Cubes.
Contains tables starting with "DWA_"; usually materialized views.
Represents the information access layer: It covers all the metadata.

About Business Areas and Subject Areas in Oracle Utilities Data Model

A Business Area is a broad slice through Oracle Utilities Data Model grouping where all tables that cover the associated business processes (reports, metadata, Mining, OLAP, 3NF) are all accessible through the same GUI (if OBIEE is used). A business area is a conceptual grouping, used at the default report level. The reports are organized by related subject areas gathered in "business areas". Table 1-4 lists the Oracle Utilities Data Model business areas.

A Subject Area is a thin slice through Oracle Utilities Data Model grouping all tables, mainly at the foundation layer, that cover a specific (logical) concept, business process or question. For example, the subject area PARTY defines the notion of a "PARTY". The "Individual" and "Organization" are both a subset of PARTY.

From an implementation perspective, Oracle Utilities Data Model can be filled by subject or business area, without taking care of having to feed all tables to have tangible and usable results.

After filling all reports of a given business area with data, this does not mean that the whole business area is covered. Feeding all the tables needed to have all reports of a given business area probably also feeds some reports of other business areas. For example, some PRODUCT, COST and COLLECTION AGENCY entities are required in the Business Area Revenue (for the Revenue OLAP cube). This also partly covers the Product Management, Cost and Contribution, as well as the Partner Management business areas.

About the Logical Data Model and Physical Data Model

A logical data model describes how to store information that defines business processes. The logical data model is an interface between business and technical staff, and allows these groups to provide a common understanding of business data elements and requirements.

The logical data model also provides the foundation for designing an Enterprise Data Warehouse. In Oracle Utilities Data Model, the logical data model is designed to avoid data redundancy, as much as possible, without impacting performance, and thus prevent data and business transaction inconsistency. The idea is to facilitate data reuse and sharing, hence reducing development and maintenance cycle and cost.

The logical data model is a single source for the model definition, with its own naming conventions that are valid for both business and IT.

In describing the business processes independently of the data sources and the technology, the logical data model clarifies the functional specifications, while avoiding (unnecessary) assumptions.

This implies that, in principle, the logical data model of Oracle Utilities Data Model could work on any platform. However, on top of the fact that it would not be supported by Oracle, such an implementation would not benefit from all the pre-built pre-integrated technologies leveraged with Oracle Utilities Data Model, in particular in the analytical layer, such as Partitioning, OLAP, Mining models, and so on.

The Oracle Utilities Data Model physical data model is the concrete implementation of the logical data model. It is fully technology dependent. The physical data model transforms business relationships into keys or indexes. It takes into account the infrastructure and technology to optimize the performance for end-users. The physical data model has its own naming convention in parallel to the one of the logical data model. Looking at the physical data model, one should be able to "build-back" the logical data model from the entity relationship, even if one could not have all the key understanding of a business process behind, unless one knows the business.

About Entity Relationships in Oracle Utilities Data Model

A relationship between two entities should exist in the model only if there is a direct (business) relationship between those entities. You can categorize the relationships as:

Description or added information (typically for Lookup tables): An entity contains codes that describe or validate the various values that an attribute of the original entity has. Physically, the two entities are related through a Foreign Key.
Direct relationship: Typically, when two entities are related from a business point of view, the model needs to ensure that this relationship is explicitly present and described. A direct relationship contains a direct business link between the entities (typically serves, uses, owns, and so on). There must be a distinction between clear 1 to many (1:n) or 0 to many (0:n), and many to many (n:m) relationship.
- 0:n or 1:n relationships: typically business types like "owns", "has got", "serves", "uses"… It is usually directly linked to an attribute (like description), and may be a foreign key link.
- m:n relationships: If the relationship can be "many to many", use an "Assignment" Entity between the entities to transform this m:n relationship in m:1 (or 0) and (0 or) 1:n.

PARTY ASSIGNMENT represents the relationship between two parties uniquely identified in Oracle Utilities Data Model, whatever the role they play within the model: As Customer, Employee, dealer or even all three for the same individual!

The only exception to this rule is with ADDRESS LOCATION. One uses the "ADDRESS RELATED" table, for example to feed the fact that an alternative billing address has been given by customer when the first one fails or because he is in holiday.

Very often, two rows of a given entity (say "ENTITY") have to be related with one another. Most of the time, Oracle Utilities Data Model uses a table named "ENTITY ASSIGNMENT". For example, PARTY and PARTY ASSIGNMENT.

In Party, the PARTY TYPE is a "person", an "organization", or an "organization business unit". It is not "Customer". This is a Party role. A given Party can have several roles which are "chosen" depending on the type of business interaction that takes place. However, the type never changes.

Self-Relationship: When an entity have a self-referencing attributes.

Sometimes a customer is referred by another customer. To keep the referral customer details Oracle Utilities Data Model has a self-referencing column REFERRAL CUSTOMER CODE in CUSTOMER entity, which refers to the primary key CUSTOMER CODE of CUSTOMER entity.

Understanding Named and Flexible Hierarchies

A named hierarchy is a pre-built hierarchy of general interest, usually used/seen in the market, with fixed levels (with a specific name for each).
A flexible hierarchy is a hierarchical structure that is freely definable: for levels, attributes per level, relationships and numbers of hierarchies (for the same base entity) with various possible versions.
These hierarchies do the following
- Follow Slowly Changing dimension Type II rules.
- Have the same leaf level (Organization Business Unit for "Organization" and Address Location for "Geography")
- Have pre-built tools to feed/change them easily (for implementation team).
- Can be associated in parallel (for example, In an Organization, several hierarchies can be defined: Administrative Hierarchy and Sales Hierarchy)

ORGANIZATION BUSINESS UNIT refers to lowest-level internal business unit of the organization that delivers a limited range of specific utilities services or merchandise through any sales channel (Web site, store, and so on), as shown in Figure 1-3.

Figure 1-3 Organization Business Unit Entity (Named Hierarchy)

Description of "Figure 1-3 Organization Business Unit Entity (Named Hierarchy)"

ORGANIZATION BUSINESS ENTITY refers to any internal logical entity that is recognized as a part of the enterprise for business analysis and transactions. Classification for a business entity can include company, operation unit, store, or warehouse. This is part of "Flexible Hierarchy" of organization.

Figure 1-4 Organization Business Entity (Flexible Hierarchy)

Description of "Figure 1-4 Organization Business Entity (Flexible Hierarchy)"

Business Interaction and Business Interaction Item: In Business Interaction Business Area

BUSINESS INTERACTION: is an arrangement, contract, communication or joint activity between one or more Party Roles, Resource Roles, or Customer Accounts. A Business Interaction may consist of one or more Business Interaction Items.

Sub-Types of BUSINESS INTERACTION are APPOINTMENT, CUSTOMER ORDER, PURCHASE ORDER, RESOURCE ORDER, BASE WORK, SERVICE ORDER, PARTY INTERACTION THREAD, and AGREEMENT.

BUSINESS INTERACTION may have versions.

BUSINESS INTERACTION may refer to a Product, Service, Resource, or one of their specifications.

BUSINESS INTERACTION may reference another Business Interaction and one Business Interaction Item may reference another Business Interaction Item on the same or different Business Interaction.

There are five types of BUSINESS INTERACTIONS: Requests, Responses, Notifications, Agreements, and Instructions.

BUSINESS INTERACTION ITEM: The detail items included in the business interaction. The main purpose for the Business Interaction is expressed in terms of a Product Type, Service Type or may refer to a Product, or Service.

SERVICE: Service is an internal technical presentation of available services/products to the customer.

Sub-Type of SERVICE is CUSTOMER FACING SERVICE. ELECTRICITY SERVICE is sub-type of CUSTOMER FACING SERVICE and DEMAND RESPONSE PROGRAM is sub-type of ELECTRICITY SERVICE.

PRODUCT SUBSCRIPTION: is the record of customer using a product (or product package). Customer subscription is the basis of billing. One subscription may be based on contract.

Figure 1-5 Business Interaction Item Entity

Description of "Figure 1-5 Business Interaction Item Entity"

About Common Information Model (CIM) Users Group

Oracle Utilities Data Model provides a standards-based utility industry data model using the Common Information Model (CIM). CIM is an abstract information model that provides data understanding through the identification of the relationships and associations of the data within a utility enterprise. For more information on CIM, see

http://cimug.ucaiug.org/default.aspx

About Business Areas in Oracle Utilities Data Model

A Business Area is a broad slice through Oracle Utilities Data Model grouping where all tables that cover the associated business processes (reports, metadata, Mining, OLAP, 3NF) are all accessible through the same GUI (if OBIEE is used). A business area is a conceptual grouping used at the default report level. The reports are organized by related subject areas gathered in "business areas".

Table 1-4 lists the Oracle Utilities Data Model business areas.

Table 1-4 Business Areas

Business Area	Description
Account Management	Contains information to support utility customers with the tracking, status, and plans with matters such as service requests, service agreements, monthly billing, reported trouble, planned outages, outage history, and so on.
Asset Management	Specifies the information management for network data sets, assets, and asset catalogs.
Customer Management	Contain customer service, trouble management, and point of sale related information within the utility enterprise.
Meter Reading and Control	Covers customer service, trouble management, and point of sale related information within the utility enterprise.
Network Operation	This business area provides the information on network operation for both distribution and transmission within utility industry such as supervising main substation topology, control equipment status, handling network connectivity and loading conditions. It also makes it possible to locate and supervise the location of field crews.
Outage Management	This business area supports the utilities to identify disruptions in the system, to perform restoration switching actions and to provide customers with notification of disruptions detected.
Weather Model	This is a weather model to support utility operation.
Work Management	This package contains the core information classes that support work management and network extension planning applications.