Preface This IBM Redbook is primarily intended for use by IBM® Clients and IBM Business Partners involved with data mart consolidation A key direction in the
Previous PDF | Next PDF |
[PDF] Data Mart Consolidation - IBM Redbooks
Preface This IBM Redbook is primarily intended for use by IBM® Clients and IBM Business Partners involved with data mart consolidation A key direction in the
[PDF] Dimensional Modeling: In a Business Intelligence - IBM Redbooks
and BI courses in IBM India Amit has also been a Co-Author® for a previous IBM Redbook on Data Mart Consolidation He holds a degree in Electronics and
[PDF] Business Intelligence Certification Guide - IBM Redbooks
5 4 Data warehouse modeling and construction process today Consolidating and organizing data for better business decisions can lead to a competitive
[PDF] Enterprise Data Warehousing with DB2 9 for z/OS - IBM Redbooks
23 nov 2007 · This edition applies to IBM DB2 Version 9 1 for z/OS (program number Consolidating warehouse data and operational data on one platform,
[PDF] Tivoli Data Warehouse Version 13: Planning - IBM Redbooks
This IBM Redbook focuses on planning, installation, customization, use, Tivoli Data Warehouse V1 3 is made available by Tivoli to consolidate historical
[PDF] Business Intelligence Architecture on S/390 - IBM Redbooks
administer the S/390 data warehouse (DW) as well as how to give users access to the DW Data is consolidated enterprise-wide, so there is a corporate view of
[PDF] Enhance Inbound and Outbound Marketing with a - IBM Redbooks
2 1 2 Approach 2: Enterprise Data Warehouse (EDW) 15 2 2 Using detected and consolidated into a single view Inconsistent: They struggle
[PDF] Query Acceleration for Business Using IBM Informix - IBM Redbooks
1 3 2 Informix and Informix Warehouse Accelerator data mart definition and 2 3 4 Data mart metadata in the Informix server to as roll-up or consolidation
[PDF] 50 TB Data Warehouse Benchmark on System z - IBM Redbooks
He is co-author of the IBM Redbooks publication Enterprise Data Consolidating warehouse data and operational data on one platform (such as System z)
[PDF] Building the Operational Data Store on DB2 UDB - IBM Redbooks
3 1 2 Consolidated customer and order information 4 1 4 Populating the ODS using Data Warehouse Center (DWC) 108 How to get IBM Redbooks
[PDF] Cours de Data Mining
[PDF] Cours IFT6266, Exemple d'application: Data-Mining
[PDF] Introduction au Data Mining - Cedric/CNAM
[PDF] Defining a Data Model - CA Support
[PDF] Learning Data Modelling by Example - Database Answers
[PDF] Nouveaux prix à partir du 1er août 2017 Mobilus Mobilus - Proximus
[PDF] règlement général de la consultation - Inventons la Métropole du
[PDF] Data science : fondamentaux et études de cas
[PDF] Bases du data scientist - Data science Master 2 ISIDIS - LISIC
[PDF] R Programming for Data Science - Computer Science Department
[PDF] Sashelp Data Sets - SAS Support
[PDF] Introduction au domaine du décisionnel et aux data warehouses
[PDF] DESIGNING AND IMPLEMENTING A DATA WAREHOUSE 1
[PDF] Datawarehouse
![[PDF] Data Mart Consolidation - IBM Redbooks [PDF] Data Mart Consolidation - IBM Redbooks](https://pdfprof.com/Listes/38/9986-38sg246653.pdf.pdf.jpg)
Data Mart Consolidation:
Getting Control of YourEnterprise Information
Chuck Ballard
Amit Gupta
Vijaya Krishnan
Nelson Pessoa
Olaf Stephan
Managing your information assets and
minimizing operational costsEnabling a single view of your
business environmentMinimizing or eliminating
those data silosFront cover
Data Mart Consolidation:
Getting Control of Your Enterprise Information
July 2005International Technical Support OrganizationSG24-6653-00
© Copyright International Business Machines Corporation 2005. All rights reserved.Note to U.S. Government Users Restricted Rights -- Use, duplication or disclosure restricted by GSA ADP
Schedule Contract with IBM Corp.
First Edition (July 2005)
This edition applies to DB2 UDB V8.2, DB2 Migration ToolKit V1.3, WebSphere Information Integrator V8.2, Oracle Database 9i, and Microsoft SQL Server 2000. Note: Before using this information and the product it supports, read the information in "Notices" on page ix. © Copyright IBM Corp. 2005. All rights reserved.iiiContents
Notices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .ix
Trademarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xi
The team that wrote this redbook. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xii
Become a published author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiv
Comments welcome. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiv
Chapter 1. Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.1 Managing the enterprise data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
1.1.1 Consolidating the data warehouse environment. . . . . . . . . . . . . . . . . 4
1.2 Management summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.2.1 Contents abstract . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Chapter 2. Data warehousing: A review . . . . . . . . . . . . . . . . . . . . . . . . . . . 112.1 Data warehousing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
2.1.1 Information environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
2.1.2 Real-time business intelligence. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
2.1.3 An architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
2.1.4 Data warehousing implementations . . . . . . . . . . . . . . . . . . . . . . . . . 18
2.2 Advent of the data mart. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
2.2.1 Types of data marts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
2.3 Other analytic structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
2.3.1 Summary tables, MQTs, and MDC . . . . . . . . . . . . . . . . . . . . . . . . . . 23
2.3.2 Online analytical processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
2.3.3 Cube Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
2.3.4 Spreadsheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
2.4 Data warehousing techniques. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
2.4.1 Operational data stores. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
2.4.2 Data federation and integration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
2.4.3 Federated access to real-time data. . . . . . . . . . . . . . . . . . . . . . . . . . 37
2.4.4 Federated access to multiple data warehouses . . . . . . . . . . . . . . . . 38
2.4.5 When to use data federation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
2.4.6 Data replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
2.5 Data models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
2.5.1 Star schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
2.5.2 Snowflake schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
2.5.3 Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
iv Data Mart Consolidation Chapter 3. Data marts: Reassessing the requirement. . . . . . . . . . . . . . . . 493.1 The data mart phenomenon . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
3.1.1 Data mart proliferation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
3.2 A business case for consolidation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
3.2.1 High cost of data marts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
3.2.2 Sources of higher cost . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
3.2.3 Cost reduction by consolidation . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
3.2.4 Metadata: consolidation and standardization . . . . . . . . . . . . . . . . . . 60
3.2.5 Platform considerations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
3.2.6 Data mart cost analysis sheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
3.2.7 Resolving the issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
3.3 Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Chapter 4. Consolidation: A look at the approaches. . . . . . . . . . . . . . . . . 674.1 What are good candidates for consolidation? . . . . . . . . . . . . . . . . . . . . . . 68
4.1.1 Data mart consolidation lifecycle. . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
4.2 Approaches to consolidation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
4.2.1 Simple migration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
4.2.2 Centralized consolidation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
4.2.3 Distributed consolidation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
4.2.4 Summary of consolidation approaches. . . . . . . . . . . . . . . . . . . . . . . 84
4.3 Combining data schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
4.3.1 Simple migration approach . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
4.3.2 Centralized consolidation approach . . . . . . . . . . . . . . . . . . . . . . . . . 89
4.3.3 Distributed consolidation approach. . . . . . . . . . . . . . . . . . . . . . . . . . 91
4.4 Consolidating the other analytic structures . . . . . . . . . . . . . . . . . . . . . . . . 93
4.5 Other consolidation opportunities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
4.5.1 Reporting environments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
4.5.2 BI tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
4.5.3 ETL processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
4.6 Tools for consolidation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
4.6.1 DB2 Universal Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
4.6.2 DB2 Data Warehouse Edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
4.6.3 WebSphere Information Integrator . . . . . . . . . . . . . . . . . . . . . . . . . 106
4.6.4 DB2 Migration ToolKit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
4.6.5 DB2 Alphablox . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
4.6.6 DB2 Entity Analytics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
4.6.7 DB2 Relationship Resolution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
4.6.8 Others... . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
4.7 Issues with consolidation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
4.7.1 When would you not consider consolidation?. . . . . . . . . . . . . . . . . 114
4.8 Benefits of consolidation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Contents vChapter 5. Spreadsheet data marts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
5.1 Spreadsheet usage in enterprises. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
5.1.1 Developing standards for spreadsheets . . . . . . . . . . . . . . . . . . . . . 118
5.2 Consolidating spreadsheet data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
5.2.1 Using XML for consolidation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
5.2.2 Transferring spreadsheet data to DB2 with no conversion . . . . . . . 129
5.2.3 Consolidating spreadsheet data using DB2 OLAP Server . . . . . . . 132
5.3 Spreadsheets and WebSphere Information Integrator . . . . . . . . . . . . . . 133
5.3.1 Adding spreadsheet data to a federated server . . . . . . . . . . . . . . . 133
5.3.2 Sample consolidation scenario using WebSphere II. . . . . . . . . . . . 137
5.4 Data transfer example with DB2 Warehouse Manager. . . . . . . . . . . . . . 139
5.4.1 Preparing the source spreadsheet file . . . . . . . . . . . . . . . . . . . . . . 139
5.4.2 Setting up connectivity to the source file. . . . . . . . . . . . . . . . . . . . . 139
5.4.3 Setting up connectivity to the target DB2 database . . . . . . . . . . . . 140
5.4.4 Sample scenario . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Chapter 6. Data mart consolidation lifecycle . . . . . . . . . . . . . . . . . . . . . . 1496.1 The structure and phases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
6.2 Assessment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
6.2.1 Analytic structures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
6.2.2 Data quality and consistency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
6.2.3 Data redundancy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
6.2.4 Source systems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
6.2.5 Business and technical metadata . . . . . . . . . . . . . . . . . . . . . . . . . . 162
6.2.6 Reporting tools and environment . . . . . . . . . . . . . . . . . . . . . . . . . . 163
6.2.7 Other BI tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
6.2.8 Hardware/software and other inventory . . . . . . . . . . . . . . . . . . . . . 167
6.3 DMC Assessment Findings Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
6.4 Planning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
6.4.1 Identify a sponsor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
6.4.2 Identify analytical structures to be consolidated . . . . . . . . . . . . . . . 179
6.4.3 Select the consolidation approach . . . . . . . . . . . . . . . . . . . . . . . . . 179
6.4.4 Other consolidation areas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
6.4.5 Prepare the DMC project plan. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
6.4.6 Identify the team . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
6.5 Implementation recommendation report . . . . . . . . . . . . . . . . . . . . . . . . . 182
6.6 Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
6.6.1 Target EDW schema design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
6.6.2 Standardize business definitions and rules. . . . . . . . . . . . . . . . . . . 185
6.6.3 Metadata standardization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
6.6.4 Identify dimensions and facts to be conformed. . . . . . . . . . . . . . . . 187
6.6.5 Source to target mapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
6.6.6 ETL design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
vi Data Mart Consolidation6.6.7 User reports requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
6.7 Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
6.8 Testing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
6.9 Deployment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
6.10 Continuing the consolidation process . . . . . . . . . . . . . . . . . . . . . . . . . . 197
Chapter 7. Consolidating the data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1997.1 Converting the data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
7.1.1 Data conversion process. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
7.1.2 Time planning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
7.1.3 DB2 Migration ToolKit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202
7.1.4 Alternatives for data movement . . . . . . . . . . . . . . . . . . . . . . . . . . . 204
7.1.5 DDL conversion using data modeling tools. . . . . . . . . . . . . . . . . . . 207
7.2 Load/unload. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
7.3 Converting Oracle data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
7.4 Converting SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
7.5 Application conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
7.5.1 Converting other Java applications to DB2 UDB . . . . . . . . . . . . . . 216
7.5.2 Converting applications to use DB2 CLI/ODBC . . . . . . . . . . . . . . . 218
7.5.3 Converting ODBC applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
7.6 General data conversion steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
Chapter 8. Performance and consolidation . . . . . . . . . . . . . . . . . . . . . . . 2278.1 Performance techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
8.1.1 Buffer pools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
8.1.2 DB2 RUNSTATS utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
8.1.3 Indexing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232
8.1.4 Efficient SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
8.1.5 Multidimensional clustering tables . . . . . . . . . . . . . . . . . . . . . . . . . 236
8.1.6 MQT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240
8.1.7 Database partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
8.2 Data refresh considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
8.2.1 Data refresh types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
8.2.2 Impact analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
8.3 Data load and unload . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
8.3.1 DB2 Export and Import utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
8.3.2 The db2batch utility. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
8.3.3 DB2 Load utility. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250
8.3.4 The db2move utility. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253
8.3.5 The DB2 High Performance Unload utility . . . . . . . . . . . . . . . . . . . 253
Chapter 9. Data mart consolidation: A project example . . . . . . . . . . . . . 2559.1 Using the data mart consolidation lifecycle . . . . . . . . . . . . . . . . . . . . . . . 256
9.2 Project environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
Contents vii9.2.1 Overview of the architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
9.2.2 Issues with the present scenario. . . . . . . . . . . . . . . . . . . . . . . . . . . 260
9.2.3 Configuration objectives and proposed architecture. . . . . . . . . . . . 262
9.2.4 Hardware configuration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264
9.2.5 Software configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
9.3 Data schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266
9.3.1 Star schemas for the data marts. . . . . . . . . . . . . . . . . . . . . . . . . . . 266
9.3.2 EDW data model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 272
9.4 The consolidation process. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
9.4.1 Choose the consolidation approach . . . . . . . . . . . . . . . . . . . . . . . . 274
9.4.2 Assess independent data marts . . . . . . . . . . . . . . . . . . . . . . . . . . . 275
9.4.3 Understand the data mart metadata definitions . . . . . . . . . . . . . . . 277
9.4.4 Study existing EDW . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278
9.4.5 Set up the environment needed for consolidation. . . . . . . . . . . . . . 280
9.4.6 Identify dimensions and facts to conform . . . . . . . . . . . . . . . . . . . . 280
9.4.7 Design target EDW schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282
9.4.8 Perform source/target mapping. . . . . . . . . . . . . . . . . . . . . . . . . . . . 283
9.4.9 ETL design to load the EDW from data marts. . . . . . . . . . . . . . . . . 283
9.4.10 Metadata standardization and management. . . . . . . . . . . . . . . . . 291
9.4.11 Consolidating the reporting environment . . . . . . . . . . . . . . . . . . . 293
9.4.12 Testing the populated EDW data with reports. . . . . . . . . . . . . . . . 294
9.5 Reaping the benefits of consolidation . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
Appendix A. Consolidation project example: Table descriptions. . . . . . 301Data schemas on the EDW . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302
Data schemas on the ORACLE data mart . . . . . . . . . . . . . . . . . . . . . . . . . . . 308 Data schemas on the SQL Server 2000 data mart . . . . . . . . . . . . . . . . . . . . 310 Appendix B. Data consolidation examples. . . . . . . . . . . . . . . . . . . . . . . . 315DB2 Migration ToolKit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316
Consolidating with the MTK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
Example: Oracle 9i to DB2 UDB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324 Example: SQL Server 2000 to DB2 UDB . . . . . . . . . . . . . . . . . . . . . . . . . 335Consolidating with WebSphere II . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344
Example - Oracle 9i to DB2 UDB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344 Example - SQL Server to DB2 UDB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353 Appendix C. Data mapping matrix and code for EDW. . . . . . . . . . . . . . . 365Source to target data mapping matrix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 366
SQL ETL Code to populate the EDW. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376 Appendix D. Additional material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381Locating the Web material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381
Using the Web material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 382
viii Data Mart Consolidation How to use the Web material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 382 Abbreviations and acronyms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387
Related publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393
IBM Redbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393
Other publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393
How to get IBM Redbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394
Help from IBM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395
© Copyright IBM Corp. 2005. All rights reserved.ixNotices
This information was developed for products and services offered in the U.S.A.IBM may not offer the products, services, or features discussed in this document in other countries. Consult your local IBM representative for information on the products and services currently available in your area. Any reference to an IBM product, program, or service is not intended to state or imply that only that IBM product, program, or service may be used. Any functionally equivalent product, program, or service that does not infringe any IBM intellectual property right may be used instead. However, it is the user's responsibility to evaluate and verify the operation of any non-IBM product, program, or service.
IBM may have patents or pending patent applications covering subject matter described in this document. The furnishing of this document does not give you any license to these patents. You can send license inquiries, in writing, to: IBM Director of Licensing, IBM Corporation, North Castle Drive Armonk, NY 10504-1785 U.S.A.
The following paragraph does not apply to the United Kingdom or any other country where such provisions are inconsistent with local law: INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS PUBLICATION "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or implied warranties in certain transactions, therefore, this statement may not apply to you.
This information could include technical inaccuracies or typographical errors. Changes are periodically made to the information herein; these changes will be incorporated in new editions of the publication. IBM may make improvements and/or changes in the product(s) and/or the program(s) described in this publication at any time without notice.
Any references in this information to non-IBM Web sites are provided for convenience only and do not in any manner serve as an endorsement of those Web sites. The materials at those Web sites are not part of the materials for this IBM product and use of those Web sites is at your own risk.
IBM may use or distribute any of the information you supply in any way it believes appropriate without incurring any obligation to you.
Information concerning non-IBM products was obtained from the suppliers of those products, their published announcements or other publicly available sources. IBM has not tested those products and cannot confirm the accuracy of performance, compatibility or any other claims related to non-IBM products. Questions on the capabilities of non-IBM products should be addressed to the suppliers of those products.
This information contains examples of data and reports used in daily business operations. To illustrate them as completely as possible, the examples include the names of individuals, companies, brands, and products. All of these names are fictitious and any similarity to the names and addresses used by an actual business enterprise is entirely coincidental.
COPYRIGHT LICENSE: This information contains sample application programs in source language, which illustrates programming techniques on various operating platforms. You may copy, modify, and distribute these sample programs in any form without payment to IBM, for the purposes of developing, using, marketing or distributing application programs conforming to the application programming interface for the operating platform for which the sample programs are written. These examples have not been thoroughly tested under all conditions. IBM, therefore, cannot guarantee or imply reliability, serviceability, or function of these programs. You may copy, modify, and distribute these sample programs in any form without payment to IBM for the purposes of developing, using, marketing, or distributing application programs conforming to IBM's application programming interfaces.
x Data Mart ConsolidationTrademarks
The following terms are trademarks of the International Business Machines Corporation in the United States, other countries, or both:
AIX®
Approach®
Architecture™
AS/400®
Cube Views™
Database 2™
Distributed Relational Database
DB2®
DB2 Connect™
DB2 Extenders™DB2 OLAP Server™
DB2 Universal Database™
DRDA®
Eserver®
Informix®
Intelligent Miner™
iSeries™IBM®
IMS™
Lotus®OS/390®
Rational®
Rational Rose®
Redbooks™
Redbooks (logo) ™
Red Brick™
WebSphere®
Workplace™
z/OS® The following terms are trademarks of other companies:Solaris, J2SE, J2EE, JVM, JDK, JDBC, JavaBeans, Java, EJB, and Enterprise JavaBeans are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.
Microsoft, Windows, Windows NT, Windows server, Natural, Excel, and the Windows logo are trademarks of Microsoft Corporation in the United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other countries. Linux is a trademark of Linus Torvalds in the United States, other countries, or both. Other company, product, or service names may be trademarks or service marks of others. © Copyright IBM Corp. 2005. All rights reserved.xi