IBM Data Warehousing: With IBM Business Intelligence Tools

@Team LiB......Page 0
Contents......Page 12
About the Contributors......Page 25
Introduction......Page 28
How the Book Is Organized......Page 30
Summary......Page 32
PART
One
Fundamentals of
Business Intelligence
and the Data Warehouse......Page 34
CHAPTER 1 Overview of the BI Organization......Page 36
Overview of the BI Organization Architecture......Page 37
Planning for Information Content......Page 43
Designing for Information Content......Page 46
Implementing Information Content......Page 48
Measuring ROI......Page 51
Applying ROI......Page 52
Questions for ROI Benefits......Page 54
IBM and The BI Organization......Page 55
Seamless Integration......Page 56
Online Analytic Processing......Page 57
Database-Resident Tools......Page 58
Simplified Data Delivery System......Page 59
Zero-Latency......Page 60
Summary......Page 61
CHAPTER 2 Business Intelligence
Fundamentals......Page 62
Data Warehouse......Page 64
Data Targets......Page 65
Warehouse Components......Page 69
Extraction, Transformation, and Loading......Page 70
Extraction......Page 71
Data Refining......Page 72
Data Access......Page 73
Meta Data......Page 74
Analytical User Requirements......Page 75
Online Analytical Processing......Page 76
Multidimensional Views......Page 77
Time Intelligence......Page 78
Data Mining......Page 79
Dimensional Technology and BI......Page 80
The OLAP Server......Page 81
MOLAP......Page 82
Defining the Dimensional Spectrum......Page 83
Touch Points......Page 85
Closed-Loop Learning......Page 86
Historical Integrity......Page 87
Summary......Page 91
CHAPTER 3 Planning Data
Warehouse Iterations......Page 92
Planning Any Iteration......Page 94
Building Your BI Plan......Page 95
Enterprise Strategy......Page 96
Designing the Technical Architecture......Page 97
Designing the Data Architecture......Page 99
Implementing and Maintaining the Warehouse......Page 102
Planning the First Iteration......Page 103
Conducting a Readiness Assessment......Page 104
Resource Planning......Page 107
Identifying Opportunities with the DIF Matrix1......Page 110
Applying the DIF Matrix......Page 111
Select Candidate Iteration Opportunities......Page 113
User JAD Session and Scoring......Page 114
Dysfunctional......Page 115
Impact......Page 116
DIF Matrix Results......Page 117
Identifying Strategic Business Questions......Page 120
Implementing a Project Approach......Page 122
The Inmon Approach......Page 123
The Spiral Approach......Page 124
Reducing Risk......Page 125
The Spiral Approach and Your Life Cycle Model......Page 126
Warehouse Development and the Spiral Model......Page 127
Flattening Spiral Rounds to Time Lines......Page 131
The IBM Approach......Page 133
Summary......Page 136
PART
Two
Business Intelligence
Architecture......Page 138
CHAPTER 4 Designing the Data Architecture......Page 140
Choosing the Right Architecture......Page 143
Atomic Layer Alternatives......Page 146
ROLAP Platform on a 3NF Atomic Layer......Page 149
HOLAP Platform on a Star Schema Atomic Layer......Page 150
Data Marts......Page 151
Atomic Layer with Dependent Data Marts......Page 153
Independent Data Marts......Page 154
Data Delivery Architecture......Page 155
Comparing ETL and EAI......Page 159
Expected Deliverables......Page 160
Modeling the Architecture......Page 162
Business Logical Model......Page 163
Atomic-Level Model......Page 165
Modeling the Data Marts......Page 166
Comparing Atomic and Star Data......Page 170
Operational Data Store......Page 171
Data Architecture Strategy......Page 173
Summary......Page 176
CHAPTER 5 Technical Architecture and Data
Management Foundations......Page 178
Centralized Data Warehousing......Page 181
Distributed Data Warehousing......Page 185
Parallelism and the Warehouse......Page 187
Partitioning Data Storage......Page 190
Redistribution and Table Collocation......Page 191
Replicated Tables......Page 193
Multidimensional Clusters as Indexes......Page 194
Hierarchical Storage Considerations......Page 195
DB2 and Star Schemas......Page 197
SMP, MPP, and Clusters......Page 199
Shared-Resource vs. Shared-Nothing......Page 201
DB2 on Hardware Architectures......Page 202
Static and Dynamic Parallelism......Page 203
Backup......Page 205
Multidimensional Clustering......Page 207
Unplanned Outages......Page 208
Sizing Requirements......Page 212
Summary......Page 214
PART
Three
Data Management......Page 216
CHAPTER 6 DB2 BI Fundamentals......Page 218
High Availability......Page 219
Multidimensional Clustering......Page 220
Online Loads......Page 221
Load From Cursor......Page 222
Online Load and MQT Maintenance......Page 223
MQT Staging Tables......Page 224
Online Table Reorganization......Page 225
Dynamic Bufferpool Management......Page 227
Database Managed Storage Considerations......Page 228
Logging Considerations......Page 229
eLiza and SMART......Page 230
AUTOCONFIGURE......Page 231
Maintenance Mode......Page 232
INSTEAD OF Triggers......Page 233
DML Operations through UNION ALL......Page 234
Informational Constraints......Page 235
Connection Concentrator......Page 236
Type-2 Indexes......Page 237
Extensibility......Page 239
Spatial Extender......Page 240
XML Extender......Page 241
DB2 Scoring......Page 242
Summary......Page 244
CHAPTER 7 DB2 Materialized Query Tables......Page 246
Populating......Page 252
Deferred Refresh......Page 254
Immediate Refresh......Page 259
Loading Underlying Tables......Page 260
New LOAD Options......Page 261
Using DB2 ALTER......Page 264
State Considerations......Page 265
Matching Criteria......Page 266
Matching Permitted......Page 267
Matching Inhibited......Page 273
MQT Design......Page 276
MQT Tuning......Page 277
Refresh Optimization......Page 278
Materialized View Limitations......Page 280
Summary......Page 282
PART
Four
Warehouse
Management......Page 284
CHAPTER 8 Warehouse Management
with IBM DB2 Data
Warehouse Center......Page 286
Warehouse Source......Page 287
Warehouse Agent and Agent Site......Page 288
Warehouse Control Database......Page 289
Warehouse Process and Step......Page 290
Replication Step......Page 291
OLAP Server Program Step......Page 292
User-Defined Program Step......Page 293
Browsing the Source Data......Page 294
Building a Data Warehouse Using the Launchpad......Page 295
Task 2: Define a Process......Page 297
Task 3: Define a Warehouse Source......Page 299
Task 4: Define a Warehouse Target......Page 300
Task 5: Define a Step......Page 301
Task 7: Link a Step to a Target......Page 303
Task 8: Define the Step Parameters......Page 305
Defining Keys on Target Tables......Page 307
Maintaining the Data Warehouse......Page 308
Cataloging Warehouse Objects for Users......Page 309
Process and Step Task Control......Page 310
Scheduling......Page 311
Notifying the Data Administrator......Page 315
Scheduling a Process......Page 316
Triggering Steps Outside IBM DB2
Data Warehouse Center......Page 319
Starting the External Trigger Client......Page 320
IBM DB2 Data Warehouse Center Monitoring Tools......Page 322
Monitoring Data Warehouse Population......Page 324
Monitoring Data Warehouse Usage......Page 331
DB2 Monitoring Tools......Page 332
Replication Center Monitoring......Page 333
Updating Statistics......Page 336
Using DB2 Snapshot and Monitor......Page 337
Using Visual Explain......Page 338
Maintaining IBM DB2 Data Warehouse Center......Page 340
DB2 Data Warehouse Center V8 Enhancements......Page 341
Summary......Page 345
CHAPTER 9 Data Transformation with IBM
DB2 Data Warehouse Center......Page 346
IBM DB2 Data Warehouse Center Process Model......Page 349
Identify the Sources and Targets......Page 350
Identify the Transformations......Page 351
The Process Model......Page 353
IBM DB2 Data Warehouse Center Transformations......Page 355
Refresh Considerations......Page 360
Manage Data Editions......Page 361
Ensure Warehouse Data Is Up-to-Date......Page 362
SQL Transformation Steps......Page 366
SQL Select and Insert......Page 368
SQL Select and Update......Page 370
Export Utility Step......Page 371
LOAD Utility......Page 372
Cleansing Transformer......Page 373
Generating Key Table......Page 376
Generating Period Table......Page 377
Inverting Data Transformer......Page 379
Pivoting Data......Page 381
Date Format Changing......Page 384
Analysis of Variance (ANOVA)......Page 385
Calculating Statistics......Page 388
Calculating Subtotals......Page 390
Chi-Squared Transformer......Page 392
Correlation Analysis......Page 395
Moving Average......Page 397
Regression Analysis......Page 399
Data Replication Steps......Page 402
Setting Up Replication......Page 404
Defining Replication Steps in IBM DB2
Data Warehouse Center......Page 406
MQSeries Integration......Page 412
Accessing Fixed-Length or Delimited
MQSeries Messages......Page 413
Using DB2 MQSeries Views......Page 415
Accessing XML MQSeries Messages......Page 417
User-Defined Program Steps......Page 418
ETI.EXTRACT Integration......Page 421
Trillium Integration......Page 429
Ascential Integration......Page 431
Microsoft OLE DB and Data
Transformation Services......Page 432
Accessing OLE DB......Page 433
Summary......Page 434
CHAPTER 10 Meta Data and the IBM DB2
Warehouse Manager......Page 436
What Is Meta Data?......Page 437
Classification of Meta Data......Page 439
Meta Data by Type of User......Page 440
Meta Data by Degree of Formality at Origin......Page 441
What Is the Meta Data Repository?......Page 442
Feeding Your Meta Data Repository......Page 443
Benefits of Meta Data and the
Meta Data Repository......Page 444
Attributes of a Healthy Meta Data Repository......Page 446
Maintaining the Repository......Page 447
Challenges to Implementing a Meta Data Repository......Page 448
Information Catalog......Page 449
IBM DB2 Data Warehouse Center......Page 450
Meta Data Acquisition by DWC......Page 451
Collecting Meta Data from ETI.EXTRACT......Page 453
Collecting Meta Data from INTEGRITY......Page 458
Collecting Meta Data from DataStage......Page 462
Collecting Meta Data from ERwin......Page 464
Collecting Meta Data from Axio......Page 466
Collecting Meta Data from IBM OLAP Integration Server......Page 467
Exchanging Meta Data between IBM DB2
Data Warehouse Center Instances......Page 470
Meta Data Exchange Formats......Page 471
Tag Export and Import......Page 472
Transmission of DWC Meta Data to Other Tools......Page 474
Transmission of DWC Meta Data to
IBM Information Catalog......Page 475
Transmission of DWC Meta Data to
OLAP Integration Server......Page 478
Transmission of DWC Meta Data to IBM DB2 OLAP Server......Page 480
Transferring Meta Data In/Out of the Information
Catalog......Page 481
Accessing Brio Meta Data in the Information Catalog......Page 483
Collecting Meta Data from BusinessObjects......Page 484
Collecting Meta Data from Cognos......Page 486
Collecting Meta Data from ERwin......Page 487
Collecting Meta Data from QMF for Windows......Page 488
Collecting Meta Data from ETI.EXTRACT......Page 490
Collecting Meta Data from DB2 OLAP Server......Page 492
Transmitting Meta Data to Another Information Catalog......Page 493
Enabling Brio to Access Information Catalog Meta Data......Page 494
Transmitting Information Catalog Meta Data to
BusinessObjects......Page 495
Summary......Page 496
PART
Five
OLAP and IBM......Page 498
CHAPTER 11 Multidimensional Data with
DB2 OLAP Server......Page 500
Understanding the Analytic Cycle of OLAP......Page 505
Generating Useful Metrics......Page 507
OLAP Skills......Page 509
Applying the Dimensional Model......Page 510
Speed-of-Thought Analysis......Page 511
The Outline of a Business......Page 512
The OLAP Array......Page 516
Relational Schema Limitations......Page 517
Derived Measures......Page 518
Implementing an Enterprise OLAP Architecture......Page 519
Database Design: Building Outlines......Page 521
Application Manager......Page 522
ESSCMD and MaxL......Page 523
OLAP Integration Server......Page 526
Support Requirements......Page 528
DB2 OLAP Database as a Matrix......Page 529
Matrix Explosion......Page 531
What DB2 OLAP Server Stores......Page 532
Using SET MSG ONLY: Pre-Version 8 Estimates......Page 533
Sizing Estimates for DB2 OLAP Server Version 8......Page 534
Database Tuning......Page 535
Outline Tuning Considerations......Page 536
Member Tags and Dynamic Calculations......Page 537
Database Partitioning......Page 539
Attribute Dimensions......Page 540
Assessing Hardware Requirements......Page 542
Disk Estimate......Page 544
OLAP Backup and Disaster Recovery......Page 545
Summary......Page 546
CHAPTER 12 OLAP with IBM DB2 Data
Warehouse Center......Page 548
IBM DB2 Data Warehouse Center Step Types......Page 549
Adding OLAP to Your Process......Page 551
OLAP Server Main Page......Page 552
Other Considerations......Page 553
Free Text Data Load......Page 554
File with Load Rules......Page 555
File without Load Rules......Page 556
SQL Table with Load Rules......Page 559
Default Calculation......Page 560
Calc with Calc Rules......Page 561
Using a File......Page 563
Using an SQL Table......Page 564
Summary......Page 566
CHAPTER 13 DB2 OLAP Functions......Page 568
RANK......Page 570
ROWNUMBER......Page 571
ORDER BY......Page 572
Window Aggregation Group Clause......Page 573
ROLLUP......Page 575
CUBE......Page 576
Ranking, Numbering, and Aggregation......Page 577
RANK Example......Page 578
RANK and PARTITION BY Example......Page 579
ROWS and ORDER BY Example......Page 581
ROWS, RANGE, and ORDER BY Example......Page 582
GROUPING, GROUP BY, and CUBE Example......Page 585
ROLLUP Example......Page 586
CUBE Example......Page 588
Data......Page 593
Steps......Page 594
Identifying Target Groups for a Campaign......Page 595
BI Functions......Page 596
Steps......Page 597
Summary......Page 599
PART
Six
Enhanced Analytics......Page 600
CHAPTER 14 Data Mining with
Intelligent Miner......Page 602
Data Mining and the BI Organization......Page 603
The Mining Process......Page 608
Step 1: Create a Precise Definition of the Business Issue......Page 610
Describing the Problem......Page 611
Understanding Your Data......Page 612
Step 2: Map Business Issue to Data Model and Data
Requirements......Page 613
Step 4: Explore and Evaluate the Data......Page 615
Discovery Data Mining......Page 616
Predictive Mining......Page 617
Step 6: Interpret the Results......Page 618
Integrating Data Mining......Page 619
Skills for Implementing a Data Mining Project......Page 620
Benefits of Data Mining......Page 621
Relevant Dimensions......Page 622
Using Mining Results in OLAP......Page 623
Benefits of Mining DB2 OLAP Server......Page 624
Summary......Page 626
CHAPTER 15 DB2-Enhanced BI
Features and Functions......Page 628
DB2 Analytic Functions......Page 629
AVG......Page 630
COUNT......Page 631
COVARIANCE......Page 632
MIN......Page 633
RAND......Page 634
VARIANCE......Page 635
Regression Functions......Page 636
COVARIANCE Example......Page 639
CORRELATION Examples......Page 640
STDDEV Examples......Page 642
Linear Regression Examples......Page 643
Using Sample Data......Page 645
BI Functions Showcased......Page 648
Steps......Page 649
Steps......Page 650
Summary......Page 652
CHAPTER 16 Blending Spatial Data
into the Warehouse......Page 654
Spatial Analysis and the BI Organization......Page 656
The Impact of Space......Page 658
Spatial Data Structures......Page 661
Raster Data......Page 662
Triangulated Data......Page 663
Spatial Data vs. Other Graphic Data......Page 664
Acquiring Spatial Data......Page 665
Vendor Data......Page 666
Spatial Data in DSS......Page 667
Spatial Analysis and Data Mining......Page 668
Serving Up Spatial Analysis......Page 670
Typical Business Questions Directed
at the Data Warehouse......Page 672
Where Are My Customers Coming From?......Page 673
I Don’t Have Customer Address Information—
Can I Still Use Spatial Analysis Tools?......Page 674
Geocoding......Page 677
Technology Requirements for Spatial Warehouses......Page 679
Adding Spatial Data to the Warehouse......Page 680
Summary......Page 682
Bibliography......Page 684
Index......Page 686