The MicrosoftВ Data Warehouse Toolkit: With SQL ServerВ 2005 and the MicrosoftВ Business Intelligence Toolset

Introduction

Figure 1: The Business Dimensional Lifecycle

Chapter 1: Defining Business Requirements

Figure 1.1: The Business Requirements Definition step of the Business Dimensional Lifecycle

Figure 1.2: Detail of sub-activities between initial scope and project requirements

Figure 1.3: The enterprise requirements definition process flow chart

Figure 1.4: Example enterprise bus matrix for a retail company

Figure 1.5: Example prioritization grid

Figure 1.6: The Adventure Works Cycles organization chart

Figure 1.7: Analytic themes and supporting business processes from the interview summary

Figure 1.8: The Adventure Works Cycles bus matrix

Figure 1.9: The Adventure Works Cycles prioritization grid

Chapter 2: Designing the Business Process Dimensional Model

Figure 2.1: The dimensional modeling step in the Lifecycle context

Figure 2.2: A basic dimensional model for retail grocery sales

Figure 2.3: Segment-level flight activity dimensional model

Figure 2.4: Adventure Works Cycles high-level enterprise bus matrix

Figure 2.5: An example Sales Rep Group bridge table

Figure 2.6: An example many-to-many bridge table between dimensions

Figure 2.7: A Subcategory table extracted from the Adventure Works Cycles Product dimension table

Figure 2.8: An example junk dimension for the Retail Grocery Sales dimensional model

Figure 2.9: The dimensional modeling process flow diagram

Figure 2.10: Example dimensional model development spreadsheet

Figure 2.11: A simple data profile report for the Adventure Works OLTP Product table

Figure 2.12: Initial Adventure Works Cycles High-Level Orders dimensional model

Figure 2.15: Adventure Works Cycles Orders dimensional model issues list

Figure 2.13: Promotion dimension portion of the Adventure Works Cycles initial Orders attribute list

Figure 2.14: The high-level dimensional model from the initial design session

Chapter 3: The Toolset

Figure 3.1: Business Dimensional Lifecycle and Microsoft technologies

Figure 3.2: Microsoft DW/BI system architecture

Figure 3.3: SQL Server Management Studio

Figure 3.4: Creating a new project in BI Studio

Figure 3.5: Basic layout of the BI Studio windows and panes

Chapter 4: Setup and Physical Design

Figure 4.1: The Business Dimensional Lifecycle

Figure 4.2: Factors influencing DW/BI system hardware requirements

Figure 4.3: All-in-one business intelligence system

Figure 4.4: SQL Server data store and separate reporting server

Figure 4.5: SQL Server data store and reporting and analysis server

Figure 4.6: A common development team configuration

Figure 4.7: Simple dimensional diagram illustrating key constraints

Chapter 5: Designing the ETL System

Figure 5.1: The Business Dimensional Lifecycle

Figure 5.2: Editing the Master_Dims package in BI Studio

Figure 5.3: Viewing a Data Flow task

Figure 5.4: High-level map for Customer (both individual and reseller)

Chapter 6: Developing the ETL System

Figure 6.1: Logic flow diagram for populating DimPromotions

Figure 6.2: Promotion package OLE DB source

Figure 6.3: OLE DB Source Editor column configuration for DimPromotions

Figure 6.4: Derived Column transform

Figure 6.5: Ready to run the Promotions Package for the first time

Figure 6.6: Parameterized source system query

Figure 6.7: Logic flow for handling dimension updates

Figure 6.8: Results of running the Slowly Changing Dimension Wizard

Figure 6.9: Data Flow task to extract and check data

Figure 6.10: Computing reasonableness checks

Figure 6.11: Calculating row counts with the Aggregate transform

Figure 6.12: Control Flow for fact extract, clean, and check

Figure 6.13: Precedence Constraint Editor

Figure 6.14: Data Flow for allocating tax and freight

Figure 6.15: Surrogate key pipeline, Type 1 dimensions

Figure 6.16: Data model for basic auditing system

Figure 6.17: Set up a configuration from a parent package

Chapter 7: Designing the Analysis Services OLAP Database

Figure 7.1: Business Dimensional LifecycleThe Data Track

Figure 7.2: Deploy development projects to a different server

Figure 7.3: DSV for the MDWT_AdventureWorksDW database

Figure 7.4: A Data Source View relationship between fact and dimension

Figure 7.5: The Dimension Wizard: Select Build Method

Figure 7.6: The Dimension Designer

Figure 7.7: Browsing dimension data

Figure 7.8: The Cube Designer

Figure 7.9: Dimension usage

Figure 7.10: Editing dimension usage

Figure 7.11: The Calculations tab

Figure 7.12: The Aggregation Design Wizard

