1 Introduction to Oracle Utilities Data Model Customization

This chapter provides an introduction to customizing Oracle Utilities Data Model. It contains the following topics:

• What is the 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.
• Implementing an Oracle Utilities Data Model Warehouse
  Although Oracle Utilities Data Model was designed following best practices, usually the model requires some customization to meet your business needs. The reasons that you might customize Oracle Utilities Data Model include: your business does not have a business area that is in the logical model of Oracle Utilities Data Model, or you must apply a new or different business rule.
• Before You Begin Customizing Oracle Utilities Data Model
  Before you begin customizing Oracle Utilities Data Model, ensure that certain teams and committees exist.
• Performing Fit-Gap Analysis for Oracle Utilities Data Model
  Fit-gap analysis is where you compare your information needs and business requirements with the structure that is available with Oracle Utilities Data Model. You identify any required functionality that is not included in the logical model and the default schema, as well as other modifications that are necessary to meet your requirements. The result of your fit-gap analysis is a customization report which is a brief explanation of the adaptations and adjustments required to customize Oracle Utilities Data Model to fit your environment.
• Data Encryption and Security for Oracle Utilities Data Model
  To comply with privacy and data protection requirements, Oracle Utilities Data Model is certified with Transparent Data Encryption and Oracle Database Vault.

What is the 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).

• Components of Oracle Utilities Data Model
  Oracle Utilities Data Model includes several components.
• Oracle Products That Make Up Oracle Utilities Data Model
  Several Oracle technologies are involved in building the infrastructure for Oracle Utilities Data Model:

Components of Oracle Utilities Data Model

Oracle Utilities Data Model includes several components.

• Logical model which is a third normal form (3NF) entity-object model. The logical model follows industry standards.

• Physical model defined as an Oracle Database schema.

• Intra-ETL database packages and SQL scripts to extract, transform, and load (ETL) data from the Oracle Utilities Data Model 3NF physical tables to the derived and aggregate tables in Oracle Utilities Data Model.

• Sample reports and dashboards developed using Oracle Business Intelligence Suite Extended Edition.

• DDL and installation scripts

Note:

When you use the Oracle Installer to install Oracle Utilities Data Model, you have the choice of performing two different types of installations:

• Installation of the Oracle Utilities Data Model component

• Installation of sample reports (and schemas)

Oracle Products That Make Up Oracle Utilities Data Model

Several Oracle technologies are involved in building the infrastructure for Oracle Utilities Data Model:

• Oracle Database with OLAP, Data Mining and Partitioning Option

• Oracle Development Tools

• Oracle Business Intelligence Suite Enterprise Edition Presentation Tools

Oracle Database with OLAP, Data Mining and Partitioning Option

Oracle Utilities Data Model uses 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).

Oracle Development Tools

You can use the following Oracle tools to customize the predefined physical models provided with Oracle Utilities Data Model, or to populate the target relational tables and materialized cube views.

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

Name	Use
SQL Developer or SQL*Plus	To modify, customize, and extend database objects
Analytic Workspace Manager	To view, create, develop, and manage OLAP dimensional objects.

Oracle Business Intelligence Suite Enterprise Edition Presentation Tools

Oracle Business Intelligence Suite Enterprise Edition is a comprehensive suite of enterprise BI 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 sample dashboard reports that are provided with Oracle Utilities Data Model.

Implementing an Oracle Utilities Data Model Warehouse

Although Oracle Utilities Data Model was designed following best practices, usually the model requires some customization to meet your business needs. The reasons that you might customize Oracle Utilities Data Model include: your business does not have a business area that is in the logical model of Oracle Utilities Data Model, or you must apply a new or different business rule.

Typical physical model modifications include: adding, deleting, modifying, or renaming tables and columns; or altering foreign keys, constraints, or indexes.

To implement an Oracle Utilities Data Model warehouse, perform the following steps:

1. Perform the required organizational tasks.

2. Create a fit-gap analysis report.

3. In a development environment, install a copy of the Oracle Utilities Data Model.

4. Customize Oracle Utilities Data Model by making the changes you documented in the fit-gap analysis report. Make the changes in the following order:

   1. Foundation layer of the physical model and the ETL to populate the foundation layer.

   2. Access layer of the physical model and the ETL to populate the access layer.

5. In a test environment, make a copy of your customized version of Oracle Utilities Data Model. Then, following the documentation you created in Step 2, test the customized version of Oracle Utilities Data Model.

6. Following your typical procedures, roll the tested customized version of Oracle Utilities Data Model out into pre-production and, then, production.

Tip:

Keep 'clean' copies of the components delivered with Oracle Utilities Data Model components. This is important when upgrading to later versions of Oracle Utilities Data Model.

Before You Begin Customizing Oracle Utilities Data Model

Before you begin customizing Oracle Utilities Data Model, ensure that certain teams and committees exist.

• Data warehouse governance steering committee.

• Implementation team.

• Fit-gap analysis team. This team consists of business analysts who can identify the business requirements and scope of the Oracle Utilities Data Model and at least some engineers in the Implementation team. Business members of this team must understand logical data modeling so that they can evaluate what changes must be made to the foundation and access layers of the physical model.

After these teams and committees are formed:

• Discuss the approach and determine the involvement and roles of every party involved in the customization (for example, business and IT).

• Agree on the scope of the project (that is, agree on what new data must be in the data warehouse and why it is needed).

• Agree on the timing and the working arrangements.

