Microsoft Certification Training
Search classes by keyword:
Search classes by category:
Microsoft Certification and Microsoft Training, Cisco Certification and MCSE Certification
MCSA SQL Server 2012 space



MCSA SQL Server 2012 Certification Training

MCSA: SQL Server 2012 Certification Training Course

Course Number: #CED-906
Course Length: 15 days
Number of Exams: 3
SATV Vouchers: 15
Certifications: MCSA: SQL Server 2012
   
Class Price: MCSA SQL Server 2012 Training Quote

Grants (discounts) are available for returning students, multiple students, active duty military, government, government contractors, non-profit companies and educational institutions or US Veterans.

"The instructor did a great job leading the class and keeping us on track to pass all of the exams. If you have the motivation to get your certification this program will enable you to do it in the shortest amount of time."

-Ben Calvin, San Francisco, CA


MCSA SQL Server 2012 top left headerMCSA SQL Server 2012 top center headerMCSA SQL Server 2012 top right headerMCSA SQL Server 2012 middle headerMCSA SQL Server 2012 header middle centerMCSA SQL Server 2012 middle right headerMCSA SQL Server 2012 bottom left headerMCSA SQL Server 2012 bottom center headerMCSA SQL Server 2012 bottom right header
Custom Training Quote
MCSA SQL Server 2012 top leftMCSA SQL Server 2012 footer top centerMCSA SQL Server 2012 footer top rightMCSA SQL Server 2012 footer middle leftMCSA SQL Server 2012 footer middle centerMCSA SQL Server 2012 footer middle rightMCSA SQL Server 2012 footer bottom leftMCSA SQL Server 2012 footer bottom centerMCSA SQL Server 2012 footer bottom right

Hurry, Fall Promotion - 10% Off All Classes

Hurry, Fall Promotion - 10% Off All Classes - Pay by October 31st

Offer ends October 31.

Payment must be received by October 31, 2014. All courses must be completed by December 31, 2015 to qualify.


  • Includes roundtrip airfare and lodging (Atlanta and Fort Lauderdale locations only)
  • Hands-on instruction by a certified instructor
  • Includes all course materials and practice exams
  • On-site Testing
  • Breakfast and Lunch provided each day

Can't travel or you want to stay with your family or business. No problem! Stay in your own city and save the additional expenses of roundtrip airfare, lodging, transportation, and meals and receive the same great instruction live from our instructors in our Live Instructor-Led Remote Classroom Training.

Remote Classroom Training

Our Remote Classroom Training is a live class with students observing the instructor and listening through your computer speakers. You will see the instructor's computer, slides, notes, etc., just like in the classroom. You will be following along, doing work, labs, and individual assignments.

Demonstrate to potential hiring managers your essential skills and breakthrough insights in developing and maintaining the next wave of mission critical environments by achieving the MCSA: SQL Server 2012 certification. This certification is your first step towards earning an MCSE: Data Platform or MCSE: Business Intelligence certification.


Microsoft Course 10774

Exam 70-461 - This instructor led course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2012. This course is the foundation for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence. This course helps people prepare for exam 70-461. Many of the exercises in this course are SQL Azure enabled. led.

This course helps students prepare for Exam 70-461.

Audience Profile

This course is intended for Database Administrators, Database Developers, and Business Intelligence professionals. The course will very likely be well attended by SQL power users who aren't necessarily database-focused or plan on taking the exam; namely, report writers, business analysts and client application developers.

At Course Completion

After completing this course, students will be able to:

  • Write SELECT Queries
  • Query Multiple Tables
  • Use Built-In Functions
  • Use Subqueries
  • Execute Stored Procedures
  • Use Set Operators
  • Implement Error Handling
  • Implementing Transactions
  • Use Table Expressions
  • Sort and Filter Data
  • Use Window Ranking, Offset and Aggregate Functions
  • Query SQL Server Metadata
  • Program with T-SQL
  • Improve Query Performance

