Microsoft SQL Server Training - United Kingdom

Microsoft Power BI Data Analyst PL300 Course Outline

Within this Microsoft training course, you will learn the following modules:

Module 1: Get Started with Microsoft Data Analytics

This module explores the distinct roles in the data space, outlines the important roles and responsibilities of a Data Analysts, and then explores the landscape of the Power BI portfolio.

Lessons

• Data Analytics and Microsoft.
• Getting Started with Power BI.

After completing this module, students will be able to:

• Explore the distinct roles in data.
• Identify the tasks that are performed by a data analyst.
• Describe the Power BI landscape of products and services.
• Use the Power BI service.

Module 2: Getting Data in Power BI

This module explores identifying and retrieving data from various data sources. You will also learn the options for connectivity and data storage and understand the difference and performance implications of connecting directly to data vs. importing it.

Lessons

• Data Analytics and Microsoft.
• Optimize Performance.
• Resolve Data Errors.

Lab: Preparing Data in Power BI Desktop

• Prepare Data.

After completing this module, students will be able to:

• Identify and retrieve data from different data sources.
• Understand the connection methods and their performance implications.
• Use Microsoft Dataverse.
• Connect to a data flow.

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

This module teaches you the process of profiling and understanding the condition of the data. They will learn how to identify anomalies, look at the size and shape of their data, and perform the proper data cleaning and transforming steps to prepare the data for loading into the model.

Lessons

• Shaping the Data.
• Profiling the Data.
• Enhance the data structure.

Lab: Loading Data in Power BI Desktop

• Loading Data.

After completing this module, students will be able to:

• Apply data shape transformations.
• Enhance the structure of the data.
• Profile and examine the data.

Module 4: Design a Data Model in Power BI

This module teaches the fundamental concepts of designing and developing a data model for proper performance and scalability. This module will also help you understand and tackle many of the common data modeling issues, including relationships, security, and performance.

Lessons

• Introduction to data modeling.
• Working with tables.
• Dimensions and Hierarchies.

Lab: Data Modeling in Power BI Desktop

• Create Model Relationships.
• Configure Tables and Column Properties.
• Create hierarchies.

After completing this module, students will be able to:

• Understand the basics of data modeling.
• Define relationships and their cardinality.
• Implement Dimensions and Hierarchies.

Module 5: Create Model Calculations using DAX in Power BI

This module introduces you to the world of DAX and its true power for enhancing a model. You will learn about aggregations and the concepts of Measures, calculated columns and tables, and Time Intelligence functions to solve calculation and data analysis problems.

Lessons

• Introduction to DAX.
• Real-time dashboards.
• Advanced DAX.

Lab: Introduction to DAX in Power BI Desktop

• Create calculated tables.
• Create calculated columns.
• Create measures.

Lab: Advanced DAX in Power BI Desktop

• Use the CALCULATE() function to manipulate filter context.
• Use Time Intelligence functions.

After completing this module, students will be able to:

• Understand DAX.
• Use DAX for simple formulas and expressions.
• Create calculated tables and measures.
• Build simple measures.
• Work with Time Intelligence and Key Performance Indicators.

Module 6: Optimize Model Performance in Power BI

In this module you are introduced to steps, processes, concepts, and data modeling best practices necessary to optimize a data model for enterprise-level performance.

Lessons

• Optimize the data model for performance.
• Optimize DirectQuery Models.

After completing this module, students will be able to:

• Understand the importance of variables.
• Enhance the data model.
• Optimize the storage model.

Module 7: Create Reports in Power BI

This module introduces you to the fundamental concepts and principles of designing and building a report, including selecting the correct visuals, designing a page layout, and applying basic but critical functionality. The important topic of designing for accessibility is also covered.

Lessons

• Design a report.
• Enhance the report.

Lab: Enhancing reports with interaction and formatting in Power BI Desktop

• Create and configure Sync Slicers.
• Create a drillthrough page.
• Apply conditional formatting.
• Create and use Bookmarks.

Lab: Designing a report in Power BI Desktop

• Design a report.
• Configure visual fields and format properties.

After completing this module, students will be able to:

• Design a report page layout.
• Select and add effective visualizations.
• Add basic report functionality.
• Add report navigation and interactions.

Module 8: Create Dashboards in Power BI

In this module you will learn how to tell a compelling story using dashboards and the different navigation tools available to provide navigation. You will be introduced to features and functionality and how to enhance dashboards for usability and insights.

