close

close

Thank you for your enquiry!

One of our training experts will be in touch shortly to go over your training requirements.

close

close

Press esc to close

close close

Back to course information

Thank you for your enquiry!

One of our training experts will be in touch shortly to go overy your training requirements.

close close

Thank you for your enquiry!

One of our training experts will be in touch shortly to go over your training requirements.

Microsoft SQL Server Training courses

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (5 days)

Online Self-paced (40 hours)

Querying Microsoft SQL Server 2014 (M20461) Outline

Module 1: Introduction to Microsoft SQL Server 2014

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.

 

Lessons

  • The Basic Architecture of SQL Server

  • SQL Server Editions and Versions

  • Getting Started with SQL Server Management Studio

Lab : Working with SQL Server 2014 Tools

After completing this module, you will be able to:

  • Describe the architecture and editions of SQL Server 2012.

  • Work with SQL Server Management Studio.

 

Module 2: Introduction to T-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.

 

Lessons

  • Introducing T-SQL

  • Understanding Sets

  • Understanding Predicate Logic

  • Understanding the Logical Order of Operations in SELECT statements

Lab : Introduction to Transact-SQL Querying

After completing this module, you will be able to:

  • Describe the elements of T-SQL and their role in writing queries

  • Describe the use of sets in SQL Server

  • Describe the use of predicate logic in SQL Server

  • Describe the logical order of operations in SELECT statements

 

Module 3: Writing SELECT Queries

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

 

Lessons

  • Writing Simple SELECT Statements

  • Eliminate Duplicates with DISTINCT

  • Using Column and Table Aliases

  • Write Simple CASE Expressions

Lab : Writing Basic SELECT Statements

After completing this module, you will be able to:

  • Write simple SELECT statements.

  • Eliminate duplicates using the DISTINCT clause.

  • Use column and table aliases.

  • Write simple CASE expressions.

 

Module 4: 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.

 

Lessons

  • Understanding Joins

  • Querying with Inner Joins

  • Querying with Outer Joins

  • Querying with Cross Joins and Self Joins

Lab : Querying Multiple Tables

After completing this module, you will be able to:

  • Describe how multiple tables may be queried in a SELECT statement using joins.

  • Write queries that use inner joins.

  • Write queries that use outer joins.

  • Write queries that use self-joins and cross joins.

 

Module 5: 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.

 

Lessons

  • Sorting Data

  • Filtering Data with Predicates

  • Filtering with the TOP and OFFSET-FETCH

  • Working with Unknown Values

Lab : Sorting and Filtering Data

After completing this module, you 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.

 

Module 6: Working with SQL Server 2014 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.

 

Lessons

  • Introducing SQL Server 2014 Data Types

  • Working with Character Data

  • Working with Date and Time Data

Lab : Working with SQL Server 2014 Data Types

After completing this module, you 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.

 

Module 7: Using DML to Modify Data

This module describes the use of Transact-SQL Data Manipulation Language to perform inserts, updates, and deletes to your data.

 

Lessons

  • Inserting Data

  • Modifying and Deleting Data

Lab : Using DML to Modify Data

After completing this module, you will be able to:

  • Insert new data into your tables.

  • Update and delete existing records in your tables.

 

Module 8: 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.

 

Lessons

  • Writing Queries with Built-In Functions

  • Using Conversion Functions

  • Using Logical Functions

  • Using Functions to Work with NULL

Lab : Using Built-In Functions

After completing this module, you will be able to:

  • Write queries with built-in scalar functions.

  • Use conversion functions.

  • Use logical functions.

  • Use functions that work with NULL.

 

Module 9: 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.

 

Lessons

  • Using Aggregate Functions

  • Using the GROUP BY Clause

  • Filtering Groups with HAVING

Lab : Grouping and Aggregating Data

After completing this module, you 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.

 

Module 10: 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.

 

Lessons

  • Writing Self-Contained Subqueries

  • Writing Correlated Subqueries

  • Using the EXISTS Predicate with Subqueries

Lab : Using Subqueries

After completing this module, you will be able to:

  • Describe the uses of queries which are nested within other queries.

  • Write self-contained subqueries which return scalar or multi-valued results.

  • Write correlated subqueries which return scalar or multi-valued results.

  • Use the EXISTS predicate to efficiently check for the existence of rows in a subquery.

 

Module 11: 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.

 

Lessons

  • Using Views

  • Using Inline Table-Valued Functions

  • Using Derived Tables

  • Using Common Table Expressions

Lab : Using Table Expressions

After completing this module, you will be able to:

  • Write queries which use derived tables.

  • Write queries which use common table expressions.

  • Create simple views and write queries against them.

  • Create simple inline table-valued functions and write queries against them.

 

Module 12: Using Set Operators

This module introduces the set operators UNION, INTERSECT, and EXCEPT to compare rows between two input sets.

 

Lessons

  • Writing Queries with the UNION Operator

  • Using EXCEPT and INTERSECT

  • Using APPLY

Lab : Using Set Operators

After completing this module, you will be able to:

  • Write queries which combine data using the UNION operator

  • Write queries which compare sets using the INTERSECT and EXCEPT operators

  • Write queries which manipulate rows in a table by using APPLY with the results of a derived table or function

 

Module 13: 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.

 

Lessons

  • Creating Windows with OVER

  • Exploring Window Functions

Lab : Using Window Ranking, Offset and Aggregate Functions

After completing this module, you will be able to:

  • Describe the benefits to using window functions.

  • Restrict window functions to rows defined in an OVER clause, including partitions and frames.

  • Write queries which use window functions to operate on a window of rows and return ranking, aggregation and offset comparison results.

 

Module 14: 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.

 

Lessons

  • Writing Queries with PIVOT and UNPIVOT

  • Working with Grouping Sets

Lab : Pivoting and Grouping Sets

After completing this module, you will be able to:

  • Write queries which pivot and unpivot result sets.

  • Write queries which specify multiple groupings with grouping sets.

 

Module 15: Querying data with 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.

 

Lessons

  • Writing Queries with PIVOT and UNPIVOT

  • Passing Parameters to Stored Procedures

  • Creating Simple Stored Procedures

  • Working with Dynamic SQL

Lab : Executing Stored Procedures

After completing this module, you will be able to:

  • Return results by executing stored procedures.

  • Pass parameters to procedures.

  • Create simple stored procedures which encapsulate a SELECT statement.

  • Construct and execute dynamic SQL with EXEC and sp_executesql.

 

Module 16: 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.

 

Lessons

  • T-SQL Programming Elements

  • Controlling Program Flow

Lab : Programming with T-SQL

After completing this module, you will be able to:

  • Describe the language elements of T-SQL used for simple programming tasks.

  • Describe batches and how they are handled by SQL Server.

  • Declare and assign variables and synonyms.

  • Use IF and WHILE blocks to control program flow.

 

Module 17: 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.

 

Lessons

  • Using TRY / CATCH Blocks

  • Working with Error Information

Lab : Implementing Error Handling

After completing this module, you will be able to:

  • Describe SQL Server's behavior when errors occur in T-SQL code.

  • Implement structured exception handling in T-SQL.

  • Return information about errors from system objects.

  • Raise user-defined errors and pass system errors in T-SQL code.

 

Module 18: 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.

 

Lessons

  • Transactions and the Database Engine

  • Controlling Transactions

Lab : Implementing Transactions

After completing this module, you will be able to:

  • Describe transactions and the differences between batches and transactions.

  • Describe batches and how they are handled by SQL Server.

  • Create and manage transactions with transaction control language statements.

  • Use SET XACT_ABORT to define SQL Server's handling of transactions outside TRY / CATCH blocks.

  • Describe the effects of isolation levels on transactions.

 

Module 19: Appendix 1: Improving Query Performance

This module presents several key guidelines for writing well-performing queries, as well as ways to monitor the execution of your queries and their impact on Microsoft SQL Server.

 

Lessons

  • Factors in Query Performance

  • Displaying Query Performance Data

Lab : Improving Query Performance

After completing this module, you will be able to:

  • Describe components of well-performing queries.

  • Display and interpret basic query performance data

 

Module 20: Appendix 2: Querying SQL Server Metadata

SQL Server provides access to structured metadata by using a variety of mechanisms, such as system catalog views, system functions, dynamic management objects, and system stored procedures. In this module, you will learn how to write queries to return system metadata using these mechanisms.

 

Lessons

  • Querying System Catalog Views and Functions

  • Executing System Stored Procedures

  • Querying Dynamic Management Objects

Lab : Querying SQL Server Metadata

After completing this module, you will be able to:

  • Write queries that retrieve system metadata using system views and functions.

  • Execute system stored procedures to return system information.

  • Write queries that retrieve system metadata and state information using system dynamic management views and functions.

Show moredown

Who Should Attend?

This course is suitable for Database Administrators, Database Developers, and Business Intelligence professionals. The course is ideal for SQL power users who aren’t necessarily database-focused or plan on taking the exam, for  example  report writers, business analysts and client application developers.

 

Prerequisites

  • Working knowledge of relational databases

  • Basic knowledge of the Microsoft Windows operating system and its core functionality

Querying Microsoft SQL Server 2014 (M20461) Overview

Our 5-day Querying Microsoft SQL Server 2014 course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2014. This course is the foundation for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence.

 

 

Show moredown

Querying Microsoft SQL Server 2014 (M20461) Exam

It is required for delegates to take the 70-461 exam and pass in order to complete this course. The topics covered in the exam are:

  • Create database objects (20-25%)

  • Work with data (25-30%)

  • Modify data (20-25%)

  • Troubleshoot and optimise data ( 25-30%)

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (5 days)

Online Self-paced (40 hours)

Administering Microsoft SQL Server 2014 Databases (M20462) Outline

Module 1: Introduction to SQL Server 2014 Database Administration

This module introduces the Microsoft SQL Server 2014 platform. It describes the components, editions, and versions of SQL Server 2014, and the tasks that a database administrator commonly performs for a SQL Server instance.

 

Lessons

  • Database Administration Overview

  • Introduction to the SQL Server Platform

  • Database Management Tools and Techniques

 

Lab : Using SQL Server Administrative Tools

After completing this module, you will be able to:

  • Describe the SQL Server platform.

  • Describe common database administration tasks.

  • Use SQL Server administration tools.

 

Module 2: Installing and Configuring SQL Server 2014

This module explains how to assess resource requirements for SQL Server 2014 and how to install it.

 

Lessons

  • Planning SQL Server Installation

  • Installing SQL Server 2014

  • Post-Installation Configuration

 

Lab : Installing SQL Server 2014

After completing this module, you will be able to:

  • Plan a SQL Server 2014 installation.

  • Install SQL Server 2014.

  • Perform post-installation configuration tasks.

 

Module 3: Working with Databases and Storage

This module describes how data is stored in databases, how to create databases, how to manage database files, and how to move them. Other tasks related to storage, include managing the tempdb database and using fast storage devices to extend the SQL Server buffer pool cache, are also discussed.

 

Lessons

  • Introduction to Data Storage with SQL Server

  • Managing Storage for System Databases

  • Managing Storage for User Databases

  • Moving Database Files

  • Configuring the Buffer Pool Extension

 

Lab : Managing Database Storage

After completing this module, you will be able to:

  • Describe how SQL Server stores data.

  • Manage storage for system databases.

  • Manage storage for user databases.

  • Move database files.

  • Configure the buffer pool extension.

 

Module 4: Planning and Implementing a Backup Strategy

In this module, you will consider how to create a backup strategy that is aligned with organizational needs, and learn how to perform the backup operations required by that strategy.

 

Lessons

  • Understanding SQL Server Recovery Models

  • Planning a Backup Strategy

  • Backing up Databases and Transaction Logs

  • Using Backup Options

  • Ensuring Backup Reliability

 

Lab : Backing Up SQL Server Databases

After completing this module, you will be able to:

  • Describe how database transaction logs function, and how they affect database recovery.

  • Plan a backup strategy for a SQL Server database.

  • Back up databases and transactions logs.

  • Perform copy-only, compressed, and encrypted backups.

  • Maximize backup reliability.

 

Module 5: Restoring SQL Server 2014 Databases

In this module, you will see how to restore user and system databases and how to implement point-in-time recovery.

 

Lessons

  • Understanding the Restore Process

  • Restoring Databases

  • Advanced Restore Scenarios

  • Working with Point-in-Time Recovery

 

Lab : Restoring SQL Server Databases

After completing this module, you will be able to:

  • Explain the restore process.

  • Restore databases.

  • Perform advanced restore operations.

  • Perform a point-in-time recovery.

 

Module 6: Importing and Exporting Data

In this module, you will briefly explore tools and techniques so that you can import and export data to and from SQL Server.

 

Lessons

  • Introduction to Transferring Data

  • Importing and Exporting Table Data

  • Copying or Moving a Database

 

Lab : Importing and Exporting Data

After completing this module, you will be able to:

  • Describe tools and techniques for transferring data.

  • Import and export data.

  • Copy or move a database.

 

Module 7: Monitoring SQL Server 2014

This module explains how to use three of the most commonly used tools: Activity Monitor, dynamic management views and functions (DMVs and DMFs), and Performance Monitor.

 

Lessons

  • Introduction to Monitoring SQL Server

  • Dynamic Management Views and Functions

  • Performance Monitor

 

Lab : Monitoring SQL Server 2014

After completing this module, you will be able to:

  • Describe considerations for monitoring SQL Server and use Activity Monitor.

  • Use dynamic management views and functions to monitor SQL Server.

  • Use Performance Monitor to monitor SQL Server.

 

Module 8: Tracing SQL Server Activity

This module describes how to use SQL Server Profiler and SQL Trace stored procedures to capture information about SQL Server, and how to use that information to troubleshoot and optimize SQL Server workloads.

 

Lessons

  • Tracing SQL Server Workload Activity

  • Using Traces

 

Lab : Tracing SQL Server Workload Activity

After completing this module, you will be able to:

  • Trace activity in SQL Server

  • Use captured traces to test, troubleshoot, and optimize database performance.

 

Module 9: Managing SQL Server Security

In this module, you will be learn about the core concepts on which the SQL Server security architecture is based, and how to manage security at the server and database levels.

 

Lessons

  • Introduction to SQL Server Security

  • Managing Server-Level Security

  • Managing Database-Level Principals

  • Managing Database Permissions

 

Lab : Managing SQL Server Security

After completing this module, you will be able to:

  • Describe core security concepts in the SQL Server security architecture.

  • Manage server-level security.

  • Manage database-level security principals.

  • Manage database permissions.

 

Module 10: Auditing Data Access and Encrypting Data

This module describes the available options for auditing in SQL Server, how to use and manage the SQL Server audit feature, and how to implement encryption.

 

Lessons

  • Auditing Data Access in SQL Server

  • Implementing SQL Server Audit

  • Implementing SQL Server Audit

 

Lab : Auditing Data Access and Encrypting Data

After completing this module, you will be able to:

  • Describe options for auditing data access.

  • Implement SQL Server audit.

  • Manage SQL Server audit.

  • Implement Transparent Data Encryption.

 

Module 11: Performing Ongoing Database Maintenance

This module describes common database maintenance tasks that a DBA must perform, and demonstrates how to automate these tasks using maintenance plans.

 

Lessons

  • Ensuring Database Integrity

  • Maintaining Indexes

  • Automating Routine Database Maintenance

 

Lab : Performing Ongoing Database Maintenance

After completing this module, you will be able to:

  • Ensure database integrity by using DBCC CHECKDB.

  • Maintain indexes.

  • Configure Database Maintenance Plans.

 

Module 12: Automating SQL Server 2014 Management

This module describes how to use SQL Server Agent to automate jobs, how to configure security contexts for jobs, and how to implement multi-server jobs.

 