Figure 7.13: The Partitions tab and the partition source definition

Chapter 8: Business Intelligence Applications

Figure 8.1: The BI application specification and development steps in the Lifecycle

Figure 8.2: Example standard template

Figure 8.3: Example Candidate Report list

Figure 8.4: Example Product Topline Performance Report mock-up

Figure 8.5: Example user interaction list

Chapter 9: Building the BI Application in Reporting Services

Figure 9.1: The BI application development step

Figure 9.2: The Reporting Services architecture

Figure 9.3: Report Manager home page

Figure 9.4: Reports in the Sales by Product directory

Figure 9.5: The Product Subcategory Sales Trend report

Figure 9.6: Example Adventure Works Cycles layout template in the Report Designer

Figure 9.7: Sales Rep Performance Ranking report mock-up

Figure 9.8: The completed data tab for the Sales Rep Performance Ranking report

Figure 9.9: Report Parameters dialog box

Figure 9.10: Final report layout for the Sales Rep Performance Ranking report

Figure 9.11: The Adventure Works Cycles BI portal home page

Chapter 10: Incorporating Data Mining

Figure 10.1: The SQL Server data mining architecture

Figure 10.7: Cluster diagram for the city economic data

Figure 10.2: A simple decision tree to predict relationship success

Figure 10.3: The data mining process

Figure 10.4: An example lift chart comparing two models designed to predict Income Range

Figure 10.5: Example classification matrices for the Income Range models

Figure 10.6: A simple spreadsheet for tracking data mining models

Figure 10.8: A graphical view of the city clusters

Figure 10.9: An Integration Services package to assign clusters to new cities

Figure 10.10: An Integration Services data flow to create test and training datasets

Figure 10.11: The ProductRecs datasets, presented as a data source view

Figure 10.12: The nested table portion of the Specify the Training Data window

Figure 10.13: The Mountain-200 decision tree

Figure 10.14: The default Dependency Network drawing for the ProductRecs1 Decision Trees model

Figure 10.15: The Dependency Network with predictive variables dragged to the upper-right corner

Figure 10.16: The Dependency Network zoomed in on the predictive variables

Figure 10.17: The initial decision tree for Womens Mountain Shorts

Figure 10.18: The expanded decision tree for Womens Mountain Shorts after reducing the number of input variables

Figure 10.19: Sample DMX for a data mining query to get product recommendations based on an individuals demographics

Chapter 12: Security

Figure 12.1: Analysis Services database administration role

Figure 12.2: Assigning users and groups to roles

Figure 12.3: Test role definitions by impersonating credentials

Figure 12.4: Defining basic dimension security

Figure 12.5: Using MDX expressions to define dimension security

Figure 12.6: Defining cell -level security

Figure 12.7: Defining the BIPublic role

Chapter 13: Metadata Plan

Figure 13.1: Microsofts Reporting Services process metadata reporting schema

Figure 13.2: Analysis Services metadata in the Report Builder model selection

Figure 13.3: Analysis Services display folders in the Report Builder designer

Figure 13.4: The sample Analysis Management Objects browser

Figure 13.5: Exploring the relational databases extended properties

Figure 13.6: An example Business Metadata schema

Figure 13.7: Example databases from the Business Metadata schema

Figure 13.8: Subject areas in the MDWT_AdventureWorksDW database

Figure 13.9: Objects in the Orders subject area

Figure 13.10: Attributes of the Promotion dimension table

Chapter 14: Deployment

Figure 14.1: The Deployment step in the Business Development Lifecycle

Figure 14.2: Example test matrix information

Figure 14.3: Example test run log

Chapter 15: Operations and Maintenance

Figure 15.1: The Business Dimensional Lifecycle

Figure 15.2: SQL Agent New Job Step

Figure 15.3: Configure Integration Services logging

Figure 15.4: Set up a System Monitor alert to warn of low disk space

Figure 15.5: Set up alert to continue running after reboot or alert triggers

Chapter 16: Managing Growth

Figure 16.1: The Growth step in the Lifecycle

Figure 16.2: An example data warehouse usage report

Figure 16.3: An example departmental and user level usage report

Chapter 17: Real-Time Business Intelligence

Figure 17.1: Setting up the DataReader destination transform

Figure 17.2: Setting up the reports data source

Figure 17.3: Setting up the reports query text

Figure 17.4: With proactive caching, the query is directed to the appropriate data store.

Figure 17.5: Partition Properties dialog box

Figure 17.6: The Proactive Caching page of the Partition Properties dialog box

Figure 17.7: Storage Options dialog box

Figure 17.8: Defining polling parameters for incremental processing

Chapter 18: Present Imperatives and Future Outlook

Figure 18.1: The four phases of the Business Dimensional Lifecycle