Tools, Samples, Scripts
and other useful things
NOTE: If your browser opens a file rather than downloading
it, try right-clicking on the link and selecting Save Target As...
Chapter 1 - Defining Business Requirements
-
Additional Interview
Summaries - These are abbreviated summaries that will give you an idea
of the kinds of analytic themes you might hear from different parts of the
organization.
(Updated 17Oct2006)
-
Adventure Works analytic themes
list - This list includes all the themes identified in the interview
summaries listed above and in the book. Try and create your own list
first, then look at this one to see how you did.
(Updated 17Oct2006)
Chapter 2 - Designing the Business Process Dimensional Model
-
Dimensional modeling spreadsheet
- This Excel spreadsheet (zipped) contains the annotated data model for the
MDWT_AdventureWorksDW database used throughout the book. It also contains a
macro for generating a basic database CREATE script. The zip file also
includes a blank version of the spreadsheet so you can use it to create your
own logical and physical data models.
(Updated 16June2008)
- Data profiling reports - This
Reporting Services project is a simple example of a data profiling tool.
Version 2 is a stand-alone report set and includes a few bug fixes. We
are keeping the
original version available, but requires the installation of two stored
procedures in the target database.
(Updated 25April2008)
- 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.
(Added 18Apr2006)
Chapter 4 - Setup and 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 swap it into the partitioned table.
-
Dimensional
modeling spreadsheet - This is the same Excel spreadsheet we
described earlier, in Chapter 2.(Updated
16June2008)
-
Database create script - This SQL script is exactly the script
generated by the Excel spreadsheet. If for some reason you can't run
the Excel macro, here's what it generates. (Added
08Jun2006)
Chapter 5 - Designing the ETL System
- High
level ETL system design diagram - This Visio diagram illustrates
the ETL process for the MDWT_AdventureWorksDW database. We've also copied the content as .gif
files, in case you don't have Visio installed.
- The Dimension Master package that we
described in Chapter 5 is available under the Chapter 6 content
described below. It's part of the Integration Services solution for
historical loading.
Chapter 6 - Developing the ETL System
- Integration
Services solution for historical loading of the MDWT_AdventureWorksDW
database. For best results, unzip to the c:\MDWT_Projects folder. You can
find a ReadMe document here.
(Updated 09Jun2006)
- Sample package for
custom SCD handling. This package computes checksums on the SCD1 and
SCD2 attributes, and uses those checksums in a Script transform to perform
the SCD handling. Please install the Checksum SSIS transform, available from
www.sqlis.com, before you look at this
package.
-
Breakpoint in ScriptTask package shows the Script Task. We show a
breakpoint inside the script, and also how to use and reference package
variables inside the script. The script itself is very simple.
Chapter 13 - 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. (Updated
16Aug2006)
-
Reporting package --
This is a set of Reporting Services reports that run against the metadata
tables described in Chapter 13. You can create these metadata tables
using the scripts in the previous bullet. (Updated
16Aug2006)
Examples not described in the book
- Integration
Services solution for historical loading of the AW_Orders
database. This solution was described in talks that Joy gave at TechEd2006
and on a webcast on 7/11/2006. For best results, unzip to the c:\MDWT_Projects folder. You can
find a ReadMe document
here. You
can view the webcast at Microsoft's
TechNet. (Updated 15Jul2006)
-
Data Mining solution
to create a simple cross-sell product recommendation model. This
solution was described in webcast that Warren gave on 7/17/2006. You can
find a ReadMe document here. You
can view the webcast at Microsoft's
TechNet. (Updated
17Jul2006)
- Date Dimension Resources
- Date table auto-gen
- This
Word 2003 document is a set of instructions that guide you through using
Analysis Services to auto-generate a fully populated relational Date
dimension. OK, "auto" might be a bit generous, but it is
easier than creating the table from scratch.
(Added
05Sep2007)
- This zipped spreadsheet from the original Data
Warehouse Lifecycle Toolkit book generates a set of SQL insert
statements: Date_Dimension
Spreadsheet
(Added
26Nov2007)
- Intelligent reader, Don McMunn, maintains a date dimension kit. Check it out
at:
http://www.ipcdesigns.com/dim_date/index.html
|