Lessons

  • Automating SQL Server Management

  • Implementing SQL Server Agent Jobs

  • Managing SQL Server Agent Jobs

  • Managing Job Step Security Contexts

  • Managing Jobs on Multiple Servers

 

Lab : Automating SQL Server Management

After completing this module, you will be able to:

  • Describe methods for automating SQL Server management.

  • Create jobs, job step types, and schedules.

  • Manage SQL Server Agent jobs.

  • Configure job security contexts.

  • Configure master and target servers.

 

Module 13: Monitoring SQL Server 2014 by Using Alerts and Notifications

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

 

Lessons

  • Monitoring SQL Server Errors

  • Configuring Database Mail

  • Configuring Operators, Alerts, and Notifications

 

Lab : Monitoring SQL Server by Using Alerts and Notifications

After completing this module, you will be able to:

  • Configure Database Mail.

  • Monitor SQL Server errors.

  • Configure operators, alerts, and notifications

Show moredown

Who should attend?

The course is intended for those who administer and maintain SQL Server databases as part of their daily responsibilities. It is also suitable for those who develop applications which deliver content from SQL Server databases.

 

Prerequisites

You should have the following knowledge and experience before attending:

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

 

Administering Microsoft SQL Server 2014 Databases (M20462)​ Overview

This 5-day official Microsoft course teaches how to maintain a Microsoft SQL Server 2014 database. It provides knowledge and skills for using SQL Server products and tools, as well as features new to SQL Server 2014 in particular. 

Show moredown

Administering Microsoft SQL Server 2014 Databases (M20462)​ Exam

This course prepares you for Exam 70-462: Administering Microsoft SQL Server 2012/2014 Databases. This exam tests ability to carry out technical tasks in the following areas:

  • Install and configure

  • Maintain instances and databases

  • Optimise and troubleshoot

  • Manage data

  • Implement security

  • Implement high availability

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (5 days)

Online Self-paced (40 hours)

Querying Data with Transact-SQL Outline

Module 1: Introduction to Microsoft SQL Server 2016

  • The Basic Architecture of SQL Server

  • SQL Server Editions and Versions

  • Getting Started with SQL Server Management Studio

  • Lab: Working with SQL Server 2016 Tools

    • Working with SQL Server Management Studio

    • Creating and Organizing T-SQL Scripts

    • Using Books Online

 

Module 2: Introduction to T-SQL Querying

  • Introducing T-SQL

  • Understanding Sets

  • Writing Queries

  • Understanding Predicate Logic

  • Understanding the Logical Order of Operations in SELECT statements

  • Lab: Introduction to T-SQL Querying

    • Executing Basic SELECT Statements

    • Executing Queries that Filter Data using Predicates

    • Executing Queries That Sort Data Using ORDER BY

 

Module 3: Writing SELECT Queries

  • Writing Simple SELECT Statements

  • Eliminating Duplicates with DISTINCT

  • Using Column and Table Aliases

  • Writing Simple CASE Expressions

  • Lab: Writing Basic SELECT Statements

    • Writing Simple SELECT Statements

    • Eliminating Duplicates Using DISTINCT

    • Using Column and Table Aliases

    • Using a Simple CASE Expression

 

Module 4: Querying Multiple Tables

  • Understanding the Fundamentals of Joins

  • Querying with Inner Joins

  • Querying with Outer Joins

  • Querying with Cross Joins and Self Joins

  • Lab: Querying Multiple Tables

    • Writing Queries that use Inner Joins

    • Writing Queries that use Multiple-Table Inner Joins

    • Writing Queries that use Self-Joins

    • Writing Queries that use Outer Joins

    • Writing Queries that use Cross Joins

 

Module 5: Sorting and Filtering Data

  • Sorting Data

  • Filtering Data with Predicates

  • Filtering Data with TOP and OFFSET-FETCH

  • Working with Unknown Values

  • Lab: Sorting and Filtering Data

    • Writing Queries that Filter Data using a WHERE Clause

    • Writing Queries that Sort Data Using an ORDER BY Clause

    • Writing Queries that Filter Data Using the TOP Option

 

Module 6: Working with SQL Server 2016 Data Types

  • Introducing SQL Server 2016 Data Types

  • Working with Character Data

  • Working with Date and Time Data

  • Lab: Working with SQL Server 2016 Data Types

    • Writing Queries that Return Date and Time Data

    • Writing Queries that use Date and Time Functions

    • Writing Queries That Return Character Data

    • Writing Queries That Return Character Functions

 

Module 7: Using DML to Modify Data

  • Inserting Data

  • Creating DML Queries

  • Modifying and Deleting Data

  • Lab: Using DML to Modify Data

    • Inserting Data

    • Updating, Merging, and Deleting Data

 

Module 8: Using Built-In Functions

  • Writing Queries with Built-In Functions

  • Using Conversion Functions

  • Using Logical Functions

  • Using Functions to Work with NULL

  • Lab: Using Built-In Functions

    • Writing Queries That Use Conversion Functions

    • Writing Queries that use Logical Functions

    • Writing Queries that Test for Nullability

 

Module 9: Grouping and Aggregating Data

  • Using Aggregate Functions

  • Using the GROUP BY Clause

  • Filtering Groups with HAVING

  • Lab: Grouping and Aggregating Data

    • Writing Queries That Use the GROUP BY Clause

    • Writing Queries that Use Aggregate Functions

    • Writing Queries that Use Distinct Aggregate Functions

    • Writing Queries that Filter Groups with the HAVING Clause

 

Module 10: Using Subqueries

  • Writing Self-Contained Subqueries

  • Writing Correlated Subqueries

  • Using the EXISTS Predicate with Subqueries

  • Lab: Using Subqueries

    • Writing Queries That Use Self-Contained Subqueries

    • Writing Queries That Use Scalar and Multi-Result Subqueries

    • Writing Queries That Use Correlated Subqueries and an EXISTS Clause

 

Module 11: Using Table Expressions

  • Using Views

  • Using Inline Table-Valued Functions (TVFs)

  • Using Derived Tables

  • Using Common Table Expressions (CTEs)

  • Lab: Using Table Expressions

    • Writing Queries That Use Views

    • Writing Queries That Use Derived Tables

    • Writing Queries That Use Common Table Expressions (CTEs)

    • Writing Queries That Sue Inline Table-Valued Expressions

 

Module 12: Using Set Operators

  • Writing Queries with the UNION operator

  • Using EXCEPT and INTERSECT

  • Using APPLY

  • Comparing Rows Between Two Input Sets

  • Lab: Using Set Operators

    • Writing Queries That Use UNION Set Operators and UNION ALL

    • Writing Queries That Use CROSS APPLY and OUTER APPLY Operators

    • Writing Queries That Use the EXCEPT and INTERSECT Operators

 

Module 13: Using Windows Ranking, Offset, and Aggregate Functions

  • Benefits of Window Functions

  • Restricting Window Functions

  • Creating Windows with OVER

  • Exploring Window Functions

  • Lab: Using Windows Ranking, Offset, and Aggregate Functions

    • Writing Queries that use Ranking Functions

    • Writing Queries that use Offset Functions

    • Writing Queries that use Window Aggregate Functions

 

Module 14: Pivoting and Grouping Sets

  • Writing Queries with PIVOT and UNPIVOT

  • Working with Grouping Sets

  • Lab: Pivoting and Grouping Sets

    • Writing Queries that use the PIVOT Operator

    • Writing Queries that use the UNPIVOT Operator

    • Writing Queries that use the GROUPING SETS CUBE and ROLLUP Subclauses

 

Module 15: Executing Stored Procedures

  • Querying Data with Stored Procedures

  • Passing Parameters to Stored procedures

  • Creating Simple Stored Procedures

  • Working with Dynamic SQL

  • Lab: Executing Stored Procedures

    • Using the EXECUTE statement to Invoke Stored Procedures

    • Passing Parameters to Stored procedures

    • Executing System Stored Procedures

 

Module 16: Programming with T-SQL

  • T-SQL Programming Elements

  • Controlling Program Flow

  • Lab: Programming with T-SQL

    • Declaring Variables and Delimiting Batches

    • Using Control-Of-Flow Elements

    • Using Variables in a Dynamic SQL Statement

    • Using Synonyms

 

Module 17: Implementing Error Handling

  • Implementing T-SQL error handling

  • Implementing structured exception handling

  • Lab: Implementing Error Handling

    • Redirecting errors with TRY/CATCH

    • Using THROW to pass an error message back to a client

 

Module 18: Implementing Transactions

  • Transactions and the Database Engines

  • Controlling Transactions

  • Lab: Implementing Transactions

    • Controlling transactions with BEGIN, COMMIT, and ROLLBACK

    • Adding error handling to a CATCH block

Show moredown

Who should attend?

Querying Data with Transact-SQL M20761 is designed for those within SQL Server-related roles, predominantly Database Administrators, Database Developers, and Business Intelligence professionals.

 

Prerequisites

Delegates should possess knowledge of:

  • Relational databases

  • The Microsoft Windows operating system

Querying Data with Transact-SQL Overview

This 5 day Microsoft Querying Data with Transact-SQL training course provides delegates with an introduction to Transact-SQL. The first 3 days take the form of classroom teaching whereby candidates acquire the knowledge needed to complete more SQL Server courses. The final 2 days focus more on the exam, developing the skills needed to complete it successfully. Delegates will understand Transact-SQL language adopted by those within SQL Server disciplines.

 

They will be taught the fundamentals of Transact-SQL, how to write queries, how to sort data, and the process of executing procedures. By the end of this training course, delegates will be ready to complete and pass the Querying Data with Transact-SQL 70-761 examination.

Show moredown

Querying Data with Transact-SQL Examination

The exam associated with this course is the Querying Data with Transact-SQL 70-761 examination, which will cover the following areas:

  • Managing Data with Transact-SQL

  • Querying Data with Advanced Transact-SQL Components

  • Programming Databases by Using Transact-SQL

 

Delegates will need to sign a non-disclosure exam agreement, which states that they will not share information relating to the exam structure with anyone. 

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (5 days)

Online Self-paced (40 hours)

Administering a SQL Database Infrastructure Course Outline | M20764

The following modules are covered during this course:

Module 1: SQL Server Security

  • Authenticating Connections to SQL Server
  • Authorizing Logins to Connect to databases
  • Authorization Across Servers
  • Partially Contained Databases

Lab: Authenticating Users

  • Create Logins
  • Create Database Users
  • Correct Application Login Issues
  • Configure Security for Restored Databases

Module 2: Assigning Server and Database Roles

  • Working with server roles
  • Working with Fixed Database Roles
  • Assigning User-Defined Database Roles

Lab: Assigning server and database roles

  • Assigning Server Roles
  • Assigning Fixed Database Roles
  • Assigning User-Defined Database Roles
  • Verifying Security

Module 3: Authorizing Users to Access Resources

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

Lab: Authorizing users to access resources

  • Granting, Denying, and Revoking Permissions on Objects
  • Granting EXECUTE Permissions on Code
  • Granting Permissions at the Schema Level

Module 4: Protecting Data with Encryption and Auditing

  • Options for auditing data access in SQL Server
  • Implementing SQL Server Audit
  • Managing SQL Server Audit
  • Protecting Data with Encryption

Lab: Using Auditing and Encryption

  • Working with SQL Server Audit
  • Encrypt a Column as Always Encrypted
  • Encrypt a Database using TDE

Module 5: Recovery Models and Backup Strategies

  • Understanding Backup Strategies
  • SQL Server Transaction Logs
  • Planning Backup Strategies

Lab: Understanding SQL Server recovery models

  • Plan a Backup Strategy
  • Configure Database Recovery Models

Module 6: Backing Up SQL Server Databases

  • Backing Up Databases and Transaction Logs
  • Managing Database Backups
  • Advanced Database Options

Lab: Backing Up Databases

  • Backing Up Databases
  • Performing Database, Differential, and Transaction Log Backups
  • Performing a Partial Backup

Module 7: Restoring SQL Server 2016 Databases

  • Understanding the Restore Process
  • Restoring Databases
  • Advanced Restore Scenarios
  • Point-in-Time Recovery

Lab: Restoring SQL Server Databases

  • Restoring a Database Backup
  • Restring Database, Differential, and Transaction Log Backups
  • Performing a Piecemeal Restore

Module 8: Automating SQL Server Management

  • Automating SQL Server management
  • Working with SQL Server Agent
  • Managing SQL Server Agent Jobs
  • Multi-server Management

Lab: Automating SQL Server Management

  • Create a SQL Server Agent Job
  • Test a Job
  • Schedule a Job
  • Configure Master and Target Servers

Module 9: Configuring Security for SQL Server Agent

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

Lab: Configuring Security for SQL Server Agent

  • Analyzing Problems in SQL Server Agent
  • Configuring a Credential
  • Configuring a Proxy Account
  • Configuring and testing the Security Context of a Job

Module 10: Monitoring SQL Server with Alerts and Notifications

  • Monitoring SQL Server Errors
  • Configuring Database Mail
  • Operators, Alerts, and Notifications
  • Alerts in Azure SQL Database

Lab: Monitoring SQL Server with Alerts and Notifications

  • Configuring Database Mail
  • Configuring Operators
  • Configuring Alerts and Notifications
  • Testing Alerts and Notifications

Module 11: Introduction to Managing SQL Server by using PowerShell

  • Getting Started with Windows PowerShell
  • Configure SQL Server using PowerShell
  • Administer and Maintain SQL Server with PowerShell
  • Managing Azure SQL Databases using PowerShell

Lab: Using PowerShell to Manage SQL Server

  • Getting Started with PowerShell
  • Using PowerShell to Change SQL Server settings

Module 12: Tracing Access to SQL Server with Extended events

  • Extended Events Core Concepts
  • Working with Extended Events

Lab: Extended Events

  • Using the System_Health Extended Events Session
  • Tracking Page Splits Using Extended Events.

Module 13: Monitoring SQL Server

  • Monitoring activity
  • Capturing and Managing Performance Data
  • Analyzing Collected Performance Data
  • SQL Server Utility

Module 14: Troubleshooting SQL Server

  • A Trouble-Shooting Methodology for SQL Server
  • Resolving Service Related Issues
  • Resolving Connectivity and Log-in issues

Lab: Troubleshooting Common Issues

  • Troubleshoot and Resolve a SQL Login Issue
  • Troubleshoot and Resolve a Service Issue
  • Troubleshoot and Resolve a Windows Login Issue
  • Troubleshoot and Resolve a Job Execution Issue
  • Troubleshoot and Resolve a Performance Issue

Module 15: Importing and Exporting Data

  • Transferring Data to and from SQL Server
  • Importing and Exporting Table Data
  • Using bcp and BULK INSERT to Import Data
  • Deploying and Upgrading Data-Tier Application

Lab: Importing and Exporting Data

  • Import and Excel Data Using the Import Wizard
  • Import a Delimited Text File Using bcp
  • Import a Delimited Text File using BULK INSERT
  • Create and Test a SSIS Package to Extract Data
  • Deploy a Data-Tier Application

Show moredown

Who should attend?

The course is aimed at those who can administer and maintain SQL Server databases as part of their daily responsibilities. It is also suitable for those who develop applications which deliver content from SQL Server databases.

Prerequisites

You should have the following knowledge and experience before attending this course:

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

Administering a SQL Database Infrastructure Course Overview | M20764​

This 5-day official Microsoft classroom course teaches delegates the knowledge and skills required to administer a SQL Server database infrastructure. Delegates will also learn the basics of servers and how they are connected.

Many other skills such as creating authorisation for users and using custom database roles and application roles to manage database-level security are also taught during this Microsoft technical certification.

Show moredown

Administering a SQL Database Infrastructure Exam Information Overview | M20764​

