Data Warehouse

Overview

Symptoms

Organizations with an immediate need to bring a client/server "look and feel" to end users typically begin by adding a graphical user interface (GUI) front-end to one or more systems. This is a scenario that does little to impact core systems but supports cross system data inquiries and similar data "warehouse" types of requirements.

Symptoms for the data warehouse scenario are listed below:

· Redesigned business functions require service functions to be consolidated to single points of access, i.e., a customer only having to deal with a single service representative

· Users require access to data that crosses organizational and application boundaries

· Corporate data warehouse requires disparate systems to feed a comprehensive data model

· Disparate data across multiple systems make it difficult to access user summary information

· Users want a graphical user interface (GUI)

· There is not enough time or budget to integrate or modify core applications

Requirements

This scenario builds an integrated, downloaded database and graphical front-end to access the data. A graphical front-end to integrate information gathered from disparate systems is a stop gap measure. Long-term solution involves using approaches detailed in the Application Integration or Client/Server scenarios.

Requirements for this scenario are listed below:

· Provide a graphical, summary level view of cross system and cross functional data

· Consolidate/redesign information presentation without impacting core systems

· Provide timely, integrated access to mainframe data

· Provide summary level information to end users

· Make cross functional data available through a single point of access with minimal effort

· Create an integrated view of potentially redundant or conflicting data values derived from multiple systems

· Support data warehousing efforts

Data Warehouse Planning Considerations

Comsys-TIM views development of an integration database and graphical front-end to summarize information gathered from disparate systems as a stop gap measure. It offers a short-term solution to user demands for cross functional integration. This scenario establishes a query database that could, with additional effort, be extended to send updates back to the host. The feasibility of creating an update capability is dependent on issues that include data redundancy, consistency and physical data disparities.

Planning considerations include:

· Multiple systems are not a prerequisite for this scenario but are supported under integrated data redesign

· Decision as to place integrated data view on host or workstation environment is made on case by case basis

· Workstation resident database requires refreshing from multiple host data stores on regular basis

· Host resident database requires refreshing and interface software to send SQL queries up to host

· Workstation resident database is recommended when a high number of queries are anticipated

Build Data Warehouse Plan

Develop Assessment Proposal

Objective setting/proposal development

Note: Objectives are limited given scenario requirements. Approach options include creating summary DBMS on host or in a workstation environment. This is a preplanning decision. Based on these factors, this scenario moves directly to task definition as defined in the steps that directly following

Establish Assessment Task List

Note: Baseline of system is important in order to identify physical data stores and relationships to systems involved. Scan each system being included in assessment - omit metric gathering and summary analysis.

Finalize Environmental Analysis Work Plan

Note: Data definition analysis is limited to system-wide redundancy/consistency analysis of I/O record definitions as defined in 2nd, 3rd and last steps of DDA task below. Bypass all other steps except for the Assess Multi-System Data Definition Usagestep.

Finalize Data Definition Analysis Work Estimates

Note: This analysis will focus on capturing on-line user views and, optionally, batch reporting views of various systems' data.

Finalize Presentation Layer Analysis Plan

Note: Current data structures are compared against an integrated relational target database structure.

Finalize Data Access Layer Assessment Plan

Note: User backlog analysis is limited to identifying specific requests linked to user data integration requests.

Finalize User Backlog Analysis Plan

Note: Bottom-up data views are captured for all systems undergoing analysis.

Finalize Subject Area Analysis Work Plan

Note: Interim and strategic redevelopment plans are highly modified hybrids - see suggested tasks identified in remainder of this scenario.

Finalize Interim Planning Task Effort

Finalize Strategic Redevelopment Work Effort

Develop Inventory Analysis Work Plan

Complete Inventory Analysis Assessment Proposal

Capture Basic Current System Meta-Data

Environmental analysis

Note: The following steps are performed for each system input to this assessment.

Identify & Categorize Physical System Components

Inventory & Cross Reference Mainframe Components

Note: Concentrate on missing components that could cause later analysis problems with on-line screens or database definitions.

Review & Refine Environmental Analysis Results

Data definition analysis

Note: The following steps are performed for each system input to this assessment.

Perform System-Wide Data Definition Analysis

Note: Only relevant metrics are related to summary I/O record counts.

Assign Data Definition Metric Counts

Note: The following step is a required component of this analysis for all system candidates for GUI integrated data access - assumption of multiple systems is inherent in this scenario.

Assess Multi-System Data Definition Usage

