The Thinking Person's Guide to Data Warehouse Design

Talk Type: 
45 Minute Talk
Technical Level: 

Many times, data warehouses are thrown up without much thought going into the logical/ physical design. But, high performance data warehouses and analytic databases don’t happen by accident; they come from solid up-front design work. This session describes the procedures to go from an empty data model to a physical data warehouse that can stand up to terabytes of data and intense user activity.

Topics include:

- Building the logical design
-Moving from transactional to analytic schemas
-Stars, snowflakes, and more
-Vertical and horizontal partitioning model trade-off’s
-Transitioning to the physical design
-How to decide on the right storage engine(s)
-When to/not to use row and column-oriented storage engines
-How to decide on a partitioning strategy
-The love-hate relationship with indexes
-Hardware architectures: SMP, MPP, or both?
-Use case benchmarks for the above
-Monitoring and tuning the design
-Monitoring checklist (database, OS, and storage)
-SQL diagnostic troubleshooting best practices
-When to quit and start over

PG West Thinking Persons Guide to Data Warehouse Design.pptx2.38 MB