This course prepares you for Exam 70-764, which is not included. This exam measures ability to accomplish technical tasks in the following areas:

  • Configure data access and auditing
  • Manage backup and restore of databases
  • Manage and monitor SQL Server instances
  • Manage high availability and disaster recovery

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (5 days)

Online Self-paced (40 hours)

Developing SQL Databases Course (M20762) Outline

This is a 5 day course, with a total of 18 modules. Each day consists of expert instructor led training and the Developing SQL Database exam (Exam 70-762) is included as part of the course price. Upon completing the course, delegates may progress onto the Developing SQL Databases exam.

 

Module 1: Introduction to Database Development

  • Introduction to the SQL Server Platform

  • SQL Server Database Development Tasks

 

Module 2: Designing and Implementing Tables

  • Designing Tables

  • Data Types

  • Working with Schemas

  • Creating and Altering Tables

  • Lab: Designing and Implementing Tables

 

Module 3: Advanced Table Designs

  • Partitioning Data

  • Compressing Data

  • Temporal Tables

  • Lab: Using Advanced Table Designs

  • Partitioning Data

  • Compressing Data

 

Module 4: Ensuring Data Integrity through Constraints

  • Enforcing Data Integrity

  • Implementing Data Domain Integrity

  • Implementing Entity and Referential Integrity

  • Lab: Using Data Integrity Through Constraints

 

Module 5: Introduction to Indexes

  • Core Indexing Concepts

  • Data Types and Indexes

  • Heaps, Clustered, and Nonclustered Indexes

  • Single Column and Composite Indexes

  • Lab: Implementing Indexes

 

Module 6: Designing Optimized Index Strategies

  • Index Strategies

  • Managing Indexes

  • Execution Plans

  • The Database Engine Tuning Advisor

  • Query Store

  • Lab: Optimizing Indexes

 

Module 7: Columnstore Indexes

  • Introduction to Columnstore Indexes

  • Creating Columnstore Indexes

  • Working with Columnstore Indexes

  • Lab: Using Columnstore Indexes

 

Module 8: Designing and Implementing Views

  • Introduction to Views

  • Creating and Managing Views

  • Performance Considerations for Views

  • Lab: Designing and Implementing Views

 

Module 9: Designing and Implementing Stored Procedures

  • Introduction to Stored Procedures

  • Working with Stored Procedures

  • Implementing Parameterized Stored Procedures

  • Controlling Execution Context

  • Lab: Designing and Implementing Stored Procedures

 

Module 10: Designing and Implementing User-Defined Functions

  • Overview of Functions

  • Designing and Implementing Scalar Functions

  • Designing and Implementing Table-Valued Functions

  • Considerations for Implementing Functions

  • Alternatives to Functions

  • Lab: Designing and Implementing User-Defined Functions

 

Module 11: Responding to Data Manipulation via Triggers

  • Designing DML Triggers

  • Implementing DML Triggers

  • Advanced Trigger Concepts

  • Lab: Responding to Data Manipulation by Using Triggers

 

Module 12: Using In-Memory Tables

  • Memory-Optimized Tables

  • Natively Compiled Stored Procedures

  • Lab: Using In-Memory Database Capabilities

 

Module 13: Implementing Managed Code in SQL Server

  • Introduction to CLR Integration in SQL Server

  • Implementing and Publishing CLR Assemblies

  • Lab: Implementing Managed Code in SQL Server

 

Module 14: Storing and Querying XML Data in SQL Server

  • Introduction to XML and XML Schemas

  • Storing XML Data and Schemas in SQL Server

  • Implementing the XML Data Type

  • Using the Transact-SQL FOR XML Statement

  • Getting Started with XQuery

  • Shredding XML

  • Lab: Storing and Querying XML Data in SQL Server

 

Module 15: Storing and Querying Spatial Data in SQL Server

  • Introduction to Spatial Data

  • Working with SQL Server Spatial Data Types

  • Using Spatial Data in Applications

  • Lab: Working with SQL Server Spatial Data

 

Module 16: Storing and Querying BLOBs and Text Documents in SQL Server

  • Considerations for BLOB Data

  • Working with FILESTREAM

  • Using Full-Text Search

  • Lab: Storing and Querying BLOBs and Text Documents in SQL Server

 

Module 17: SQL Server Concurrency

  • Concurrency and Transactions

  • Locking Internals

  • Lab: SQL Server Concurrency

Module 18: Performance and Monitoring

  • Extended Events

  • Working with extended Events

  • Live Query Statistics

  • Optimize Database File Configuration

  • Metrics

  • Lab: Monitoring, Tracing, and Baselining

Show moredown

Who should attend?

IT professionals who wish to become proficient in SQL Server 2016 product features and technologies when implementing a database. IT developers with an interest in the SQL Server 2016, may also be suited to this course.

 

Prerequisites

Delegates wishing to attend this course are expected to have:

  • Basic knowledge of the Microsoft Windows operating system and its core functionality

  • Working knowledge of Transact-SQL

  • Working knowledge of relational databases

Developing SQL Databases Course (M20762) Overview

This 5 day Microsoft course is tailored to delegates who wish to develop the knowledge and skills required to develop a Microsoft SQL Server 2016 database. This course shall cover all the necessary tools and techniques required to develop a database.

 

Delegates who have completed the course are expected to be able to:

  • Design and Implement Tables

  • Describe advanced table designs

  • Ensure Data Integrity through Constraints

  • Describe indexes, including Optimized and Columnstore indexes

  • Design and Implement Views

  • Design and Implement Stored Procedures

  • Design and Implement User Defined Functions

  • Respond to data manipulation using triggers

  • Design and Implement In-Memory Tables

  • Implement Managed Code in SQL Server

  • Store and Query XML Data

  • Work with Spatial Data

  • Store and Query Blobs and Text Documents

 

We are fully accredited by Microsoft, for our Developing SQL Databases course and we are a Silver Partner. 

Show moredown

Developing SQL Databases Exam

Once the M20762 course has been completed the 70-762 exam can be taken. Developing SQL Databases exam information is protected as part of a Non-Disclosure Agreement. Therefore information on the exam format is not available. However information regarding how the exam content is divided is available and is as follows:

  • Design and implement database objects (25-30%)

  • Manage database concurrency (25-30%)

  • Implement programmability objects (20-25%)

  • Optimise database objects and SQL infrastructure (20-25%)

The percentages indicate the weighting of each of the major topic areas covered in the exam. 

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (5 days)

Online Self-paced (40 hours)

Implementing a Data Warehouse with Microsoft SQL Server Outline

Module 1: Introduction to Data Warehousing

  • Overview of Data Warehousing

  • Considerations for a Data Warehouse Solution

Lab: Exploring a Data Warehousing Solution

 

Module 2: Data Warehouse Hardware Considerations

  • Selecting hardware

  • Considerations for building a Data Warehouse

  • Data Warehouse Reference Architectures and Appliances

Lab: Planning Data Warehouse Infrastructure

 

Module 3: Designing and Implementing a Data Warehouse

  • Logical Design for a Data Warehouse

  • Physical design for a Data Warehouse

Lab: Implementing a Data Warehouse Schema

 

Module 4: Creating an ETL Solution with SSIS

  • Introduction to ETL with SSIS

  • Exploring Data Sources

  • Implementing Data Flow

Lab: Implementing Data Flow in an SSIS Package

 

Module 5: Implementing Control Flow in an SSIS Package

  • Introduction to Control Flow

  • Creating Dynamic Packages

  • Using Containers

  • Managing Consistency

Lab: Implementing Control Flow in an SSIS Package

Lab: Using Transactions and Checkpoints

 

Module 6: Debugging and Troubleshooting SSIS Packages

  • Debugging an SSIS Package

  • Logging SSIS Package Events

  • Handling Errors in an SSIS Package

Lab: Debugging and Troubleshooting an SSIS Package

 

Module 7: Implementing an Incremental ETL Process

  • Introduction to Incremental ETL

  • Extracting Modified Data

  • Loading Modified Data

Lab: Extracting Modified Data

Lab: Loading Incremental Changes

 

Module 8: Enforcing Data Quality

  • Introduction to Microsoft SQL Server Data Quality Services (DQS)

  • Using Data Quality Services to Cleanse Data

  • Using Data Quality Services to Match Data

Lab: Cleansing Data

Lab: De-duplicating data

 

Module 9: Using Master Data Services

  • Master Data Services Concepts

  • Implementing a Master Data Services Model

  • Managing Master Data

  • Creating a Master Data Hub

Lab: Implementing Master Data Services

 

Module 10: Extending SQL Server Integration Services

  • Using Scripts in SSIS

  • Using Custom Components in SSIS

Lab: Using Custom Components and Scripts

 

Module 11: Deploying and Configuring SSIS Packages

  • Overview of SSIS Deployment

  • Deploying SSIS Projects

  • Planning and Monitoring SSIS Package Execution

Lab: Deploying and Configuring SSIS Packages

 

Module 12: Consuming Data in a Data Warehouse

  • Introduction to Business Intelligence

  • Introduction to Reporting

  • An Introduction to Data Analysis

Lab: Using Business Intelligence Tools

Show moredown

Who should attend?

This training course is designed for database professionals who are looking to accomplish the role of a Business Intelligence Developer. Attendees should be familiar with generating Business Intelligence solutions such as Data Warehouse implementation and data cleansing. They should be accustomed with the advancement of SSIS packages for information extraction, conversion, and loading, and the administering of data integrity through the use of Master Data Services.

 

Prerequisites

Delegates must possess at least 2 years of relational database experience, specifically in:

  • Designing normalized databases

  • Constructing tables and relationships

  • Querying with Transact-SQL

  • Fundamental programming constructs

 

Understanding of business elements like profitability, revenue, and accounting would be useful, although this is not compulsory.

Implementing a Data Warehouse with Microsoft SQL Server Overview

This 5 day Implementing a Data Warehouse with Microsoft SQL Server training course provides delegates with the ability to implement data warehouse structures in order to support Business Intelligence solutions. Individuals will become familiar with the Microsoft SQL Server 2014 for the purposing of data warehouse construction, and will learn how to apply ETL with the SQL Server. As well as this, delegates will use SQL Server Data Quality Services and SQL Server Master Data Services to cleanse and verify information.

 

This training course is suitable for those who wish to learn about SQL Server 2012 or SQL Server 2014. The new features of the 2014 SQL Server are explored, as are the fundamental proficiencies of the overall SQL Server system. 

 

 

Show moredown

Implementing a Data Warehouse with Microsoft SQL Server Examination

The Implementing a Data Warehouse with Microsoft SQL Server 2012/2014 70-463 exam is not included with this training course. Delegates will be able to sit this examination, which will cover the following topics:

  • Designing and Implementing a Data Warehouse

  • Extracting and Transforming Data

  • Loading Data

  • Configuring and Deploying SSIS Solutions

  • Building Data Quality Solutions

 

Delegates will need to sign a non-disclosure exam agreement, which confirms that they agree not to share information regarding the exam format with any other person.

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (5 days)

Online Self-paced (40 hours)

Provisioning SQL Databases Outline

This course will cover the following content:

Module 1: SQL Server 2016 Components

  • Introduction to the SQL Server Platform

  • Overview of SQL Server Architecture

  • SQL Server Services and Configuration Options

  • Lab : Discussion: SQL Server versions

  • When we do use a separate installation of SQL Server and when would use a separate instance?

  • Which version of SQL Server is most suitable in your organization?

 

Module 2: Installing SQL Server 2016

  • Considerations for SQL Installing Server

  • TempDB Files

  • Installing SQL Server 2016

  • Automating Installation

  • Lab : Installing SQL Server

  • Assess the available resources

  • Install an instance of SQL Server

  • Perform post installation checks

  • Automating Installation

 

Module 3: Upgrading SQL Server to SQL Server 2017

  • Upgrade Requirements

  • Upgrade SQL Server Services

  • Migrating SQL Server Data and Applications

  • Lab : Upgrading SQL Server

  • Create the Application Logins

  • Restore Database Backups

  • Orphaned Users and Database Compatibility Level

 

Module 4: Working with Databases

  • Introduction to Data Storage with SQL Server

  • Managing Storage for System Databases

  • Managing Storage for User Databases

  • Moving and Copying Database Files

  • Side-by-Side Upgrade: Migrating SQL Server Data and Applications

  • Buffer Pool Extension

  • Lab : Managing Database Storage

  • Configuring tempdb Storage

  • Creating Databases

  • Attaching a Database

  • Enable Buffer Pool Extension

 

Module 5: Performing Database Maintenance

  • Ensuring Database Integrity

  • Maintaining Indexes

  • Automating Routine Database Maintenance

  • Lab : Performing Database Maintenance

  • Use DBCC CHECKDB to Verify Database Integrity

  • Rebuild Indexes

  • Create a Database Maintenance Plan

 

Module 6: Database Storage Options

  • SQL Server storage Performance

  • SMB Fileshare

  • SQL Server Storage in Microsoft Azure

  • Stretch Databases

  • Lab : Implementing Stretch Database

  • Run Stretch Database Advisor

  • Implement Stretch Database

 

Module 7: Planning to Deploy SQL Server on Microsoft Azure

  • SQL Server Virtual Machines in Azure

  • Azure Storage

  • Azure SQL Authentication

  • Deploying an Azure SQL Database

  • Lab : Plan and Deploy an Azure SQL Database

  • Plan an Azure SQL Database, Networking, performance tiers, security

  • Provision an Azure SQL Database

  • Connect to an Azure SQL Database

 

Module 8: Migrating Databases to Azure SQL Database

  • Database Migration Testing Tools

  • Database Migration Compatibility Issues

  • Migrating a SQL Server Database to Azure SQL Database

  • Lab : Migrating SQL Server Databases to Azure

  • Perform Migration Testing

  • Migrate a SQL Server Database to Azure SQL Database

  • Test a Migrated Database

 

Module 9: Deploying SQL Server on a Microsoft Azure Virtual Machine

  • Deploying SQL Server on an Azure VM

  • The Deploy Database to a Microsoft Azure VM Wizard

  • Lab : Deploying SQL Server on an Azure Virtual Machine

  • Provision an Azure VM

  • Use the Deploy Database to Azure VM Wizard

 

Module 10: Managing databases in the Cloud

  • Managing Azure SQL Database Security

  • Configure Azure storage

  • Azure Automation

  • Lab : Managing Databases in the Cloud

  • Configure Azure Security

  • Automate Azure Deployment

Show moredown

Who should attend?

This course is designed for individuals who need to have the skills to administer and manage SQL databases in order to fulfill their job role.

 

Prerequisites

In order to enroll on this Provisioning SQL Databases course, candidates must have a basic knowledge of: Microsoft Windows operating systems and their functions, SQL, relational databases, and some experience in designing databases. This is to ensure that candidates fully understand the content of the course.

Provisioning SQL Databases Overview

This 5 day Provisioning SQL Databases trainer-led course aims to provide candidates with the necessary skills to provision an SQL Server database both in SQL Azure and on premise. By the completion of the course delegates will have the ability to provision a Database Server, upgrade and configure an SQL Server, manage Databases and Files, and provision, migrate, and maintain databases in the cloud.

 

For each module of the course, delegates will complete a lab that will enable them to obtain hands-on experience within each topic of the course. This will result in the development of skills and knowledge that can be implemented in the workplace.

 

The Knowledge Academy are a Microsoft Silver Partner, therefore this Provisioning SQL Databases training course is fully accredited by Microsoft.

Show moredown

Provisioning SQL Databases Examination

The Provisioning SQL Databases 70-765 examination must be passed in order to complete this course. We do not provide the exam and it must be taken separately. The exam will cover the following areas:

  • Implement SQL in Azure (40–45%)

  • Manage databases and instances (30-35%)

  • Manage Storage (30–35%)

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (5 days)

Online Self-paced (40 hours)

Implementing a SQL Data Warehouse Outline

