John Wiley & Sons, 2011

The Microsoft Data Warehouse Toolkit, 2nd Edition
Buy on Amazon

Joy Mundy and Warren Thornthwaite co-authored this guide to building a successful business intelligence system and its underlying data warehouse databases using Microsoft SQL Server 2008 R2.

They provide invaluable advice about designing, developing, deploying, and operating your Kimball Method data warehouse/business intelligence system on the Microsoft BI platform.

Information regarding the first edition of this book based on the SQL Server 2005 platform is available.

Tools and Utilities

Chapter 1—Defining Business Requirements

This zip file contains a more detailed look at business requirements for a company like Adventure Works. The four documents in the zip file are:

  1. Adventure works Requirements Example.docx—Includes some data exploration, an interview summary with the VP of Sales, a preliminary bus matrix, and an idea of how the prioritization process might play out.
  2. A PowerPivot for Excel database/workbook that was used to create the data exploration figures in the Requirements Example document.
  3. Several abridged interview summaries to highlight more analytic opportunities.
  4. An Analytic Themes list that group and summarize the various opportunities.

Chapter 2—Designing the Business Process Dimensional Model

Dimensional modeling spreadsheet—This Excel spreadsheet (zipped) contains the annotated data model for the MDWT_2008R2 database used throughout the book. The zip file includes two spreadsheets: one with the MDWT_2008R2 model, and one that is blank so you can use it to create your own logical and physical data models. The spreadsheet contains a macro that generates a basic database CREATE script you can use to instantiate your model in SQL Server.

Data profiling reports—This Reporting Services project is a simple example of a data profiling tool. It is a stand-alone report set in a BIDS project. Users must have permission to read the tables in the selected database.

Naming conventions—Many universities make their internal information available on the internet. You can search for current examples like this one from Indiana University. Stanford University was kind enough to give us permission to make a draft set of their data model and DBA naming standards available as well.

Commute distance Type 2 example—This is a more detailed example of the differences between Type 1 and Type 2 change tracking and the impact this choice has on analytics.

Example data model Issues List—This spreadsheet can be used to track tasks and issues during the development of the dimensional model.

Chapter 5—Relational Database Physical Design

Table partitioning script—This SQL script illustrates how to partition a table. It also shows how to create a pseudo-partition table, populate it with data (presumably using fast load techniques), and switch it into the partitioned table.

Chapter 6—Master Data Services

MDS Model for a simple customer dimension—This is the Customer model shown in the book.

Chapter 7—Design and Develop the ETL System

Sample ETL Specification Outline—This Word document outlines the major components of what we’d like to see in an ETL specification.

Sample SSIS packages—This zip file contains all of the sample Integration Services packages referred to in the chapter.

Date dimension—This spreadsheet contains a row for each day from 2000 to 2020 along with the DDL needed to create the target table.  You may want to create a simple SSIS package to load this data. You will likely want to add columns that are relevant to your organization.

Chapter 8— Design and Develop the Core Analysis Services OLAP Database

SSAS Package—This Analysis Services package defines the simple MDWT_2008R2 Analysis Services cube. You need to have the fully populated MDWT_2008R2 relational database built in Chapter 7 before you can process the cube. If you didn’t create this already, you can download it here.

MDWT_2008R2 Relational Database backup—This is a zipped backup of the relational database built in Chapter 7. You need to unzip it before you restore it to your server.

Chapter 10—Building the BI App in Reporting Services

Example SSRS project—This is the Sales Rep Performance Report project used in the Reporting Services example in the chapter.  Its data source is the Adventure Works 2008 R2 Analysis Services cube, so you need to download that from CodePlex.com and install it first. MDX and SQL versions of the queries are also included in the zip file.

Instructions for creating an SSIS to RS Link—In one of the sidebars we describe a way to have an ETL package automatically start a set of reports as soon as the ETL job is finished. This zip file has more detailed instructions and an example SSIS/RS solution. It is not for the faint at heart. You need to be competent with SSIS, RS, and the relational database to make this work.

Chapter 11—PowerPivot and Excel

PowerPivot example files—These are the files required to follow the simple customer and population example in the chapter.

Date dimension—This is the same spreadsheet referred to in the Chapter 7 downloads.

USA 2000 Census Data by Zip—This data, from the US Census Bureau, is a pipe-delimited file with population, households, land and water area, and latitude and longitude for 30,000+ zip codes.  This is just a sample of the useful data you can find out on the internet.

Chapter 12—The BI Portal and SharePoint

SharePoint install guide—This zip file includes a 23 page guide to installing the SharePoint test system described in the chapter. It also includes a test Reporting Services report and PowerPivot file, and a site template that includes many of the BI Portal functions in SharePoint.

Chapter 14—Designing and Implementing Security

Sample data sensitivity document—A simple example of how you might document the elements in a dimensional model that need to have restricted access.

Chapter 15—Metadata Plan

Metadata database create and load scripts—These scripts create a metadata database and populate it based on the extended property values generated from the modeling spreadsheet described in Chapter 2. We’ve also included the modeling spreadsheet that generated the database create script in case you want to modify the metadata data model.

Reporting package—This is a set of Reporting Services reports that run against the metadata tables described in Chapter 15. You can create these metadata tables using the scripts in the previous bullet.

Additional Examples

Slowly Changing Dimension Management with SQL 2008
MERGE command

There are two scripts in this zip file. The first creates a set of tables and populates them with sample data. The second demonstrates how to use the new MERGE command in SQL Server 2008 to manage Type 1 and Type 2 changes in a dimension table. These are explained in more detail in Kimball University Design Tip #107.

References

The page is a good starting point for a product overview and links to several useful information sources

The MSDN SQL Server Tech Center is more technical, with links to pages by tool and task

The MSDN Business Intelligence page is an obvious choice. It contains some of the same links as the Tech Center page, with additional BI-specific links.

Lots of good information on building and using the SQL Server relational data warehouse.

The Integration Services group has put a lot of effort into building useful add-ons and white papers

This page is a collection of SQL Server best practices from a group of smart folks at Microsoft on TechNet. Very helpful.