Prerequisites

Before attending this course, students must have:

  • Working knowledge of relational databases.
  • Basic knowledge of the Microsoft Windows operating system and its core functionality.

Course Outline

Introduction to Microsoft SQL Server 2012

This module introduces the SQL Server platform and major tools. It discusses editions, versions, tools used to query, documentation sources, and the logical structure of databases.

  • SQL Server Denali Architecture
  • Working with SQL Server Denali Tools
  • Working with SQL Server Databases

After completing this module, students will be able to:

  • Describe SQL Server components
  • Create and save T-SQL scripts in SQL Server Management Studio
  • Browse the objects in a database
  • Search T-SQL syntax definitions in Books Online
Introduction to Transact-SQL Querying

This module introduces Transact SQL as the primary querying language of SQL Server. It discusses the basic structure of T-SQL queries, the logical flow of a SELECT statement, and introduces concepts such as predicates and set-based operations.

  • Introducing Transact-SQL
  • Understanding Sets
  • Understanding Predicate Logic
  • Understanding the Logical Order of Operations in SELECT Statements

After completing this module, students will be able to:

  • Describe the elements of T-SQL and its role in writing queries
  • Describe the use of sets in SQL Server
  • Describe the use of predicate logic in SQL Server
  • Define the logical order of operations in SELECT statements
Writing SELECT Queries

This module introduces the fundamentals of the SELECT statement, focusing on queries against a single table.

  • Writing Simple SELECT Statements
  • Eliminating Duplicates with DISTINCT
  • Using Column and Table Aliases
  • writing Simple CASE Expressions

After completing this module, students will be able to:

  • Write Simple SELECT statements
  • Eliminate duplicates using the DISTINCT clause
  • Use column and table aliases
  • Write simple CASE expressions
Querying Multiple Tables

This module explains how to write queries which combine data from multiple sources in SQL Server. The module introduces the use of JOINs in T-SQL queries as a mechanism for retrieving data from multiple tables.

  • Understanding Joins
  • Querying With Inner Joins
  • Querying With Outer Joins
  • Querying Using Self and Cross Joins

After completing this module, students will be able to:

  • Describe how multiple tables may be queried in a SELECT statement using joins
  • Write queries which use inner joins
  • Write queries which use outer joins
  • Write queries which use self-joins and cross joins
Sorting and Filtering Data

This module explains how to enhance queries to limit the rows they return, and to control the order in which the rows are displayed. The module also discusses how to resolve missing and unknown results.

  • Sorting Data
  • Filtering Data
  • Filtering with the TOP and OFFSET-FETCH Options
  • Working with Unknown Values

After completing this module, students will be able to:

  • Filter data with predicates in the WHERE clause
  • Sort data using ORDER BY
  • Filter data in the SELECT clause with TOP
  • Filter data with OFFSET and FETCH
  • Understand and handle NULLs
Working with SQL Server Denali Data Types

This module explains the data types SQL Server uses to store data. It introduces the many types of numeric and special-use data types. It also explains conversions between data types, and the importance of type precedence.

  • Introducing SQL Server Denali Data Types
  • Working with Character Data
  • Working with Date and Time Data

After completing this module, students will be able to:

  • Describe numeric data types, type precedence and type conversions
  • Write queries using character data types
  • Write queries using date and time data types
Using Built-In Functions

This module introduces the use of functions that are built in to SQL Server Denali, and will discuss some common usages including data type conversion, testing for logical results and nullability.

  • Writing Queries with Built-In Functions
  • Using Conversion Functions
  • Using Logical Functions
  • Using Functions to Test for Nullability

After completing this module, students will be able to:

  • Write queries with built-in scalar functions
  • Use conversion functions
  • Use logical functions
  • Use functions that work with NULL
Grouping and Aggregating Data

