Data Mart Consolidation: Getting Control of Your Enterprise Information

Data Mart Consolidation: Getting Control of Your Enterprise Information

by Chuck Ballard, Amit Gupta, Vijaya Krishnan, Nelson Pessoa, Olaf Stephan
Released August 2005
Publisher(s): IBM Redbooks
ISBN: None

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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.

Table of contents

  1. Notices
    1. Trademarks
  2. Preface
    1. The team that wrote this redbook
    2. Become a published author
    3. Comments welcome
  3. Chapter 1: Introduction
    1. Managing the enterprise data
      1. Consolidating the data warehouse environment
    2. Management summary
      1. Contents abstract
  4. Chapter 2: Data warehousing: A review
    1. Data warehousing
      1. Information environment
      2. Real-time business intelligence
      3. An architecture
      4. Data warehousing implementations
    2. Advent of the data mart
      1. Types of data marts
    3. Other analytic structures
      1. Summary tables, MQTs, and MDC
      2. Online analytical processing
      3. Cube Views
      4. Spreadsheets
    4. Data warehousing techniques
      1. Operational data stores
      2. Data federation and integration
      3. Federated access to real-time data
      4. Federated access to multiple data warehouses
      5. When to use data federation
      6. Data replication
    5. Data models
      1. Star schema
      2. Snowflake schema
      3. Normalization
  5. Chapter 3: Data marts: Reassessing the requirement
    1. The data mart phenomenon
      1. Data mart proliferation
    2. A business case for consolidation
      1. High cost of data marts
      2. Sources of higher cost
      3. Cost reduction by consolidation
      4. Metadata: consolidation and standardization
      5. Platform considerations
      6. Data mart cost analysis sheet
      7. Resolving the issues
    3. Summary
  6. Chapter 4: Consolidation: A look at the approaches
    1. What are good candidates for consolidation?
      1. Data mart consolidation lifecycle
    2. Approaches to consolidation
      1. Simple migration
      2. Centralized consolidation (1/2)
      3. Centralized consolidation (2/2)
      4. Distributed consolidation
      5. Summary of consolidation approaches
    3. Combining data schemas
      1. Simple migration approach
      2. Centralized consolidation approach
      3. Distributed consolidation approach
    4. Consolidating the other analytic structures
    5. Other consolidation opportunities
      1. Reporting environments
      2. BI tools
      3. ETL processes
    6. Tools for consolidation
      1. DB2 Universal Database
      2. DB2 Data Warehouse Edition
      3. WebSphere Information Integrator
      4. DB2 Migration ToolKit
      5. DB2 Alphablox
      6. DB2 Entity Analytics
      7. DB2 Relationship Resolution
      8. Others...
    7. Issues with consolidation
      1. When would you not consider consolidation?
    8. Benefits of consolidation
  7. Chapter 5: Spreadsheet data marts
    1. Spreadsheet usage in enterprises
      1. Developing standards for spreadsheets
    2. Consolidating spreadsheet data
      1. Using XML for consolidation (1/2)
      2. Using XML for consolidation (2/2)
      3. Transferring spreadsheet data to DB2 with no conversion
      4. Consolidating spreadsheet data using DB2 OLAP Server
    3. Spreadsheets and WebSphere Information Integrator
      1. Adding spreadsheet data to a federated server
      2. Sample consolidation scenario using WebSphere II
    4. Data transfer example with DB2 Warehouse Manager
      1. Preparing the source spreadsheet file
      2. Setting up connectivity to the source file
      3. Setting up connectivity to the target DB2 database
      4. Sample scenario
  8. Chapter 6: Data mart consolidation lifecycle
    1. The structure and phases
    2. Assessment
      1. Analytic structures
      2. Data quality and consistency (1/2)
      3. Data quality and consistency (2/2)
      4. Data redundancy
      5. Source systems
      6. Business and technical metadata
      7. Reporting tools and environment
      8. Other BI tools
      9. Hardware/software and other inventory
    3. DMC Assessment Findings Report (1/2)
    4. DMC Assessment Findings Report (2/2)
    5. Planning
      1. Identify a sponsor
      2. Identify analytical structures to be consolidated
      3. Select the consolidation approach
      4. Other consolidation areas
      5. Prepare the DMC project plan
      6. Identify the team
    6. Implementation recommendation report
    7. Design
      1. Target EDW schema design
      2. Standardize business definitions and rules
      3. Metadata standardization
      4. Identify dimensions and facts to be conformed
      5. Source to target mapping
      6. ETL design
      7. User reports requirements
    8. Implementation
    9. Testing
    10. Deployment
    11. Continuing the consolidation process
  9. Chapter 7: Consolidating the data
    1. Converting the data
      1. Data conversion process
      2. Time planning
      3. DB2 Migration ToolKit
      4. Alternatives for data movement
      5. DDL conversion using data modeling tools
    2. Load/unload
    3. Converting Oracle data
    4. Converting SQL Server
    5. Application conversion
      1. Converting other Java applications to DB2 UDB
      2. Converting applications to use DB2 CLI/ODBC
      3. Converting ODBC applications
    6. General data conversion steps
  10. Chapter 8: Performance and consolidation
    1. Performance techniques
      1. Buffer pools
      2. DB2 RUNSTATS utility
      3. Indexing
      4. Efficient SQL
      5. Multidimensional clustering tables
      6. MQT
      7. Database partitioning
    2. Data refresh considerations
      1. Data refresh types
      2. Impact analysis
    3. Data load and unload
      1. DB2 Export and Import utility
      2. The db2batch utility
      3. DB2 Load utility
      4. The db2move utility
      5. The DB2 High Performance Unload utility
  11. Chapter 9: Data mart consolidation: A project example
    1. Using the data mart consolidation lifecycle
    2. Project environment
      1. Overview of the architecture
      2. Issues with the present scenario
      3. Configuration objectives and proposed architecture
      4. Hardware configuration
      5. Software configuration
    3. Data schemas
      1. Star schemas for the data marts
      2. EDW data model
    4. The consolidation process
      1. Choose the consolidation approach
      2. Assess independent data marts
      3. Understand the data mart metadata definitions
      4. Study existing EDW
      5. Set up the environment needed for consolidation
      6. Identify dimensions and facts to conform
      7. Design target EDW schema
      8. Perform source/target mapping
      9. ETL design to load the EDW from data marts
      10. Metadata standardization and management
      11. Consolidating the reporting environment
      12. Testing the populated EDW data with reports
    5. Reaping the benefits of consolidation
  12. Appendix A: Consolidation project example: Table descriptions
    1. Data schemas on the EDW (1/2)
    2. Data schemas on the EDW (2/2)
    3. Data schemas on the ORACLE data mart
    4. Data schemas on the SQL Server 2000 data mart
  13. Appendix B: Data consolidation examples
    1. DB2 Migration ToolKit
    2. Consolidating with the MTK (1/2)
    3. Consolidating with the MTK (2/2)
      1. Example: Oracle 9i to DB2 UDB (1/3)
      2. Example: Oracle 9i to DB2 UDB (2/3)
      3. Example: Oracle 9i to DB2 UDB (3/3)
      4. Example: SQL Server 2000 to DB2 UDB (1/2)
      5. Example: SQL Server 2000 to DB2 UDB (2/2)
    4. Consolidating with WebSphere II
      1. Example - Oracle 9i to DB2 UDB
      2. Example - SQL Server to DB2 UDB (1/3)
      3. Example - SQL Server to DB2 UDB (2/3)
      4. Example - SQL Server to DB2 UDB (3/3)
  14. Appendix C: Data mapping matrix and code for EDW
    1. Source to target data mapping matrix (1/2)
    2. Source to target data mapping matrix (2/2)
    3. SQL ETL Code to populate the EDW
  15. Appendix D: Additional material
    1. Locating the Web material
    2. Using the Web material
      1. How to use the Web material
  16. Abbreviations and acronyms
  17. Glossary (1/2)
  18. Glossary (2/2)
  19. Related publications
    1. IBM Redbooks
    2. Other publications
    3. How to get IBM Redbooks
    4. Help from IBM
  20. Index (1/3)
  21. Index (2/3)
  22. Index (3/3)
  23. Back cover

Product information

  • Title: Data Mart Consolidation: Getting Control of Your Enterprise Information
  • Author(s): Chuck Ballard, Amit Gupta, Vijaya Krishnan, Nelson Pessoa, Olaf Stephan
  • Release date: August 2005
  • Publisher(s): IBM Redbooks
  • ISBN: None