[PDF] Data Mart Consolidation: - Getting Control of Your Enterprise

View - Download Data Mart Consolidation: - Getting Control of Your Enterprise

Previous PDF

Next PDF

ibm.com/redbooks

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 costs

Enabling a single view of your

business environment

Minimizing or eliminating

those data silos

Front cover

Data Mart Consolidation:

Getting Control of Your Enterprise Information

July 2005International Technical Support Organization

SG24-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.iii

Contents

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 . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

2.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. . . . . . . . . . . . . . . . 49

3.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. . . . . . . . . . . . . . . . . 67

4.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 . . . . . . . . . . . . . . . . . . . . . . 149

6.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 Consolidation

6.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. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199

7.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 . . . . . . . . . . . . . . . . . . . . . . . 227

8.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 . . . . . . . . . . . . . 255

9.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. . . . . . 301

Data 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. . . . . . . . . . . . . . . . . . . . . . . . 315

DB2 Migration ToolKit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316

Consolidating with the MTK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318

Example: Oracle 9i to DB2 UDB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324 Example: SQL Server 2000 to DB2 UDB . . . . . . . . . . . . . . . . . . . . . . . . . 335

Consolidating 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. . . . . . . . . . . . . . . 365

Source to target data mapping matrix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 366

SQL ETL Code to populate the EDW. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376 Appendix D. Additional material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381

Locating the Web material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381

Using the Web material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 382

viii Data Mart Consolidation How to use the Web material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 382 Abbreviations and acronyms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383

Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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.ix

Notices

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 Consolidation

Trademarks

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

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 business intelligence marketplace is towards data mart consolidation. Originally data marts were built for many good reasons, such as departmental or organizational control, faster query response times, easier and faster to design and build, and fast application payback. However, data marts did not always provide the best solution when it came to viewing the business enterprise as an entity. And consistency between the data marts was, and is, a continuing source of frustration with business management. They to 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. This has become a real concern with the current emphasis on, and dramatic benefits gained from, business performance management. In many cases data marts have led to the creation of departmental or organizational data silos. That is, information is available to a specific department or organization, but not integrated across all the departments or organizations. Worse yet, many of these silos were built without concern for the others. This led to inconsistent definitions of the data, inconsistent collection of data, inconsistent currency of the data across the organization, and so on. The result is an inconsistent picture of the business for management, and an inability to achieve good business performance management. The solution is to consolidate those data silos to provide management a consistent and complete set of information for the business needs. In this redbook we provide details on the data warehousing environment, and best practices for consolidating and integrating your environment to produce the information you need to best manage your business. We are certain you will find this redbook informative and helpful, and of great benefit as you develop your data mart consolidation strategies. xii Data Mart Consolidation

The team that wrote this redbook

This redbook was produced by a team of specialists from around the world working at the International Technical Support Organization, San Jose Center. Some team members worked locally at the International Technical Support Organization - San Jose Center, while others worked from remote locations. The team members are depicted below, along with a short biographical sketch of each: Chuck Ballard is a Project Manager at the International Technical Support organization, in San Jose, California. He has over 35 years experience, holding positions in the areas of Product Engineering, Sales, Marketing, Technical Support, and Management. His expertise is in the areas of database, data management, data warehousing, business intelligence, and process re-engineering. He has written extensively on these subjects, taught classes, and presented at conferences and seminars worldwide. Chuck has both a Bachelors degree and a Masters degree in Industrial Engineering from Purdue University. Amit Gupta is a Data Warehousing Consultant in IBM, India. He is a Microsoft® Certified Trainer, MCDBA, and a Certified OLAP Specialist. He has 6 years of experience in the areas of databases, data management, data warehousing, and business intelligence. He teaches extensively on dimensional modeling, data warehousing, and BI courses in IBM India. His areas of expertise include dimensional modeling, data warehousing, and metadata management. He holds a degree in Electronics and Communications from Delhi Institute of Technology, Delhi University, New Delhi, India. Vijaya Krishnan is a Database Administrator in IBM Global Services, Bangalore, India in the Siebel Technology center department. He has over 8 years of experience in application development, DB2® UDB database administration and design, business Intelligence, and data warehouse development. He is an IBM certified Business Intelligence solutions designer and an IBM certified DB2 Database Administrator. He holds a Bachelors degree in engineering from the University of Madras in India. Preface xiiiNelson Pessoa is a Database Administrator at IBM Brazil where he has worked for 6 years. He holds a Bachelors degree in Computer Science from the Centro Universitário Nove de Julho, São Paulo, Brazil, and currently works as a Systems Specialist working with customers around the country and in internal projects. He has also worked with other IRM applications and ITIL disciplines. His areas of expertise include Data WareHouse, DB2, Data Integration, Data Modeling, Programming ETLs, Reporting Applications. Olaf Stephan is a Data Integration Specialist at the E&TS, Engineering & Technology Services organization in Mainz, Germany. He has 6 years of experience in DB2 UDB, data management, data warehousing, business intelligence, and data integration. He holds a Masters degree in Electrical Engineering, specializing in Communications Technology, from the University of Applied Sciences, Koblenz, Germany.