Presentation layer assessment

Note: The following steps are performed for each system input to this assessment.

Identify Batch Output Presentation Media

Identify & Categorize Batch Input Sources

Identify & Categorize On-Line Presentation Media

Summarize User Supported Environments

Record Presentation Layer Analysis Metrics

Data access layer assessment

Note: The following steps are performed for each system input to this assessment.

Finalize Database & Data File Inventory

Assess Physical Data Usage Redundancy

Determine Data Integration Levels

Rate Data Architecture Conformance

Summarize Data Architecture Analysis

User backlog requirements analysis

Note: Focus on identifying specific presentation and data integration requirements.

Categorize User Backlog Requests

Subject Area & Entity Type Analysis

Derive Current System Subject Areas

Note: The following two steps are performed for each system being assessed.

Prepare & Load Current System Entity Types

Derive Existing Entity Relationship Diagram

Note: The following step is performed using current to target data views based on an integrated, relational target (preferably top-down) data model. A top-down model is not required unless a corporate view of data is to drive this scenario.

Perform Current to Target Data Gap Analysis

Perform Current & Target ER Model Merge

Create Subject Area Analysis Summary

Build Data Warehouse Plan

Create interim support plan

Create Interim Plan Outline

Note: Only Positioning task will be data name rationalization.

Finalize Interim Support Work Plan

Finalize Application Staging Plan

Assess Data Definition Rationalization Scope

Note: Data name rationalization scope is limited to grouping analysis, composite build and meaningful name assignment.

Finalize Data Definition Rationalization Plan

Identify Support Structure Adjustments

Integrate Interim Plan &Strategic Objectives

Create strategic redevelopment plan

Note: This plan is limited to a few steps as identified below.

Build Strategic Redevelopment Plan

Note: The following steps identify the work tasks and associated estimates required to complete the above two planning steps.

Finalize Logical Data Analysis Work Plan

Finalize Physical Data Design & Migration Plan

Note: Steps used in middleware enabling are finalize middleware tool requirements, design client/server interfaces and build middleware data store links. Note that steps are distributed across last two activities of last phase of this scenario.

Estimate Middleware Enabling Work Effort

Finalize Presentation Analysis Work Plan

Finalize Redevelopment Work Plan

Rationalize Data Representations

Rationalize Data Representations

Data name rationalization

Note: Rationalized data definitions will not be put back into production source. It is used as input to bottom-up data modeling effort only. Systems, to degree possible, should be rationalized as a single unit.

Setup Data Name Rationalization Libraries

Review Record Grouping Analysis

Build Composite Record Definitions

Apply Descriptive Composite Names

Finalize Data Warehouse Environment

Finalize Integrated Data Architecture

Logical Data Analysis

Load & Merge Bottom-Up ER Model(s)

Note: The following step is used to integrate existing system data usage.

Refine Bottom-Up Derived ER Model

Note: The following step is optional based availability of top-down model.

Merge Top-Down & Bottom-Up ER Models

Note: The following step creates normalized data model representing an integrated view of existing stand alone systems and new data as defined by top-down view or as added to integrated bottom-up view.

Normalize Merged ER Model

Review and Sign-Off Normalized Data Model

Physical Data Analysis & Migration

Note: The target model may be implemented either on a mainframe or in some type of server environment - this is based on the target environmental requirements.

Finalize Physical Data Model

Note: Data migration under the integration scenario emphasizes reconciliation and consolidation of redundant or overlapping physical data for query purposes only.

Perform Physical Data Migration Process

Gain Physical Design & Migration Plan Sign-Off

Complete Front-End & Server Design

Client/server design finalization

Note: The following steps are taken from middleware enabling and deal with specific configurations within a client/server environment. Use as a check list for environmental design.

Finalize Middleware Tool Requirements

Design Client Server Interfaces

Presentation analysis

Note: Perform the following step for each existing system being integrated.

Categorize Existing Screen & Report Layouts

Perform Presentation Functional Analysis

Determine Reusability Under Target System

Note: The import target is typically some type of GUI screen designer that can be linked to a relational view of an integrated database.

Refine Target System Report & Screen Design

Note: A number of GUI tools link to or have a built-in capability to access relational data stores across environments. These capabilities should be explored in preparation for next section of this scenario.

Deploy Data Warehouse Capability

Note: The following step supports relational data store access directly on host or another server. Direct linkages are facilitated using various middleware tools. Refer to step below and related tool guidelines.

Build Middleware Direct Data Store Links