The Microsoft Data Warehouse Toolkit, 2nd Edition: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset 

John Wiley & Sons, 2011

Description

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.

Buy From Amazon

 

 

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.
DOWNLOAD

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. DOWNLOAD
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. DOWNLOAD
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. DOWNLOAD
Example data model Issues List—This spreadsheet can be used to track tasks and issues during the development of the dimensional model. DOWNLOAD

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. DOWNLOAD

Chapter 6—Master Data Services

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

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. DOWNLOAD
Sample SSIS packages—This zip file contains all of the sample Integration Services packages referred to in the chapter. DOWNLOAD
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. DOWNLOAD

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. DOWNLOAD
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. DOWNLOAD

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. DOWNLOAD
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. DOWNLOAD

Chapter 11—PowerPivot and Excel

PowerPivot example files—These are the files required to follow the simple customer and population example in the chapter. DOWNLOAD
Date dimension—This is the same spreadsheet referred to in the Chapter 7 downloads. DOWNLOAD
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. DOWNLOAD

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. DOWNLOAD

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. DOWNLOAD

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. DOWNLOAD
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. DOWNLOAD

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.
DOWNLOAD

 

References

Microsoft Sites Description
Microsoft BI Home The page is a good starting point for a product overview and links to several useful information sources
MSDN SQL Server
Tech Center
The MSDN SQL Server Tech Center is more technical, with links to pages by tool and task
MSDN Business Intelligence 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.
SQL Server Data Warehousing Lots of good information on building and using the SQL Server relational data warehouse.
Technet Integration Services The Integration Services group has put a lot of effort into building useful add-ons and white papers
TechNet Best Practices This page is a collection of SQL Server best practices from a group of smart folks at Microsoft on TechNet. Very helpful.