• Prerequisite Knowledge for Implementors
  The Oracle Utilities Data Model uses much of the Oracle stack. Consequently, to successfully implement the Oracle Utilities Data Model, the implementation team needs certain prerequisite knowledge.
• Responsibilities of a Data Warehouse Governance Committee
  Governance is of concern to any enterprise, executive team or individual with an interest in the processes, standards, and compliance. It is even more important to organizations that have invested in data warehousing. Data warehouse governance occurs within the context of overall IT governance. It provides the necessary policies, process and procedures, which must be clearly communicated to the entire corporation, from the IT employees to the front-end operational personnel.

Prerequisite Knowledge for Implementors

The Oracle Utilities Data Model uses much of the Oracle stack. Consequently, to successfully implement the Oracle Utilities Data Model, the implementation team needs certain prerequisite knowledge.

• Experience performing information and data analysis and data modeling. (Experience using Oracle SQL Data Modeler, is a plus.)

• An understanding of the Oracle technology stack, especially data warehouse (Database, Data Warehouse, OLAP, Data Mining, Oracle Business Intelligence Suite Extended Edition)

• Hands-on experience using:

  • Oracle Database

  • PL/SQL

  • SQL DDL and DML syntax

  • Analytic Workspace Manager

  • Oracle SQL Developer

  • Oracle Business Intelligence Suite Extended Edition Administrator, Answers, and Dashboards

Responsibilities of a Data Warehouse Governance Committee

Governance is of concern to any enterprise, executive team or individual with an interest in the processes, standards, and compliance. It is even more important to organizations that have invested in data warehousing. Data warehouse governance occurs within the context of overall IT governance. It provides the necessary policies, process and procedures, which must be clearly communicated to the entire corporation, from the IT employees to the front-end operational personnel.

Before you customize Oracle Utilities Data Model, setup a data warehouse governance steering committee if one does not exist. The role of this steering committee is to oversee the data warehouse to provide an environment that reaches across the enterprise and drives the best business value.

Data Warehouse Governance Committee: Overall Responsibilities

The data warehouse governance steering committee sets direction and response for the governance framework and covers the follow areas:

• The entire data warehouse life cycle.

• Agree on the data to process and make available to end-users.

• Determine what is the minimum quality criteria for the data that is available to end users and determine how to measure and analyze these criteria against the quality of the data that is the source data for the data warehouse.

• The business goals of the organization to apply core information from data warehouse.

• The policies, procedures and standards for data resource and data access.

• The life cycle of data warehouse component management.

Data Warehouse Governance Committee: Data Governance Responsibilities

The more detailed focus in data warehouse governance is data governance. Data governance tasks include:

• Approving the data modeling standards, metadata standards and other related standards. This includes determining a metadata strategy and identifying the data modeling tools to use that support these standards.

• Determining the data retention policy.

• Designing a data access policy based on legal restrictions and data security rules.

• Designing a data backup strategy that aligns with the impact analysis to the business unit.

• Monitoring and reporting on data usage, activity, and alerts.

Performing Fit-Gap Analysis for Oracle Utilities Data Model

Fit-gap analysis is where you compare your information needs and business requirements with the structure that is available with Oracle Utilities Data Model. You identify any required functionality that is not included in the logical model and the default schema, as well as other modifications that are necessary to meet your requirements. The result of your fit-gap analysis is a customization report which is a brief explanation of the adaptations and adjustments required to customize Oracle Utilities Data Model to fit your environment.

The fit-gap analysis team writes the customization report by performing the following steps:

1. If you have performed previous evaluations, review the documentation from the previous phases, and if necessary add team members with the required business and technical expertise.

2. Review the data and map your logical entities and data structure with the Oracle Utilities Data Model logical model and schema:

   • Starting from business requirements, questions, and rules, identify any entities and attributes that are not in the Oracle Utilities Data Model.

   • Compare the Oracle Utilities Data Model to your existing application model if have one.

   • Compare the Oracle Utilities Data Model to the OLTP data that you are using as a data source to the Oracle Utilities Data Model warehouse.

3. Determine the differences between your needs and Oracle Utilities Data Model schema. To help you with this task, produce a list of actions people may take with the system (examples rather than models), and create use cases for appraising the functionality of the Oracle Utilities Data Model Warehouse. Answer the following questions about the differences you find:

   • What differences you can live with, and what must be reconciled?

   • What can you do about the differences you cannot live with?

4. Identify the changes you must make to the default design of Oracle Utilities Data Model to create the customized warehouse. Identify these changes in the following order:

   1. Physical model.

   2. ETL mapping. Identify and design the source-ETL that you must create and identify and make any changes to the intra-ETL provided with Oracle Utilities Data Model.

   Tip:

   When identifying changes, ensure that the changes meet your security and metadata requirements.

5. Write the customization report, detailing what changes are required to make the Oracle Utilities Data Model match your business needs. This includes any additions and changes to interfaces to existing systems.

6. Based on the customization report update the project plan.

Data Encryption and Security for Oracle Utilities Data Model

To comply with privacy and data protection requirements, Oracle Utilities Data Model is certified with Transparent Data Encryption and Oracle Database Vault.

For more information on these topics, see:

Transparent Data Encryption

http://www.oracle.com/technetwork/database/options/database-vault/index-085211.html

See Also:

• Oracle Database Administrator's Guide

• Oracle Database Vault Administrator's Guide for information on using Oracle Database Vault