The Official Guide to Informix Red Brick Data Warehousing

Contents

Preface

Part I: Getting Started

Chapter 1: Why Red Brick Decision Server?
Data Mart or Data Warehouse -- Which Do I Build?
Overview of Red Brick Decision Server Technology Components
STARjoins and indexes
TARGETindex and TARGETjoin
RISQL
Referential integrity
Table Management Utility
Warehouse Administrator
Vista
DST/ECC
Connectivity and security
What's new on Red Brick Decision Server
How to Get Started in Red Brick Decision Server
Single source or subject specific
Appropriate sponsorship
Identify business pain
Supporting data
The correct number and type of resources
Measurable results
Lessons from the Trenches
Think big, start small
Don't underestimate
Be sure of your sponsorship

Chapter 2: Red Brick Decision Server Project Fundamentals
A Typical Project
Components
Scope and scope creep
Critical areas to monitor
How data warehouse projects differ from traditional IS projects
Project Resources and Roles
Sponsor
Project manager
Business analyst
DBA
Dimensional data modeler
Staffing from Within Your Organization
Leverage existing skill sets
Don't overextend in-house resources
Be willing to engage consultants if necessary
Lessons from the Trenches
Manage expectations
Manage scope creep
Don't wait to ask for help

Chapter 3: Gathering Requirements
Getting Started With Requirements Gathering
The Mechanics of Interviewing
True interviews
Facilitated sessions
A day in the life
Who Should You Be Talking To?
The project sponsor
Data owners
End users and analysts
IT people
What You Should Learn
Group/individual responsibilities
Relationship to other parts of the organization
Business objectives
Obstacles interfering with the objectives
How the current process works
Critical success factors and key performance indicators
Data sources
Warehouse expectations
Understanding Who Your Customer Really Is
How to Ask Questions
Ask early and often
Ask open-ended questions
Repeat answers
Take nothing for granted
A Word about Group Dynamics
Who talks
Who doesn't talk
Aggressive personalities
Tips on How to Stay on Track
The Logical Process Reviewed
How Do You Know When You're Done?
The Importance of Vocabulary
Lessons from the Trenches
Listen
Manage expectations
Document everything
Render a logical model as interviews are conducted
Introduction to the Sample Database
The business problem
Customer analysis and profiling
Line type analysis
Calling patterns
Billing analysis

Part II: Modeling

Chapter 4: Logical Modeling
A Schema Design Overview
A 10,000-foot view
Defining terms
The correct way to approach dimensional logical modeling
The Basics of Logical Modeling
Dimensional modeling basics
Transaction databases
DSS databases
A word about other schema types
Dimensional modeling myths
Fact and dimension tables
Digging deeper: fact tables
The PERIOD dimension
Digging deeper: dimension tables
The Mechanics of Fact and Dimension Tables
Some definitions and observations
Logical Model Components
Fact Table How To
Identify the process
Identify the grain
Identify the dimensions
Identify the facts
Dimension Table How-to
Attribute definition
Advanced Dimensional Issues
The level column
The concept of roles
Lessons from the Trenches
Strive for (relative) simplicity
Do not duplicate the OLTP model
Carefully consider slowly changing issues
Let the model evolve
Don't denormalize unnecessarily
Sample Project: Logical Model
Customer
Telephone

Chapter 5: Physical Modeling
Turning a Logical Model into a Physical Model
The physical model
File-naming and location conventions
A backup and recovery strategy
An indexing strategy
Data object sizing
Segmentation
Data source to target mapping
Query Tool Considerations
Developing a Load Strategy
Creating the Data Definition Language
Developing an Aggregation Strategy
Key Definitions
Lessons from the Trenches
Be reasonable
Denormalize carefully
Consider natural/generated key issues carefully
A Case Study Schema Review

Part III: Planning the Implementation

Chapter 6: Red Brick Indexing
Why Are Indexes Important?
Types of Red Brick Indexes
B-tree indexes
TARGETindex
STARindexes
Uniqueness and Referential Integrity Checking
Aggregate Query Index Optimizations
Maintenance versus Performance
Mechanics and DDL
System catalog information
Configuration file information
Indexing Strategies
Best practices for fact tables
Best practices for dimension tables
Sample database example
Lessons from the Trenches
Index appropriately
Follow best practices
Use hybrid TARGETindexes

Chapter 7: Aggregates
Why Are Aggregates Important?
The Vista Solution
Implementing Vista
Hierarchies
Validity of precomputed views
Key-based rollups
Rewritten Queries
Cost-based analysis
Flexible rewrites
Advisor Analysis
Utilization analysis
Candidate view analysis
Automatic Aggregate Maintenance
How it works
Disadvantages
Key maintenance points
Mechanics and DDL
Administrator wizards
SQL statements
System tables
Sample Database Example
The AGG_CDR example
The WEEKLY_CDR example
Lessons from the Trenches
Don't be in a rush to aggregate
Define as many hierarchies as possible
General guidelines

