Mastering Data Warehouse Aggregates Solutions for Star Schema Performance

Note: Supplemental materials are not guaranteed with Rental or Used book purchases.

ISBN: 9780471777090 | 0471777099
Cover: Paperback
Copyright: 7/19/2006

Rent

(Recommended)

$47.48

Term

Due

Price

Semester

Dec 13

$49.99

Quarter

Aug 16

$47.48

Short Term

Jul 4

$44.99

*This item is part of an exclusive publisher rental program and requires an additional convenience fee. This fee will be reflected in the shopping cart.
Buy New

Currently Available, Usually Ships in 24-48 Hours

$68.29

This is the first book to provide in-depth coverage of star schema aggregates used in dimensional modeling-from selection and design, to loading and usage, to specific tasks and deliverables for implementation projects Covers the principles of aggregate schema design and the pros and cons of various types of commercial solutions for navigating and building aggregates Discusses how to include aggregates in data warehouse development projects that focus on incremental development, iterative builds, and early data loads

Foreword

xix

Acknowledgments

xxi

Introduction

xxiii

Fundamentals of Aggregates

(34)

Star Schema Basics

(20)

Operational Systems and the Data Warehouse

(1)

Operational Systems

(1)

Data Warehouse Systems

(1)

Facts and Dimensions

(2)

The Star Schema

(1)

Dimension Tables and Surrogate Keys

(3)

Fact Tables and Grain

(3)

Using the Star Schema

(2)

Multiple Stars and Conformance

(5)

Data Warehouse Architecture

(2)

Invisible Aggregates

(7)

Improving Performance

(2)

The Base Schema and the Aggregate Schema

(1)

The Aggregate Navigator

(1)

Principles of Aggregation

(1)

Providing the Same Results

(1)

The Same Facts and Dimension Attributes as the Base Schema

(1)

Other Types of Summarization

(3)

Pre-Joined Aggregates

(1)

Derived Tables

(1)

Tables with New Facts

(1)

Summary

(3)

Choosing Aggregates

(32)

What Is a Potential Aggregate?

(4)

Aggregate Fact Tables: A Question of Grain

(1)

Aggregate Dimensions Must Conform

(2)

Pre-Joined Aggregates Have Grain Too

(1)

Enumerating Potential Aggregates

(1)

Identifying Potentially Useful Aggregates

(15)

Drawing on Initial Design

(1)

Design Decisions

(3)

Listening to Users

(1)

Where Subject Areas Meet

(1)

The Conformance Bus

(1)

Aggregates for Drilling Across

(3)

Query Patterns of an Existing System

(1)

Analyzing Reports for Potential Aggregates

(5)

Choosing Which Reports to Analyze

(1)

Assessing the Value of Potential Aggregates

(10)

Number of Aggregates

(1)

Presence of an Aggregate Navigator

(1)

Space Consumed by Aggregate Tables

(1)

How Many Rows Are Summarized

(2)

Examining the Number of Rows Summarized

(3)

The Cardinality Trap and Sparsity

(2)

Who Will Benefit from the Aggregate

(1)

Summary

(2)

Designing Aggregates

(42)

The Base Schema

(13)

Identification of Grain

(1)

When Grain Is Forgotten

(1)

Grain and Aggregates

(1)

Conformance Bus

(2)

Rollup Dimensions

(2)

Aggregation Points

(1)

Natural Keys

(1)

Source Mapping

(1)

Slow Change Processing

(1)

Hierarchies

(2)

Housekeeping Columns

(3)

Design Principles for the Aggregate Schema

(17)

A Separate Star for Each Aggregation

(1)

Single Schema and the Level Field

(3)

Drawbacks to the Single Schema Approach

(1)

Advantages of Separate Tables

(1)

Pre-Joined Aggregates

(1)

Naming Conventions

(1)

Naming the Attributes

(1)

Naming Aggregate Tables

(2)

Aggregate Dimension Design

(1)

Attributes of Aggregate Dimensions

(1)

Sourcing Aggregate Dimensions

(1)

Shared Dimensions

(1)

Aggregate Fact Table Design

(1)

Aggregate Facts: Names and Data Types

(1)

No New Facts, Including Counts