This course covers the following content:

Module 1: Introduction to Data Warehousing

  • Overview of Data Warehousing

  • Considerations for a Data Warehouse Solution

  • Lab : Exploring a Data Warehouse Solution

  • Exploring data sources

  • Exploring an ETL process

  • Exploring a data warehouse

 

Module 2: Planning Data Warehouse Infrastructure

  • Considerations for data warehouse infrastructure.

  • Planning data warehouse hardware.

  • Lab : Planning Data Warehouse Infrastructure

  • Planning data warehouse hardware

 

Module 3: Designing and Implementing a Data Warehouse

  • Designing dimension tables

  • Designing fact tables

  • Physical Design for a Data Warehouse

  • Lab: Implementing a Data Warehouse Schema

  • Implementing a star schema

  • Implementing a snowflake schema

  • Implementing a time dimension table

 

Module 4: Columnstore Indexes

  • Introduction to Columnstore Indexes

  • Creating Columnstore Indexes

  • Working with Columnstore Indexes

  • Lab: Using Columnstore Indexes

  • Create a Columnstore index on the Fact Product Inventory table

  • Create a Columnstore index on the FactInternetSales table

  • Create a memory optimized Columnstore table

 

Module 5: Implementing an Azure SQL Data Warehouse

  • Advantages of Azure SQL Data Warehouse

  • Implementing an Azure SQL Data Warehouse

  • Developing an Azure SQL Data Warehouse

  • Migrating to an Azure SQ Data Warehouse

  • Copying data with the Azure data factory

  • Lab: Implementing an Azure SQL Data Warehouse

  • Create an Azure SQL data warehouse database

  • Migrate to an Azure SQL Data warehouse database

  • Copy data with the Azure data factory

 

Module 6: Creating an ETL Solution

  • Introduction to ETL with SSIS

  • Exploring Source Data

  • Implementing Data Flow

  • Lab: Implementing Data Flow in an SSIS Package

  • Exploring source data

  • Transferring data by using a data row task

  • Using transformation components in a data row

 

Module 7: Implementing Control Flow in an SSIS Package

  • Introduction to Control Flow

  • Creating Dynamic Packages

  • Using Containers

  • Managing consistency.

  • Lab: Implementing Control Flow in an SSIS Package

  • Using tasks and precedence in a control flow

  • Using variables and parameters

  • Using containers

  • Lab : Using Transactions and Checkpoints

  • Using transactions

  • Using checkpoints

 

Module 8: Debugging and Troubleshooting SSIS Packages

  • Debugging an SSIS Package

  • Logging SSIS Package Events

  • Handling Errors in an SSIS Package

  • Lab: Debugging and Troubleshooting an SSIS Package

  • Debugging an SSIS package

  • Logging SSIS package execution

  • Implementing an event handler

  • Handling errors in data flow

 

Module 9: Implementing a Data Extraction Solution

  • Introduction to Incremental ETL

  • Extracting Modified Data

  • Loading modified data

  • Temporal Tables

  • Lab: Extracting Modified Data

  • Using a datetime column to incrementally extract data

  • Using change data capture

  • Using the CDC control task

  • Using change tracking

  • Lab: Loading a data warehouse

  • Loading data from CDC output tables

  • Using a lookup transformation to insert or update dimension data

  • Implementing a slowly changing dimension

  • Using the merge statement

 

Module 10: Enforcing Data Quality

  • Introduction to Data Quality

  • Using Data Quality Services to Cleanse Data

  • Using Data Quality Services to Match Data

  • Lab: Cleansing Data

  • Creating a DQS knowledge base

  • Using a DQS project to cleanse data

  • Using DQS in an SSIS package

  • Lab: De-duplicating Data

  • Creating a matching policy

  • Using a DS project to match data

 

Module 11: Using Master Data Services

  • Introduction to Master Data Services

  • Implementing a Master Data Services Model

  • Hierarchies and collections

  • Creating a Master Data Hub

  • Lab: Implementing Master Data Services

  • Creating a master data services model

  • Using the master data services add-in for Excel

  • Enforcing business rules

  • Loading data into a model

  • Consuming master data services data

 

Module 12: Extending SQL Server Integration Services (SSIS)

  • Using scripting in SSIS

  • Using custom components in SSIS

  • Lab: Using scripts

  • Using a script task

 

Module 13: Deploying and Configuring SSIS Packages

  • Overview of SSIS Deployment

  • Deploying SSIS Projects

  • Planning SSIS Package Execution

  • Lab: Deploying and Configuring SSIS Packages

  • Creating an SSIS catalog

  • Deploying an SSIS project

  • Creating environments for an SSIS solution

  • Running an SSIS package in SQL server management studio

  • Scheduling SSIS packages with SQL server agent

 

Module 14: Consuming Data in a Data Warehouse

  • Introduction to Business Intelligence

  • An Introduction to Data Analysis

  • Introduction to reporting

  • Analyzing Data with Azure SQL Data Warehouse

  • Lab: Using a data warehouse

  • Exploring a reporting services report

  • Exploring a PowerPivot workbook

  • Exploring a power view report

Show moredown

Who should attend?

This Implementing a SQL Database Warehouse training is designed for individuals who need to create BI solutions as part of their role. Specifically, Business Intelligence Developers would this course beneficial.

 

Prerequisites

There are no formal prerequisites for attending this course.

Implementing a SQL Data Warehouse Overview

This 5 day Implementing a SQL Database Warehouse training course aims to teach delegates the necessary skills to provision a Microsoft SQL Server 2016 database. The course will provide training on both how to migrate from an existing install and how to start fresh with a new install, as well as the provisioning of an SQL Server 2016 on premise and in Microsoft Azure.

 

Throughout the training, delegates will be required to complete labs for each module of the course. This will enable delegates to get firsthand experience with each of the topic and allow them to practise the skills and knowledge they will need to implement after the completion of the course.

 

The Knowledge Academy is a Microsoft Silver Partner, therefore this Implementing an SQL Database Warehouse training course is fully accredited by Microsoft.

Show moredown

Implementing a SQL Data Warehouse Examination

Candidates carry out this course in roder to  pass the Implementing a Data Warehouse with Microsoft SQL Server 2012/2014 70-463 examination. We do not provide the exam and it must be taken separately. This will cover the following areas:

  • Design and implement a data warehouse (10–15%)

  • Extract and transform data (20–25%)

  • Load data (25–30%)

  • Configure and deploy SSIS solutions (20–25%)

  • Build data quality solutions (15–20%)

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (3 days)

Online Self-paced (24 hours)

Developing SQL Data Models (M20768)​ Outline

Module 1: Introduction to Business Intelligence and Data Modeling

This module introduces key BI concepts and the Microsoft BI product suite.

 

Lessons

  • Introduction to Business Intelligence

  • The Microsoft business intelligence platform

 

Lab : Exploring a BI Solution

  • Exploring a Data Warehouse

  • Exploring a data model

 

After completing this module, students will be able to:
 

  • Describe BI scenarios, trends, and project roles.

  • Describe the products that make up the Microsoft BI platform.

 

Module 2: Creating Multidimensional Databases

This module describes how to create multidimensional databases using SQL Server Analysis Services.

 

Lessons

  • Introduction to Multidimensional Analysis

  • Creating Data Sources and Data Source Views

  • Creating a Cube

  • Overview of Cube Security

  • Configure SSAS

  • Monitoring SSAS

 

Lab : Creating a multidimensional database

  • Creating a Data Source

  • Creating and Modifying a data Source View

  • Creating and Modifying a Cube

 

After completing this module, you will be able to:

  • Describe considerations for a multidimensional database.

  • Create data sources and data source views.

  • Create a cube

  • Implement security in a multidimensional database.

  • Configure SSAS to meet requirements including memory limits, NUMA and disk layout.

  • Monitor SSAS performance.

 

Module 3: Working with Cubes and Dimensions

This module describes how to implement dimensions in a cube.

 

Lessons

  • Configuring Dimensions

  • Defining Attribute Hierarchies

  • Sorting and Grouping Attributes

  • Slowly Changing Dimensions

 

Lab : Working with Cubes and Dimensions

  • Configuring Dimensions

  • Defining Relationships and Hierarchies

  • Sorting and Grouping Dimension Attributes

 

After completing this module, you will be able to:

  • Configure dimensions.

  • Define attribute hierarchies.

  • Implement sorting and grouping for attributes.

  • Implement slowly changing dimensions.

 

Module 4: Working with Measures and Measure Groups

This module describes how to implement measures and measure groups in a cube.

 

Lessons

  • Working with Measures

  • Working with Measure Groups

 

Lab : Configuring Measures and Measure Groups

  • Configuring Measures

  • Defining Dimension Usage and Relationships

  • Configuring Measure Group Storage

 

After completing this module, you will be able to:

  • Configure measures.

  • Configure measure groups.

 

Module 5: Introduction to MDX

This module describes the MDX syntax and how to use MDX.

 

Lessons

  • MDX fundamentals

  • Adding Calculations to a Cube

  • Using MDX to Query a Cube

 

Lab : Using MDX

  • Querying a cube using MDX

  • Creating a Calculated Member

 

After completing this module, you will be able to:

  • Use basic MDX functions.

  • Use MDX to add calculations to a cube.

  • Use MDX to query a cube.

 

Module 6: Customizing Cube Functionality

This module describes how to customize a cube.

 

Lessons

  • Introduction to Business Intelligence

  • The Implementing Key Performance Indicators

  • Implementing Actions

  • Implementing Perspectives

  • Implementing Translations

 

Lab : Customizing a Cube

  • Implementing a KPI

  • Implementing an action

  • Implementing a perspective

  • Implementing a translation

 

After completing this module, you will be able to:

  • Implement KPIs in a Multidimensional database

  • Implement Actions in a Multidimensional database

  • Implement perspectives in a Multidimensional database

  • Implement translations in a Multidimensional database

 

Module 7: Implementing a Tabular Data Model by Using Analysis Services

This module describes how to implement a tabular data model in Power Pivot.

 

Lessons

  • Introduction to Tabular Data Models

  • Creating a Tabular Data Model

  • Using an Analysis Services Tabular Data Model in an Enterprise BI Solution

 

Lab : Working with an Analysis Services Tabular Data Model

  • Creating an Analysis Services Tabular Data Model

  • Configure Relationships and Attributes

  • Configuring Data Model for an Enterprise BI Solution.

 

After completing this module, students will be able to:

  • Describe tabular data models

  • Describe how to create a tabular data model

  • Use an Analysis Services Tabular Model in an enterprise BI solution

 

Module 8: Introduction to Data Analysis Expression (DAX)

This module describes how to use DAX to create measures and calculated columns in a tabular data model.

 

Lessons

  • DAX Fundamentals

  • Using DAX to Create Calculated Columns and Measures in a Tabular Data Model

 

Lab : Creating Calculated Columns and Measures by using DAX

  • Creating Calculated Columns

  • Creating Measures

  • Creating a KPI

  • Creating a Parent – Child Hierarchy

 

After completing this module, students will be able to:

  • Describe the key features of DAX

  • Create calculated columns and measures by using DAX

 

Module 9: Performing Predictive Analysis with Data Mining

This module describes how to use data mining for predictive analysis.

 

Lessons

  • Overview of Data Mining

  • Creating a Custom Data Mining Solution

  • Validating a Data Mining Model

  • Connecting to and Consuming a Data-Mining Model

  • Using the Data Mining add-in for Excel

 

Lab : Using Data Mining

  • Creating a Data Mining Structure and Model

  • Exploring Data Mining Models

  • Validating Data Mining Models

  • Consuming a Data Mining Model

  • Using the Excel Data Mining add-in

 

After completing this module, students will be able to:

  • Describe considerations for data mining

  • Create a data mining model

  • Validate a data mining model

  • Connect to a data-mining model

  • Use the data mining add-in for Excel

Show moredown

Who should attend?

The course is aimed at database professionals who need to create enterprise BI solutions as part of a BI Developer role. Responsibilities of the target audience may include the following:

  • Implementing multidimensional databases by using SQL Server Analysis Services

  • Creating tabular semantic data models for analysis by using SQL Server Analysis Services

 

Prerequisites

You should have experience of querying data using Transact-SQL before attending.

Developing SQL Models (M20768)​ Overview

This 3-day official Microsoft course focuses on creating managed enterprise BI (Business Intelligence) solutions. On the course, you will learn how to implement both multidimensional and tabular data models, and how to create cubes, dimensions, measures, and measure groups. You will also prepare for the accompanying Exam 70-768, which is included.

 

Show moredown

Developing SQL Data Models (M20768)  Exam

The course prepares you for Exam 70-798: Developing SQL Models. This exam is not included. It measures ability to accomplish technical tasks within these areas:

  • Design a multidimensional business intelligence semantic model

  • Design a tabular BI semantic model

  • Develop queries using Multidimensional Expressions (MDX) and Data Analysis Expressions (DAX)

  • Configure and maintain SQL Server Analysis Services

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (3 days)

Online Self-paced (24 hours)

Analyzing Data with Power BI (M20778) Outline

Module 1: Introduction to Self-Service BI Solutions

  • Introduction to business intelligence

  • Introduction to data analysis

  • Introduction to data visualization

  • Overview of self-service BI

  • Considerations for self-service BI

  • Microsoft tools for self-service BI

Lab: Exploring an Enterprise BI solution

  • Viewing reports

  • Creating a Power BI report

  • Creating a Power BI dashboard

 

Module 2: Introducing Power BI

  • Power BI

  • The Power BI service

Lab: Creating a Power BI dashboard

  • Connecting to Power BI data

  • Create a Power BI dashboard

 

Module 3: Power BI

  • Using Excel as a data source for Power BI

  • The Power BI data model

  • Using databases as a data source for Power BI

  • The Power BI service

Lab: Importing data into Power BI

  • Importing Excel files into Power BI

  • Viewing reports from Excel files

 

Module 4: Shaping and Combining Data

  • Power BI desktop queries

  • Shaping data

  • Combining data

Lab: Shaping and combining data

  • Shape power BI data

  • Combine Power BI data

 

Module 5: Modelling data

  • Relationships

  • DAX queries

  • Calculations and measures

Lab: Modelling Data

  • Create relationships

  • Calculations

 

Module 6: Interactive Data Visualizations

  • Creating Power BI reports

  • Managing a Power BI solution

Lab: Creating a Power BI report

  • Connecting to Power BI data

  • Building Power BI reports

  • Creating a Power BI dashboard

 

Module 7: Direct Connectivity

  • Cloud data

  • Connecting to analysis services

Lab: Direct Connectivity

  • Direct connectivity from Power BI desktop

  • Direct connectivity from the Power BI service

 

Module 8: Developer API

  • The developer API

  • Custom visuals

Lab: Using the developer API

  • Using custom visuals

 

Module 9: Power BI mobile app

  • The Power BI mobile app

  • Using the Power BI mobile app

  • Power BI embedded

Show moredown

Who should attend?

This course is widely attended by SQL Server report creators who wish to expand their knowledge in the field, and/or would like to use alternate means of presenting data. 

 

Prerequisites

In order to enrol on this Analyzing Data with Power BI course, delegates should have the following previous experience and knowledge:

  • A level of understanding of data warehouse schema topology (including snowflake and star schematics)

  • Understanding of relational databases and reporting

  • Some experience of basic programming concepts (e.g. branching and looping)

  • A familiarity of key business priorities like profitability, revenue, and financial accounting (although this is not vital)

  • Awareness of Microsoft Office Applications, in particular Excel

Analyzing Data with Power BI (M20778) Overview

This is a three-day Microsoft Power BI training course, allowing delegates to grasp a broad understanding of data analysis using Power BI. This is a collection of business analytics tools, which permits a series of data-based tasks to be completed, including desktop modelling and visualization, and data transformation. Delegates will learn how to complete these actions, whilst also learning broadly about the Power BI service, and the Power BI mobile app.

 

