SQL Server 2005 Business Intelligence: Integration Services and Analysis Services

Duration: 5 Days | Price: CDN$2875 *Prices are subject to GST/HST

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

Course Description: This hands-on course walks students through the complete data warehousing and data mining process using SQL Server. Students will design and create data warehouses, then use the Business Intelligence Development Studio (BIDS) 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.

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 Business Intelligence Tools Requirements for Installation Steps to Install SSIS and SSAS Using BIDS to Create Business Intelligence Projects Business Intelligence Editions Compared		Integration Services Architecture Architecture of the SSIS Data Engine Using Data Transformation Tasks Managing Connections to Sources and Destinations Data Buffering ADO.NET Data Source and Destination Connection Project Wizard
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 Data Profiling Task		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 Customizing Packages Package Deployment, Management and Security		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 Dimension Designer Tool
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 Cube Designer Tool
Deploying, Securing and Processing Cubes Cube Storage Calculations Configuring Desired Aggregation Configuring Caching Deploying and Processing Cubes Connecting to Cubes from Excel Pivot Tables Using Cubes as Data Sources from Reporting Services Backup and Restore Capabilities Setting Role Security Levels		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 SQL Server Built-In 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 Built-In Data Mining Algorithms 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

Student Testimonials

"I really liked the format of the class - the labs gave me a chance to apply what was being discussed in class as well as giving me a chance to work with the tools. The instructor brought a lot of industry experience to the table and was able to further clarify various examples with real experiences which I found to be invaluable."
– K.R., Trican Well Service

Multiple Training Locations - Convenience and Cost Control

Regularly Scheduled Courses in 11 Cities Across Canada

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