Chapter 8: Sizing the Database
The Importance of Sizing
Estimation Techniques
Using Dbsize
Using Red Brick Decision Server Administrator
Other Space Requirements
INDEX_TEMPSPACE
QUERY_TEMPSPACE
Data staging space
System
Lessons from the Trenches
Be accurate
Build in a "pad"
Don't be intimidated
Sample Database Sizing

Chapter 9: Segmentation
The Basics of Segmented Storage
Segmentation and data management
Query considerations
The PERIOD Table Revisited
Time-cyclic data
Fiscal calendar considerations
Considering a segmenting period table
Choosing a Segmenting Time Period
Business requirements
Historical duration
Planning horizon
Data-loading criteria
Query Considerations
System configuration
Coordinated Segmentation Plans
The benefits
The costs
Implementing a Segmentation Plan
PSUs and Their Function
PSU guidelines
Determining the number and size of segments
Guard segments
Placing the segments on disk
Creating segments
Basic Segmentation Guidelines and Considerations
Lessons from the Trenches
Be reasonable
Don't micromanage your disk space
Change the question
Sample Database Segmentation
BILLING_CDR
CUSTOMER
TELEPHONE
The rest of the tables

Part IV: Building and Maintaining the Data Warehouse

Chapter 10: Installing and Configuring
Red Brick PreInstallation
Hardware Environments
Determining an appropriate environment
General system parameters
load_processes
max_active_databases
max_active_revision
max_nuniq_idxs
max_query_tasks
max_parallel_tasks
max_PSUs
max_rbw_users
num_cpus
size_of_version_log
Kernel Parameter Calculations
Selected Configuration Parameters and Initial Settings
General parameters
TUNE section
Overriding configuration parameters
Creating a Database Instance
Creating a database directory
Initializing the directory
Verifying the database
Overview of Users, Roles, and Connectivity
Adding user accounts
Database object privileges
Lessons from the Trenches
Carefully calculate configuration and kernel parameters
Don't overlook user security

Chapter 11: Loading the Data
Data: The Final Frontier
Availability
Cleanliness
Data preparation
Suitability
Data validation
How to Load Red Brick Databases
The Table Management Utility
TMU control files
Load modes
Load types
Parallel loading
Versioned loads
Typical Load Rates
Setting expectations
Your mileage may vary
Query response times
Discussion of Sample Data and Load Scripts
Lessons from the Trenches
Make sure segmentation ranges are correct
Sort data if possible
TMU is your friend

Chapter 12: Red Brick Query Processing
How Red Brick Processes Queries
Subselects
SELECT clause subselects
FROM clause subselects
WHERE clause subselects
Case statements
Other optimizations
Red Brick Join Algorithms
STARjoin
TARGETjoin
Nested loops join (B11M)
HASH join
Naive 1-to-1 match (cross join)
Scanning in Red Brick
Smartscan
Tablescans
Index scans
Understanding the EXPLAIN Function
The operator model
The EXPLAIN command
Reading EXPLAIN output
Reading TARGETjoin EXPLAIN output
SET STATS Output
Improving Query Performance via SQL/EXPLAIN
UNION versus OR
FROM clause subquery versus correlated subquery
Avoid table scans
Change DISTINCT to GROUP BY
Running Queries
Sample Database Queries
Lessons from the Trenches
Know your SQL
Learn to read EXPLAIN output
Strive to understand the query processing engine

Chapter 13: Parallel Queries
Query Engine: How Parallel Queries Are Processed
Parallel Operators
A word about reasonable values
TUNE FILE_GROUP
TUNE GROUP
TUNE TOTALQUERYPROCS
TUNE QUERYPROCS
TUNE ROWS_PER_SCAN_TASK
TUNE ROWS_PER_FETCH_TASK, ROWS_PER_JOIN_TASK
TUNE FORCE_SCAN_TASKS
TUNE FORCE_FETCH_TASKS, TUNE FORCE_JOIN_TASKS
TUNE FORCE_HASHJOIN_TASKS
Recommended NT configuration
Factors That Limit Parallelism
File groups
System load and resources
Enabling Parallel Query Processing
Enabling Parallel Aggregation
Evaluating Parallel Performance
Parallel everything isn't the answer
Considerations for multiuser environments
Lessons from the Trenches
Don't over-parallelize
Start conservatively
Parallelism won't fix everything

Chapter 14: Maintaining a Red Brick Decision Server Warehouse
New Dimensions
Adding new dimensions
Connecting to existing fact tables
Altering Tables and Indexes
Adding columns
Changing column data type
Changing MAXROWS and MAXSEGMENTS
Managing Segments
Segments and Time-Cyclic Data
Roll-Off
Adding and extending PSUs and segments
Recovering a damaged segment
Validating Tables and Indexes
IXVALID
TBLCHK
Copying and Moving a Database
Lessons from the Trenches
A day of planning is worth two weeks of maintenance
Schedule routine maintenance downtime
Don't rush to a solution
Maintaining the Sample Database

Appendix A:What's on the CD-ROM
Appendix B: Sample Database
Index