This chapter provides an introduction to customizing the Oracle Retail Data Model. It contains the following topics:
Oracle Retail Data Model is a standards-based, pre-built approach to retail data warehousing enabling a retail company to gain insight from their data more quickly. Oracle Retail Data Model reduces costs for both immediate and on-going operations by leveraging out-of-box Oracle based data warehouse and business intelligence solutions, making world-class database and business intelligence technology solutions available with a retail specific data model. You can use Oracle Retail Data Model in any application environment. Also, you can easily extend the model.
Using Oracle Retail Data Model you can jump-start the design and implementation of an Oracle Retail Data Model warehouse to quickly achieve a positive ROI for your data warehousing and business intelligence project with a predictable implementation effort.
Oracle Retail Data Model provides the following features:
Enterprise wide data model for retail industry
Over 1,250 tables and 18,500 attributes
Over 1,800 industry measures and KPIs
Based on ARTS 6.0
Prebuilt mining models, OLAP cubes, and reports
Automatic data movement across the warehouse (Intra-ETL)
Easily extensible and customizable
Usable within any retail application
Metadata Browser with Refresh
Oracle Retail Data Model provides much of the data modeling work that you must do for a retail business intelligence solution. The Oracle Retail Data Model logical and physical data models were designed following best practices for retail service providers. Oracle Retail Data Model is based on the ARTS 6.0 standard.
Logical model which is a third normal form (3NF) entity-object standards-based model.
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 Retail Data Model 3NF physical tables to the Oracle Retail Data Model derived and aggregate objects.
Sample reports and dashboards developed using Oracle Business Intelligence Suite Enterprise Edition.
Data Mining Models models for: Employee Analysis, Customer Analysis, Store Analysis, Item Analysis, and Product Analysis.
DDL and installation scripts.
Oracle Retail Data Model uses a complete Oracle technical stack. It leverages the following data warehousing features of the Oracle Database: compression, partitioning, advanced statistical functions, materialized views, data mining, and online analytical processing (OLAP).
You can use the following Oracle tools to customize the predefined physical models provided with Oracle Retail Data Model, or to populate the target relational tables and materialized cube views.
SQL Developer or SQL*Plus
To modify, customize, and extend database objects
Oracle Warehouse Builder
For the process control of the intra ETL process
Analytic Workspace Manager
To view, create, develop, and manage OLAP dimensional objects.
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 dashboard reports that are provided with Oracle Retail Data Model.
The reasons that you might customize Oracle Retail Data Model include: your business does not have a business area that is in the logical model of Oracle Retail Data Model, or you must apply a new or different business rule. Typical physical model modifications include: adding, deleting, modifying tables; or altering foreign keys, constraints, or indexes.
Perform the organizational tasks outlined in "Before You Begin Customizing the Oracle Retail Data Model".
Create a fit-gap analysis report by following the process outlined "Performing Fit-Gap Analysis for Oracle Retail Data Model".
In a development environment, install a copy of the Oracle Retail Data Model.
In the copy of the Oracle Retail Data Model you created in Step 3, customize Oracle Retail Data Model by making the changes you documented in the fit-gap analysis report. Make the changes in the following order:
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 "Creating Source-ETL for Oracle Retail Data Model".
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 the Oracle Retail Data Model".
In a test environment, make a copy of your customized version of Oracle Retail Data Model. Then, following the documentation you created in Step 2, test the customized version of Oracle Retail Data Model.
Following your typical procedures, roll the tested customized version of Oracle Retail Data Model out into pre-production and, then, production.
Note:Keep 'clean' copies of the components delivered with Oracle Retail Data Model. This is important when upgrading to later versions of Oracle Retail Data Model.
Data warehouse governance steering committee. This steering committee has the responsibilities outlined in "Responsibilities of a Data Warehouse Governance Committee".
Implementation team. This team consists of IT engineers who have the expertise outlined in "Prerequisite Knowledge for Implementors". This team has the responsibilities outlined in "Steps for Implementing an Oracle Retail Data Model Warehouse".
Fit-gap analysis team. This team consists of business analysts who can identify the business requirements and scope of the Oracle Retail 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. This team has the responsibilities outlined in "Performing Fit-Gap Analysis for Oracle Retail Data 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.
As outlined in "Oracle Products That Make Up Oracle Retail Data Model", the Oracle Retail Data Model uses much of the Oracle stack. Consequently, to successfully implement the Oracle Retail Data Model, the implementation team needs:
Experience performing information and data analysis and data modeling. (Experience using Oracle SQL Developer Data Modeler, is a plus.)
An understanding of the Oracle technology stack, especially data warehouse (Database, Data Warehouse, OLAP, Data Mining, Warehouse Builder, Oracle Business Intelligence Suite Enterprise Edition)
Hands-on experience using:
SQL DDL and DML syntax
Analytic Workspace Manager
Oracle SQL Developer Data Modeler
Oracle Business Intelligence Suite Enterprise Edition Administrator, Answers, and Dashboards
One implementation team member is familiar or has training with Oracle Retail Data Model, or the team engages a partner with Oracle Retail Data Model implementation experience.
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 Retail 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.
The data warehouse governance steering committee sets the direction and is responsible for the governance framework, including the following areas:
The entire data warehouse life cycle
The data to process and the data to make available to end-users
The minimum quality criteria for the data that is available to end users and 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 the data warehouse.
The policies, procedures, and standards for data resources and data access.
The life cycle of data warehouse component management.
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 "Managing Metadata for Oracle Retail 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.
Metadata is any data about data and, as such, is an important aspect of the data warehouse environment. Metadata allows the end user and the business analyst to navigate through the possibilities without knowing the context of the data or what the data represents.
Metadata management is a comprehensive, ongoing process of overseeing and actively managing metadata in a central environment which helps an enterprise to identify how data is constructed, what data exists, and what the data means. It is particularly helpful to have good metadata management when customizing Oracle Retail Data Model so that you can do impact analysis to ensure that changes do not adversely impact data integrity anywhere in your data warehouse.
Metadata is organized into three major categories:
Business metadata describes the meaning of data in a business sense. The business interpretation of data elements in the data warehouse is based on the actual table and column names in the database. Gather this mapping information and business definition and rules information into business metadata.
Technical metadata represents the technical aspects of data, including attributes such as data types, lengths, lineage, results from data profiling, and so on.
Process execution metadata presents statistics on the results of running the ETL process itself, including measures such as rows loaded successfully, rows rejected, amount of time to load, and so on.
Since metadata is so important in information management, many organizations attempt to standardize metadata at various levels, such as:
Metadata Encoding and Transmission Standard (METS). A standard for encoding descriptive, administrative, and structural metadata regarding objects within a digital library.
American National Standards Institute (ANSI). The organization that coordinates the U.S. voluntary standardization and conformity-assessment systems.
International Organization for Standardization (ISO). The body that establishes, develops, and promotes standards for international exchange.
Common Warehouse Metamodel (CWM). A specification, released and owned by the Object Management Group, for modeling metadata for relational, non-relational, multi-dimensional, and most other objects found in a data warehousing environment.
When you implement your metadata management solution, reference your data warehouse infrastructure environment and make the decision which standard to follow.
A Physical layer. This metadata layer identifies the source data.
A Business Model and Mapping layer. This metadata layer organizes the physical layer into logical categories and records the appropriate metadata for access to the source data.
The Presentation layer. This metadata layer exposes the business model entities for end-user access.
The first step in creating a Metadata Repository is to scope your metadata management needs by:
Identifying the metadata consumers. Typically, there are business consumers and technical consumers.
Determine the business and technical metadata requirements.
Aligning metadata requirements to specific data elements and logical data flows.
Decide how important each part is.
Assign responsibility to someone for each piece.
Decide what constitutes a consistent and working set of metadata
Determine where to store, backup, and recover the metadata.
Ensure that each piece of metadata is available only to those people who need it.
Quality-assure the metadata and ensure that it is complete and up to date.
Identify the Metadata Repository to use and how to control that repository from one place
After creating the metadata definitions, review your data architecture to ensure you can acquire, integrate, and maintain the metadata.
As the data is updated in your data warehouse day by day, you need to update the Metadata Repository. To change business rules, definitions, formulas or process especially when customizing the Oracle Retail Data Model, your first step is to survey the metadata and do an impact analysis to list all of the attributes in the data warehouse environment that would be affected by a proposed change.
To customize the Oracle Retail Data Model model, you must understand the dependencies among Oracle Retail Data Model components, especially how the report KPIs are mapped to the physical tables and columns and how that data has been transformed through the intra-ETL.
Oracle Retail Data Model provides a tool called the "Oracle Retail Data Model Metadata" browser that helps you discover these dependencies. When you install Oracle Retail Data Model with and the sample reports, the metadata browser is delivered as a Dashboard.
See:Oracle Retail Data Model Installation Guide for more information on installing the sample reports and deploying the Oracle Retail Data Model RPD and webcat on the Business Intelligence Suite Enterprise Edition instance.
There are four tabs (reports) in the Oracle Retail Data Model Metadata browser:
On the Measure-Entity tab the measure descriptions, computational formulas with physical columns, physical tables, and corresponding entities can be viewed by Business Area.
To browse the data, select the business area and measure description that you are interested in.
The Entity-Measure tab displays the measures supported by the entities and how they are calculated. You can discover information about particular entities and attributes.
For example, take the following steps to learn more about an entity:
Select the entity.
The Program-Table tab displays the input and output tables used in the selected programs. For example, take the following steps to learn more about intra-ETL mappings:
Select the program type (that is, intra-ETL or report) and program name for showing particular report or intra-ETL information.
The Table-Program tab lists the Programs used by a given table and whether that table is an input or output, or both, of that program.
To discover what reports use a particular table, you must move a particular table from the right pane to the left (Selected) pane.
For example, to see the reports that use a particular table, take the following steps:
In the right pane of the Table-Program tab, select the table.
Move the table to the Selected list on the left by clicking on < (left arrow), and click OK.
The reports for the selected table are displayed.
As described in "Browsing the Metadata Repository Supplied With Oracle Retail Data Model", Oracle Retail Data Model metadata browser lets you view and analyze metadata through a set of BIEE reports to better understand relationships between metadata objects and for end-to-end impact analysis.
You use the Oracle Retail Data Model metadata browser generation packages to generate and update the Oracle Retail Data Model metadata browser.
There are four main tables and other staging tables and views in the metadata generation package. The tables are: MD_ENTY, MD_PRG, MD_KPI, and MD_REF_ENTY_KPI; these are the tables that support metadata browser reports.
For more information, see Chapter 6, "Metadata Collection and Reports".
Before you import source metadata into Oracle Warehouse Builder, you create a module to contain metadata definitions. The type of module you create depends on the source from which you are importing metadata into your Metadata Repository. Oracle Warehouse Builder supports many different sources and targets as discussed in Oracle Warehouse Builder Sources and Targets Guide. For example, when connecting to an Oracle database, Oracle Warehouse Builder queries the database dictionary to extract all needed metadata on tables, views, sequences, dimensions, cubes, data types, PL/SQL packages, and so on.
Oracle Warehouse Builder also provides an interface tool called the Metadata Dependency Manager through which you can explore dependencies among data objects, as represented by the metadata in your Oracle Warehouse Builder repository. The Metadata Dependency Manager presents dependencies in the form of interactive lineage and impact diagrams. A lineage diagram traces the data flows for an object back to the sources and displays all objects along those paths. An impact diagram identifies all the objects that are derived from the selected object.
Information provided by the Metadata Dependency Manager can help you in many circumstances. For example:
Starting from a target object, such as a dimension, cube, or business intelligence tool report, you can identify the columns in each data source that are used in computing the results in the target.
You can assess the impact of design changes in an object such as a source table or a pluggable mapping that is used throughout a project.
You can propagate a design change, such as a change to the data type of a source table column, to downstream objects in the design.
Using end-to-end data lineage and impact analysis reduces project risk by allowing better planning for design changes, faster identification of unanticipated impacts when source systems change, and enabling more effective auditing of your business intelligence results, master data or other data integration processes.
You can also define and use SQL-based or XML-based custom metadata stores to retrieve definitions of source and target objects such as tables and views.
For data files extracted from some mainframe sources, Oracle Warehouse Builder can interpret Cobol Copybook files that describes the structure of the data file, and create its source metadata based on that.
Oracle Warehouse Builder application adapters or application connectors provide additional metadata about ERP and CRM application sources.
Oracle Warehouse Builder can deploy or execute process flows and schedules to Oracle Enterprise Manager and Oracle Workflow. In general, you can deploy a schedule in any Oracle Database location (Oracle Database 11g or later).
Fit-Gap analysis is the process by which data in source tables are mapped to the Oracle Retail Data Model tables and columns and gaps are identified which would require customization. You identify any required functionality that is not included in the logical 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 Retail Data Model to fit your retail environment.
The fit-gap analysis team writes the customization report by taking the following steps:
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.
Review the data and map your logical entities and data structure with the Oracle Retail Data Model logical model and schema:
Starting from business requirements, questions, and rules, identify any entities and attributes that are not in the Oracle Retail Data Model.
Compare the Oracle Retail Data Model to your existing application model if you have one.
Compare the Oracle Retail Data Model to the transactional data that you are using as a data source to the Oracle Retail Data Model warehouse.
Determine the differences between your needs and Oracle Retail 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 Retail 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?
Identify the changes you must make to the default design of Oracle Retail Data Model to create the customized warehouse. Identify these changes in the following order:
Physical model. Follow the guidelines outlined in Chapter 2, "Physical Model Customization".
ETL mapping. Follow the guidelines outlined in Chapter 4, "ETL Implementation and Customization" to identify and design the source-ETL that you must write and any changes you must make to the intra-ETL provided with Oracle Retail Data Model.
Tip:When identifying changes, ensure that the changes meet your security and metadata requirements.
Write the customization report, detailing what changes are required to make the Oracle Retail Data Model match your business needs. This includes any additions and changes to interfaces to existing systems.
Based on the customization report, update the Project Plan and perform the steps outlined in "Steps for Implementing an Oracle Retail Data Model Warehouse".