This module introduces methods for grouping data within a query, aggregating the grouped data and filtering groups with HAVING. The module is designed to help the student grasp why a SELECT clause has restrictions placed upon column naming in the GROUP BY clause as well as which columns may be listed in the SELECT clause.

  • Using Aggregate Functions
  • Using the GROUP BY Clause
  • Filtering Groups with the HAVING Clause

After completing this module, students will be able to:

  • Write queries which summarize data using built-in aggregate functions
  • Use the GROUP BY clause to arrange rows into groups
  • Use the HAVING clause to filter out groups based on a search condition
Using Subqueries

This module will introduce the use of subqueries in various parts of a SELECT statement. It will include the use of scalar and multi-result subqueries, and the use of the IN and EXISTS operators.

  • Writing Self-Contained Subqueries
  • Writing Correlated Subqueries
  • Using the EXISTS Predicate with Subqueries

After completing this module, students will be able to:

  • Use self-contained subqueries, correlated subqueries, scalar subqueries, and multi-valued subqueries
  • Use the IN and EXISTS predicates
Using Table Expressions

This module introduces T-SQL expressions which return a valid relational table, typically for further use in the query. The module discusses views, derived tables, common table expressions and inline table-valued functions.

  • Views
  • Table Expressions

After completing this module, students will be able to:

  • Write queries against views
  • Use derived tables
  • Use common table expressions
  • Use inline table-valued expressions
Use Set Operators

This module introduces operations involving multiple sets of data. It will cover the use of the UNION, UNION ALL, APPLY, CROSS APPLY, OUTER APPLY operators as well as the EXCEPT and INTERSECTS operators.

After completing this module, students will be able to:

  • Describe the difference between a set and a multi-set
  • Use the UNION and UNION ALL operators
  • Use CROSS APPLY, OUTER APPLY and APPLY
  • Use EXCEPT and INTERSECT operators
Using Window Ranking, Offset and Aggregate Functions

This module introduces window functions including ranking, aggregate and offset functions. Much of this functionality is new to SQL Server 2012. It will cover the use of T-SQL functions such as ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE and LAST_VALUE to perform calculations against a set, or window, of rows.

After completing this module, students will be able to:

  • Use window ranking functions ROW_NUMBER, RANK, DENSE_RANK, NTILE
  • Use offset functions LAG, LEAD, FIRST_VALUE, LAST_VALUE
  • Use window aggregate functions
  • Use the basic forms of window partitioning, window ordering and window framing
Pivoting and Grouping Sets

This module discusses techniques for pivoting data in T-SQL as well to introduce the fundamentals of the GROUPING SETS clause. It will also cover the use of GROUP BY ROLLUP and GROUP BY CUBE syntax in SQL Server 2012.

After completing this module, students will be able to:

  • Use the basic form of PIVOT and UNPIVOT operators
  • Use the fundamental form of GROUPING SETS clause
Querying SQL Server Metadata

This module introduces the use of SQL Server system objects in T-SQL queries. It will cover the use of system catalog views, system stored procedures, system functions, and dynamic management objects.

After completing this module, students will be able to:

  • Write queries against system catalog views and functions
  • Execute system stored procedures
  • Identify and query system dynamic management objects
  • Describe security permissions necessary to query system objects
Executing Stored Procedures

This module introduces the use of existing stored procedures in a T-SQL querying environment. It discusses the use of EXECUTE, how to pass input and output parameters to a procedure, and how to invoke system stored procedures.

After completing this module, students will be able to:

  • Invoke existing stored procedures with EXECUTE
  • Pass input parameters and receive output parameters
  • Generate dynamic SQL statements
Programming with T-SQL

This module provides a basic introduction to T-SQL programming concepts and objects. It discusses batches, variables, control of flow elements such as loops and conditionals, how to create and execute dynamic SQL statements, and how to use synonyms.

After completing this module, students will be able to:

  • Understand where T-SQL programming objects can be used
  • Declare variables and delimit batches
  • Use control of flow elements such as IF, WHILE, BEGIN, END, RETURN
  • Use synonyms
