Oracle 10g Data Warehousing

855 Pages·2004·18.11 MB·English

by  Lilian Hobbs PhD, Susan Hillson MS in CIS Boston University, Shilpa Lawande, Pete Smith

TEAM LinG - Live, Informative, Non-cost and Genuine!
Oracle Database 10g
Data Warehousing
TEAM LinG - Live, Informative, Non-cost and Genuine!
Oracle 9iR2 Data Warehousing, Hobbs, et al
ISBN: 1-55558-287-7, 2004
Oracle High Performance Tuning for 9i and 10g, Gavin Powell,
ISBN: 1-55558-305-9, 2004
Oracle SQL Jumpstart with Examples, Gavin Powell,
ISBN: 1-55558-323-7, 2005
Oracle Real Applications Clusters, Murali Vallath,
ISBN: 1-55558-288-5, 2004
For more information or to order these and other Digital Press
titles, please visit our website at www.books.elsevier.com/digitalpress!
At www.books.elsevier.com/digitalpress you can:
•Join the Digital Press Email Service and have news about
our books delivered right to your desktop
•Read the latest news on titles
•Sample chapters on featured titles for free
•Question our expert authors and editors
•Download free software to accompany select texts
TEAM LinG - Live, Informative, Non-cost and Genuine!
Oracle Database 10g
Data Warehousing
Lilian Hobbs
Susan Hillson
Shilpa Lawande
Pete Smith
Amsterdam • Boston • Heidelberg • London • New York • Oxford
Paris • San Diego• San Francisco • Singapore • Sydney • Tokyo
TEAM LinG - Live, Informative, Non-cost and Genuine!
Elsevier Digital Press
30 Corporate Drive, Suite 400, Burlington, MA 01803, USA
Linacre House, Jordan Hill, Oxford OX2 8DP, UK
Copyright © 2005, Elsevier Inc. All rights reserved.
No part of this publication may be reproduced, stored in a retrieval system, or
transmitted in any form or by any means, electronic, mechanical, photocopying,
recording, or otherwise, without the prior written permission of the publisher.
Permissions may be sought directly from Elsevier’s Science & Technology Rights
Department in Oxford, UK: phone: [phone], fax: [phone],
e-mail:
To Mum, Dad & Reggie, no longer with us, but always in my thoughts
—Lilian
For Aji and Ajoba, my grandparents
—Shilpa
To Family and Friends, Thanks!
—Pete
To Max, who can fix most anything
—Susan
TEAM LinG - Live, Informative, Non-cost and Genuine!
Contents
Foreword xix
Preface xxi
Acknowledgments xxv
1 Data Warehousing 1
1.1 An Introduction to Oracle Database 10g 1
1.1.1 The Economic Climate Influences Technology Spending 1
1.1.2 Consolidation 1
1.1.3 Consolidation of the Hardware 2
1.1.4 Consolidation of Data into a Single Company View 2
1.1.5 Consolidation of Applications 3
1.1.6 The g in Oracle Database 10g—The Grid 3
1.2 What Is a Data Warehouse? 4
1.2.1 Why Do You Need a Data Warehouse? 4
1.3 A Historical Perspective 5
1.3.1 The Rise of the Data Warehouse 6
1.3.2 Data Warehouses Evolved As Separate Systems 8
1.3.3 The Data Mart 11
1.3.4 Reporting, Query, and Analysis Tools Became
Browser Based 14
1.3.5 OLAP and Data Mining Functionality Are Embedded
in the Oracle Database 14
1.4 Data Warehousing Features in the Oracle Database 10g 16
1.5 Building a Data Warehouse Poses Many Challenges 17
1.5.1 Managing the Warehouse 17
1.5.2 The Role of Metadata 18
1.5.3 Increasing Data Volume 19
1.5.4 Higher Availability 19
vii
MAET iL n G - iL ve , nI of tamr ive , oN noc- st na d eG niu ne!
viii Contents
1.5.5 More Users/Better Performance 20
1.5.6 New Types Of Applications 20
1.6 The Future of Data Warehousing 21
1.6.1 Real-Time Data Warehouses 21
1.6.2 The Disappearance of the Separate Data Warehouse 21
1.7 Summary 22
2 Designing a Warehouse 23
2.1 Designing a Warehouse 23
2.1.1 Don’t Use Entity Relationship (E-R) Modeling 25
2.1.2 Dimensional Modeling 25
2.1.3 Fact Table 26
2.1.4 Dimension Table 27
2.1.5 Warehouse Keys 28
2.1.6 Normalizing the Data Warehouse 28
2.1.7 Data Warehouse or Data Mart 30
2.1.8 The Easy Shopping Inc. Example 30
2.2 Other Design Considerations 31
2.2.1 Design to Manage 31
2.2.2 Design for Performance 33
2.3 Implementing the Design 34
2.3.1 Single Database or Many? 35
2.3.2 Naming Conventions 35
2.3.3 Database Configuration Assistant 36
2.3.4 Which Schema? 44
2.3.5 Data Files and Tablespaces 48
2.3.6 Creating the Fact and Dimension Tables 53
2.3.7 Constraints 5
2.3.8 Indexes 63
2.3.9 Partitioning 65
2.3.10 Materialized Views 66
2.3.1 Security 67
2.3.12 Using the Parallel Option 69
2.4 Testing the Design 70
2.5 The Schema for Easy Shopping Inc. 71
3 Architecture of a Data Warehouse 73
3.1 Introduction 73
3.2 Hardware Configurations for a Warehouse 74
3.2.1 Server Architectures 74
MAET iL n G - iL ve , nI of tamr ive , oN noc- st na d eG niu ne!
Contents ix
3.2.2 The Oracle Database Architecture 80
3.3 Hardware Components 85
3.3.1 Memory 86
3.3.2 Procesors 87
3.3.3 Storage Configurations for a Warehouse 87
3.4 Automatic Storage Management 94
3.4.1 ASM Overview 95
3.4.2 Administering ASM 97
3.4.3 Using ASM in the Warehouse 107
3.5 File Management in Oracle 108
3.5.1 Oracle Managed Files 108
3.5.2 Bigfiles and Big Databases 110
3.6 Summary 111
4 Physical Design of the Data Warehouse 113
4.1 Introduction 113
4.2 Data Partitioning 113
4.2.1 How to Partition Data? 114
4.2.2 Range Partitioning 115
4.2.3 Hash Partitioning 116
4.2.4 List Partitioning 118
4.2.5 Composite Partitioning 119
4.2.6 Multicolumn Partition Keys 123
4.2.7 Choosing the Partitioning Method 125
4.2.8 Partitioning Using Oracle Enterprise Manager 126
4.2.9 Partition Maintenance Operations 130
4.3 Indexing 131
4.3.1 B*tree Indexes 132
4.3.2 Bitmapped Indexes 133
4.3.3 Bitmapped Join Indexes 136
4.3.4 Function-based Indexes 138
4.3.5 Partitioned Indexes 139
4.3.6 Which Indexes to Create? 143
4.3.7 Using Oracle Enterprise Manager to Create Indexes 145
4.4 Index-Organized Tables 148
4.4.1 Creating an IOT 149
4.4.2 Partitioning and Indexing an IOT 150
4.4.3 Using an IOT in a Data Warehouse 151
4.5 Data Compression 151
4.5.1 Table Compression 151
4.5.2 Index Compression 153
Contents
MAET iL n G - iL ve , nI of tamr ive , oN noc- st na d eG niu ne!
x Contents
4.6 Summary 154
5 Loading Data into the Warehouse 155
5.1 The ETL Process 155
5.2 Extracting Data from the Operational Systems 157
5.2.1 Identifying Data That Has Changed 159
5.2.2 Oracle Change Data Capture 161
5.3 Transforming the Data into a Common Representation 176
5.3.1 Integrating Data from Multiple Sources 177
5.3.2 Cleansing Data 177
5.3.3 Deriving New Data 178
5.3.4 Generating Warehouse Keys 178
5.3.5 Choosing the Optimal Place to Perform
the Transformations 180
5.4 Loading the Warehouse 181
5.4.1 Using SQL*Loader to Load the Warehouse 183
5.4.2 The Data File 185
5.4.3 Loading the Warehouse Using Data Pump 205
5.4.4 Loading the Warehouse Using External Tables 212
5.4.5 Loading the Warehouse Using
Transportable Tablespaces 220
5.4.6 Loading the Dimensions Using SQL MERGE 227
5.5 Transformations inside the Oracle Database 232
5.5.1 Transformations That Cleanse Data and
Derive New Data 233
5.5.2 Validating Data Using a Dimension 240
5.5.3 Looking up the Warehouse Key 240
5.5.4 Table Functions 242
5.5.5 Transformations That Split One Data Source into
Multiple Targets 245
5.5.6 Moving Data from a Staging Table into the Fact Table 246
5.6 Postload Operations 251
5.6.1 Step 1: Gather Optimizer Statistics for the Tables 251
5.6.2 Step 2: Verify the Dimensions 251
5.6.3 Step 3: Refresh the materialized views 252
5.6.4 Step 4: Gather Optimizer Statistics for
the Materialized Views 252
5.6.5 Step 5: Back up the Database Table, or Partition 252
5.6.6 Step 6: Publish the Data 252
5.7 Using Tools for the ETL process 253
5.8 Summary 253
MAET iL n G - iL ve , nI of tamr ive , oN noc- st na d eG niu ne