SQL Server 2005 End-to-End Business Intelligence Workshop
 

5-Day (BI-BIE2E05-201-EN)

Description

Business intelligence solutions provide the infrastructure that enables people at all levels of a business to make better decisions based on more accurate and up-to-date information. This workshop focuses on the teaching IT professionals the best practices and skills required to successfully design, build, deploy, and operate a business intelligence solution using SQL Server 2005.

Course Summary

During this 5-day course, you will learn about integrating Integration Services, Analysis Services and Reporting Services to build a business intelligence solution. When you complete this course, you will understand how to design, develop, deploy, and operate a business intelligence solution that integrates data from an On-Line Transactional Processing (OLTP) into a SQL Server Analysis Services (SSAS) cube and delivers reports to business users.
  • Integrate: Define, debug, deploy, manage and secure SQL Server Integration Services (SSIS) packages that extract, transform, and load data from an OLTP database into an SSAS cube and a relational data warehouse
     
  • Analyze: Define, deploy, process, manage and secure SQL Server Analysis Services (SSAS) cubes, mining models, dimensions, hierarchies, calculations, translations, and Key Performance Indicators (KPIs)
     
  • Report: Define, deploy, manage, and secure SQL Server Reporting Services (SSRS) reports that consume data stored in SSAS cubes and in the relational data warehouse and deliver reports to business users.
This course will demonstrate a fully working end-to-end solution, including extensive demos, hands-on labs, sample projects that will not only teach how to use the new technology, but also illustrate business intelligence and data warehouse best practices.

Audience

This course is intended for IT Professionals that are responsible for designing, developing, deploying, and operating business intelligence solutions.

Prerequisites

The target audience for this session is database warehouse, decision support, and business intelligence solution architects, IT professionals, and business intelligence developers who want to learn the details of how to use the new capabilities provided by SSIS, SSAS, and SSRS to build an end-to-end business intelligence solution. While this course is targeted at database professionals with some experience with data warehouse solutions, this workshop requires no prior experience with SQL Server 2000 DTS, Analysis Services, and Reporting Services.

Course Outline

Day 1

Section A: Overview
  • Intro 00 - Intro 01

    • Understanding the Design and Operation of a Business Intelligence Solution and reviewing the background and concepts of the SQL Server 2005 Business Intelligence technologies
       
  • Intro 02: Dimensional Modeling Overview
    • High level review of business intelligence architecture and dimension modeling basics, including dimension tables and fact table descriptions
       
  • Intro 03: Getting Familiar with SQL Server 2005 Development and Management Tools
    • Business Intelligence Developer Studio, SQL Server Management Studio, Solutions, Projects, Properties Windows, and Viewers, SQL Server 2005 supporting tools
       
Section B: Integration Services (SSIS)
  • SSIS 01: SSIS Features and Concepts Overview
    • SSIS package storage options, Package execution options, Shared data sources, Data source views, Connections, Package variables, Package configurations, Deployment tool, Checkpoints
       
    • SSIS Lab 1 - Creating your first SSIS Project and Package
       
  • SSIS 02: Control Flow Containers, Tasks and Constraints
    • Management Tasks, Standard Tasks, Execute SQL Task and parameters, Script Task, Sequence Container, Looping Containers, Standard and Expression based Constraints
       
    • SSIS Lab 2 - Working in the Control Flow
       
  • SSIS 03: Data Flow Transformations and Adapters
    • Data Flow source and destination adapters, Data expressions and conversion transformations, Data association and correlation transformations (IE: Lookup, Merge), Data cleansing (Fuzzy transformations), Copying, Merging, Splitting, Filtering, and Union-ing
       
    • SSIS Lab 3 - Working with the Data Flow