(2)

Degenerate Dimensions

(1)

Audit Dimension

(1)

Sourcing Aggregate Fact Tables

(1)

Pre-Joined Aggregate Design

(1)

Documenting the Aggregate Schema

(10)

Identify Schema Families

(1)

Identify Dimensional Conformance

(2)

Documenting Aggregate Dimension Tables

101

(2)

Documenting Aggregate Fact Tables

103

(3)

Pre-Joined Aggregates

106

(2)

Materialized Views and Materialized Query Tables

108

(1)

Summary

108

(1)

Using Aggregates

109

(44)

Which Tables to Use?

110

(6)

The Schema Design

110

(3)

Relative Size

113

(1)

Aggregate Portfolio and Availability

114

(2)

Requirements for the Aggregate Navigator

116

(10)

Why an Aggregate Navigator?

116

(1)

Two Views and Query Rewrite

117

(3)

Dynamic Availability

120

(1)

Multiple Front Ends

121

(2)

Multiple Back Ends

123

(3)

Evaluating Aggregate Navigators

126

(10)

Front-End Aggregate Navigators

127

(1)

Approach

127

(1)

Pros and Cons

128

(1)

Back-End Aggregate Navigation

129

(1)

Approach

129

(1)

Pros and Cons

130

(4)

Performance Add-On Technologies and OLAP

134

(1)

Approach

134

(1)

Pros and Cons

135

(1)

Specific Solutions

136

(15)

Living with Materialized Views

137

(1)

Using Materialized Views

137

(1)

Materialized Views as Pre-Joined Aggregates

137

(3)

Materialized Views as Aggregate Fact Tables (Without Aggregate Dimensions)

140

(1)

Materialized Views and Aggregate Dimension Tables

141

(1)

Additional Considerations

142

(2)

Living with Materialized Query Tables

144

(1)

Using Materialized Query Tables

144

(1)

Materialized Query Tables as Pre-Joined Aggregates

145

(1)

Materialized Query Tables as Aggregate Fact Tables (Without Aggregate Dimensions)

146

(1)

Materialized Query Tables and Aggregate Dimension Tables

147

(1)

Additional Considerations

148

(1)

Working Without an Aggregate Navigator

148

(1)

Human Decisions

149

(1)

Maintaining the Aggregate Portfolio

150

(1)

Impact on the ETL Process

151

(1)

Summary

151

(2)

ETL Part 1: Incorporating Aggregates

153

(34)

The Load Process

154

(4)

The Importance of the Load

154

(1)

Tools of the Load

155

(1)

Incremental Loads and Changed Data Identification

156

(1)

The Top-Level Process

157

(1)

Loading the Base Star Schema

158

(16)

Loading Dimension Tables

159

(1)

Attributes of the Dimension Table

159

(2)

Requirements for the Dimension Load Process

161

(1)

Extracting and Preparing the Record

161

(2)

Process New Records

163

(1)

Process Type 1 Changes

164

(1)

Process Type 2 Changes

165

(2)

Loading Fact Tables

167

(1)

Requirements for the Fact Table Load Process

167

(1)

Acquire Data and Assemble Facts

168

(2)

Identification of Surrogate Keys

170

(2)

Putting It All Together

172

(2)

Loading the Aggregate Schema

174

(8)

Loading Aggregates Separately from Base Schema Tables

174

(1)

Invalid Aggregates

175

(1)

Load Frequency

176

(1)

Taking Aggregates Off-Line

176

(1)

Off-Line Load Processes

177

(1)

Materialized Views and Materialized Query Tables

178

(2)

Drop and Rebuild Versus Incremental Load

180

(1)

Drop and Rebuild

180

(1)

Incremental Loading of Aggregates

181

(1)

Real-Time Loads

182

(3)

Real-Time Load of the Base Schema

182

(1)

Real-Time Load and Aggregate Tables

183

(1)

Partitioning the Schema

183

(2)

Summary

185

(2)

ETL Part 2: Loading Aggregates

187

(38)

The Source Data for Aggregate Tables

188

(5)

Changed Data Identification

188

(1)

Elimination of Redundant Processing

189

(1)

Ensuring Conformance

190

(2)