Implementing Error Handling

This module introduces the use of error handlers in T-SQL code. It will introduce the difference between compile errors and run-time errors, and will cover how errors affect batches. The module will also cover how to control error handling using TRY/CATCH blocks, the use of the ERROR class of functions, and the use of the new THROW statement

After completing this module, students will be able to:

  • Describe the behavior of SQL Server when errors occur in T-SQL code
  • Use TRY/CATCH blocks to handle error flow
  • Understand ERROR class functions
Implementing Transactions

This module introduces the concepts of transaction management in SQL Server. It will provide a high-level overview of transaction properties, cover the basics of marking transactions with BEGIN, COMMIT and ROLLBACK.

After completing this module, students will be able to:

  • Describe the benefits of using transactions
  • Control transactions with BEGIN, COMMIT, ROLLBACK
  • Describe error handling behavior in transactions
Improving Query Performance

This module introduces the concepts of system resource usage and the performance impact of querying SQL Server 2012. It will cover, at a high level, the use of indexes in SQL Server, the use of execution plans in SQL Server Management Studio, and the use of SET options to view system resource usage when executing queries. It will also compare set-based operations with cursor-based operations.

After completing this module, students will be able to:

  • Describe how SQL Server uses indexes to support query execution
  • Enable the display of execution plans in SQL Server Management Studio
  • Enable system IO and time information in query results
  • Describe the differences between accessing data in sets versus via cursors

[ back to top ]

Microsoft Course 10775

Exam 70-462 - This instructor-led course provides students with the knowledge and skills to maintain a Microsoft SQL Server 2012 database. The course focuses on teaching individuals how to use SQL Server 2012 product features and tools related to maintaining a database. Also this course helps you prepare for the Exam 70-462.

This course utilizes software in the virtual machine for the labs. Microsoft SQL Server 2012 Release Candidate 0 (RC0) is used in this course. Some of the exercises in this course are SQL Azure enabled.

Below are the some of the new features of SQL Server that has been discussed in this course

Discussion around servicing SQL Server (Service Packs, Cumulative Updates, Hotfixes)

  • Understanding the relationship between the different levels of updates to the SQL Server product is very important for those working with it. The course now covers details on how hotfixes, cumulative updates and service packs are related and when to apply each type of update.

SQL Server use of automated update

  • SQL Server can now utilize Windows Update for servicing. The course shows how to enable automated updates and when the use of these updates should and should not be used.

Partial database containment

  • A significant challenge when migrating databases between servers is the management of objects such as logins that are not contained within the database. The course now discusses the partial containment options that are provided by SQL Server 2012. In particular, authentication-related changes are discussed.

Users with passwords (contained users)

  • The course shows how SQL Server 2012 allows users to be authenticated at the database level, rather than at the server level. This is an important first step in for database containment.

User-defined server roles

  • In earlier versions of SQL Server, user-defined roles could be created at the database level but not at the server level. In SQL Server 2012, user-defined roles can also be defined at the server level. This can help to increase the security of systems. Having the ability to configure more fine-grained permissions lets logins be assigned only the permissions that they need to do their work.

In-place upgrades of data-tier applications

  • In earlier versions of SQL Server, an upgrade of a data-tier application involved migrating all the data within a database to a new database. In SQL Server 2012 this is no longer needed and the course shows how data-tier applications can be upgraded much more quickly, and using less system resources.

Appendix with intro to AlwaysOn, High Availability and Replication concepts

  • Students who will be attempting the certification exam that is relevant to this course require knowledge of high availability and replication. In earlier versions of this course, none of these details were covered. In this version of the course, an appendix that covers the core concepts for high availability (including SQL Server 2012 AlwaysOn technologies) and replication is provided.

Audience Profile

The primary audience for this course is individuals who administer and maintain SQL Server databases. These individuals perform database administration and maintenance as their primary area of responsibility, or work in environments where databases play a key role in their primary job.

