Welcome

BI-300

Level 300

Seamless Data Integration

What Will We Cover?

Overview of Business Intelligence (BI) SolutionsExport Data from Heterogeneous Data Sources Using SQL Server Integration ServicesTransform and Load Data to System Center Data Warehouse Using SQL Server Integration Services

Agenda

Business Intelligence Overview Meeting the Challenges of Integrating Data from Multiple Data SourcesIntegrating Disparate Data and Importing to Data Warehouse Using SQL Server Integration Services

Business Intelligence Overview

Delivery

End-User Tools and Performance Management Applications

BI Platform

Office Excel Office PerformancePoint™ Server

SQL Server Reporting Services

SQL Server Analysis Services

SQL Server database management system (DBMS)

SQL Server Integration Services

Reports Dashboards Office Excel®

WorkbooksAnalyticViews Scorecards Plans

Microsoft Office SharePoint® Server

Search Collaboration Content Management

Microsoft Dynamics

Database Flat Files

ETL Process in Business Intelligence Solution

Business DataClient Access

Staging Area

System Center Operations

Manager Data Warehouse

IT Support Database

Text Files

Manual Cleansing

Extraction

Data Marts

Data Warehouse

Load

Bu

siness R

ules

Transform

Agenda

Business Intelligence Overview Meeting the Challenges of Integrating Data from Multiple Data SourcesIntegrating Disparate Data and Importing to Data Warehouse Using SQL Server Integration Services

Challenges in Integrating Data from Multiple Data Sources

Integrating Data in Different FormatsIntegrating Structured, Semi-Structured, and Unstructured DataIntegrating Huge Data VolumesQuickly Transforming Data into Meaningful FormatLoading Data to Diverse Destinations

SQL Server Integration Services Capabilities

Support to Various Database VendorsSupports Various Data FormatFlexible, Fast, and Scalable ArchitectureEffective and Large ToolsetInteractive and Easy-to-Use Designer

SQL Server Integration Services Architecture

Task Flow and Data Flow

ADO.NET Connectivity

Pipeline Architecture

Persistent Lookups

Agenda

Business Intelligence Overview Meeting the Challenges of Integrating Data from Multiple Data Sources Integrating Disparate Data and Importing to Data Warehouse Using SQL Server Integration Services

SQL Server Integration Services Data Sources

Connection ManagerBuilt-in, downloadable, customConnects to databaseConnects to spreadsheetsConnects to text filesConnects to Web services

ADO.NET Connection Manager

Enables Access to Data Sources Using .NET ProviderTypically Used for Connecting

SQL Server XML data sources Assembly data source OLE DB

ADO.NET in Action

SQL Server Import and Export Wizard

Welcome Screen Source Selection

Task 1: Use the Import and Export Wizard to Import Data from a Flat File

Integrate Disparate Data and Import to Data Warehouse

Demo

Designer Graphical ToolControl Flow and Toolbox

Designer Graphical ToolData Flow and Toolbox

Create a PackageIntegration Services Designer

Data Transformations

Transformation ComponentsAggregateSortLookupPivot and UnPivotMerge, Merge Join, and UnionAllDerived columnData conversionAuditSlowly changing dimensions

Change Data Capture

Valuable Change Information About Data Manipulation Language ChangesEliminates Expensive Techniques, Like Triggers, Timestamp Columns, and Expensive Join Queries

Task 1: Configure SSIS package to Access Data Sources

Task 2: Load Dimensions and MeasuresTask 3: Deploy Package

Configure and Create Package Using Graphical Designer Tool

Demo

Session Summary

Integrating Disparate Data and Importing to Data WarehouseTransforming DataADO.NET Connectivity

Where to Find More Information

Visit TechNet at technet.microsoft.com

Also, check out TechNet Edge

edge.technet.com

Or just visit www.microsoft.com/?

linkid=9638674

for additional information on this session

For more titles, visithttp://www.microsoft.com/?linkid=9638674

Supporting Publications

Course ID Title

6235A Implementing and Maintaining Microsoft®

SQL Server® 2008 Integration Services

For more training information http://www.microsoft.com/?linkid=9638674

Training Resources

Become a Microsoft Certified Professional

What are MCP certifications?Validation in performing critical IT functions

Why Certify?Worldwide recognition of skills gained via experienceMore effective deployments with reduced costs

What Certifications Are There for IT Pros?MCTS, MCITP

www.microsoft.com/certification

Microsoft TechNet Plus

TechNet Plus is an essential, premium, Web-enabled, and live support resource that provides IT Professionals with fast and easy access to Microsoft experts, software, and technical information, enhancing IT productivity, control, and planning.

Evaluate and Learn Plan and Deploy Support and Maintain

Use the TechNet Library to plan for deployment using the Knowledge Base, resource kits, and technical training

Use exclusive tools, like System Center Capacity Planner, to accurately plan for and deploy Exchange Server and System Center Operations Manager

Two complimentary Professional Support incidents for use 24/7 (20-percent discount on additional incidents)

Access more than 100 managed newsgroups and get next business day response—guaranteed

Use the TechNet Library to maintain your IT environment with security updates, service packs, and utilities

Get all these resources and more with a TechNet Plus subscription.For more information visit: technet.microsoft.com/subscriptions

Evaluate full versions of all Microsoft commercial software for evaluation—without time limits; this includes all client, server, and Microsoft Office system applications

Try out all the latest betas before public release

Keep your skills current with quarterly training resources, including select Microsoft E-Learning courses