Loading the Base Schema and Aggregates Simultaneously

192

(1)

Loading Aggregate Dimensions

193

(12)

Requirements for the Aggregate Dimension Load Process

194

(1)

Extracting and Preparing the Records

195

(2)

Identifying and Processing New Records

197

(1)

Identifying and Processing Type 1 Changes

198

(5)

Processing Type 2 Changes

203

(1)

Key Mapping

204

(1)

Loading Aggregate Fact Tables

205

(9)

Requirements for Loading Aggregate Fact Tables

205

(1)

Acquire Data and Assemble Facts

205

(1)

Selecting Source Columns

206

(2)

Processing New Facts Only

208

(1)

Calculating and Aggregating Facts

208

(1)

One Query Does It All

209

(1)

Identification of Surrogate Keys

210

(2)

Aggregating Over Time

212

(2)

Dropping and Rebuilding Aggregates

214

(3)

Dropping and Rebuilding Aggregate Dimension Tables

214

(2)

Dropping and Rebuilding Aggregate Fact Tables

216

(1)

Pre-Joined Aggregates

217

(4)

Dropping and Rebuilding a Pre-Joined Aggregate

217

(2)

Incrementally Loading a Pre-Joined Aggregate

219

(2)

Materialized Views and Materialized Query Tables

221

(2)

Defining Attributes for Aggregate Dimensions

221

(1)

Optimizing the Hierarchy

222

(1)

Summary

223

(2)

Aggregates and Your Project

225

(32)

Data Warehouse Implementation

226

(7)

Incremental Implementation of the Data Warehouse

226

(1)

Planning Data Marts Around Conformed Dimensions

226

(4)

Other Approaches

230

(1)

Incorporating Aggregates into the Project

230

(1)

Aggregates and the First Data Mart

231

(1)

Subsequent Subject Areas

232

(1)

The Aggregate Project

233

(19)

Strategy Stage

234

(1)

Technology Selection: Choosing an Aggregate Navigator

234

(6)

Additional Strategic Tasks and Deliverables

240

(1)

Design Stage

241

(2)

Design of the Aggregate Schema and Load Specification

243

(1)

Design Documentation

243

(1)

Developing Test Plans for Aggregates

244

(1)

Build Stage

245

(1)

Iterative Build and Aggregates

245

(1)

Build Tasks and Aggregates

246

(4)

Deployment

250

(1)

Transitioning to Production, Final Testing, and Documentation

250

(2)

End User Education

252

(1)

Management of Aggregates

252

(3)

Maintenance Responsibilities

252

(2)

Ad Hoc Changes to Aggregate Portfolio

254

(1)

An Ongoing Process

254

(1)

Summary

255

(2)

Advanced Aggregate Design

257

(34)

Aggregating Facts

258

(14)

Periodic Snapshots Designs

258

(1)

Transactions

258

(1)

Snapshots

259

(1)

Semi-Additivity

260

(1)

Invisible Aggregates for Periodic Snapshots

261

(2)

Averaging Semi-Additive Facts Produces a Derived Schema

263

(1)

Taking Less Frequent Snapshots Does Not Produce an Invisible Aggregate

264

(1)

Accumulating Snapshots

265

(1)

The Accumulating Snapshot

265

(2)

Aggregating the Accumulating Snapshot

267

(2)

Factless Fact Tables

269

(1)

Factless Events and Aggregates

269

(2)

Coverage Tables and Aggregates

271

(1)

Aggregating Dimensions

272

(11)

Transaction Dimensions

273

(1)

Timestamping a Dimension

273

(1)

Aggregating a Timestamped Dimension

274

(1)

Bridge Tables

275

(1)

Dealing with Multi-Valued Attributes

276

(2)

Aggregates and Bridge Tables

278

(4)

Core and Custom Stars

282

(1)

Other Schema Types

283

(7)

Snowflakes and Aggregates

283

(1)

The Snowflake Schema

283

(1)

Aggregating Snowflakes

284

(3)

Third Normal Form Schemas and Aggregates

287

(3)

Summary

290

(1)

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Mastering Data Warehouse Aggregates Solutions for Star Schema Performance

Summary

Author Biography

Table of Contents

Supplemental Materials