Data mart consolidation : getting control of your enterprise information / Chuck Ballard et al.

Format:

Book

Author/Creator:

Ballard, Chuck.

Contributor:

Gupta, Amit.

Krishnan, Vijaya.

Pessoa, Nelson.

Stephan, Olaf.

Series:

IBM redbooks.

Redbooks

Language:

English

Subjects (All):

Database management.

Electronic data processing.

Physical Description:

1 online resource (422 p.)

Place of Publication:

San Jose, CA : IBM, c2005.

Language Note:

English

System Details:

text file

Summary:

This IBM Redbooks publication is primarily intended for use by IBM Clients and IBM Business Partners. The current direction in the Business Intelligence marketplace is towards data mart consolidation. Originally data marts were built for many different reasons, such as departmental or organizational control, faster query response times, easier and faster to design and build, and fast payback. However, data marts did not always provide the best solution when it came to viewing the business enterprise as a whole. They provide benefits to the department or organization to whom they belong, but typically do not give management the information they need to efficiently and effectively run the business. In many cases the data marts led to the creation of departmental or organizational data silos (non-integrated sources of data). That is, information was available to the particular department or organization, but was not integrated across all the department’s or organizations. Worse yet, many data marts were built without concern for the others. This led to inconsistent definitions of the data, inconsistent collection of data, inconsistent collection times for the data, and so on. The result was an inconsistent picture of the business for management, and an inability for good business performance management. The solution is to consolidate those data silos to provide management the information they need.

Contents:

Front cover

Contents

Notices

Trademarks

Preface

The team that wrote this redbook

Become a published author

Comments welcome

Chapter 1. Introduction

1.1 Managing the enterprise data

1.1.1 Consolidating the data warehouse environment

1.2 Management summary

1.2.1 Contents abstract

Chapter 2. Data warehousing: A review

2.1 Data warehousing

2.1.1 Information environment

2.1.2 Real-time business intelligence

2.1.3 An architecture

2.1.4 Data warehousing implementations

2.2 Advent of the data mart

2.2.1 Types of data marts

2.3 Other analytic structures

2.3.1 Summary tables, MQTs, and MDC

2.3.2 Online analytical processing

2.3.3 Cube Views

2.3.4 Spreadsheets

2.4 Data warehousing techniques

2.4.1 Operational data stores

2.4.2 Data federation and integration

2.4.3 Federated access to real-time data

2.4.4 Federated access to multiple data warehouses

2.4.5 When to use data federation

2.4.6 Data replication

2.5 Data models

2.5.1 Star schema

2.5.2 Snowflake schema

2.5.3 Normalization

Chapter 3. Data marts: Reassessing the requirement

3.1 The data mart phenomenon

3.1.1 Data mart proliferation

3.2 A business case for consolidation

3.2.1 High cost of data marts

3.2.2 Sources of higher cost

3.2.3 Cost reduction by consolidation

3.2.4 Metadata: consolidation and standardization

3.2.5 Platform considerations

3.2.6 Data mart cost analysis sheet

3.2.7 Resolving the issues

3.3 Summary

Chapter 4. Consolidation: A look at the approaches

4.1 What are good candidates for consolidation?

4.1.1 Data mart consolidation lifecycle

4.2 Approaches to consolidation

4.2.1 Simple migration

4.2.2 Centralized consolidation

4.2.3 Distributed consolidation

4.2.4 Summary of consolidation approaches.

4.3 Combining data schemas

4.3.1 Simple migration approach

4.3.2 Centralized consolidation approach

4.3.3 Distributed consolidation approach

4.4 Consolidating the other analytic structures

4.5 Other consolidation opportunities

4.5.1 Reporting environments

4.5.2 BI tools

4.5.3 ETL processes

4.6 Tools for consolidation

4.6.1 DB2 Universal Database

4.6.2 DB2 Data Warehouse Edition

4.6.3 WebSphere Information Integrator

4.6.4 DB2 Migration ToolKit

4.6.5 DB2 Alphablox

4.6.6 DB2 Entity Analytics

4.6.7 DB2 Relationship Resolution

4.6.8 Others...

4.7 Issues with consolidation

4.7.1 When would you not consider consolidation?

4.8 Benefits of consolidation

Chapter 5. Spreadsheet data marts

5.1 Spreadsheet usage in enterprises

5.1.1 Developing standards for spreadsheets

5.2 Consolidating spreadsheet data

5.2.1 Using XML for consolidation

5.2.2 Transferring spreadsheet data to DB2 with no conversion

5.2.3 Consolidating spreadsheet data using DB2 OLAP Server

5.3 Spreadsheets and WebSphere Information Integrator

