10337 - Updating Your Microsoft SQL Server 2008 BI Skills to SQL Server 2008 R2

This 3 day ILT course focuses on the new features SQL Server 2008 R2 for BI specialists

After completing this course, students will be able to:

    * Describe the new features of SQL Server 2008 R2, and use them to manage BI applications.
    * Use SQL Server PowerPivot to create self-service BI solutions
    * Use new and enhanced Reporting Services tools to create and manage self-service reporting solutions
    * Use new report features to create compelling reports
    * Use StreamInsight to create an application that captures and analyzes streams of event data.
    * Use Master Data Services to enable data consistency across the enterprise to help increase the accuracy of BI solutions.
    * Describe the options available for implementing large-scale data warehousing solutions built on SQL Server 2008 R2.
    * Integrate data from SQL Azure into a SQL Server BI solution

After completing this course, students will be able to:

    * Describe the new features of SQL Server 2008 R2, and use them to manage BI applications.
    * Use SQL Server PowerPivot to create self-service BI solutions
    * Use new and enhanced Reporting Services tools to create and manage self-service reporting solutions
    * Use new report features to create compelling reports
    * Use StreamInsight to create an application that captures and analyzes streams of event data.
    * Use Master Data Services to enable data consistency across the enterprise to help increase the accuracy of BI solutions.
    * Describe the options available for implementing large-scale data warehousing solutions built on SQL Server 2008 R2.
    * Integrate data from SQL Azure into a SQL Server BI solution

Module 1: SQL Server 2008 R2 for BI ProfessionalsMicrosoft SQL Server 2008 R2 offers significant advances in business intelligence (BI) functionality. This is most obvious in the features that are collectively referred to as managed self-service BI. However, other new features that are included in SQL Server 2008 R2, such as data-tier applications, utility control points (UCPs), and SQL Server SysPrep, are also of interest to the BI specialist. This is because they enable faster application deployment and better management of the resources on which BI applications rely. For example, you could use a data-tier application to iteratively develop and deploy a data warehouse that SQL Server 2008 R2 Analysis Services (SSAS) and SQL Server 2008 R2 Reporting Services (SSRS) applications use. This module introduces these new features of SQL Server 2008 R2 and explains why they are important for developers and administrators of SQL Server-based  BI solutions.Lessons

    * Introduction to SQL Server 2008 R2
    * SQL Server 2008 R2 and SharePoint 2010
    *  Database Server Provisioning by Using SQL Server SysPrep
    * Application Management by Using Data-Tier Applications
    * Managing Multiple Servers by Using Utility Control Points

Lab : Managing SQL Server 2008 R2

    * Working with Data-Tier Applications
    * Managing Multiple Servers with a UCP

After completing this module, students will be able to:

    * Identify the key features and scenarios addressed by SQL Server 2008 R2
    * Describe the ways in which SQL Server 2008 R2 and SharePoint 2010 can be integrated to create a comprehensive self-service BI platform
    * Use SQL Server SysyPrep to provision database and reporting servers
    * Use data-tier applications to manage BI databases
    * Use Utility Control Points to manage BI database server resource utilization

Module 2: Implementing Self-Service BI with PowerPivot
PowerPivot technologies empower information workers and provide new opportunities for BI professionals, helping them to maintain governance over user-generated applications, identify key applications for future development, and to use their time more efficiently. This module helps BI professionals to understand the benefits of PowerPivot technologies and explains how to use them to improve the provision of BI services and support business processes.
Lessons

    * Introduction to PowerPivot
    * Power Pivot for Excel 2010
    * Data Analysis Expressions (DAX)
    * Power Pivot for SharePoint 2010

Lab : Implementing Self-Service BI with PowerPivot

    * Using PowerPivot for Excel 2010
    * Using PowerPivot for SharePoint

After completing this module, students will be able to:

    * Install and configure PowerPivot for Excel and PowerPivot for SharePoint.
    * Use PowerPivot for Excel to create a PowerPivot workbook.
    * Use Data Analysis Expressions to build formulas in PowerPivot.
    * Use PowerPivot for SharePoint to manage PowerPivot workbooks.

Module 3: SQL Server 2008 R2 Reporting Services Tool and API Enhancements
This module discusses the tools and application programming interfaces (APIs) that you can use to manage and develop Microsoft SQL Server Reporting Services solutions. SQL Server 2008 R2 provides some new tools and some enhancements to existing tools and APIs and business intelligence (BI) professionals who plan to implement a reporting solution should become familiar with these new features.
A core part of a BI specialist’s role is to know how to use the management and authoring tools and understand how to extend Reporting Services or integrate reporting into custom applications. This module describes the changes in this release to help you to work with SQL Server 2008 R2 Reporting Services solutions more effectively.
Lessons

    * Deploying and Managing Reporting Services
    * Reporting Services Authoring Tool Enhancements
    * Enabling Self-Service BI Through Item Re-Use
    * Developing Custom Reporting Services Solutions

Lab : Working with Reporting Services

    * Viewing and Managing Reports
    * Using Report Builder 3.0
    * Working with Shared Datasets and Report Parts

After completing this module, students will be able to:

    * Use the enhanced Reporting Services management tools
    * Use the enhanced Reporting Services authoring tools
    * Collaborate with other report developers by using shared datasets and report parts
    * Use the enhanced Reporting Services development APIs

