Acknowledgments xx
Introduction xxiii
Part One Fundamentals of Business Intelligence
and the Data Warehouse 1
Chapter 1 Overview of the BI Organization 3
Overview of the BI Organization Architecture 4
Providing Information Content 10
Planning for Information Content 10
Designing for Information Content 13
Implementing Information Content 15
Justifying Your BI Effort 18
Linking Your Project to Known Business Requirements 18
Measuring ROI 18
Applying ROI 19
Questions for ROI Benefits 21
Making the Most of the First Iteration of the Warehouse 22
IBM and The BI Organization 22
Seamless Integration 23
Data Mining 24
Online Analytic Processing 24
Spatial Analysis 25
Database-Resident Tools 25
Simplified Data Delivery System 26
Zero-Latency 27
Summary 28
CChapter 2 Business Intelligence Fundamentals 29
BI Components and Technologies 31
Business Intelligence Components 31
Data Warehouse 31
Data Sources 32
Data Targets 32
Warehouse Components 36
Extraction, Transformation, and Loading 37
Extraction 38
Transformation/Cleansing 39
Data Refining 39
Data Management 40
Data Access 40
Meta Data 41
Analytical User Requirements 42
Reporting and Querying 43
Online Analytical Processing 43
Multidimensional Views 44
Calculation-Intensive Capabilities 45
Time Intelligence 45
Statistics 46
Data Mining 46
Dimensional Technology and BI 47
The OLAP Server 48
MOLAP 49
ROLAP 50
Defining the Dimensional Spectrum 50
Touch Points 52
Zero-Latency and Your Warehouse Environment 53
Closed-Loop Learning 53
Historical Integrity 54
Summary 58
Chapter 3 Planning Data Warehouse Iterations 59
Planning Any Iteration 61
Building Your BI Plan 62
Enterprise Strategy 63
Designing the Technical Architecture 64
Designing the Data Architecture 66
Implementing and Maintaining the Warehouse 69
Planning the First Iteration 70
Aligning the Warehouse with Corporate Strategy 71
Conducting a Readiness Assessment 71
Resource Planning 74
Identifying Opportunities with the DIF Matrix 1 77
Determining the Right Approach 78
Applying the DIF Matrix 78
IT JAD Sessions 80
Select Candidate Iteration Opportunities 80
Get IT Scores 81
Create DIF Matrix 81
User JAD Session and Scoring 81
Average DIF Scores 82
Select According to Score 82
Submit to Management 82
Dysfunctional 82
Impact 83
Feasibility 84
DIF Matrix Results 84
Planning Subsequent Iterations 87
Defining the Scope 87
Identifying Strategic Business Questions 87
Implementing a Project Approach 89
BI Hacking Approach 90
The Inmon Approach 90
Business Dimensional Lifecycle Approach 91
The Spiral Approach 91
Reducing Risk 92
The Spiral Approach and Your Life Cycle Model 93
Warehouse Development and the Spiral Model 94
Flattening Spiral Rounds to Time Lines 98
The IBM Approach 100
Choosing the Right Approach 103
Summary 103
Part Two Business Intelligence Architecture 105
Chapter 4 Designing the Data Architecture 107
Choosing the Right Architecture 110
Atomic Layer Alternatives 113
ROLAP Platform on a 3NF Atomic Layer 116
HOLAP Platform on a Star Schema Atomic Layer 117
Data Marts 118
Atomic Layer with Dependent Data Marts 120
Independent Data Marts 121
Data Delivery Architecture 122
EAI and Warehousing 126
Comparing ETL and EAI 126
Expected Deliverables 127
Modeling the Architecture 129
Business Logical Model 130
Atomic-Level Model 132
Modeling the Data Marts 133
Comparing Atomic and Star Data 137Operational Data Store 138
Data Architecture Strategy 140
Summary 143
Chapter 5 Technical Architecture and Data Management Foundations 145
Broad Technical Architecture Decisions 148
Centralized Data Warehousing 148
Distributed Data Warehousing 152
Parallelism and the Warehouse 154
Partitioning Data Storage 157
Technical Foundations for Data Management 158
DB2 and the Atomic Layer 158
Redistribution and Table Collocation 158
Replicated Tables 160
Indexing Options 161
Multidimensional Clusters as Indexes 161
Defined Types, User-Defined Functions, and DB2 Extenders 162
Hierarchical Storage Considerations 162
DB2 and Star Schemas 164
DB2 Technical Architecture Essentials 166
SMP, MPP, and Clusters 166
Shared-Resource vs. Shared-Nothing 168
DB2 on Hardware Architectures 169
Static and Dynamic Parallelism 170
Catalog Partition 172
High Availability 172
Online Space Management 172
Backup 172
Parallel Loading 174
OnLine Load 174
Multidimensional Clustering 174
Unplanned Outages 175
Sizing Requirements 179
Summary 181
Part Three Data Management 183
Chapter 6 DB2 BI Fundamentals 185
High Availability 186
Multidimensional Clustering 187
Online Loads 188
Load From Cursor 189
Batch Window Elimination 190
Elimination of Table Reorganization 190
Online Load and MQT Maintenance 190
MQT Staging Tables 191
Online Table Reorganization 192
x ContentsDynamic Bufferpool Management 194
Dynamic Database Configuration 195
Database Managed Storage Considerations 195
Logging Considerations 196
Administration 197
eLiza and SMART 197
Automated Health Management Framework 198
AUTOCONFIGURE 198
Administration Notification Log 199
Maintenance Mode 199
Event Monitors 200
SQL and Other Programming Features 200
INSTEAD OF Triggers 200
DML Operations through UNION ALL 201
Informational Constraints 202
User-Maintained MQTs 203
Performance 203
Connection Concentrator 203
Compression 204
Type-2 Indexes 204
MDC Performance Enhancement 206
Blocked Bufferpools 206
Extensibility 206
Spatial Extender 207
Text Extender and Text Information Extender 208
Image Extender 208
XML Extender 208
Video Extender and Audio Extender 209
Net Search Extender 209
MQSeries 209
DB2 Scoring 209
Summary 211
Chapter 7 DB2 Materialized Query Tables 213
Initializing MQTs 219
Creating 219
Populating 219
Tuning 221
MQT DROP 221
MQT Refresh Strategies 221
Deferred Refresh 221
Immediate Refresh 226
Loading Underlying Tables 227
New States 228
New LOAD Options 228
Using DB2 ALTER 231
Contents xiMaterialized View Matching 232
State Considerations 232
Matching Criteria 233
Matching Permitted 234
Matching Inhibited 240
MQT Design 243
MQT Tuning 244
Refresh Optimization 245
Materialized View Limitations 247
Summary 249
Part Four Warehouse Management 251
Chapter 8 Warehouse Management with IBM DB2 Data
Warehouse Center 253
IBM DB2 Data Warehouse Center Essentials 254
Warehouse Subject Area 254
Warehouse Source 254
Warehouse Target 255
Warehouse Server and Logger 255
Warehouse Agent and Agent Site 255
Warehouse Control Database 256
Warehouse Process and Step 257
SQL Step 258
Replication Step 258
DB2 Utilities Step 259
OLAP Server Program Step 259
File Program Step 260
Transformer Step 260
User-Defined Program Step 260
IBM DB2 Data Warehouse Center Launchpad 261
Setting Up Your Data Warehouse Environment 261
Creating a Warehouse Database 261
Browsing the Source Data 261
Establishing IBM DB2 Data Warehouse Center Security 262
Building a Data Warehouse Using the Launchpad 262
Task 1: Define a Subject Area 264
Task 2: Define a Process 264
Task 3: Define a Warehouse Source 266
Task 4: Define a Warehouse Target 267
Task 5: Define a Step 268
Task 6: Link a Source to a Step 270
Task 7 Link a Step to a Target 270
Task 8: Define the Step Parameters 272
Task 9: Schedule a Step to Run 274
Defining Keys on Target Tables 274
Maintaining the Data Warehouse 275
Authorizing Users of the Warehouse 276
Cataloging Warehouse Data for Users 276
xii ContentsProcess and Step Task Control 277
Scheduling 278
Notifying the Data Administrator 282
Scheduling a Process 283
Triggering Steps Outside IBM DB2
Data Warehouse Center 286
Starting the External Trigger Server 287
Starting the External Trigger Client 287
Monitoring Strategies with IBM DB2 Data Warehouse Center 289
IBM DB2 Data Warehouse Center Monitoring Tools 289
Monitoring Data Warehouse Population 291
Monitoring Data Warehouse Usage 298
DB2 Monitoring Tools 299
Replication Center Monitoring 300
Warehouse Tuning 303
Updating Statistics 303
Reorganizing Your Data 304
Using DB2 Snapshot and Monitor 304
Using Visual Explain 305
Tuning Database Performance 307
Maintaining IBM DB2 Data Warehouse Center 307
Log History 308
Control Database 308
DB2 Data Warehouse Center V8 Enhancements 308
Summary 312
Chapter 9 Data Transformation with IBM DB2 Data Warehouse Center 313
IBM DB2 Data Warehouse Center Process Model 316
Identify the Sources and Targets 317
Identify the Transformations 318
The Process Model 320
IBM DB2 Data Warehouse Center Transformations 322
Refresh Considerations 327
Data Volume 328
Manage Data Editions 328
User-Defined Transformation Requirements 329
Multiple Table Loads 329
Ensure Warehouse Data Is Up-to-Date 329
Retry 333
SQL Transformation Steps 333
SQL Select and Insert 335
SQL Select and Update 337
DB2 Utility Steps 338
Export Utility Step 338
LOAD Utility 339
Warehouse Transformer Steps 340
Cleansing Transformer 340
Generating Key Table 343
Contents xiiiGenerating Period Table 344
Inverting Data Transformer 346
Pivoting Data 348
Date Format Changing 351
Statistical Transformers 352
Analysis of Variance (ANOVA) 352
Calculating Statistics 355
Calculating Subtotals 357
Chi-Squared Transformer 359
Correlation Analysis 362
Moving Average 364
Regression Analysis 366
Data Replication Steps 369
Setting Up Replication 371
Defining Replication Steps in IBM DB2 Data Warehouse Center 373
MQSeries Integration 379
Accessing Fixed-Length or Delimited MQSeries Messages 380
Using DB2 MQSeries Views 382
Accessing XML MQSeries Messages 384
User-Defined Program Steps 385
Vendor Integration 388
ETI•EXTRACT Integration 388
Trillium Integration 396
Ascential Integration 398
Microsoft OLE DB and Data Transformation Services 399
Accessing OLE DB 400
Accessing DTS Packages 401
Summary 401
Chapter 10 Meta Data and the IBM DB2 Warehouse Manager 403
What Is Meta Data? 404
Classification of Meta Data 406
Meta Data by Type of User 407
Meta Data by Degree of Formality at Origin 408
Meta Data by Usage Context 409
What Is the Meta Data Repository? 409
Feeding Your Meta Data Repository 410
Benefits of Meta data and the Meta Data Repository 411
Attributes of a Healthy Meta Data Repository 413
Maintaining the Repository 414
Challenges to Implementing a Meta Data Repository 415
IBM Meta Data Technology 416
Information Catalog 416
IBM DB2 Data Warehouse Center 417
Meta Data Acquisition by DWC 418
Collecting Meta Data from ETI•EXTRACT 420
Collecting Meta Data from INTEGRITY 425
Collecting Meta Data from DataStage 429
xiv ContentsCollecting Meta Data from ERwin 431
Collecting Meta Data from Axio 433
Collecting Meta Data from IBM OLAP Integration Server 434
Exchanging Meta Data between IBM DB2 Data Warehouse
Center Instances 437
Maintaining Test and Production Systems 438
Meta Data Exchange Formats 438
Tag Export and Impot 439
CWM Export and Impot 441
Transmission of DWC Meta Data to Other Tools 441
Transmission of DWC Meta Data to IBM Information Catalog 442
Transmission of DWC Meta Data to
OLAP Integration Server 445
Transmission of DWC Meta Data to IBM DB2 OLAP Server 447
Transmission of DWC Meta Data to Ascential INTEGRITY 448
Transferring Meta Data In/Out of the Information Catalog 448
Acquisition of Meta Data by the Information Catalog 450
Collecting Meta Data from IBM DB2 Data Warehouse Center 450
Collecting Meta Data from another Information Catalog 450
Accessing Brio Meta Data in the Information Catalog 450
Collecting Meta Data from BusinessObjects 451
Collecting Meta Data from Cognos 453
Collecting Meta Data from ERwin 454
Collecting Meta Data from QMF for Windows 455
Collecting Meta Data from ETI•EXTRACT 457
Collecting Meta Data from DB2 OLAP Server 459
Transmission of Information Catalog Meta Data 460
Transmitting Meta Data to Another Information Catalog 460
Enabling Brio to Access Information Catalog Meta Data 461
Transmitting Information Catalog Meta Data to BusinessObjects 462
Transmitting Information Catalog Meta Data to Cognos 463
Summary 463
Part Five OLAP and IBM 465
Chapter 11 Multidimensional Data with DB2 OLAP Server 467
Understanding the Analytic Cycle of OLAP 472
Generating Useful Metrics 474
OLAP Skills 476
Applying the Dimensional Model 477
Steering Your Organization with OLAP 478
Speed-of-Thought Analysis 478
The Outline of a Business 479
The OLAP Array 483
Relational Schema Limitations 484
Derived Measures 485
Implementing an Enterprise OLAP Architecture 486
Contents xvPrototyping the Data Warehouse 488
Database Design: Building Outlines 488
Application Manager 489
ESSCMD and MaxL 490
OLAP Integration Server 493
Support Requirements 495
DB2 OLAP Database as a Matrix 496
Block Creation Explored 498
Matrix Explosion 498
DB2 OLAP Server Sizing Requirements 499
What DB2 OLAP Server Stores 499
Using SET MSG ONLY: Pre-Version 8 Estimates 500
What is Representative Data? 501
Sizing Estimates for DB2 OLAP Server Version 8 501
Database Tuning 502
Goal Of Database Tuning 503
Outline Tuning Considerations 503
Batch Calculation and Data Storage 504
Member Tags and Dynamic Calculations 504
Disk Subsystem Utilization and Database File Configuration 506
Database Partitioning 506
Attribute Dimensions 507
Assessing Hardware Requirements 509
CPU Estimate 511
Disk Estimate 511
OLAP Auxiliary Storage Requirements 512
OLAP Backup and Disaster Recovery 512
Summary 513
Chapter 12 OLAP with IBM DB2 Data Warehouse Center 515
IBM DB2 Data Warehouse Center Step Types 516
Adding OLAP to Your Process 518
OLAP Server Main Page 519
OLAP Server Column Mapping Page 520
OLAP Server Program Processing Options 520
Other Considerations 520
OLAP Server Load Rules 521
Free Text Data Load 521
File with Load Rules 522
File without Load Rules 523
SQL Table with Load Rules 526
OLAP Server Calculation 527
Default Calculation 527
Calc with Calc Rules 528
Updating the OLAP Server Outline 530
Using a File 530
Using an SQL Table 531
Summary 533
xvi ContentsChapter 13 DB2 OLAP Functions 535
OLAP Functions 537
Specific Functions 537
RANK 537
DENSE_RANK 538
ROWNUMBER 538
PARTITION BY 539
ORDER BY 539
Window Aggregation Group Clause 540
GROUPING Capabilities: ROLLUP and CUBE 542
ROLLUP 542
CUBE 543
Ranking, Numbering, and Aggregation 544
RANK Example 545
ROW_NUMBER, RANK, and DENSE_RANK Example 546
RANK and PARTITION BY Example 546
OVER clause example 548
ROWS and ORDER BY Example 548
ROWS, RANGE, and ORDER BY Example 549
GROUPING, GROUP BY, ROLLUP, and CUBE 552
GROUPING, GROUP BY, and CUBE Example 552
ROLLUP Example 553
CUBE Example 555
OLAP Functions in Use 560
Presenting Annual Sales by Region and City 560
Data 560
BI Functions 561
Steps 561
Identifying Target Groups for a Campaign 562
Data 563
BI Functions 563
Steps 564
Summary 566
Part Six Enhanced Analytics 567
Chapter 14 Data Mining with Intelligent Miner 569
Data Mining and the BI Organization 570
Effective Data Mining 575
The Mining Process 575
Step 1: Create a Precise Definition of the Business Issue 577
Describing the Problem 578
Understanding Your Data 579
Using the Results 580
Step 2: Map Business Issue to Data Model and
Data Requirements 580
Step 3: Source and Preprocess the Data 582
Step 4: Explore and Evaluate the Data 582
Contents xviiStep 5: Select the Data Mining Technique 583
Discovery Data Mining 583
Predictive Mining 584
Step 6: Interpret the Results 585
Step 7: Deploy the Results 586
Integrating Data Mining 586
Skills for Implementing a Data Mining Project 587
Benefits of Data Mining 588
Data Quality 589
Relevant Dimensions 589
Using Mining Results in OLAP 590
Benefits of Mining DB2 OLAP Server 591
Summary 593
Chapter 15 DB2-Enhanced BI Features and Functions 595
DB2 Analytic Functions 596
AVG 597
CORRELATION 598
COUNT 598
COUNT_BIG 599
COVARIANCE 599
MAX 600
MIN 600
RAND 601
STDDEV 602
SUM 602
VARIANCE 602
Regression Functions 603
COVAR, CORR, VAR, STDDEV, and Regression Examples 606
COVARIANCE Example 606
CORRELATION Examples 607
VARIANCE Example 609
STTDEV Examples 609
Linear Regression Examples 610
BI-Centric Function Examples 612
Using Sample Data 612
Listing the Top Five Salespersons by Region This Year 615
Data Description 615
BI Functions Showcased 615
Steps 616
Determining Relationships between Product Purchases 617
Data Description 617
BI Functions Showcased 617
Steps 617
Summary 619
xviii ContentsChapter 16 Blending Spatial Data into the Warehouse 621
Spatial Analysis and the BI Organization 623
The Impact of Space 625
What Is Spatial Data? 628
The Onion Analogy 628
Spatial Data Structures 628
Vector Data 629
Raster Data 629
Triangulated Data 630
Spatial Data vs. Other Graphic Data 631
Obtaining Spatial Data 632
Creating Your Own Spatial Data 632
Acquiring Spatial Data 632
Government Data 633
Vendor Data 633
Spatial Data in DSS 634
Spatial Analysis and Data Mining 635
Serving Up Spatial Analysis 637
Typical Business Questions Directed at the Data Warehouse 639
Where are my customers coming from? 640
I don’t have customer address information-can
I still use spatial analysis tools? 641
Understanding a Spatially Enabled Data Warehouse 644
Geocoding 644
Technology Requirements for Spatial Warehouses 646
Adding Spatial Data to the Warehouse 647
Summary 649
Bibliography 651
Index 653
Other Data Warehouse Books
Data warehouse - Wikipedia, the free encyclopedia
Data Warehousing - What Is Data Warehouse
Data Warehousing Architecture and Implementation
Data Warehousing and Knowledge Discovery
Download
No comments:
Post a Comment