Day 2

  • SSIS 04: Loading Dimension Tables
    • Slowly Changing Dimension types and theory, Using the SCD wizard, Drilling into the SCD transformations, Customizing the SCD, Alternate SCD approaches, Inferred Members, Handling Snowflake and Parent-Child Hierarchies, Managing High Volume Dimensions
       
    • SSIS Lab 4 - Dimension Table ETL
       
  • SSIS 05: Loading Fact Tables
    • Fact types and theory, Aspects of the fact table loading, Dimension lookups with the Lookup transformation, Missing Dimension Members, Measures and Calculations, Handling fact inserts and updates, Changing data grain (Using Pivot, Unpivot, and Aggregation transformations), Adding Inferred Members when dimension records don't exist, Handling Large Fact Volumes (fact table partitioning)
       
    • SSIS Lab 5 - Fact Table Loading, AdventureWorksDW sales fact tables 
       
  • SSIS 06: Package Configurations, SSIS logging, and Package Execution
    • Planning your configuration environment, SSIS Configuration Types, Configuration schema, Built in SSIS Logging, Master/Child package design, Package execution
       
    • SSIS Lab 6 - Setting up SSIS configurations and logging
       
Section C: Analysis Services (SSAS)
  • SSAS 01: SSAS Features and Concepts Overview
    • BI Development Studio, Analysis Services projects, Unified data model (UDM), Solution architectures, Attribute based dimensions, Manual vs automatic processing, Measure groups

Day 3

  • SSAS 02: Defining Data Sources, Data Source Views and a Simple Cube
    • Multiple data sources, Data source views, Named Calculations, Named queries, Cube wizard, Deploying a project to the development environment
       
    • SSAS Lab 2 - Defining a DSV and a Simple Cube
       
  • SSAS 03, part 1: Configuring Dimensions, Attributes, and Hierarchies
    • Dimension properties, Modifying attribute properties, Utilizing named queries, Sort order, Parent-child dimension properties, Discretization, Composite member keys, Defining hierarchies, Attribute relationships, Null value error handling
       
    • SSAS Lab 3A - Defining measure properties
       
    • Configuring Dimensions, Attributes, and Hierarchies
       
  • SSAS 03, part 2: Configuring Measure Groups and Dimension Usage
    • Measure groups, Measure properties, Aggregation settings, Reference dimension relationships, Fact dimension relationships, Dimension granularity
       
    • SSAS Lab 3B - Configuring Measure Groups and Dimension Usage

Day 4

  • SSAS 04: Advanced Cube Options
    • MDX scripts, calculated members, actions, KPIs, Default and custom perspectives, Translations of cube and dimension meta data
       
  • SSAS 05: Cube Storage and Proactive Caching
    • Multiple partitions, storage mode, proactive caching
       
  • SSAS 06: Security, Deployment and Management
    • Administrative security roles, Configuring Administrative and User Access, User access roles, Deployment wizard, AMO scripts, synchronization, manual processing
       
Section D: Reporting Services (SSRS)
  • SSRS 01: Reporting Services Features and Concepts
    • Components and architecture, report authoring, report deployment, report delivery, report management, Report Definition Language (RDL) Overview, BI Development Studio Report Designer, Report Viewer, Report Manager, Report Builder, Custom extensions
       
    • SSRS Lab 01 - Authoring, Managing and Delivering a Report from an Analysis Services UDM

Day 5

  • SSRS 02: Building Basic Reporting Services Reports with Report Designer
    • Data Regions, Expressions, Using Grouping and Visibility for Report Interactivity, Using Report Parameters and Filters for versatile and performance optimized reports, Using Report Actions and Drill-through Reports, Relational and multidimensional data sources, Query builder, dynamic SQL, Stored procedures and Parameters, KPIs, Actions, Translations, SSIS as a data source, data mining as a data source
       
    • SSRS Lab 02 - Building reports with Report Designer and data 
       
  • SSRS 03: Introducing Reporting Services Report Builder for Ad-Hoc Reporting
    • Authoring Report Builder Reports, Building and Managing Report Builder Semantic Models
       
    • SSRS Lab 03 - Building a Report Builder Semantic Model and Ad-Hoc Reports
       
  • SSRS 04: Securing and Managing Reporting Services
    • Content Management and Security, Instance Configuration
       
    • SSRS Lab 04 - Securing, scheduling and managing reports
       
  • SSRS 05: Tying It Together
    • BI Integration, SharePoint, Excel
       
    • SSRS Lab 05 - Working with SharePoint to build a BI site
​