SQL Server 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

Course Description: This hands-on course walks students through the complete data warehousing and data mining process on SQL Server 2005 and 2008. Students will design and create data warehouses, 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.

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 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

"This was one of the most productive and informative classes I have experienced in my 27 years in IT. The instructor is truly an expert in his field."
– T.D., Rainmaker

Multiple Training Locations - Convenience and Cost Control

Regularly Scheduled Courses in more than 75 Cities Across North America and the United Kingdom

Northeast/New England	Southeast	Midwest	West/Southwest	Canada	United Kingdom
CT - Hartford CT - New Haven CT - Stamford DC - Washington MA - Boston MA - Chelmsford MA - Quincy MA - Springfield MA - Waltham MA - Woburn MA - Worcester MD - Baltimore MD - Columbia NH - Manchester NH - Nashua NJ - Jersey City NJ - Newark NJ - Princeton NY - Albany NY - Binghamton NY - Buffalo NY - Ithaca NY - Manhattan NY - Rochester NY - Syracuse PA - Harrisburg PA - Philadelphia PA - Pittsburgh RI - Providence	AL - Huntsville FL - Fort Lauderdale FL - Jacksonville FL - Miami FL - Orlando FL - Pensacola FL - Sarasota FL - Tampa FL - West Palm Beach GA - Atlanta GA - Savannah LA - Shreveport NC - Charlotte NC - Raleigh-Durham NC - Winston-Salem SC - Charleston VA - Alexandria VA - Fairfax VA - Norfolk VA - Richmond VA - Williamsburg	AR - Little Rock IA - Cedar Rapids IA - Des Moines IL - Chicago IL - Schaumburg IL - Springfield IN - Indianapolis KY - Louisville MI - Detroit MI - Lansing MN - Minneapolis MO - Kansas City MO - St. Charles MO - Springfield NE - Omaha OH - Cincinnati OH - Cleveland OH - Columbus OH - Dayton OK - Oklahoma City OK - Tulsa TN - Memphis TN - Nashville WI - Milwaukee	AZ - Phoenix AZ - Tucson CA - Bakersfield CA - Fresno CA - Irvine CA - Los Angeles CA - Modesto CA - Oakland CA - Riverside CA - Sacramento CA - San Diego CA - San Francisco CA - San Jose CA - Santa Clarita CA - Santa Monica CA - Temecula CO - Denver ID - Boise NM - Albuquerque NM - Las Cruces NV - Las Vegas OR - Portland TX - Austin TX - Corpus Christi TX - Dallas TX - El Paso TX - Fort Worth TX - Houston TX - San Antonio UT - Salt Lake City WA - Seattle WA - Spokane	AB - Calgary AB - Edmonton BC - Vancouver MB - Winnipeg QC - Montreal QC - Quebec City ON - Kingston ON - Mississauga ON - Ottawa ON - Toronto SK - Regina	Birmingham Bristol Chelmsford Edinburgh Glasgow Leeds Liverpool London Manchester

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