Microsoft Data Warehouse/Business Intelligence in Depth (PDF version)
Why Attend
This class teaches you how to design and build a successful data warehouse / business intelligence (DW/BI) system on the Microsoft SQL Server platform. Using the Microsoft Data Warehouse Toolkit, Second Edition with SQL Server 2008 R2 as its foundation, it translates proven Kimball Lifecycle methods and patterns to the Microsoft SQL Server 2005, 2008 R2, and SQL Server 2012 platforms.
Through a combination of lectures and demonstrations, this course delivers a detailed technical introduction to designing and building a Microsoft-based DW/BI system that meets the needs of your business users. It provides an understanding of what you need to do to succeed, plus specific tools and techniques.
Our goal is to teach you the hard stuff: not which button to push, but how to design and build a successful Microsoft DW/BI system. The focus of the course is architectural. The pace of the course permits only demos by the instructor; do not expect hands-on tutorials during class time.
You should be familiar (at least at the tutorial level) with the SQL Server product family, including BI Studio, SQL Manager Studio, SSIS, SSAS, Reporting Services, and the relational database. You should also have a basic understanding of dimensional modeling; the class only covers dimensional modeling topics at a high level. These principles are summarized in the following articles available at www.kimballgroup.com.
- The 10 Essential Rules of Dimensional Modeling (May 2009)
- Essential Steps for the Integrated Enterprise Data Warehouse, Part 1 (Mar 2008)
- Essential Steps for the Integrated Enterprise Data Warehouse, Part 2 (Apr 2008)
- Facts and Fables about Dimensional Modeling (Jan 2008)
Who Should Attend
This course is designed for all major roles on a Microsoft-centric DW/BI project team, including data warehouse team managers, system architects, ETL system architects and developers, data warehouse operational staff, and BI application designers and developers.
Instructors
Warren Thornthwaite and Joy Mundy, co-authors of the best-selling Microsoft Data Warehouse Toolkit, Second Edition
Course Overview
Day 1
- Introduction to the Kimball Lifecycle Approach
- Project Planning and Management
- Defining Business Requirements
- Designing the Business Process Dimensional Model
- Microsoft DW/ BI System Architecture
- System Setup
Day 2
- SQL Server Relational DW Physical Design
- Business Intelligence Applications
- Delivering BI Applications with Reporting Services, Office and SharePoint
- Adding Business Value with Data Mining and Predictive Analytics
- Metadata
- ETL System Design
- Data Quality Services and Master Data Management
Day 3
- Introduction to Integration Services
- Developing the ETL System
- Introduction to Analysis Services
- Developing the Analysis Services Multidimensional Database
Day 4
- Developing the Analysis Services Tabular Database
- Real Time Business Intelligence
- Securing the Microsoft DW/BI System
- Deployment
- Operations, Maintenance, and Growth
Course Details
Day 1
Introduction to the Kimball Lifecycle
- Roadmap for creating the data warehouse/business intelligence system
Project Planning and Management
- Assess readiness
- Define, plan, and manage the project
Defining Business Requirements
- Gathering business requirements
- Requirements prioritization session
- Exercise: Translating requirements into the DW Bus Matrix
Designing the Business Process Dimensional Model
- Basic dimensional modeling concepts
- Conformed dimensions
- Slowly changing dimensions
- Additional concepts in dimensional modeling
- Hierarchies and snowflaking
- Degenerate and junk dimensions
- Many to many dimensions
- Dimensional modeling process and design spreadsheet
Microsoft Data Warehouse/Business Intelligence System Architecture
- Common components of DW/BI system architecture
- Mapping MS components to the general DW/BI architecture
- Process: How to create an architecture plan
- Exercise/discussion: Develop/present summary architecture for attendees’ systems
System Setup
- System configuration: location of components, memory, and storage
- System sizing factors
- Installing SQL Server and issues to consider during installation and configuration
Day 2
SQL Server Relational Data Warehouse, Physical Design
- Column names, data types, keys, and compression
- Initial index plan, including columnstore indexes
- Setting up the Resource Governor
- Fact table partitioning
Business Intelligence Applications
- Basic BI application concepts
- Reporting system design process
- Standard report template
- Report specifications
- Reporting system navigation design –BI Portal
- BI application development process
Delivering BI Applications with Reporting Services
- Reporting Services overview
- Extended demo: Designing and deploying a report
- Sourcing from the relational database
- Sourcing from the Analysis Services database
- Report Manager and the SharePoint BI portal
- Reporting Services metadata
- Other BI options: Report Builder, PowerPivot, Power View, PerformancePoint
Adding Business Value with Data Mining and Predictive Analytics
- Data mining overview
- SQL Server data mining architecture
- Data mining process
- Extended demo: Creating a data mining model
- Developing the input data set
- Selecting algorithms
- Data mining designer
- Using the data mining add-ins for Microsoft Office
- Validating the model and moving to production
- Data mining metadata and maintenance
The Metadata Morass
- Defining and managing metadata
- Metadata in SQL Server
- Simple business metadata data model
ETL System Design
- Develop a high-level map
- Exercise: High-level map for Adventure Works customer dimension
- Develop standard design patterns for common tasks
- ETL system specification
Data Quality Services and Master Data Management
- Defining master data management (MDM)
- Introducing Microsoft Data Quality Services (DQS)
- Introducing Microsoft Master Data Services (MDS)
- Getting started with DQS and MDS
Day 3
Introduction to Integration Services
- Introduction to Integration Services and development environment
- Control Flow tasks and Data Flow components
- Variables and Expressions
- Managing Connections
- Demo: creating a new package; introduction to variables
- Extracting data
- Extract design tips; extended demo
- Capturing changed data
- Alternative approaches
- Changed Data Capture features
- Identifying changed data by brute force
- Loading data
- Extended demo: Data load best practices
Developing the ETL System
- Transformations for dimension tables
- Cleaning and conforming; extended demo
- Populating multi-valued or many-to-many dimensions
- Incremental updates and managing slowly changing dimensions; demo
- Transformations for fact tables
- Surrogate key pipeline; extended demos
- Early arriving facts, late arriving facts; demos
- Periodic snapshot facts; accumulating snapshot facts; demos
- Audit system overview
- Configurations and parameters
Introduction to Analysis Services
- What is Analysis Services
- Overview
- Multidimensional versus Tabular modes
- Why use Analysis Services?
- Extended demo: Designing dimensions
- Vocabulary of dimensions
- Dimension Wizard
- Dimension Editor
Developing the Analysis Services Multidimensional Database
- Getting started
- Extended demo: creating a new dimension
- Key properties to edit (and which properties most can ignore)
- Hierarchies and attribute relationships
- Processing and fine-tuning
- Designing multidimensional cubes; extended demo
- Terminology
- Cube structure and physical facts
- Dimension usage
- Calculations, KPIs, and Actions
- Physical design considerations
- Cube processing
Day 4
Developing the Analysis Services Tabular Database
- Extended demo: Designing tabular models
- Design a dimension
- Create facts and calculations
- Relationships
- Direct query versus in memory
Real Time Business Intelligence
- Defining real-time BI
- Making the case for (and against) real-time
- Alternative approaches to providing real-time data
- Executing reports in real-time
- Loading DW/BI system in real-time
- Using Analysis Services with real-time data
Securing the Microsoft Data Warehousing and Business Intelligence System
- Developing a security plan
- Securing core SQL Server components
Deployment: The Great Unveiling
- Setting up the environments
- Testing
- Deploying to production
- User readiness
Operations, Maintenance, and Growth
- Providing ongoing user support
- Monitoring the DW/BI system
- Killing queries
- Backup and recovery