Special acknowledgement

Henry Cook, Manager, BI Sector, Competitive Team, EMEA, UK. Henry is an expert in data warehousing and data mart consolidation. He provided guidance when forming the structure of the book, contributed significant content, and offered valuable feedback during the technical review process

Other contributors

Thanks to the following people for their contributions to this project:

From IBM locations worldwide

Garrett Hall - DB2 Information Management Skills, Austin, Texas Barry Devlin - Software Group, Lotus® and IBM Workplace™, Dublin, Ireland Bill O'Connell - Senior Technical Staff Member, Chief BI Architect, DB2 UDB

Development, Markham, ON Canada

Stephen Addison - SWG Services for Data Management, UK Keith Brown - Business Intelligence Practice Leader, UK

Paul Gittins - Software Sales Consultant, UK

Tim Newman - DB2 Alphablox Technical Sales, Bedfont, UK Paul Hennessey - IBM Global Services, CRM Marketing and Analytics, UK Karen Van Evans - Application Innovation Services - Business Intelligence,

Markham, ON Canada

John Kling - IGS Consulting and Services, Cincinnati, Ohio David Marcotte - Retail Industry Software Sales, Waltham, Massachusetts xiv Data Mart Consolidation Bruce Johnson - Consultant and Data Architect, IGS, Minneapolis, Minnesota Aviva Phillips - Data Architect, Southfield, Michigan

Koen Berton - Consultant, IGS, Belgium

From the International Technical Support Organization, San Jose Center

Mary Comianos - Operations and Communications

Yvonne Lyon - Technical Editor

Deanna Polm - Residency Administration

Emma Jacobs - Graphics

Become a published author

Join us for a two- to six-week residency program! Help write an IBM Redbook dealing with specific products or solutions, while getting hands-on experience with leading-edge technologies. You'll team with IBM technical professionals,

Business Partners and/or customers.

Your efforts will help increase product acceptance and customer satisfaction. As a bonus, you'll develop a network of contacts in IBM development labs, and increase your productivity and marketability. Find out more about the residency program, browse the residency index, and apply online at: ibm.com/redbooks/residencies.html

Comments welcome

Your comments are important to us!

We want our Redbooks™ to be as helpful as possible. Send us your comments about this or other Redbooks in one of the following ways: ?Use the online Contact us review redbook form found at: ibm.com/redbooks ?Send your comments in an email to: redbook@us.ibm.com ?Mail your comments to: IBM Corporation, International Technical Support Organization

Dept. QXXE Building 80-E2

650 Harry Road

San Jose, California 95120-6099

© Copyright IBM Corp. 2005. All rights reserved.1

Chapter 1.Introduction

In this redbook, we discuss the topic of data mart consolidation. That includes the issues involved and approaches for resolving them, as well as the requirements for, and benefits of, data mart consolidation. But why consolidate data marts? Are they not providing good information and value to the enterprise? The answers to these and similar questions are discussed in detail throughout this book. In general, businesses are consolidating their data marts for three basic reasons:

1.Cost savings: There is a significant cost associated with data marts in the

form of such things as: a. Additional servers. b. Additional software licenses, such as database management systems and operating systems. c. Operating and maintenance costs for activities such as software updates, backup/recovery, data capture and synchronization, data transformations, and problem resolution. d. Additional resources for support, including the cost of their training and ongoing skills maintenance - particularly in a heterogeneous environment. e. Additional networks for their connectivity and operations.quotesdbs_dbs31.pdfusesText_37

[PDF] Data mining 1 Exploration Statistique - Institut de Recherche

[PDF] Cours de Data Mining

[PDF] Qu'est-ce que le text and data mining - OpenEdition Books

[PDF] Data Mining & Statistique

[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