This 3 day ILT course focuses on the new features SQL Server 2008 R2 for BI specialists
Target Audience:
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.
Pre-requisites:
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:
Microsoft SQL Server 2008 BI Development Studio
Microsoft SQL Server 2008 Management Studio
Report Builder and Report Manager
Possible experience in using Microsoft Visual Studio
At Course Completion:
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
Microsoft 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 StreamInsight
This 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