The secondary audiences for this course are individuals who develop applications that deliver content from SQL Server databases.

At Course Completion

After completing this course, students will be able to:

  • Plan and install SQL Server.
  • Describes the system databases, the physical structure of databases and the most common configuration options related to them.
  • Explain the concept of the transaction log and SQL Server recovery models and implement different backup strategies available with SQL Server.
  • Create SQL Server Backups.
  • Restore SQL Server databases.
  • Use the import/export wizards and explain how they relate to SSIS.
  • Work with SQL Server security models, logins and users.
  • Work with fixed server roles, user-defined server roles, fixed database roles and user-defined database roles.
  • Work with permissions and the assignment of permissions.
  • Work with SQL Server Audit.
  • Work with SQL Server Agent, jobs and job history.
  • Implement SQL Server agent security, proxy accounts and credentials.
  • Configure database mail, alerts and notifications.
  • Create database maintenance plans.
  • Work with SQL Profiler and SQL Trace stored procedures.
  • Introduce DMVs and the configuration of data collection.
  • Work with Central Management Servers and Multi-Server queries, Virtualization of SQL Server and Data-Tier Applications.
  • Troubleshoot SQL Server databases.

Prerequisites

In addition to their professional experience, students who attend this training should already have the following technical knowledge:

  • Basic knowledge of the Microsoft Windows operating system and its core functionality.
  • Working knowledge of Transact-SQL.
  • Working knowledge of relational databases.
  • Some experience with database design.
  • Students who attend this training can meet the prerequisites by attending the following courses, or obtaining equivalent knowledge and skills:
  • 10774A: Writing T-SQL Queries for Microsoft SQL Server 2012

Course Outline

Introduction to SQL Server 2012 and its Toolset

This module introduces the entire SQL Server platform and its major tools. It covers editions, versions, basics of network listeners, and concepts of services and service accounts.

  • Introduction to the SQL Server Platform
  • Working with SQL Server Tools
  • Configuring SQL Server Services

After completing this module, students will be able to:

  • Describe the SQL Server Platform.
  • Work with SQL Server Tools.
  • Configure SQL Server Services.
Preparing Systems for SQL Server 2012

This module covers planning for an installation related to SQL Server I/O requirements, 32 bit vs 64 bit, memory configuration options and I/O subsystem pre-installation checks using SQLIOSim and SQLIO.

  • Overview of SQL Server Architecture
  • Planning Server Resource Requirements
  • Pre-installation Testing for SQL Server

After completing this module, students will be able to:

  • Describe the SQL Server architecture.
  • Plan for server resource requirements.
  • Conduct pre-installation stress testing for SQL Server.
Installing and Configuring SQL Server 2012

This module details installing and configuring SQL Server.

  • Preparing to Install SQL Server
  • Installing SQL Server
  • Upgrading and Automating Installation

After completing this module, students will be able to:

  • Prepare to install SQL Server.
  • Install SQL Server.
  • Upgrade and automate the installation of SQL Server.
Working with Databases

This module describes how data is stored in databases, how to create databases, and how to move databases either within a server or between servers.

  • Overview of SQL Server Databases
  • Working with Files and Filegroups
  • Moving Database Files

After completing this module, students will be able to:

  • Describe the role and structure of SQL Server databases.
  • Work with files and filegroups.
  • Move database files within servers and between servers.
Understanding SQL Server 2012 Recovery Models

This module describes the concept of the transaction log and SQL Server recovery models. It introduces the different backup strategies available with SQL Server.

  • Backup Strategies
  • Understanding SQL Server Transaction Logging
  • Planning a SQL Server Backup Strategy

After completing this module, students will be able to:

  • Describe the critical concepts surrounding backup strategies.
  • Explain the transaction logging capabilities within the SQL Server database engine.
  • Plan a SQL Server backup strategy.
Backup of SQL Server 2012 Databases

