SQL Server 2005 Business Intelligence: Integration Services and Analysis Services

Duration: 5 Days | Price: $2495 | Class Dates:

Students Will Learn:
  • Importing Excel and XML Data
  • Exporting SQL Server Data
  • Automating ETL Routines
  • Migrating SQL 2000 DTS packages
  • Designing Data Warehouses
  • Creating Cubes
  • Mining Data
  • Managing Cube Security

Description: This hands-on course walks students through the complete data warehousing and data mining process. Students will design and create data warehouses using Microsoft SQL Server 2005. Then the Business Intelligence Development Studio (BIDS) will be used to create SQL Server Integration Services (SSIS) routines to move records from a relational database into the data warehouse. Once the data warehouse is populated, the data will be explored using SQL Server Analysis Services (SSAS). BIDS will again be used to create cubes to support business decisions. Hands-on exercises will use the cubes to develop Key Performance Indicators (KPI) and custom performance measures. Additional exercises will demonstrate how to update and manage cubes. Analysis Service's support of data mining is also covered, and hands-on labs will configure data mining algorithms to search for patterns and trends in the data. In addition to using SSIS to support data warehouses, students will also complete labs that demonstrate how to use the ETL utility to move data in and out of SQL Servers. This includes exercises that import and export data between Excel spreadsheets, Access databases, XML files, flat files and SQL Server. Techniques to clean data will also be demonstrated, such as finding duplicate records or performing look-ups in tables. Other exercises will merge data from multiple sources, as well as splitting data to multiple destinations. Troubleshooting and security configurations for these Business Intelligence services are incorporated throughout the course.

Prerequisites: Familiarity with database concepts and Windows desktop navigation. Attendance at HOTT's 3-day SQL Programming course, or equivalent knowledge, is recommended but not required.

SQL Server 2005 Business Intelligence: Integration Services and Analysis Services Course Overview:

Installing SQL Server BI Tools
  • Requirements for Installation
  • Steps to Install SSIS and SSAS
  • Using BIDS to Create BI Projects
  • BI Editions Compared
Integration Services Architecture
  • Architecture of the SSIS Data Engine
  • Using Data Transformation Tasks
  • Managing Connections to Sources and Destinations
Common SSIS Control Flow Tasks
  • Executing SQL Statements
  • Connecting to XML Data Sources
  • Connecting to FTP Servers
  • Sending Emails
  • Notifying Administrators of Errors
  • Completing Bulk Data Inserts
Common Data Transformations
  • Changing Datatypes
  • Merging Data
  • Sending Data to Multiple Destinations
  • Counting Records
  • Sampling Records
  • Sorting Data
  • Copying Columns
Advanced SSIS Control Flow Tasks
  • Interacting with the File System
  • Executing .NET Scripts
  • Using the WMI Tasks
  • Using Loops with Control Flow Tasks
  • Using System Variables in Tasks
 Advanced Data Transformations
  • Using Fuzzy Lookups and Fuzzy Grouping
  • Conditionally Splitting Data
  • Merging Data
  • Using Term Lookups
Migrating SQL Server 2000 DTS Packages
  • Using the Upgrade Advisor
  • Running Legacy DTS Packages on SQL Server 2005
  • Migration DTS Packages to SSIS
Automating SSIS Routine Execution
  • Deploying SSIS Routines to SQL Servers
  • Manually Running SSIS Routines Automating SSIS Routine Execution
  • Configuring Automatic Notifications of SSIS Routine Execution
OLAP Design
  • Understanding Facts and Dimensions
  • Modeling Slowly Changing and Rapidly Changing Dimensions
  • Defining Fact Tables
  • Using Star and Snowflake Schemas
  • Working with Surrogate Keys
  • Defining Business Keys
 Creating Data Warehouses
  • Creating OLAP Data Warehouses
  • Creating Fact Tables
  • Adding Dimension Tables
  • Joining Fact Tables to Dimension Tables
  • Loading Data into Data Warehouses
Creating and Managing Cubes
  • Customizing Cube Dimensions
  • Processing Dimensions
  • Adding Time-Based Dimensions
  • Defining Business Measures
  • Creating Parent-Child Dimensions
 Customizing Cubes
  • Adding Key Performance Indicators
  • Customizing Dimensions
  • Adding Translations to Support Multiple Languages
  • Translating Currency Values
  • Adding Custom Calculations
SSAS Management
  • Deploying Cubes to SSAS Servers
  • Processing Cubes
  • Implementing Security on Cubes
  • Defining Aggregation Levels
  • Partitioning Cubes for Efficient Storage
  • Adding Proactive Caching to Cubes
  • Managing SSAS Servers
Multidimensional (MDX) Queries
  • Writing MDX Queries to Pull Data from Cubes
  • Using MDX Functions
  • Working with Financial Functions
  • Defining Custom Calculations for Cubes Using MDX Functions
Understanding the Data Mining Process
  • Types of Business Analysis Supported by Data Mining
  • Data Mining Process Explained
  • Understanding the Key Components of Data Mining
  • Matching Mining Algorithms to Business Needs
 Working with Data Mining Structures
  • Adding Data Mining Structures
  • Mining for Hidden Information
  • Discovering Patterns in Data
  • Creating Predictive Models
  • Using the Data Mining Wizard
  • Modifying Mining Structures with the Data Mining Designer

Hands On Technology Transfer, Inc.
1 Village Square, Suite 8
14 Fletcher Street
Chelmsford, MA 01824
1-800-413-0939 | 1-978-250-4299

Copyright© 2008 Hands On Technology Transfer, Inc.