PASS Community Summit 2009: Post-Conference Sessions (BI Platform Architecture, Development and Administration)
Return to Session Listings
Jump-Start to Data Warehouse Data Modeling and Architecture
Erik Veerman (Solid Quality Mentors)
A successful data warehouse project begins with the right design and planning. Conversely, poor design leads to bad performance, limited scalability, and even user rejection. This seminar covers the design principles of data warehouse solutions focusing on dimensional modeling techniques and then the planning steps involved in the solution architecture. By laying the foundation to dimensional modeling, this seminar begins with an overview of dimensional design theory and the structures involved in data marts and data warehouses. We will review the core aspects of dimension table surrogate keys, attributes, and hierarchies as well as advanced hierarchy types and the best practices in tracking changes historically. In addition, fact tables hold the core data that you are analyzing and are a critical component to design right the first time. We will dive into fact table types and consider the various kinds of columns, measures, aggregation, and also volume considerations. Most importantly, we will discuss how to choose the right dimension and fact types based on business requirements. Planning your data warehouse infrastructure and architecture is another up-front step critical to a successful solution. This includes estimating user load and volume, selecting the right hardware platform, and planning a partitioning and indexing strategy. Altogether, the dimensional modeling best practices and architecture planning you will learn in this seminar will set the stage for a scalable solution that adds value to your business users.
Watch 24 Hours of PASS Session Recording
Q&A with Erik
Q: If you could share one piece of advice with someone new to data warehousing, what would it be?
A: Easy—avoid wide tables (lots of columns) that mix a lot of text and numeric data type columns together. I see this so often at my clients, where someone has a “data warehouse,” but all it really is is a bunch of inefficiently designed flattened tables. The right answer is a dimensional model, including dimension and fact tables. It’s tremendously more efficient for both storage volume and query performance.
Read the rest of Erik's conversation with PASS here.
About the Speaker
Erik Veerman (SQL Server MVP) is a Mentor for Solid Quality Mentors focusing on training, mentoring and architecting solutions on the SQL Server BI platform. His industry recognition includes Microsoft's Worldwide BI Solution of the Year and SQL Server Magazine's Innovator Cup winner. Erik is the lead author for the Business Intelligence Training Kits (MS Press 70-445, 70-448) and a co-author of the WROX Integration Services titles (Professional SSIS & Expert SSIS).
Erik has designed dozens of BI solutions across a broad business spectrum-telecommunications, marketing, retail, commercial real estate, finance, supply chain, and information technology. His experience with high volume multi-terabyte environments and SQL Server 64-bit has enabled clients to scale their Microsoft-based BI solutions for optimal potential. As an expert in OLAP design, ETL processing, and dimensional modeling, Erik is a presenter, author and instructor. He led the ETL architecture and design for the first production implementation of Integration Services (SSIS) and helped drive the ETL standards and best practices for SSIS on Microsoft's SQL Server 2005 reference initiative, Project REAL.
As a resident of Atlanta, Erik often speaks at the Atlanta PASS Chapter user group. Furthermore, he has presented at the PASS summit for the last several years as well as the SQL Connections conference, and is often a speaker for the SQL Server Magazine road-shows or Microsoft launch events.
See the other sessions Erik is presenting at PASS Community Summit 2009 here.
Register now for PASS Summit 2009!