The Knowledge Academy is a proud Microsoft Silver Partner, and so is a fully accredited training provider of Microsoft training. 

Show moredown

Analyzing Data with Power BI (M20778) Exam

The exam that corresponds with this course is the Analyzing and Visualizing Data with Microsoft Power BI (beta) (70-778) exam. This exam tests the skills of consuming and transforming data using Power BI desktop, modelling and visualizing data, and configuring and dashboards in the Power BI service.

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (4 days)

Online Self-paced (32 hours)

Performance Tuning and Optimizing SQL Databases (M10987) Outline

This training course will cover the following modules:

 

Module 1: SQL Server Architecture, Scheduling, and Waits

  • SQL Server Components and SQL OS

  • Windows Scheduling vs SQL Scheduling

  • Waits and Queues

Lab: SQL Server Architecture, Scheduling, and Waits

 

Module 2: SQL Server I/O

  • Core Concepts

  • Storage Solutions

  • I/O Setup and Testing

Lab: Testing Storage Performance

 

Module 3: Database Structures

  • Database Structure Internals

  • Data File Internals

  • TempDB Internals

Lab: Database Structures

 

Module 4: SQL Server Memory

  • Windows Memory

  • SQL Server Memory

  • In-Memory OLTP

Lab: SQL Server Memory

 

Module 5: SQL Server Concurrency

  • Concurrency and Transactions

  • Isolation Levels

  • Locking Internals

Lab: SQL Server Concurrency

 

Module 6: Statistics and Index Internals

  • Statistics Internals and Cardinality Estimation

  • Index Internals

  • Columnstore Indexes

Lab: Statistics and Index Internals

 

Module 7: Query Execution and Query Plan Analysis

  • Query Execution and Optimizer Internals

  • Query Execution Plans

  • Analyzing Query Execution Plans

Lab: Query Execution and Query Plan Analysis

 

Module 8: Plan Caching and Recompilation

  • Plan Cache Internals

  • Troubleshooting Plan Cache Issues

  • Query Store

Lab: Plan Caching and Recompilation

 

Module 9: Extended Events

  • Extended Events Core Concepts

  • Working with Extended Events

Lab: Extended Events

 

Module 10: Monitoring, Tracing, and Baselining

  • Monitoring and Tracing

  • Data Collection Strategy

  • Baselining and Benchmarking

Lab: Monitoring, Tracing, and Baselining

Show moredown

Who should attend?

This course is designed for those who administer and maintain SQL Server databases, manage the performance of SQL Server instances, and write queries against data. Likewise, this course is intended for individuals who develop applications that produce data from SQL Server databases. Therefore, IT Professionals in particular would benefit from this Performance Tuning and Optimizing SQL Databases training course.

 

Prerequisites

In order to attend this course, delegates should possess some professional experience and knowledge of the following:

  • Microsoft Windows operating system and its key functions

  • Database administration and maintenance

  • Transact-SQL

Performance Tuning and Optimizing SQL Databases (M10987) Overview

This Microsoft SQL Server training course provides delegates with the knowledge and techniques needed to optimise and monitor the performance of their databases.

 

During this 4 day course, delegates will become familiar with the functionalities of SQL Server, and will explore its fundamental concepts, including the execution model, waits, and queues. The architectural concepts and best practices relating to data files, transactions, locking, concurrency, and solving query plan problems will be explored. As well as this, the elements and strategy of the plan cache and extended events will be studied.

 

By the end of this Performance Tuning and Optimizing SQL Databases training course, delegates will be able to explain data collection strategy, identify techniques for data analysis, and determine where improvements can be made to enhance overall performance.

 

The Knowledge Academy is a Microsoft Silver Partner, therefore we are an accredited provider of Microsoft training courses.

Show moredown

There is no associated exam for Performance Tuning and Optimizing SQL Databases.

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (3 days)

Online Self-paced (24 hours)

Database Administration Fundamentals Course Outline | SQL Training | M40364

This course includes the following modules:

  • Module 1: Understanding Core Database Concepts
  • Module 2: Creating Database Objects
  • Module 3: Manipulating Data
  • Module 4: Understanding Data Storage
  • Module 5: Administering a Database

Show moredown

Who Should Attend?

This course is intended for IT professionals or others who are interested in:

  • Learning about Database Administration
  • Learning about Data Storage
  • Preparing for the MTA Exam 98-364

Prerequisites

This is an introductory course, and as such there are no formal prerequisites – The Knowledge Academy is pleased to offer this course to any individuals who are interested, especially those preparing for an MTA exam.

Database Administration Fundamentals Training | SQL Training | M40364

This 3-day course is designed to help delegates prepare for the Microsoft Technology Associate Exam 98-364, and facilitate comprehension of Core Database Concepts, Creating Database Objects, Manipulating Data, Data Storage, and Administering a Database.

This Database Administration Fundamentals course is fully accredited by Microsoft through the Microsoft Silver Partnership held by The Knowledge Academy.

After completing this course, delegates will be able to:

  • Understand Core Database Concepts
  • Create Database Objects
  • Manipulate Data
  • Understand Data Storage
  • Administer a Database 

Show moredown

Database Administration Fundamentals Exam Information | SQL Training | M40364

Taking this course will help to prepare delegates for the Microsoft Technology Associate Exam Database Fundamentals 98-364 exam. The Knowledge Academy does not provide the exam and it must be taken separately. The examination will cover the following areas:

  • Understanding core database concepts (20–25%)
  • Create database objects (20–25%)
  • Manipulate data (25–30%)
  • Understand data storage (15–20%)
  • Administer a database (10–15%) 

What's Included?

  • Delegate pack consisting of course notes and exercises
  • Manual
  • Experienced Instructor
  • Refreshments

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (3 days)

Online Self-paced (24 hours)

Analyzing Big Data with Microsoft R Course Outline | SQL Server Training | M20773

 

Module 1: Microsoft R Server and R Client

Explain how Microsoft R Server and Microsoft R Client work.

Lessons:

  • What is Microsoft R server
  • Using Microsoft R client
  • The ScaleR functions

Lab: Exploring Microsoft R Server and Microsoft R Client

  • Using R client in VSTR and RStudio
  • Exploring ScaleR functions
  • Connecting to a remote server

Module 2: Exploring Big Data

At the end of this module delegates will be able to use R Client with R Server to explore big data held in different data stores.

Lessons:

  • Understanding ScaleR data sources
  • Reading data into an XDF object
  • Summarising data in an XDF object

Lab: Exploring Big Data

  • Reading a local CSV file into an XDF file
  • Transforming data on input
  • Reading data from SQL Server into an XDF file
  • Generating summaries over the XDF data

Module 3: Visualising Big Data

Explain how to visualise data by using graphs and plots.

Lessons:

  • Visualising In-memory data
  • Visualising big data

Lab: Visualising data

  • Using ggplot to create a faceted plot with overlays
  • Using rxlinePlot and rxHistogram

Module 4: Processing Big Data

Explain how to transform and clean big data sets.

Lessons:

  • Transforming Big Data
  • Managing datasets

Lab: Processing big data

  • Transforming big data
  • Sorting and merging big data
  • Connecting to a remote server

Module 5: Parallelising Analysis Operations

Explain how to implement options for splitting analysis jobs into parallel tasks.

Lessons:

  • Using the RxLocalParallel compute context with rxExec
  • Using the revoPemaR package

Lab: Using rxExec and RevoPemaR to parallelise operations

  • Using rxExec to maximise resource use
  • Creating and using a PEMA class

Module 6: Creating and Evaluating Regression Models

Explain how to build and evaluate regression models generated from big data.

Lessons:

  • Clustering Big Data
  • Generating regression models and making predictions

Lab: Creating a linear regression model

  • Creating a cluster
  • Creating a regression model
  • Generate data for making predictions
  • Use the models to make predictions and compare the results

Module 7: Creating and Evaluating Partitioning Models

Explain how to create and score partitioning models generated from big data.

Lessons:

  • Creating partitioning models based on decision trees.
  • Test partitioning models by making and comparing predictions

Lab: Creating and evaluating partitioning models

  • Splitting the dataset
  • Building models
  • Running predictions and testing the results
  • Comparing results

Module 8: Processing Big Data in SQL Server and Hadoop

Explain how to transform and clean big data sets.

Lessons:

  • Using R in SQL Server
  • Using Hadoop Map/Reduce
  • Using Hadoop Spark

Lab: Processing big data in SQL Server and Hadoop

  • Creating a model and predicting outcomes in SQL Server
  • Performing an analysis and plotting the results using Hadoop Map/Reduce
  • Integrating a sparklyr script into a ScaleR workflow

Show moredown

Who Should Attend this Course?

The primary audience for this course is people who wish to analyse large datasets within a big data environment. The secondary audience are developers who need to integrate R analyses into their solutions.

Prerequisites

In addition to their professional experience, students who attend this course should have:

  • Programming experience using R, and familiarity with common R packages
  • Knowledge of common statistical methods and data analysis best practices
  • Basic knowledge of the Microsoft Windows operating system and its core functionality
  • Working knowledge of relational databases

 

Analyzing Big Data with Microsoft R Course Overview | SQL Server Training | M20773

This three-day course is designed to equip students with the ability to use Microsoft R Server. Delegates will learn how to create and run an analysis on a large dataset, and show how to utilise it in Big Data environments, such as a Hadoop, Spark cluster, or a SQL Server database.

After completing this course, students will be able to:

  • Explain how Microsoft R Server and Microsoft R Client work
  • Use R Client with R Server to explore big data held in different data stores
  • Visualise data by using graphs and plots
  • Transform and clean big data sets
  • Implement options for splitting analysis jobs into parallel tasks 
  • Build and evaluate regression models generated from big data 
  • Create, score, and deploy partitioning models generated from big data
  • Use R in the SQL Server and Hadoop environments 

Show moredown

Analyzing Big Data with Microsoft R Exam Information | SQL Server Training | M20773

The Knowledge Academy does not provide an examination for this course. ​

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (5 days)

Online Self-paced (40 hours)

Implementing Data Models and Reports with SQL Server 2014 Course Outline | M20466

This course includes the following modules:

Module 1: Introduction to Business Intelligence and Data Modelling

As SQL Server database professionals, delegates may be required to participate in, or perhaps even lead, a project with the aim of implementing an effective enterprise BI solution. Therefore, it is important that they possess a good understanding of the various elements that comprise a BI solution, the business and IT personnel typically involved in a BI project, and the Microsoft products that can be used to implement the solution.

Lessons:

  • Introduction to Business Intelligence
  • The Microsoft Enterprise BI Platform

Lab: Exploring a BI Solution

Module 2: Creating Multidimensional Databases

This module provides an introduction to multidimensional databases and introduces the core components of an Online Analytical Processing (OLAP) cube.

Lessons:

  • Introduction to Multidimensional Analysis
  • Creating Data Sources and Data Source Views
  • Creating a Cube
  • Overview of Cube Security

Lab: Creating a Multidimensional Database

Module 3: Working with Cubes and Dimensions

This module describes how to create and configure dimensions and dimension hierarchies in an Analysis Services multidimensional data model.

Lessons:

  • Configuring Dimensions
  • Defining Attribute Hierarchies
  • Sorting and Grouping Hierarchies

Lab: Working with Cubes and Dimensions

Module 4: Working with Measures and Measure Groups

This module describes measures and measure groups. It also explains how they can be used to define fact tables and associate dimensions with measures.

Lessons:

  • Working with Measures
  • Working with Measure Groups

Lab: Configuring Measures and Measure Groups

Module 5: Introduction to MDX

This module describes the fundamentals of MDX and explains how to build calculations, such as calculated members and named sets.

Lessons:

  • MDX Fundamentals
  • Adding Calculations to a Cube
  • Using MDX to Query a Cube

Lab: Using MDX

Module 6: Customising Cube Functionality

This module describes how to enhance a cube with Key Performance Indicators (KPIs), actions, perspectives, and translations.

Lessons:

  • Implementing Key Performance Indicators
  • Implementing Actions
  • Implementing Perspectives
  • Implementing Translations

Lab: Customising a Cube

Module 7: Implementing an Analysis Services Tabular Data Model

This module describes Analysis Services tabular data models and explains how to develop a tabular data model using the SQL Server Data Tools for Business Intelligence (BI) add-in for Visual Studio.

Lessons:

  • Introduction to Tabular Data Models
  • Creating a Tabular Data Model
  • Using an Analysis Services Tabular Data Model in an Enterprise BI Solution

Lab: Implementing an Analysis Services Tabular Data Model

Module 8: Introduction to Data Analysis Expression (DAX)

This module explains the fundamentals of the DAX language. It also explains how delegates can use DAX to create calculated columns and measures, and how they can be used in your tabular data models.

Lessons:

  • DAX Fundamentals
  • Using DAX to Create calculated Columns and Measures in a Tabular Data Model

Lab: Creating Calculated Columns and Measures by using DAX

Module 9: Implementing Reports with SQL Server Reporting Services

This module introduces Microsoft SQL Server Reporting Services and discusses the tools and techniques that a professional BI developer can use to create and publish reports.

Lessons:

  • Introduction to Reporting Services
  • Creating a Report with Report Designer
  • Grouping and Aggregating Data in a Report
  • Showing Data Graphically
  • Filtering Reports Using Parameters

Lab: Creating a Report with Report Designer

Module 10: Automating Report Execution and Delivery

This module describes how to apply security and report execution settings, and how to create subscriptions to deliver reports.

Lessons:

  • Managing Report Security
  • Managing Report Execution
  • Delivering Reports with Subscriptions and Data Alerts
  • Troubleshooting Reporting Services

Lab: Implementing Report Subscriptions

Module 11: Delivering BI with SharePoint PerformancePoint Services

This module introduces Microsoft SharePoint Server as a platform for BI, and then focuses on building BI dashboards and scorecards with PerformancePoint Services.

Lessons:

  • Introduction to SharePoint Server as a BI Platform
  • Planning Security for a SharePoint Server BI Solution
  • Planning for PerformancePoint Services

Lab: Implementing PerformancePoint Services

Module 12: Performing Predictive Analysis with Data Mining

This module introduces data mining, describes how to create a data mining solution, how to validate data mining models, how to use the Data Mining Add-ins for Microsoft Excel, and how to incorporate data mining results into Reporting Services reports.

Lessons:

  • Overview of Data Mining
  • Using the Data Mining Add-in for Excel
  • Creating a Custom Data Mining Solution
  • Validating a Data Mining Model
  • Connecting to and Consuming Data Mining Data

Lab: Using Data Mining to Support a Marketing Campaign

Show moredown

Who should attend this Microsoft SQL Server Training?

This course is intended for delegates who need to fulfil a Business Intelligence Developer role to create analysis and reporting solutions. Their responsibilities may include:

  • Implementing analytical data models, such as OLAP cubes
  • Implementing reports and managing report delivery
  • Creating business performance dashboards
  • Supporting data mining and predictive analysis

Prerequisites

Before attending this course, delegates should possess or be able to demonstrate:

  • At least 2 years’ experience of working with relational databases
  • Designing a normalised database
  • Creating tables and relationships
  • Querying with Transact-SQL
  • Some basic knowledge of data warehouse schema topology (including star and snowflake schemas)
  • Some exposure to basic programming constructs (such as looping and branching)
  • An awareness of key business priorities such as revenue, profitability, and financial accounting is desirable

 

Implementing Data Models and Reports with SQL Server 2014 Course Overview | M20466

