SQL Server 2014 Business Intelligence: Integration Services and Analysis Services
Length: 5 Days | Price: Please contact us for pricing information.
This hands-on course focuses on the Integration Services (SSIS) and Analysis Services (SSAS) business intelligence tools that are bundled with SQL Server 2014. Students will install and configure SQL Server 2014 servers that are enabled for SSIS and SSAS, which are then used for comprehensive hands on exercises demonstrating key features of SSIS/SSAS.
Students work with complex SSIS data migration projects that import/export data between multiple formats including: Access, SQL Server, Oracle and other databases, XML files, Excel spreadsheets and flat files. Also demonstrated are SSIS tasks that can download from FTP Servers, copy/move files and folders, execute Win32 processes and .NET scripts, perform bulk imports, perform database maintenance, contact (via text or e-mail) administrators, and transfer database components between SQL Servers. Error handling options are also demonstrated with hands-on exercises, as well as how to use variables and input parameters in SSIS routines. The course provides thorough coverage of how to deploy, automate, secure and troubleshoot SSIS routines.
Students will learn how to use SSAS to design, build and populate data warehouses to stage data for use by data cubes, and will perform hands-on exercises to design and build a data warehouse. Advanced data warehouse concepts, such as surrogate keys and rapidly changing dimensions are also reviewed. Subsequent exercises use SSAS to create data cubes and analyze them. Cube customization options are demonstrated, including how to add Key Performance Indicators (KPIs), custom calculations, and actions that link to SSRS reports. Attendees also learn how to optimize cube performance by defining attribute relationships and custom aggregation levels.
The course also includes an overview of data mining and the tools available to do this in SSAS, supported by a hands-on exercise that walks through the process of defining a data mining structure to predict future sales. Administration topics related to data cubes are covered, including exercises that deploy and secure cubes, process and manage cubes.
Prerequisites: Familiarity with database concepts, Windows desktop navigation and software installation techniques. Attendance at SQL Programming course or Microsoft Transact-SQL Programming course is highly recommended although not required.
- Installing SQL Server Business Intelligence Tools
- Integration Services Architecture
- Executing SQL Statements
- Merging Data from Multiple Sources
- Splitting Data to Multiple Destinations
- Using the Windows Management Instrumentation (WMI) Tasks
- Performing Database Maintenance and Backups During SSIS Routines
- Filling in Missing Data with Lookups
- Locating Near Duplicate Rows with Fuzzy Grouping
- Deploying SSIS Routines
- Manually Running SSIS Routines
- Understanding Fact and Dimension Tables
- Modeling Slowly Changing and Rapidly Changing Dimensions
- Creating Data Warehouses (OLAP Databases)
- Creating Data Sources to Connect to Data Warehouses
- Using SSAS to Create Cubes
- Customizing Cubes
- Cube Storage Calculations
- Securing Cubes
- Using MDX Queries to Pull Data from Cubes
- Using MDX Aggregate Functions
- Understanding the Data Mining Process
- Working with Data Mining Structures
- Course materials include student guide, hands-on lab manual and USB flash drive containing examples and lab files
- Students receive a certificate of completion at the end of class
- Students can retake any portion of a class that has been completed, within 12 months at no extra cost
- There are no registration fees or cancellation fees
- Class sizes are kept small in order to provide you with more personalized attention
- Our instructors have many years of training and industry experience working in their respective fields