This module describes SQL Server Backup and the backup types.

  • Backing up Databases and Transaction Logs
  • Managing Database Backups
  • Working with Backup Options

After completing this module, students will be able to:

  • Back up databases and transaction logs.
  • Manage database backups.
  • Work with more advanced backup options.
Restoring SQL Server 2012 Databases

This module describes the restoration of databases.

  • Understanding the Restore Process
  • Restoring Databases
  • Working with Point-in-time recovery
  • Restoring System Databases and Individual Files

After completing this module, students will be able to:

  • Understand the restore process.
  • Restore databases.
  • Work with Point-in-time Recovery.
  • Restore system databases and individual files.
Importing and Exporting Data

This module covers the use of the import/export wizards and explains how they relate to SSIS. Also introduces BCP.

  • Transferring Data To/From SQL Server
  • Importing & Exporting Table Data
  • Inserting Data in Bulk

After completing this module, students will be able to:

  • Transfer data to and from SQL Server.
  • Import and export table data.
  • Insert data in bulk and optimize the bulk insert process.
Authenticating and Authorizing Users

This module covers SQL Server security models, logins and users.

  • Authenticating Connections to SQL Server
  • Authorizing Logins to Access Databases
  • Authorization Across Servers

After completing this module, students will be able to:

  • Describe how SQL Server authenticates connections.
  • Describe how logins are authorized to access databases.
  • Explain the requirements for authorization across servers.
Assigning Server and Database Roles

This module covers fixed server roles, user-defined server roles, fixed database roles and user-defined database roles.

  • Working with Server Roles
  • Working with Fixed Database Roles
  • Creating User-defined Database Roles

After completing this module, students will be able to:

  • Work with server roles.
  • Work with fixed database roles.
  • Create user-defined database roles.
Authorizing Users to Access Resources

This module covers permissions and the assignment of permissions.

  • Authorizing User Access to Objects
  • Authorizing Users to Execute Code
  • Configuring Permissions at the Schema Level

After completing this module, students will be able to:

  • Authorize user access to objects.
  • Authorize users to execute code.
  • Configure permissions at the schema level.
Auditing SQL Server Environments

This module covers SQL Server Audit.

  • Options for Auditing Data Access in SQL
  • Implementing SQL Server Audit
  • Managing SQL Server Audit

After completing this module, students will be able to:

  • Describe the options for auditing data access in SQL Server.
  • Implement SQL Server Audit.
  • Manage SQL Server Audit.
Automating SQL Server 2012 Management

This module covers SQL Server Agent, jobs and job history.

  • Automating SQL Server Management
  • Working with SQL Server Agent
  • Managing SQL Server Agent Jobs

After completing this module, students will be able to:

  • Automate SQL Server Management.
  • Work with SQL Server Agent.
  • Manage SQL Server Agent jobs.
Configuring Security for SQL Server Agent

This module covers SQL Server agent security, proxy accounts and credentials.

  • Understanding SQL Server Agent Security
  • Configuring Credentials
  • Configuring Proxy Accounts

After completing this module, students will be able to:

  • Explain SQL Server Agent security.
  • Configure credentials.
  • Configure Proxy accounts.
Monitoring SQL Server 2012 with Alerts and Notifications

This module covers the configuration of database mail, alerts and notifications.

  • Configuration of Database Mail
  • Monitoring SQL Server Errors
  • Configuring Operators, Alerts and Notifications

After completing this module, students will be able to:

  • Configure database mail.
  • Monitor SQL Server errors.
  • Configure operators, alerts and notifications.
Performing Ongoing Database Maintenance

This module covers database maintenance plans.

  • Ensuring Database Integrity
  • Maintaining Indexes
  • Automating Routine Database Maintenance

After completing this module, students will be able to:

  • Ensure database integrity.
  • Maintain indexes.
  • Automate routine database maintenance.
Tracing Access to SQL Server 2012