This 5-day course focuses on creating managed enterprise BI solutions. It describes how to implement multidimensional and tabular data models, deliver reports with Microsoft® SQL Server® Reporting Services, create dashboards with Microsoft SharePoint Server PerformancePoint Services, and discover business insights by using data mining. This course is designed for delegates who are interested in learning SQL Server 2012 or SQL Server 2014. It covers the new features in SQL Server 2014 as well as the important capabilities across the SQL Server data platform. This Implementing Data Models and Reports with SQL Server course is fully accredited by Microsoft through the Microsoft Silver Partnership held by The Knowledge Academy.

After completing this course, delegates will be able to:

  • Describe the components, architecture, and nature of a BI solution
  • Create a multidimensional database with Analysis Services
  • Implement dimensions in a cube
  • Implement measures and measure groups in a cube
  • Use MDX Syntax
  • Customise a cube
  • Implement a Tabular Data Model in SQL Server Analysis Services
  • Use DAX to enhance a tabular model
  • Create reports with Reporting Services
  • Enhance reports with charts and parameters
  • Manage report execution and delivery
  • Implement a dashboard in SharePoint Server with PerformancePoint Services
  • Use Data Mining for Predictive Analysis

Show moredown

Implementing Data Models and Reports with SQL Server 2014 Exam Information | M20466

 

What's included in this Microsoft SQL Server Course?

  • Delegate pack consisting of course notes and exercises
  • Manual
  • Experienced Instructor
  • Refreshments

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (2 days)

Online Self-paced (16 hours)

Updating Your Skills to SQL Server 2017 (M10998) Course Outline

 

Module 1: Introduction to SQL Server 2017

The aim of this module is to introduce delegates to the key capabilities and components of SQL Server 2017

Lessons:

  • Overview of SQL Server 2017
  • Functionality across versions

Module 2: What’s new in SQL Server Performance, Scalability, and Availability

This module introduces the performance enhancements provided by Adaptive Query Processing and Automatic Tuning, as well as the scalability and availability enhancements provided by new Availability Group architectures, including Read-Scale Availability Groups and Availability Groups with SQL Server on Linux

Lessons:

  • Adaptive query processing
  • Automatic tuning
  • Availability Scalability

Lab: Performance and availability – adaptive query processing

  • Interleaved execution
  • Batch mode memory grant feedback
  • Batch mode adaptive joins

Module 3: What’s New in SQL Server data Access

SQL Server 2017 introduces SQL Graph which enables you to define the relationships between your data items in a table rather than calculating them during a query, reducing the query cost and simplifying your data structure.

Lessons:

  • SQL Graph

Lab: SQL Graph

  • Create a Graph database
  • Query a Graph database

Module 4: What’s new in SQL Server Reporting and BI

This module describes what’s new in SQL Server reporting and BI.

Lessons:

  • Power BI report Server
  • Reporting Services update

Lab: Reporting and BI

  • Deploy Power BI report server
  • Add commenting to a report

Module 5: New and Enhanced Features in SQL Server Analysis Services

This module introduces new and enhanced features in SQL Server Analysis Services, with particular emphasis on the tabular data model.

Lessons:

  • Tabular model updates

Lab: Ragged hierarchies

  • View existing reports
  • Hide blank members

Module 6: New and Enhanced Data Analytics Features

This module introduces new and enhanced data analytics features.

Lessons:

  • New and enhanced data analytics features

Lab: Data analytics with Python

  • Enable external script execution
  • Run Python scripts

Module 7: What’s new in SQL Server in the Cloud

Organizations want the best value from a mixture of cloud services and their existing investment in on-premises hardware and software. This module covers a comparison between the features of Microsoft Azure SQL Database and Microsoft SQL Server 2017, as well as new features that make it easier for you to migrate databases from on-premises SQL Server instances to Azure SQL Database.

Lessons:

  • Azure SQL database features
  • Managed Instances

Lab: Managed instances

  • Use the different features available in Azure SQL database and SQL Server 2017
  • Manage Azure instances

Module 8: SQL Server on Linux

This module covers the features of SQL Server on Linux, as well as the process for deploying SQL Server on Linux and using Docker.

Lessons:

  • SQL Server on Linux
  • Deploying SQL Server on Linux

Lab: SQL Server in Docker containers

  • Deploy a SQL Server Docker image
  • Run a SQL Server instance inside a Docker container
  • Connect to a SQL Server Instance running in a Docker container

Show moredown

Who Should Attend this Course?

The primary audience for this course is existing database professionals with experience of SQL Server 2016 who want to update their skills to SQL Server 2017. 

The secondary audience is existing SQL Server 2016 MCSAs who want to prepare for the Upgrade exam for SQL Server 2017 certification.

Prerequisites

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

  • Experience of building and managing database, data warehouse, and business intelligence (BI) solutions with SQL Server 2016.
  • Familiarity with the Windows Server 2016 operating system and networking.
  • Familiarity with Microsoft Office 2016

Updating Your Skills to SQL Server 2017 (M10998) Course Overview

This two-day course is aimed at database professionals looking to update their skills to cover SQL Server 2017.

After completing the course, delegates will be able to:

  • Describe key capabilities and components of SQL Server 2017
  • Describe new and enhanced features in SQL Server Performance, Availability, and Scalability
  • Describe new and enhanced features in SQL Server data access
  • Describe new and enhanced features in SQL Server reporting and BI
  • Describe new and enhanced features in SQL Server OLAP
  • Describe new and enhanced data analytics features
  • Describe new and enhanced features in SQL Server Cloud deployments
  • Describe SQL Server on Linux functionality

 

Show moredown

Updating Your Skills to SQL Server 2017 (M10998) Examination

The Knowledge Academy does not provide an examination for this course. ​

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (5 days)

Online Self-paced (40 hours)

Developing Microsoft SQL Server 2014 Databases Course Outline | M20464

This course includes the following modules:

Module 1: Introduction to Database Development

This module introduces database development and the key tasks that a database developer would typically perform.

Lessons:

  • Introduction to the SQL Server Platform
  • SQL Server Database Development Tasks

Lab: Introduction to Database Development

Module 2: Designing and Implementing Tables

This module explains how to design, create, and alter tables. It also focuses on working with schemas.

Lessons:

  • Designing Tables
  • Data Types
  • Working with Schemas
  • Creating and Altering Tables
  • Partitioning Data
  • Compressing Data

Lab: Designing and Implementing Tables

Module 3: Ensuring Data Integrity through Constraints

This module explains how to enforce data integrity, and implement domain integrity to maintain high quality data. In addition, it focuses on implementing Entity and Referential Integrity.

Lessons:

  • Enforcing Data Integrity
  • Implementing Domain Integrity
  • Implementing Entity and Referential Integrity

Lab: Ensuring Data Integrity through Constraints

Module 4: Introduction to Indexing

This module describes the concept of an index and discusses selectivity, density, and statistics. It covers appropriate data type choices and choices around composite index structures.

Lessons:

  • Core Indexing Concepts
  • Data Types and Indexes
  • Single Column and Composite Indexes

Lab: Implementing Indexes

Module 5: Designing Optimised Index Strategies

This module explains covering indexes and the INCLUDE clause as well as the use of padding, hints, and statistics. The module also covers the use of the Database Engine Tuning Advisor and index-related dynamic management views to assess indexing strategies.

Lessons:

  • Covering Indexes
  • Managing Indexes
  • Working with Execution Plans
  • Using the DTE

Lab: Designing Optimised Index Strategies

Module 6: Columnstore Indexes

This module explains columnstore indexes and how to use them to maximise the performance and scalability of database applications.

Lessons:

  • Introduction to Columnstore indexes
  • Creating Columnstore Indexes
  • Working with Columnstore Indexes

Lab: Using Columnstore Indexes

Module 7: Designing and Implementing Views

This module introduces Views, and explains how to create and manage Views. It also focuses on the performance consideration for Views.

Lessons:

  • Introduction to Views
  • Creating and Managing Views
  • Performance Considerations for Views

Lab: Designing and Implementing Views

Module 8: Designing and Implementing Stored Procedures

This module describes the potential advantages of the use of stored procedures along with guidelines on creating them.

Lessons:

  • Introduction to Stored Procedures
  • Working with Stored Procedures
  • Implementing Parameterised Stored Procedures
  • Controlling Execution Context

Lab: Designing and Implementing Stored Procedures

Module 9: Designing and Implementing User-Defined Functions

This module explains how to design and implement user-defined functions that enforce business rules or data consistency, and modify and maintain existing functions written by other developers.

Lessons:

  • Overview of Functions
  • Designing and Implementing Scalar Functions
  • Designing and Implementing Table-Valued Functions
  • Implementation Considerations for Functions
  • Alternatives to Functions

Lab: Designing and Implementing User-Defined Functions

Module 10: Responding to Data Manipulation via Triggers

This module explains what DML triggers are and how they enforce data integrity. It also focuses on the different types of triggers available, and how to define triggers in a database.

Lessons:

  • Designing DML Triggers
  • Implementing DML Triggers
  • Advanced Trigger Concepts

Lab: Responding to Data Manipulation via Triggers

Module 11: Using In-Memory Tables

This module covers the creation of in-memory tables and native stored procedures and discusses the advantages and disadvantages of using in-memory tables.

Lessons:

  • In-Memory Tables
  • Native Stored Procedures

Lab: In-Memory OLTP

Module 12: Implementing Managed Code in SQL Server 2014

This module explains how to use CLR integrated code to create user-defined database objects that are managed by the .NET Framework.

Lessons:

  • Introduction to SQL CLR Integration
  • Importing and Configuring Assemblies
  • Implementing SQL CLR Integration

Lab: Implementing Managed Code in SQL Server 2014

Module 13: Storing and Querying XML Data in SQL Server

This module introduces XML and shows how XML data can be stored within SQL Server and then queried, including queries written in a language called XQuery.

Lessons:

  • Introduction to XML and XML Schemas
  • Storing XML Data and Schemas in SQL Server
  • Implementing the XML Data Type
  • Using the T-SQL FOR XML Statement
  • Getting Started with XQuery

Lab: Storing and Querying XML Data in SQL Server 2014

Module 14: Working with SQL Server 2014 Spatial Data

This module introduces Spatial Data, and explains how to work with SQL Server Spatial Data Types.

Lessons:

  • Introduction to Spatial Data
  • Working with SQL Server Spatial Data Types
  • Using Spatial Data in Applications

Lab: Working with SQL Server Spatial Data

Module 15: Incorporating Data Files into Databases

Lessons:

  • Querying Data with Stored Procedures
  • Implementing FILESTREAM and File Tables
  • Searching Data Files

Lab: Implementing a Solution for Storing Data Files

Show moredown

Who should attend this Microsoft SQL Server Course?

The primary audience for this course is IT Professionals who want to become skilled on SQL Server 2014 product features and technologies for implementing a database.

The secondary audiences for this course are individuals who are developers from other product platforms or previous versions of SQL Server looking to become skilled in the implementation of a SQL Server 2014 database.

This course is also intended for delegates preparing for the Microsoft Exam 70-464, Developing Microsoft SQL Server Databases.

Prerequisites

Before attending this course, delegates should possess or be able to demonstrate:

  • Knowledge of writing T-SQL queries
  • Knowledge of basic relational database concepts​

Developing Microsoft SQL Server 2014 Databases Training | M20464

This 5-day course is intended to teach delegates about SQL Server 2014, with a focus on logical table design, indexing, and query plans. It also explains how to create database objects including views, stored procedures, along with parameters, and functions. Other common aspects of procedure coding, such as indexes, concurrency, error handling, and triggers are also covered in this course. Taking this course will also help delegates to prepare for Exam 70-464, Developing Microsoft SQL Server Databases.

This Configuring and Operating a Hybrid Cloud with Microsoft Azure Stack course is fully accredited by Microsoft through the Microsoft Silver Partnership held by The Knowledge Academy.

After completing this course, delegates will be able to:

  • Introduce the entire SQL Server platform and its major tools. The course will cover editions, versions, basics of network listeners, and concepts of services and service accounts
  • Determine appropriate data types to be used when designing tables, convert data between data types, and create alias data types
  • Be aware of good design practices regarding SQL Server tables and be able to create tables using T-SQL (partitioned tables are not covered)
  • Implement PRIMARY KEY, FOREIGN KEY, DEFAULT, CHECK, and UNIQUE constraints, and investigate cascading FOREIGN KEY constraints
  • Determine appropriate single column and composite indexes strategies
  • Create tables as heaps and tables with clustered indexes
  • Consider the design of a table and suggest an appropriate structure
  • Read and interpret details of common elements from execution plans
  • Design effective non-clustered indexes
  • Design and implement views and stored procedures
  • Work with table types, table valued parameters, and use the MERGE statement to create stored procedures that update data warehouses
  • Design and implement functions, both scalar and table-valued (also describe where they can lead to performance issues)
  • Perform a basic investigation of a deadlock situation and learn how transaction isolation levels affect application concurrency
  • Use both traditional T-SQL error handling code and structured exception handling
  • Design and implement DML triggers
  • Learn appropriate uses for SQL CLR integration and implement an existing .NET assembly within SQL Server
  • Store XML data and schemas in SQL Server
  • Perform basic queries on XML data in SQL Server

Show moredown

Developing Microsoft SQL Server 2014 Databases Exam Information | M20464

Taking this course will help to prepare delegates for the Microsoft Exam 70-464, Developing Microsoft SQL Server Databases. The Knowledge Academy does not provide the exam and it must be taken separately. The examination will cover the following areas:

  • Implement database objects (30–35%)
  • Implement programming objects (15-20%)
  • Design database objects (25–30%)
  • Optimise and troubleshoot queries (25–30%)

What's included in this Microsoft SQL Server Course?

  • Delegate pack consisting of course notes and exercises
  • Manual
  • Experienced Instructor
  • Refreshments

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (5 days)

Online Self-paced (40 hours)

Analyzing Data with SQL Server Reporting Services Course Outline | SQL Training | M10990

This course includes the following modules:

Module 1: Introduction to Reporting Services

This module introduces Microsoft SQL Server Reporting Services, its components, and the tools used to work with it.

Lessons:

  • Introduction to reporting services
  • Reporting services components
  • Reporting services tools

Lab: Exploring reporting services

  • Exploring reports
  • Reporting services configuration

Module 2: Reporting Services Data Sources

Almost every report published by using SQL Server Reporting Services (SSRS) is built using data obtained from one or more source systems. This lesson explains how to configure SSRS to interact with source data systems by working with data sources.

Lessons:

  • Data sources
  • Connection strings
  • Datasets

Lab: Configuring data access with report builder

  • Configuring data access with report builder

Lab: Configuring data access with report designer

  • Configuring data access with report designer

Module 3: Creating Paginated Reports

In this module, delegates will discover how to create reports. This module demonstrates how to create different types of reports in both applications, in addition to using the Report Wizard.

Lessons:

  • Creating a report with the report wizard
  • Creating a report
  • Publishing a report

Lab: Creating reports

  • Use the report wizard – report designer
  • Use the report wizard – report builder
  • Creating and publishing a report – report designer
  • Creating and publishing a report – report builder

Module 4: Working with Reporting Services Data

It is common for business requirements to change regarding the information they need and how they want data presented. On viewing a detailed report, senior management might ask for higher level, summarised, or filtered versions of the same report. Report Builder and Report Designer support these scenarios via filtering, sorting, drilldowns, grouping and the parameterising of reports. In this module, delegates will learn how to use filters and parameters to make reports more dynamic and useful to business users.

Lessons:

  • Data filters
  • Report parameters
  • Implementing report filters and parameters

Lab: Create a parameterised report

  • Using parameters in report designer
  • Using parameters in report builder

Module 5: Visualising Data with Report Services

As the amount of data being generated continues to grow, the need to make sense of its meaning increases. Delegates will use data visualisation to make data easier to understand, and faster to interpret. Data visualisations highlight comparisons, show trends, and convey scale much faster than a table of numbers could. The detail is important, but visualisations are a highly effective way of conveying meaning and insights quickly and accurately.