Lessons

• Create a Dashboard.
• Real-time Dashboards.
• Enhance a Dashboard.

Lab: Creating a Dashboard in Power BI Service

• Create a Dashboard.
• Pin visuals to a Dashboard.
• Use Q&A to create a dashboard tile.

After completing this module, students will be able to:

• Create a Dashboard.
• Understand real-time Dashboards.
• Enhance Dashboard usability.

Module 9: Identify Patterns and Trends in Power BI

This module helps you apply additional features to enhance the report for analytical insights in the data, equipping you with the steps to use the report for actual data analysis. You will also perform advanced analytics using AI visuals on the report for even deeper and meaningful data insights.

Lessons

• Advanced Analytics.
• Data Insights through AI visuals.

Lab: Data Analysis in Power BI Desktop

• Create animated scatter charts.
• Use the visual to forecast values.

After completing this module, students will be able to:

• Use the Analyze feature.
• Identify outliers in data.
• Use the AI visuals.
• Use the Advanced Analytics custom visual.

Module 10: Create and Manage Workspaces in Power BI

This module will introduce you to Workspaces, including how to create and manage them. You will also learn how to share content, including reports and dashboards, and then learn how to distribute an App.

Lessons

• Creating Workspaces.
• Sharing and Managing Assets.

After completing this module, students will be able to:

• Create and manage a workspace.
• Understand workspace collaboration.
• Monitor workspace usage and performance.
• Distribute an App.

Module 11: Manage Files and Datasets in Power BI

This module will introduce you to parameters and datasets. You will also learn how to manage datasets and parameters, build dynamic reports with parameters, and set dataset refresh options.

Lessons

• Parameters.
• Datasets.

After completing this module, students will be able to:

• Manage datasets and parameters.
• Build dynamic reports with parameters.
• Schedule dataset refresh.
• Troubleshoot gateway service connectivity.

Module 12: Row-level Security in Power BI

This module will introduce you to row-level security, both static and dynamic methods, and how to implement.

Lessons

• Security in Power BI.

Lab: Enforce Row-Level Security

• Configure many-to-many relationships.
• Enforce row-level security.

After completing this module, students will be able to:

• Implement row-level security using both the Static and Dynamic methods.

Microsoft Power BI for End Users 55400AC course outline

This 2-day course will take students who have little to no knowledge of Power BI to the point where they can create, publish and share rich reports and dashboards.

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

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

Updating Your Skills to SQL Server 2016 M10986 Course Outline

Module 1: Introduction to SQL Server 2016

• Overview of SQL Server 2016
• SQL Server 2016 Editions and Components
• SQL Server Management Studio Enhancements
• Installing SQL Server 2016
• Capabilities SQL Server 2016

Lab: Explore SQL Server 2016

Module 2: New in SQL Server Security?

• Enhanced and New Features in SQL Server performance.
• Using Always Encrypted
• Dynamic Data Masking and Row-level Security

Lab: Implementing SQL Server 2016 Security Improvements

Module 3: New in SQL Server Availability and Scalability?

• Enhanced Always On Availability Groups
• What's New with tempdb?
• PowerShell 5.0
• Use Windows Server 2016 with SQL Server 2016

Lab: Monitoring tempdb

Module 4: What's New in SQL Server Reporting and BI

• Reporting Services Enhancements
• Mobile Report Publisher and PowerBI Enhancements

Lab: Implementing PowerBI

Module 5: What's New in SQL Server Performance

• Operational Analytics
• In-memory OLTP Enhancements
• Query Store and Live Query Statistics
• Native JSON and Temporal Tables

Lab: Implement SQL Server 2016 Performance Improvements

Module 6: What's New in SQL Server Data Access?

• What's new in Integration Services
• Working with SSIS and Azure
• PolyBase

Lab: Explore the new features of SQL Server Integrated Services (SSIS)

Module 7: New and Enhanced Features in SQL Server OLAP

• What's New in SQL Server Analysis Services

Lab: OLAP with SQL Server

Module 8: What's New for SQL Server in the Cloud?

• Stretch Database
• Enhanced Backups to Azure
• What's New in SQL Server Azure Database
• Introduction to SQL Server 2016 Hybrid Cloud

Lab: Using Stretch Database

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

Important notes about this course: This course and exam has been retired by Microsoft. This course has been replaced with Microsoft Power BI Data Analyst PL300

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

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

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

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

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

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

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