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

---

Description

Audience

Prerequisites

• Basic knowledge of Business Intelligence
• Knowledge of relational database systems
• Experience with SQL Server database
• Basic knowledge of windows security

Course Outline

Day 1-2

Module 01:  Introduction to Business Intelligence

This module introduces BI and components of a BI solution and then discusses the various products that are available from Microsoft for implementing a BI solution and how those technologies fit into the BI component stack. We will then introduce in more detail the technologies that are part of the SQL Server BI Platform and also highlight some of the major changes in each of those technologies in 2008. We will also look at the various tools that are available for developing and managing the technologies. 

• BI Practice
• Components of a BI solutions
• MS BI stack
• Intro to the MS SQL BI Platform
• Highlight some major changes between SQL Server 2005 and 2008
• Client Tools for development and management – also highlight differences from 2005

Module 02:  Preparing Data for Analytics 

This module provides an introduction to dimensional modeling and lays the foundation for more detailed topics covered over the next few modules. We will cover Dimensional Modeling concepts including Fact and Dimension tables and how to implement a dimensional model within your organization. We will also address the needs for implementing dimensional models and talk about Data Warehousing and Data Marts. We will also briefly discuss processes for loading data into these dimensional tables.

• The Need
• Dimensional Modeling
• Dimensional Modeling Concepts
• LAB: Documenting a dimensional model

Module 03:  Implementing workflows using SQL Server Integration Services

This module provides an introduction to the components of SQL Server Integration Services and the package. It also highlights changes in 2008 for in the control flow environment and explains how to use the components and features of SSIS to build process workflows. 

• The Need
• SSIS Components
• Package Components
• Changes in SQL Server 2008 – Reasons for upgrade
• Data Sources and Connections
• Tasks, Containers and Precedence Constraints
• Highlight: Script Task and VSTA
• Variables
• SSIS Expressions
• LAB: Implementing simple workflows including loops and complex constraints

Module 04: Processing data using SQL Server Integration Services

This module highlights the various components of a data flow environment and then explains how to use these components and features to implement ETL processes. This module does not go into great details around the internals of SSIS Data Flow. 

• The Need
• Data Flow Components: Source and destination adapters, transformations, data flow paths
• Building data flows
• Changes in SQL Server 2008 – Reasons for upgrade
• Highlight: ADO.Net data provider
• LAB: Building a simple data flow including a custom source for Date Process

Module 05: Loading a Dimensional Model using SQL Server Integration Services

This module covers the specific components of the data flow that are used to implement ETL processes to load dimension and fact tables.  

• The Need
• Slowly Changing Dimension Concept
• Highlight: Slowly Changing Dimension Transform
• Loading Date Dimension
• Load Fact Tables
• Highlight: Lookup transform and persistent cache
• LAB: Load dimension and fact tables including dimDate

 Module 06: Deploying SQL Server Integration Services Packages

This module covers deployment and scheduling of SSIS packages.  

• The Need
• Deployment locations
• Deployment options
• Scheduling a package

Day 3

Module 07: Introduction to the Unified Dimensional Model (UDM)

• The Need
• Introduction to OLAP Fundamentals
• The UDM and Benefits
• Analysis Services Fundamentals
• Basic Components of the UDM: Data Sources, Data Source Views, Cubes, Dimensions
• Components of a SQL Server Analysis Services Database
• Building a basic cube
• Deployment and processing

Module 08: Customizing the UDM

• The Need
• Introduction to the SSAS designer
• New design features in SSAS 2008
• Dimension customization: Dimension properties, attributes and hierarchies (Customization scenario/how-to approach)
• Cube customization: Cubes, measure groups, measures (Customization scenario/how-to approach)
• Advanced SSAS database components: Dimension relationships, Calculations, KPI’s,
• Review of the best practices
• Highlight: Designer improvements
• Highlight: Best Practices Warnings
• Highlight: Attribute Relationship Designer
  

Module 09: Deployment, Management and Optimization

This module discusses SSAS Database deployment and management including storage modes, partitioning, proactive caching, performance tuning and optimization – including the new aggregation designer. We will also provide a very high-level look at the enhancements in SSAS 2008 and reasons for upgrade. 

• Storage Modes
• Scale-out deployment options
• Data processing optimization techniques
• Aggregation design
• Backup Improvements
• Scaling out with Shared Databases

Day 4  

Module 10: Managing SSAS using SSIS

This module explains the features of SQL Server Integration Services that can be used to interact with data in SQL Server Analysis Services and also manage SQL Server Analysis Services objects. 

• The Need
• Accessing data from the UDM
• Interacting with and managing SSAS objects
• Data Processing options

Module 11: Introduction to Data Mining

This module introduces Data Mining and provides an understanding of the business uses of data mining and also provides an overview of the data mining process. 

• The Need
• Data Mining Process
• Data Mining Algorithms
• Changes in SQL Server 2008
• Data Mining add-ins in Excel 2007

Module 12: Introduction to SQL Server Reporting Services

This module will introduce the Reporting Services, the architecture and components of SSRS. Additionally, we will look at the components of a Report and understand the features and functionality including new features in SSRS 2008. 

• The Need
• SSRS 2008
• SSRS Architecture and Components
• What’s new in SSRS 2008 Architecture
• Report Components
• What’s new in SSRS 2008 Report Components

Day 5

Module 13: Designing Reports in SQL Server Reporting Services

This module will familiarize people with the design tools for designing reports and explain features available to customize report look and functionality. 

• The Need
• SSRS 2008 Designer
• What’s New in SSRS 2008 for Report design
• Report Queries and Parameters
• Report layout and customization
• Report Interactivity
• Charts and Graphs
• Extending Report Functionality

Module 14: Publishing, Accessing and Managing Reports

This module looks at mechanisms for publishing and accessing the published reports as well as looks at new tools in 2008 to manage reports. 

• The Need
• Report deployment options
• Report deployment architectural scenarios (Scalable deployment)
• Mechanisms for accessing reports
• Processing and rendering architecture
• Memory usage management
• What’s New in SSRS 2008 Rendering Architecture

Module 15: Advanced Reporting against the UDM

This topic will cover some other key technologies that can be used by end-users to report against this data. We will see how to expose the UDM to end users for self service reporting, use pivot tables in Excel to report against the UDM and also highlight some features in Excel 2007 that make data analysis easier for the end user. Finally we will briefly discuss other technologies for reporting against the UDM including ProClarity, Excel Services and SharePoint Server, Business Scorecard Manager and Performance Point Server.

• Report Designer as an end-user tool
• Creating and Deploying Report Models
• Ad-hoc Reporting using Report Builder Client
• Reporting with Microsoft Office  Excel 2007
• Other Reporting and Delivery Mechanisms