Lessons:

  • Formatting data
  • Images and charts
  • Databars, sparklines, indicators, gauges, and maps

Lab: Manage formatting

  • Report designer
  • Report builder

Module 6: Summarising Report Data

In this module, delegates will learn how to create group structures, summarise data, and provide interactivity in your reports, so that users see the level of detail or summary that they need.

Lessons:

  • Sorting and grouping
  • Report sub-reports
  • Drilldown and drill through

Lab: Summarising report data

  • Sorting and grouping in report builder
  • Sorting and grouping in report designer

Module 7: Sharing Reporting Services Reports

This module covers report scheduling, caching, and the report life cycle, and automatic subscription and delivery of reports.

Lessons:

  • Schedules
  • Report caching, snapshots, and comments
  • Report subscription and delivery

Lab: Sharing reporting services reports

  • Create a shared schedule
  • Configure caching
  • Subscribe to a report

Module 8: Administering Reporting Services

System administrators take responsibility for the configuration and day-to-day operations of IT systems. In SQL Server Reporting Services (SSRS), administrative tasks include the configuration of the web portal and web service, branding the web portal, and ensuring that access to sensitive reports is carefully controlled. Administrators also monitor and optimize performance.

Lessons:

  • Administering reporting services
  • Reporting services configuration
  • Reporting services performance

Lab: Administering reporting services

  • Authorise access to reports
  • Web portal branding

Module 9: Extending and Integrating Reporting Services

Although Reporting Services is a powerful tool, its built-in capabilities might not always meet requirements. This module covers the methods for extending the functionality of Reporting Services with expressions and custom code. Delegates will also learn about the methods for working with Reporting Services programmatically, and integrating Reporting Services reports into other applications.

Lessons

  • Expressions and embedded code
  • Extending reporting services
  • Integrating reporting services

Lab: Extending and integrating reporting services

  • Custom code – report designer
  • Custom code – report builder
  • URL access

Module 10: Introduction to Mobile Reports

This module introduces the design and publication of reports that are intended for consumption on mobile devices, such as smartphones and tablets. Microsoft SQL Server Reporting Services (SSRS) includes support for mobile reports, although the tools that are used to design and publish mobile reports are different to the tools used for the paginated reports discussed in the earlier modules of this course.

Lessons:

  • Overview of mobile reports
  • Preparing data for mobile reports
  • Mobile report publisher

Lab: Introduction to mobile reports

  • Format data for a mobile report
  • Create a mobile report
  • Create KPIs

Module 11: Developing Mobile Reports

In this module, delegates will learn about the element types that can be added to Microsoft SQL Server Reporting Services mobile reports. Delegates will also learn about working with dataset parameters, and how to add drillthrough actions to your reports.

Lessons

  • Designing and publishing mobile reports
  • Drillthrough in mobile reports

Lab: Developing mobile reports

  • Add a dataset with parameters
  • Design a mobile report
  • Publish a mobile report
  • Add a drillthrough to a custom URL

Show moredown

Who Should Attend this Course?

The primary audience for this course is database professionals who need to undertake a BI developer role to create reports. Primary responsibilities include implementing reports and creating mobile reports.

The secondary audiences for this course are power information workers. 

Prerequisites

Before attending this course, delegates should possess or be able to demonstrate:

  • Basic knowledge of the Microsoft Windows operating system and its core functionality
  • Working knowledge of Transact-SQL 

Analyzing Data with SQL Server Reporting Services Training | SQL Training | M10990

This 5-day course is designed to help delegates learn how to implement an SQL Server 2016 Reporting Services solution for data analysis in an organisation. The course discusses how to use the Reporting Services development tools to create and manage reports and implement self-service BI solutions.

This Analysing Data with SQL Server Reporting Services course is fully accredited by Microsoft through the Microsoft Silver Partnership held by The Knowledge Academy.

After completing this course, delegates will be able to:

  • Describe Reporting Services and its components
  • Describe Reporting Services data sources
  • Implement paginated reports
  • Work with Reporting Services data
  • Visualise data with Reporting Services
  • Aggregate report data
  • Share reporting services reports
  • Administer Reporting Services
  • Expand and integrate Reporting Services
  • Describe mobile reports
  • Develop mobile reports​​

Show moredown

What's Included?

  • Delegate pack consisting of course notes and exercises
  • Manual
  • Experienced Instructor
  • Refreshments

 

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (5 days)

Online Self-paced (40 hours)

Designing Business Intelligence Solutions with Microsoft SQL Server 2014 Course Outline | M20467

This course includes the following modules:

Module 1: Planning a BI Solution

This module enables delegates to plan the components of a BI Solution.

Lessons:

  • Elements of a BI Solution
  • Planning a BI Project
  • The Microsoft BI Platform

Lab: Planning BI Solutions

Module 2: Planning SQL Server Business Intelligence Infrastructure

This module describes how to use plan appropriate BI Infrastructure given a set of BI Parameters.

Lessons:

  • Considerations of BI Infrastructure
  • Planning Data Warehouse Hardware

Lab: Planning BI Infrastructure

Module 3: Designing a Data Warehouse

This module describes how to design a data warehouse given a set of user requirements.

Lessons:

  • Data Warehouse Design Overview
  • Designing Dimension Tables
  • Design FACT tables

Lab: Designing a Data Warehouse Logical Schema

Module 4: Designing an ETL solution

This lesson describes how to design an extract, transform, and load (ETL) solution.

Lessons:

  • ETL Overview
  • Planning for Data Extraction
  • Planning for Data Transformations
  • Planning for Data Loading

Lab: Designing an ETL Solution

Module 5: Designing Analytical Data Models

This module describes how to design analytical data models for specific BI scenarios.

Lessons:

  • Introduction to Analytical Data Models
  • Designing Analytical Data Models

Lab: Designing Analytical Data Models

Lab: Designing Dimensions and Hierarchies

Module 6: Planning a BI Delivery Solution

This module describes how to choose an appropriate delivery solution for a given scenario.

Lessons:

  • Considerations for BI delivery
  • Common Reporting Scenarios
  • Choosing a Reporting Tool

Lab: Planning a BI Delivery Solution

Module 7: Designing a Reporting Services Solution

This module describes how to design a reporting services solution.

Lessons:

  • Planning a Reporting Solution
  • Designing Reports
  • Planning Report Consistency

Lab: Designing a Reporting Services Solution

Module 8: Designing an Excel-based Reporting Solution

In this module, delegates will learn how to design a reporting solution using Excel.

Lessons:

  • Using Excel for Data Reporting and Analysis
  • PowerPivot in Excel
  • Power View in Excel

Lab: Designing and Excel Reporting Solution

Module 9: Planning a SharePoint Server BI Solution

This module introduces the use of SharePoint Server as a BI solution.

Lessons:

  • Introduction to SharePoint Server as a BI Platform
  • Planning Security for a SharePoint Server BI Solution
  • Planning Reporting Services Configuration
  • Planning PowerPivot Configuration
  • Planning for PerformancePoint Services

Lab: Implementing a SharePoint Server BI Solution

Lab: Implementing PerformancePoint Services

Module 10: Monitoring and Optimising a BI Solution

At the conclusion of this module, delegates will be able to optimise and monitor a BI solution.

Lessons:

  • Overview of BI Monitoring
  • Monitoring and Optimising the Data Warehouse
  • Monitoring and Analysing Analysis Services
  • Monitoring and Optimising Reporting Services

Lab: Monitoring and Optimising a BI Solution

Module 11: Operating a BI Solution

By the end of this module, delegates will be able to plan management and maintenance operations of a BI solution.

Lessons:

  • Overview of BI Operations
  • ETL Operations
  • Data Warehouse Operations
  • Analysis Services Operations
  • Reporting Services Operations

Lab: Operating a BI Solution

Show moredown

Who should attend this Microsoft SQL Server Course?

This course is intended for delegates who need to fulfil a Business Intelligence Developer role to create analysis and reporting solutions. Their responsibilities may include:

  • Implementing analytical data models, such as OLAP cubes
  • Implementing reports and managing report delivery
  • Creating business performance dashboards
  • Supporting data mining and predictive analysis

Prerequisites

Before attending this course, delegates should possess or be able to demonstrate:

  • A basic understanding of dimensional modelling (star schema) for data warehouses
  • Basic server hardware knowledge
  • The ability to create Integration Services packages that include control flows and data flows
  • The ability to create a basic multidimensional cube with Analysis Services
  • The ability to create a basic tabular model with PowerPivot and Analysis Services
  • The ability to create Reporting Services reports with Report Designer
  • The ability to implement authentication and permissions in the SQL Server database engine, Analysis Services, and Reporting Services
  • Familiarity with SharePoint Server and Microsoft Office applications – particularly Excel​​

Designing Business Intelligence Solutions with Microsoft SQL Server 2014 Course Overview | M20467

This 5-day course focuses on teaching delegates such as database and business intelligence (BI) professionals how to plan and design a BI solution that is based on Microsoft SQL Server 2014 and other Microsoft BI technologies.

This Designing Business Intelligence Solutions with Microsoft SQL Server 2014 course is fully accredited by Microsoft through the Microsoft Silver Partnership held by The Knowledge Academy.

After completing this course, delegates will be able to:

  • Plan a BI solution
  • Plan SQL Server BI infrastructure
  • Design a data warehouse
  • Design an extract, transform and load (ETL) solution
  • Design analytical data models
  • Plan a BI delivery solution
  • Design a Reporting Services solution
  • Design a Microsoft Excel-based reporting solution
  • Plan a SharePoint Server BI solution
  • Monitor and optimise a BI solution
  • Operate a BI solution

Show moredown

Designing Business Intelligence Solutions with Microsoft SQL Server 2014 Exam Information | M20467

 

 

What's included in this Microsoft SQL Server Course?

  • Delegate pack consisting of course notes and exercises
  • Manual
  • Experienced Instructor
  • Refreshments

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (3 days)

Online Self-paced (24 hours)

Analyzing and Visualizing Data with Microsoft Excel Course Outline | SQL Server Training | M20779

This course includes the following modules:

Module 1: Data Analysis in Excel

This module looks at the classic Excel dashboard and at ways to extend it.

Lessons:

  • Classic Data Analysis with Excel
  • Excel Pivot Tables
  • Limitations of Classic Data Analysis

Lab: Building a Classic Excel Dashboard

  • Formatting Data
  • Building a Pivot Table
  • Adding pivot charts and a slicer

 

Module 2: The Excel Data Model

This module looks at the classic Excel data model and at ways to extend it.

Lessons:

  • Using an Excel Data Model
  • DAX

Lab: Explore an Excel Data Model

  • Create Calculated Columns
  • Format Data Model Data
  • Create Measures
  • Analyse the Data

 

Module 3: Importing Data from Files

This module looks at pre-formatting and importing CSV files.

Lessons:

  • Importing Data into Excel
  • Shaping and Transforming Data
  • Loading Data

Lab: Importing Data from a CSV File

  • Import and Transform Data from a CSV File
  • Add Data from a Folder

 

Module 4: Importing Data from Databases

This module looks at how to import data into Excel from a SQL Server database.

Lessons:

  • Available Data Sources
  • Previewing, Shaping, and Transforming Data
  • Table Relationships and Hierarchies
  • Loading Data

Lab: Import Data from Multiple Sources

  • Import Data from SQL Server
  • Import Data from a CSV File
  • Create a Data Table

 

Module 5: Importing Data from Excel Reports

This module describes how to import data from a report.

Lessons:

  • Importing Data from Excel Reports
  • Transforming Excel report Data

Lab: Importing Data from a Report

  • Import Data from Excel
  • Transform the Excel Data
  • Load the Data into an Excel Data Model

 

Module 6: Creating and Formatting Measures

This module describes how to create and format measures.

Lessons:

  • DAX
  • Measures
  • Advanced DAX Functions

Lab: Creating Measures using Advanced DAX Functions

  • Last year comparison
  • Year to date
  • Market Share

 

Module 7: Visualising Data in Excel

This module describes how to visualise data in Excel.

Lessons:

  • Pivot Charts
  • Cube Functions
  • Charts for Cube Functions

Lab: Data Visualisation in Excel

  • Create a Tabular Report
  • Create a Pivot Chart
  • Add Slicers to Charts

 

Module 8: Using Excel with Power BI

This module describes how to use Excel with Power BI.

Lessons:

  • Power BI
  • Uploading Excel Data to Power BI
  • Power BI Mobile App

Lab: Creating a Power BI Dashboard with Excel

  • Uploading Excel Data
  • Creating a Power BI Dashboard
  • Using Power BI desktop

Show moredown

Who Should Attend this Course?

This course is intended for delegates who are experienced in data analysis with Excel and who wish to add BI techniques.

The course will likely be useful to SQL Server report creators who are interested in alternative methods of presenting data. 

Prerequisites

Before attending this course, delegates should possess or be able to demonstrate:

  • Basic knowledge of the Microsoft Windows operating system and its core functionality
  • Working knowledge of relational databases
  • Extensive knowledge of Excel spreadsheets including formulas, charts, filtering, sorting, and sub-totals
  • Basic knowledge of data analytics

Analyzing and Visualizing Data with Microsoft Excel Course Overview | SQL Server Training | M20779

This 3-day course is intended for delegates who want to learn to add BI techniques to Excel data analysis. The course goes beyond the capabilities of tables and charts and uses Pivot Charts, the Excel Data Model, and Power BI.

This Analysing and Visualising Data with Microsoft Excel course is fully accredited by Microsoft through the Microsoft Silver Partnership held by The Knowledge Academy.

After completing this course, delegates will be able to:

  • Explore and extend a classic Excel dashboard
  • Explore and extend an Excel data model
  • Pre-format and import a .CSV file
  • Import data from a SQL Server database
  • Import data from a report
  • Create measures using advanced DAX functions
  • Create data visualisations in Excel
  • Create a Power BI dashboard with Excel

Show moredown

The Knowledge Academy does not provide an examination for this course. Delegates will be given access to:

  • Tuition from one of our expert trainers
  • Certificate of completion
  • Refreshments

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (3 days)

Online Self-paced (24 hours)

Designing Database Solutions for Microsoft SQL Server 2014 Course Outline | M20465

This course includes the following modules:

Module 1: Introduction to Enterprise Data Architecture

As organisations grow to enterprise scale, their IT infrastructure requirements become more complex and the network environment often includes an increasing number of servers, client computers, network segments, and other components. Because data is fundamental to most IT operations, careful thought must be given to the provisioning and management of databases across the enterprise.

Lessons:

  • Considerations for Enterprise Data
  • Assessing an Existing Infrastructure

Lab: Assessing an Existing Enterprise Data Infrastructure

Module 2: Multi-Server Configuration Management

When an enterprise infrastructure includes multiple database servers, it can be useful to standardise and enforce configuration settings in order to ensure compliance and manageability. This module discusses Policy-Based Management in SQL Server, and describes how it can be used together with enterprise configuration management tools such as Microsoft System Centre to aid enterprise database server management.

Lessons:

  • Policy-Based Management
  • Microsoft System Centre

Lab: Planning and Implementing Policy-Based Management

Module 3: Monitoring SQL Server 2014 Health

This module describes Data Collector and the SQL Server Utility Control Point (UCP), two features of SQL Server 2014 that enable delegates to perform in-depth health monitoring across the enterprise.

Lessons:

  • Introduction to Health Monitoring
  • Data Collector
  • SQL Server Utility

Lab: Monitoring SQL Server Health

Module 4: Consolidating Database Workloads with SQL Server 2014

This module provides an overview of the benefits of consolidating database workloads by using SQL Server 2014, and describes the different options for implementing a consolidation strategy. It also describes how delegates can manage a consolidated infrastructure in various scenarios.