This module covers SQL Profiler and SQL Trace stored procedures.

  • Capturing Activity using SQL Server Profiler
  • Improving Performance with the Database Engine Tuning Advisor
  • Working with Tracing Options

After completing this module, students will be able to:

  • Capture activity using SQL Server Profiler and Extended Events Profiler.
  • Improve performance with the Database Engine Tuning Advisor.
  • Work with tracing options.
Monitoring SQL Server 2012

This module introduces DMVs and the configuration of data collection.

  • Monitoring Activity
  • Capturing and Managing Performance Data
  • Analyzing Collected Performance Data

After completing this module, students will be able to:

  • Monitor current activity.
  • Capture and manage performance data.
  • Analyze collected performance data.
Managing Multiple Servers

This module covers Central Management Servers and Multi-Server queries, Virtualization of SQL Server and Data-Tier Applications.

  • Working with Multiple Servers
  • Virtualizing SQL Server
  • Deploying and Upgrading Data-Tier Applications

After completing this module, students will be able to:

  • Work with multiple servers.
  • Describe options for virtualizing SQL Server.
  • Deploy and upgrade Data-Tier Applications.
Troubleshooting Common SQL Server 2012 Administrative Issues

This module covers common issues that require troubleshooting and gives guidance on where to start looking for solutions.

  • SQL Server Troubleshooting Methodology
  • Resolving Service-related Issues
  • Resolving Concurrency Issues
  • Resolving Login and Connectivity Issues

After completing this module, students will be able to:

  • Explain SQL Server troubleshooting methodology.
  • Resolve service-related issues.
  • Resolve concurrency issues.
  • Resolve login and connectivity issues.

[ back to top ]

Microsoft Course 10777

Exam 70-463 - This instructor-led course describes how to implement a BI platform to support information worker analytics. Students will learn how to create a data warehouse with SQL Server 2012, implement ETL with SQL Server Integration Services, and validate and cleanse data with SQL Server Data Quality Services and SQL Server Master Data Services.

This course helps students prepare for Exam 70-463.

Audience Profile

The primary audience for this course are database professionals who need to fulfil a Business Intelligence Developer role. They will need to focus on hands-on work creating BI solutions including Data Warehouse implementation, ETL, and data cleansing. Primary responsibilities will include:

  • Implementing as data warehouse
  • Developing SSIS packages for data extraction and loading/transfer/transformation
  • Enforcing data integrity using Master Data Services
  • Cleansing data using Data Quality Services

At Course Completion

After completing this course, students will be able to:

  • Describe data warehouse concepts and architecture considerations.
  • Select an appropriate hardware platform for a data warehouse.
  • Design and implement a data warehouse.
  • Implement Data Flow in an SSIS Package.
  • Implement Data Flow in an SSIS Package.
  • Debug and Troubleshoot SSIS packages.
  • Implement an SSIS solution that supports incremental DW loads and changing data.
  • Integrate cloud data into a data warehouse ecosystem infrastructure.
  • Implement data cleansing by using Microsoft Data Quality Services.
  • Implement Master Data Services to enforce data integrity at source.
  • Extend SSIS with custom scripts and components.
  • Deploy and Configure SSIS packages.
  • Describe how information workers can consume data from the data warehouse.

Course Outline

Module 1: Introduction to Data Warehousing

This module provides an introduction to the key components of a data warehousing solution and the high-level considerations you must take into account when embarking on a data warehousing project.

  • Describe data warehouse concepts and architecture considerations
  • Considerations for a Data Warehouse Solution

Describe data warehouse concepts and architecture considerations.

Module 2: Data Warehouse Hardware Considerations

This module describes the considerations for selecting the appropriate hardware platform for your data warehouse solution.

  • The Challenges of Building a Data Warehouse
  • Data Warehouse Reference Architectures
  • Data Warehouse Appliances

Select an appropriate hardware platform for a data warehouse.

Module 3: Designing and Implementing a Data Warehouse

