Conclusion

Conclusion

As fun as it is to criticize Microsoft, its DW/BI toolsetnotably SQL Server 2005contains the features necessary to build a complete DW/BI system. The tools are relatively easy to use, and will scale from small operations like the hypothetical Adventure Works Cycles to large enterprises with significant data volumes . The smaller implementations can rely heavily on wizards, and not worry too much about all the technical details. Large systems will use the wizards to get started, but will need to dig far deeper into the products.

Microsoft is delivering the technology you need, but you are the ones who will put that technology to use. If you can maintain your focus on the needs of the business users and on adding business value, you should be able to build a great DW/BI system.

Good luck!

List of Figures

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