Module 4: SQL Server 2008 R2 Report Authoring Enhancements
This module describes the enhancements for report authors in Microsoft SQL Server 2008 R2. As a business intelligence (BI) professional, it is important to know how you can take advantage of the new capabilities in SQL Server 2008 R2 to create reports that present important business information to users clearly.
Lessons

    *  Overview of Report Authoring Enhancements
    * Visualizing Data
    * Visualizing Spatial Data with Maps

Lab : Authoring Reports

    * Using Lookups
    * Paginating Reports
    * Visualizing Data with Databars and Sparklines
    * Visualizing Data with Indicators
    * Visualizing Spatial Data

After completing this module, students will be able to:

    * Use new features to create reports that include rotated text boxes, linked data from multiple datasets, include rollup aggregations, and custom pagination behavior.
    * Create reports that include graphic visualizations of data with Sparklines, Data Bars, and Indicators.
    * Create reports that include visualizations of spatial data.

 
Module 5: Building Complex Event Processing Solutions with StreamInsightThis module provides an introduction to Microsoft SQL Server StreamInsight, which is a powerful platform for implementing complex event processing (CEP) solutions. StreamInsight is based on the Microsoft .NET Framework, and it requires expertise in programming with the Microsoft Visual C# development language. Business intelligence (BI) developers have varying levels of experience of programming in Visual C#; therefore, this module introduces the key concepts that relate to StreamInsight and explains how to implement a basic CEP application. The goal of this module is not to provide an in-depth exploration of the StreamInsight application programming interface (API) but rather to enable BI developers to understand how they can use StreamInsight effectively in a BI infrastructure.Lessons

    * Introduction to StreamInsight
    * StreamInsight Core Concepts
    * Developing a StreamInsight Solution

Lab : Building a StreamInsight Solution

    * Creating a StreamInsight Application
    * Using Filters and Aggregates
    * Using the StreamInsight Debugger

After completing this module, students will be able to:

    * Describe scenarios where Complex Event Processing can be used effectively
    * Describe the core concepts related to Microsoft StreamInsight.
    * Create a basic StreamInsight application

Module 6: Master Data Services
This module explains the value of Master Data Services to modern organizations and outlines the key benefits and scenarios for its use. It also introduces some of the core concepts that Master Data Services uses. It is important for BI professionals to be aware of these concepts because Master Data Services can directly affect reporting and data analysis processes and help to deliver more accurate and reliable results.
Lessons

    * Introduction to MDS
    * Key Concepts in Master Data Services
    * Importing and Browsing Data
    * Business Rules
    * Exporting Data to Subscribing Systems
    * Securing Master Data Services

Lab : Working with Master Data Services

    * Installing and Configuring Master Data Services
    * Creating Master Data Services Objects
    * Importing Data from a Source System
    * Creating a Business Rule
    * Exporting Data to a Subscribing System

After completing this module, students will be able to:

    * Describe the key scenarios and benefits of MDS
    * Describe core MDS concepts
    * Import data into MDS
    * Implement MDS Business Rules
    * Export MDS data
    * Secure MDS

Module 7: Enterprise Data Warehousing
This module explains the Microsoft SQL Server 2008 R2 features and technologies that you can use to build and maintain a high-performance data warehouse. It also introduces Microsoft SQL Server Fast Track Data Warehouse and Microsoft SQL Server 2008 R2 Parallel Data Warehouse and explains how they can help you to create a cost-efficient, high-performance data warehouse that suits the needs of your organization.
Lessons

    * SQL Server 2008 R2 Data Warehousing
    * Overview of Parallel Data Warehouse

After completing this module, students will be able to:

    * Describe the options available for data warehousing with SQL Server
    * Describe the key features and functionality of Parallel Data Warehouse

Module 8: Introduction to SQL Azure
This module describes SQL Azure, and discusses how you can use it to create cloud-based databases for BI applications. The module also introduces Microsoft “Dallas”, an online source of commercial data that you can use to extend the value of your BI solutions.
As cloud-based services and applications become increasingly prevalent, it is important for BI specialists to be aware of the cloud-based technologies for data that are available, and to understand how they can be used to augment and extend on-premise BI solutions.
Lessons

    * Overview of SQL Azure and Cloud Services
    * Using SQL Azure
    * Introduction to “Dallas”

After completing this module, students will be able to:

    * Describe the key features of SQL Azure
    * Describe how to use SQL Azure to implement a database for a BI application
    * Describe how to use Microsoft “Dallas” to augment BI application data

The primary audience for this course is BI Professionals who have 3-4 years of experience with SQL Server. The audience has an understanding of how to use SQL Server 2008 BI tools to implement a BI infrastructure and solution

Before attending this course, students must have:

    * Three to four years of experience using SQL Server 2008 BI components
    * Knowledge of relational and multi-dimensional database schemas
    * Experience creating and deploying SQL Server Reporting Services (SSRS) reports
    * Experience debugging, monitoring, and troubleshooting BI solutions
    * Experience working with the Microsoft .NET Framework
    * Experience in using the following tools:
          o Microsoft SQL Server 2008 BI Development Studio
          o Microsoft SQL Server 2008 Management Studio
          o Report Builder and Report Manager
    * Possible experience in using Microsoft Visual Studio