This module describes how to implement the logical and physical architecture of a data warehouse based on industry proven design principles.

  • Logical Design for a Data Warehouse
  • Physical Design for a Data Warehouse

Design and implement a schema for a data warehouse.

Module 4: Design and implement a schema for a data warehouse

This module discusses considerations for implementing an ETL process, and then focuses on SQL Server Integration Services (SSIS) as a platform for building ETL solutions.

  • Introduction to ETL with SSIS
  • Exploring Source Data
  • Implementing Data Flow

Implement Data Flow in an SSIS Package

Module 5: Implementing Control Flow in an SSIS Package

This module describes how to implement control flow which allows users to design robust ETL processes for a data warehousing solution that coordinate data flow operations with other automated tasks.

  • Introduction to Control Flow
  • Creating Dynamic Packages
  • Using Containers
  • Managing Consistency

Implement control flow in an SSIS package.

Module 6: Debugging and Troubleshooting SSIS Packages

This module describes how you can debug packages to find the cause of errors that occur during execution. It then discusses the logging functionality built into SSIS that you can use to log events for troubleshooting purposes. Finally, the module describes common approaches for handling errors in control flow and data flow.

  • Debugging an SSIS Package
  • Logging SSIS Package Events
  • Handling Errors in an SSIS Package

Debug and Troubleshoot SSIS packages.

Module 7: Implementing an Incremental ETL Process

This module describes the techniques you can use to implement an incremental data warehouse refresh process.

  • Introduction to Incremental ETL
  • Extracting Modified Data
  • Loading Modified Data

Implement an SSIS solution that supports incremental DW loads and changing data.

Module 8: Incorporating Data from the Cloud in a Data Warehouse

This modules describes how integrate cloud data into a data warehouse ecosystem.

  • Overview of Cloud Data Sources
  • SQL Server Azure
  • Azure Data Market

Integrate cloud data into a data warehouse ecosystem.

Module 9: Enforcing Data Quality

This modules describes how to use Data Quality Services (DQS) for cleansing and deduplicating your data.

  • Introduction to Data Cleansing
  • Using Data Quality Services to Cleanse Data
  • Using Data Quality Services to Match Data

Implement data cleansing by using Microsoft Data Quality Services.

Module 10: Using Master Data Services

This module introduces Master Data Services and explains the benefits of using it in a business intelligence (BI) context. It also describes the key configuration options, explains how to import and export data and apply rules that help to preserve data integrity, and introduces the new Master Data Services Add-in for Excel.

  • Master Data Services Concepts
  • Implementing a Master Data Services Model
  • Using the Master Data Services Excel Add-in

Implement Master Data Services to enforce data integrity at source.

Module 11: Extending SSIS

This module describes how to extend SSIS by using custom scripts and components.

  • Using Custom Components in SSIS
  • Using Scripting in SSIS

Extend SSIS with custom scripts and components

Module 12: Deploying and Configuring SSIS Packages

This modules describes how to deploy and configure SSIS packages.

  • Overview of Deployment
  • Deploying SSIS Projects
  • Planning SSIS Package Execution

Deploy and configure SSIS packages.

Module 13: Consuming Data in a Data Warehouse

This module describes how information workers can consume data from the data warehouse.

  • Using Excel to Analyze Data in a data Warehouse.
  • An Introduction to PowerPivot
  • An Introduction to Crescent

Describe how information workers can consume data from the data warehouse.

[ back to top ]


CED Solutions is your best choice for MCSA SQL Server 2012, MCSA SQL Server 2012 training, MCSA SQL Server 2012 certification, MCSA SQL Server 2012 boot camp, MCSA SQL Server 2012 certification training, MCSA SQL Server 2012 certification course, MCSA SQL Server 2012 course, MCSA SQL Server 2012 class.



MCSA SQL Server 2012 space
Search classes by keyword:
Search classes by category:


Copyright © 2014 CED Solutions. CED Solutions Refund Policy. All Rights Reserved.