Lessons:

  • Considerations for Database Server Consolidation
  • Managing Resources in a Consolidated Database Infrastructure

Lab: SQL Server Consolidation

Module 5: Introduction to Cloud Data Solutions

Cloud computing has risen to prominence very rapidly within the world of IT, and many organisations have implemented cloud-based solutions that encompass all or part of their infrastructure. This module describes some of the fundamental concepts of cloud computing and outlines how to include SQL Server 2014 in a private cloud infrastructure.

Lessons:

  • Overview of Cloud Computing
  • SQL Server in a Private Cloud

Lab: Preparing a SQL Server Installation in a Virtual Machine Template

Module 6: Introduction to High Availability in SQL Server 2014

Maintaining highly available database services is vital in a 24-hour operating environment. SQL Server 2014 includes many features that can help organizations to deliver the levels of service they require to drive their businesses. This module explains the different ways that delegates can implement high availability by using SQL Server 2014. It also describes how to use log shipping to promote resilience for individual user databases.

Lessons:

  • High Availability Concepts and Options in SQL Server 2014
  • Log Shipping

Lab: Using Log Shipping

Module 7: Clustering with Windows Server and SQL Server 2014

SQL Server 2014 is closely integrated with the Windows Server Failover Clustering feature in Windows Server 2012 and Windows Server 2012 R2, enabling delegates to create enterprise-class clustering solutions that can deliver comprehensive high availability and disaster recovery solutions. This module explains how Windows Server Failover Clustering and SQL Server AlwaysOn Failover Cluster Instances work, and describes how to implement clustering to protect service availability.

Lessons:

  • Introduction to Windows Server Failover Clustering
  • SQL Server AlwaysOn Failover Cluster Instances

Lab: Implementing an AlwaysOn Failover Cluster Instance

Module 8: AlwaysOn Availability Groups

SQL Server 2014 includes AlwaysOn Availability Groups to provide high availability for groups of databases. This module describes AlwaysOn Availability Groups in SQL Server 2014, explains the key concepts of AlwaysOn Availability Groups, and describes how delegates can use them to maintain highly available databases.

Lessons:

  • Introduction to AlwaysOn Availability Groups
  • Working with AlwaysOn Availability Groups
  • Considerations for Using AlwaysOn Availability Groups

Lab: Implementing and Testing an AlwaysOn Availability Group

Module 9: Planning High Availability and Disaster Recovery

This module describes the planning considerations for high availability and disaster recovery, and provides common implementation scenarios for on-premises, hybrid, and Microsoft Azure environments.

Lessons:

  • High Availability and Disaster Recovery with SQL Server 2014
  • SQL Server High Availability and Disaster Recovery Solutions

Lab: Planning High Availability and Disaster Recovery

Module 10: Replicating Data

SQL Server replication enables users to copy and distribute data and database objects to other computers and locations in an enterprise, which can improve availability and scalability. This module provides an overview of SQL Server replication and explains the agents used to implement replication. It also describes some common replication scenarios, how to design an appropriate replication system, and how to monitor and troubleshoot replication.

Lessons:

  • SQL Server Replication
  • Planning Replication

Lab: Planning and Implementing Replication

Show moredown

Who should attend this Microsoft SQL Server Course?

This course is intended for delegates who work as database professionals and plan, implement, and manage database solutions. Delegates will find this course useful if they engage in any of the following tasks:

  • Planning and implementing database infrastructure
  • Planning and implementing consolidation strategies
  • Planning and implementing high availability solutions
  • Planning and implementing disaster recovery solutions

Prerequisites

Before attending this course, delegates should possess or be able to demonstrate:

  • At least 2 years’ experience of working with relational databases
  • Planning and implementing databases
  • Managing databases
  • Querying with Transact-SQL
  • Some basic knowledge of high availability and disaster recovery​​

Designing Database Solutions for Microsoft SQL Server 2014 Course Overview | M20465

This 3-day course focuses on planning and implementing enterprise database infrastructure solutions by using SQL Server 2014 and other Microsoft technologies. It describes how to consolidate SQL Server workloads and how to plan and implement high availability and disaster recovery solutions.

This course is designed for delegates who are interested in learning SQL Server 2012 or SQL Server 2014. It covers the new features in SQL Server 2014 as well as the important capabilities across the SQL Server data platform

This Designing Solutions for Microsoft SQL Server 2014 course is fully accredited by Microsoft through the Microsoft Silver Partnership held by The Knowledge Academy.

After completing this course, delegates will be able to:

  • Assess an existing enterprise environment
  • Plan and implement Policy-Based Management
  • Describe the considerations for consolidating workloads with SQL Server 2014
  • Describe considerations for including SQL Server 2014 in a private cloud

Show moredown

Designing Database Solutions for Microsoft SQL Server 2014 Exam Information | M20465

 

What's included in this Microsoft SQL Server Course?

  • Delegate pack consisting of course notes and exercises
  • Manual
  • Experienced Instructor
  • Refreshments

Show moredown

accredited by

Microsoft Certifications (Microsoft Partner), on behalf of Microsoft Partner

Online Instructor-led (4 days)

Online Self-paced (32 hours)

Analysing Data with Power BI DA100 Course Outline

Module 1: Introduction to Microsoft Data Analytics

  • Data Analytics and Microsoft
  • What is Power BI?

Lab: Get Started

Module 2: Understand How to Prepare Data in Power BI?

  • Acquire Data from Different Data Sources
  • How to Optimise Performance?
  • Resolving Data Errors

Lab: Preparation of Data in Power BI Desktop

Module 3: Cleaning, Transforming, and Loading Data in Power BI

  • About Data Shaping
  • Understand How to Enhance the Data Structure?
  • What is Data Profiling?

Lab: How to Transform and Load Data?

Module 4: How to Design a Data Model in Power BI?

  • Introduction to Data Modeling
  • Understand Working with Tables
  • What are Dimensions and Hierarchies?

Lab: Data Modelling in Power BI Desktop

  • How to Create Model Relationships?
  • Configure Tables
  • Model Interface Review
  • Make Quick Measures

Lab: Advanced Data Modelling in Power BI Desktop

  • Form Many-to-Many Relationships
  • Row-Level Security

Module 5: Build Measures with DAX in Power BI

  • What is DAX?
  • Data Analysis Expressions (DAX) Context
  • Understand Advanced DAX

Lab: Fundamentals of DAX in Power BI Desktop

  • Form Calculated Tables
  • Make Calculated Columns
  • Create Measures

Lab: Introduction to Advanced DAX in Power BI Desktop

  • Usage of CALCULATE () Function to Manipulate Filter Context
  • Time Intelligence Functions

Module 6: How to Optimise the Model Performance?

  • Optimise DirectQuery Models
  • Understand Creating and Managing Aggregations

Module 7: Overview of Creating Reports

  • Designing a Report
  • Understand How to Enhance the Report?

Lab: Designing a Report in Power BI

  • Make a Live Connection in Power BI Desktop
  • How to Design a Report?
  • Configure Visual Fields ADN Format Properties

Lab: Enhance Power BI Reports with Interaction and Formatting

  • Creating and Configuring Sync Slicers
  • Creating Drill Through Page
  • Apply Conditional Formatting
  • Bookmarks Creation and Usage

Module 8: How to Create Dashboards?

  • Creating a Dashboard
  • About Real-time Dashboards
  • Develop a Dashboard

Lab: Develop a Report in Power BI Desktop - Part 1

  • Create a Dashboard
  • Pin Visuals to a Dashboard
  • Configure a Dashboard Tile Alert
  • Use Q&A to Create a Dashboard Tile

Module 9: Create Paginated Reports in Power BI

  • Paginated Report Overview
  • How to Create Paginated Reports?

Lab: Create a Paginated Report

  • Usage of Power BI Report Builder
  • Designing the Multi-Page Report Layout
  • Data Source and Dataset
  • Creating a Report Parameter
  • Exporting a Report to PDF

Module 10: Implement Advanced Analytics

  • Understand Advanced Analytics
  • Data Insights over AI Visuals

Lab: Data Analysing in Power BI Desktop

  • How to Create Animated Scatter Charts?
  • Use the Visual to Forecast Values
  • Work with 
  • Decomposition Tree Visual
  • Key Influencers Visual

Module 11: Create and Manage Workspaces

  • How to Create a Workspace?
  • Share and Manage the Assets

Lab: Publish and Share Power BI Content

  • Map Security Principals to Dataset Roles
  • Sharing Dashboard
  • Publishing an App

Module 12: Manage Datasets in Power BI

  • Understand Parameters
  • What are Datasets?

Module 13: Row-Level Security

  • Power BI Security

Show moredown

Prerequisites

There are no formal prerequisites for attending this Analysing Data with Power BI (DA-100) course. However, having a prior technical knowledge will be beneficial for the delegates.

Audience

This Analysing Data with Power BI course is suitable for anyone who wants to get knowledge about how to analyse data by using Power BI service provided by Microsoft. Although it will be more beneficial for:

  • Data Professionals
  • Business Intelligence Professionals

Analysing Data with Power BI DA100​ Course Overview

Power BI (Business Intelligence) is a business analytics interactive service which is provided by Microsoft and assists individuals to turn data into a business opportunity with the help of data visualisation and analytics tools for BI. This service allows users to derive better business decisions by analysing their enterprise data. By using Power BI service, individuals can create dashboards or reports that will track the metrics, and it will also help them to get a variety of data in the cloud systems. Microsoft’s Power BI is a powerful analytics tool that allows companies or enterprises of all sizes to analyse data and share different insights. With this Microsoft service, enterprises can monitor their business performance more closely and get immediate results with real-time dashboards available for every device. This is an extremely vast field which is currently being implemented in most of the organisations.

This 4-day Analysing Data with Power BI (DA-100) training course will provide delegates with comprehensive knowledge of basic as well as advanced concepts such as what is data analytics, acquiring data from different sources, and how to create reports. They will get familiar with setting measures using DAX (Data Analysis Expressions), how to create dashboards, managing workspaces and datasets in Power BI. This training will contain different labs with different topics which will help you to get hands-on practical experience and knowledge as well.

Delegates will also learn the following essential topics:

  • Data analytics and Microsoft
  • Enhance the data structure
  • Introduction to data modelling
  • Working with tables
  • Real-time dashboards
  • Security in Power BI
  • Data insights through AI visuals

After attending this Analysing Data with Power BI (DA-100) training course, delegates will gain knowledge about creating, transforming, and loading data with the help of Power BI tool. Delegates will be able to optimise the performance, resolve data errors, and improve the reports for the business growth. They will also be able to work on advanced DAX, different hierarchies for the data model, perform advanced analytics, and more which will help them to enhance their knowledge and skills for getting a job in this technical field.

Show moredown

  • Delegate pack consisting of course notes and exercises
  • Manual
  • Experienced Instructor

Show moredown

accredited by

Microsoft Gold Partner, on behalf of Microsoft Partner

Online Instructor-led (2 days)

Online Self-paced (16 hours)

SQL Server on Linux Training M10999 Course Outline

Please Note: Microsoft will be retiring this course M10999 on the 28th of February 2020

The following modules will provide an understanding of key capabilities and components of SQL Server on Linux. Delegates will acquire skills of how to deploy and configure SQL Server on Linux as well as install the additional optional components to SQL Server. So, let’s get started:

Module 1: Introduction to SQL Server on Linux

  • Why is SQL Server on Linux?
  • SQL Server on Linux Capabilities
  • Containers
  • Linux SQL Server VM in Azure

Module 2: Deploying SQL Server on Linux

  • Deploying SQL Server on Ubuntu
  • Deploying SQL Server on SUSE
  • Deploying SQL Server on Red Hat
  • Deploying SQL Server Containers on Docker
  • Lab: Deploying SQL Server on Linux

Module 3: Installing Additional Components

  • SQL Server Agent
  • SQL Server Full-Text Search
  • SQL Server Integration Services
  • Lab: Installing Additional Components

Module 4: Configuring SQL Server on Linux

  • Configuring SQL Server with mssql-conf
  • Configure SQL Server on Linux Docker Image

Module 5: Managing Database Solutions for SQL Server for Linux

  • Connecting to SQL Server on Linux
  • Managing SQL Server on Linux
  • Lab: Managing SQL Server on Linux

Module 6: Migrating Databases to SQL Server on Linux and Disaster Recovery

  • Backup and Restore
  • Database Export and Import
  • SQL Server Migration Assistant
  • Bulk Copy Program
  • SQL Server Integration Services
  • Lab: Migrating Databases

Module 7: High Availability

  • Log Shipping
  • Failover Clustering
  • Availability Groups
  • Lab: High Availability

Show moredown

Prerequisites

It would be great if delegates have basic knowledge of SQL language and familiarity with the Linux operating system.

Audience

This course is designed for various professional roles in the database such as Administrator and Developer. This course is also beneficial for who interested in adding database capabilities on different OS such as Linux.

Please Note: Microsoft will be retiring this course M10999 on the 28th of February 2020

SQL Server on Linux Training M10999​ Course Overview

Microsoft provides the opportunity to develop SQL Server on Linux operating system. It has the same features and services as other operating system and is easy to deploy and manage the database.

In this 2-days course, delegates will learn about essential concepts for deploying and managing SQL Server on Linux OS. The necessary capabilities and components of SQL Server on Linux is also included in this course. Along with this, delegates will get a better understanding of how to deploy strategy SQL server to Linux instances. Delegates will learn how to install additional components such as SQL server agent, full-text search, and SQL server integration. Delegates will acquire practical knowledge through hands-on labs modules. These labs will help you to grab the practical knowledge of deploying, configuring SQL Server on Linux as well as managing the database solutions on SQL Server for Linux. From this course, delegates will get an understanding of how to migrate the database to SQL Server on Linux and disaster recovery. 

On course completion, delegates will be able to deploy and manage the database on Linux OS platform as well as migrate database from another database system to SQL Server on Linux and provide disaster recovery.

Please Note: Microsoft will be retiring this course M10999 on the 28th of February 2020

Show moredown

  • Delegate pack consisting of course notes and exercises
  • Manual
  • Experienced Instructor

Please Note: Microsoft will be retiring this course M10999 on the 28th of February 2020

Show moredown

Not sure which course to choose?

Speak to a training expert for advice if you are unsure of what course is right for you. Give us a call on +44 1344 203999 or Enquire.

What our customers are saying

Frequently asked questions

FAQ's

Yes, all of our Microsoft SQL Server courses are accredited as The Knowledge Academy is a Microsoft Silver Learning Partner.
If your course requires you to take an exam then the exam will be included in the course price.
Yes, at The Knowledge Academy, we provide training in a vast number of locations across the UK and the world, meaning that it will be easy to find a location near you.
All of our training instructors have a wealth of experience in their field and provide support to our candidates throughout the course to ensure you get the most out of your learning experience.
The Knowledge Academy is the Leading global training provider in the world for Microsoft SQL Server Training.
The price for Microsoft SQL Server Training certification in Philippines starts from $1999.

Why we're the go to training provider for you

icon

Best price in the industry

You won't find better value in the marketplace. If you do find a lower price, we will beat it.

icon

Trusted & Approved

We are accredited by PeopleCert on behalf of AXELOS

icon

Many delivery methods

Flexible delivery methods are available depending on your learning style.

icon

High quality resources

Resources are included for a comprehensive learning experience.

barclays Logo
deloitte Logo
Thames Water Logo

"Really good course and well organised. Trainer was great with a sense of humour - his experience allowed a free flowing course, structured to help you gain as much information & relevant experience whilst helping prepare you for the exam"

Joshua Davies, Thames Water

santander logo
bmw Logo
Google Logo
Shell Logo

"...the trainer for this course was excellent. I would definitely recommend (and already have) this course to others."

Diane Gray, Shell

Looking for more information on Microsoft SQL Server Training