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.

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 follows
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 the following components:

  • Logical model which is a third normal form (3NF) entity-object standards-based model. The logical model is described in Oracle Utilities Data Model Reference.

  • Physical model defined as one Oracle Database schema. This schema defines all the relational, OLAP, and data mining components.

  • 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 Enterprise 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, itself

  • Installation of sample reports (and schemas)

See Oracle Utilities Data Model Installation Guide for detailed information on the different types of installation.

See:

Oracle Utilities Data Model Reference for detailed descriptions of the components.

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 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.

See:

"Reporting Approaches in Oracle Utilities Data Model".

Steps for Implementing an Oracle Utilities Data Model Warehouse

Although Oracle Utilities Data Model was designed following best practices for utilities service providers, 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 included in the Oracle Utilities Data Model.

  • You must apply a table or column, or change a calculation or business rule in the Intra-ETL due to the unique way your company does business.

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, take the following steps:

  1. Perform the organizational tasks outlined in "Before You Begin Customizing the Oracle Utilities Data Model".

  2. Create a fit-gap analysis report by following the process outlined "Performing Fit-Gap Analysis for Oracle Utilities Data Model".

  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 that layer. When customizing the physical objects, follow the guidelines in "Foundation Layer Customization". When writing the ETL, follow the guidelines in "ETL for the Foundation Layer of an Oracle Utilities Data Model Warehouse".

    2. Access layer of the physical model and the ETL to populate that layer. When designing the physical objects, follow the guidelines in Chapter 3, "Access Layer Customization" When writing the ETL, follow the guidelines in "Customizing Intra-ETL for Oracle Utilities Data Model".

  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 the Oracle Utilities Data Model

Before you begin customizing Oracle Utilities Data Model, ensure the following teams and committees exist:

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). The order of implementation either top-down (per business or subject area) or bottom-up (source-leading) should be based on the "quick wins" (easy implementation, clean and known source, no or very little changes, out-of-the-box reports), themselves ordered by business relevance (from a Return On Investment perspective and from a strategic perspective).

  • Agree on the timing and the working arrangements.

Prerequisite Knowledge for Implementers

As outlined in "Oracle Products That Make Up Oracle Utilities Data Model", the Oracle Utilities Data Model uses much of the Oracle stack. Consequently, to successfully implement the Oracle Utilities Data Model, the implementation team needs:

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

  • Hands on experience developing ETL or ELT, preferable in the chosen ETL tool (ODI, Golden Gate, and so on).

  • Knowledge of the source applications, their data and their table structures from which you want to load data into Oracle Utilities Data Model.

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

  • Hands-on experience using:

    • Oracle Database

    • PL/SQL

    • SQL DDL and DML syntax

    • Analytic Workspace Manager

    • Oracle SQL Developer

    • Oracle Business Intelligence Suite Enterprise 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 committed 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 should at least cover the following 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, and the implications it may have on the existing or future business processes. For the later, the committee must make sure to communicate early enough to the right persons the process change request to ease the integration and to save time.

  • 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 as discussed in "Overview of Managing Metadata for Oracle Utilities Data Model" 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 utilities business requirements with the structure that is available with Oracle Utilities Data Model. You identify any required functionality that is not included in the model and the default schema, and 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 utilities environment.

The fit-gap analysis team writes the customization report by taking 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 the data structures of your source to the Oracle Utilities Data Model schema:

    • Starting from business requirements, questions, and rules, identify any objects 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 application 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:

    • Which differences you can live with, and which 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. Follow the guidelines outlined in Chapter 2, "Physical Model Customization".

    2. ETL mapping. Follow the guidelines outlined in Chapter 4, "ETL Implementation and Customization" to identify and design the source-ETL that you must create and to identify and make any changes to the intra-ETL provided with Oracle Utilities Data Model.

    3. Reports: A clear distinction should be made between reports end-users could create themselves from the default data and data structure available in the OBIEE repository, and those that would require additions (from earlier changes in Physical model, or simply because the considered entity was not accessible by default in the repository). Note that this step could be done a bit later, in a second phase, not for the initial implementation, with the risk to possibly miss an important source of information if the business interviews were not run properly.

    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 and perform the steps outlined in "Steps for Implementing an Oracle Utilities Data Model Warehouse".

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 using Transparent Data Encryption, see Oracle Database Administrator's Guide and for information on using Oracle Database Vault, see Oracle Database Vault Administrator's Guide.

For more information on these topics, see:

http://www.oracle.com/technetwork/database/options/advanced-security/index-099011.html

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