5.3.1 Adding spreadsheet data to a federated server

5.3.2 Sample consolidation scenario using WebSphere II

5.4 Data transfer example with DB2 Warehouse Manager

5.4.1 Preparing the source spreadsheet file

5.4.2 Setting up connectivity to the source file

5.4.3 Setting up connectivity to the target DB2 database

5.4.4 Sample scenario

Chapter 6. Data mart consolidation lifecycle

6.1 The structure and phases

6.2 Assessment

6.2.1 Analytic structures

6.2.2 Data quality and consistency

6.2.3 Data redundancy

6.2.4 Source systems

6.2.5 Business and technical metadata

6.2.6 Reporting tools and environment

6.2.7 Other BI tools

6.2.8 Hardware/software and other inventory

6.3 DMC Assessment Findings Report

6.4 Planning.

6.4.1 Identify a sponsor

6.4.2 Identify analytical structures to be consolidated

6.4.3 Select the consolidation approach

6.4.4 Other consolidation areas

6.4.5 Prepare the DMC project plan

6.4.6 Identify the team

6.5 Implementation recommendation report

6.6 Design

6.6.1 Target EDW schema design

6.6.2 Standardize business definitions and rules

6.6.3 Metadata standardization

6.6.4 Identify dimensions and facts to be conformed

6.6.5 Source to target mapping

6.6.6 ETL design

6.6.7 User reports requirements

6.7 Implementation

6.8 Testing

6.9 Deployment

6.10 Continuing the consolidation process

Chapter 7. Consolidating the data

7.1 Converting the data

7.1.1 Data conversion process

7.1.2 Time planning

7.1.3 DB2 Migration ToolKit

7.1.4 Alternatives for data movement

7.1.5 DDL conversion using data modeling tools

7.2 Load/unload

7.3 Converting Oracle data

7.4 Converting SQL Server

7.5 Application conversion

7.5.1 Converting other Java applications to DB2 UDB

7.5.2 Converting applications to use DB2 CLI/ODBC

7.5.3 Converting ODBC applications

7.6 General data conversion steps

Chapter 8. Performance and consolidation

8.1 Performance techniques

8.1.1 Buffer pools

8.1.2 DB2 RUNSTATS utility

8.1.3 Indexing

8.1.4 Efficient SQL

8.1.5 Multidimensional clustering tables

8.1.6 MQT

8.1.7 Database partitioning

8.2 Data refresh considerations

8.2.1 Data refresh types

8.2.2 Impact analysis

8.3 Data load and unload

8.3.1 DB2 Export and Import utility

8.3.2 The db2batch utility

8.3.3 DB2 Load utility

8.3.4 The db2move utility

8.3.5 The DB2 High Performance Unload utility

Chapter 9. Data mart consolidation: A project example

9.1 Using the data mart consolidation lifecycle

9.2 Project environment.

9.2.1 Overview of the architecture

9.2.2 Issues with the present scenario

9.2.3 Configuration objectives and proposed architecture

9.2.4 Hardware configuration

9.2.5 Software configuration

9.3 Data schemas

9.3.1 Star schemas for the data marts

9.3.2 EDW data model

9.4 The consolidation process

9.4.1 Choose the consolidation approach

9.4.2 Assess independent data marts

9.4.3 Understand the data mart metadata definitions

9.4.4 Study existing EDW

9.4.5 Set up the environment needed for consolidation

9.4.6 Identify dimensions and facts to conform

9.4.7 Design target EDW schema

9.4.8 Perform source/target mapping

9.4.9 ETL design to load the EDW from data marts

9.4.10 Metadata standardization and management

9.4.11 Consolidating the reporting environment

9.4.12 Testing the populated EDW data with reports

9.5 Reaping the benefits of consolidation

Appendix A. Consolidation project example: Table descriptions

Data schemas on the EDW

Data schemas on the ORACLE data mart

Data schemas on the SQL Server 2000 data mart

Appendix B. Data consolidation examples

DB2 Migration ToolKit

Consolidating with the MTK

Example: Oracle 9i to DB2 UDB

Example: SQL Server 2000 to DB2 UDB

Consolidating with WebSphere II

Example - Oracle 9i to DB2 UDB

Example - SQL Server to DB2 UDB

Appendix C. Data mapping matrix and code for EDW

Source to target data mapping matrix

SQL ETL Code to populate the EDW

Appendix D. Additional material

Locating the Web material

Using the Web material

How to use the Web material

Abbreviations and acronyms

Glossary

Related publications

IBM Redbooks

Other publications

How to get IBM Redbooks

Help from IBM

Index

Back cover.

Notes:

Includes index.

"July 2005."

OCLC:

228139119