Microsoft SQL Server Training - New Zealand

Microsoft SQL Server Training

Our Microsoft training

course is accredited by Microsoft

Microsoft SQL Server is a relational database management system (RDBMS) used to manage and store data. SQL is an acronym for Structured Query Language, and it is the language used to manage and control these database servers. There are many different versions of Microsoft SQL Server, each with differing features. This is so that SQL Servers can be used to suit the needs of various professionals with differing roles and responsibilities. Understanding how to use a Microsoft SQL Server efficiently can significantly enhance productivity and an enterprise’s organisational capacity.

REASONS TO CHOOSE

Learn to effectively manage databases with hands-on practical exercises
Gain industry-recognised certification to enhance your career prospects
Understand advanced SQL concepts for efficient database solutions
Elevate your skills with our comprehensive Microsoft SQL Server Training
Unlock your potential with top-notch SQL Server Training excellence

View courses

Our Microsoft training

course is accredited by Microsoft

Microsoft SQL Server Training

accredited by

Our Microsoft training course is accredited by Microsoft

Duration

Online Instructor-led (3 days)

Classroom (3 days)

Online Self-paced (24 hours)

Microsoft Power BI Data Analyst PL300 Course Outline

Module 1: Discover Data Analysis

Overview of Data Analysis
Roles in Data
Tasks of a Data Analyst

Module 2: Get Started Building with Power BI

Use Power BI
Building Blocks of Power BI
Tour and Use the Power BI Service

Module 3: Get Data in Power BI

Get Data from Files
Get Data from Relational Data Sources
Create Dynamic Reports with Parameters
Get Data from a Nosql Database
Get Data from Online Services
Select A Storage Mode
Get Data from Azure Analysis Services
Fix Performance Issues
Resolve Data Import Errors
Exercise - Prepare Data in Power BI Desktop

Module 4: Clean, Transform, And Load Data in Power BI

Shape the Initial Data
Simplify the Data Structure
Evaluate and Change Column Data Types
Combine Multiple Tables into a Single Table
Profile Data in Power BI
Use Advanced Editor to Modify M Code
Exercise - Load Data in Power BI Desktop

Module 5: Describe Power BI Desktop Models

Star Schema Design
Analytic Queries
Configure Report Visuals

Module 6: Choose a Power BI Model Framework

Describe Power BI Model Fundamentals
Determine When to Develop an Import Model
Determine When to Develop a Directquery Model
Determine When to Develop a Composite Model
Choose a Model Framework

Module 7: Design a Semantic Model in Power BI

Work with Tables
Create a Date Table
Work with Dimensions
Define Data Granularity
Work with Relationships and Cardinality
Resolve Modeling Challenges
Exercise - Model Data in Power BI Desktop

Module 8: Write Dax Formulas for Power BI Desktop Models

Write Dax Formulas
Dax Data Types
Work with Dax Functions
Use Dax Operators
Use Dax Variables

Module 9: Add Measures to Power BI Desktop Models

Create Simple Measures
Create Compound Measures
Create Quick Measures
Compare Calculated Columns with Measures
Check Your Knowledge
Exercise - Create Dax Calculations in Power BI Desktop

Module 10: Add Calculated Tables and Columns to Power BI Desktop Models

Create Calculated Columns
Learn About Row Context
Choose a Technique to Add a Column

Module 11: Use Dax Time Intelligence Functions in Power BI Desktop Models

Use Dax Time Intelligence Functions
Additional Time Intelligence Calculations
Exercise - Create Advanced Dax Calculations in Power BI Desktop

Module 12: Optimise a Model for Performance in Power Bi

Introduction to Performance Optimisation
Review Performance of Measures, Relationships, And Visuals
Use Variables to Improve Performance and Troubleshooting
Reduce Cardinality
Optimise Directquery Models with Table Level Storage
Create and Manage Aggregations

Module 13: Enforce Power BI Model Security

Restrict Access to Power BI Model Data
Restrict Access to Power BI Model Objects
Apply Good Modeling Practices
Exercise: Enforce Model Security

Module 14: Scope Report Design Requirements

Identify the Audience
Determine Report Types
Define User Interface Requirements
Define User Experience Requirements
Explore Report Designs

Module 15: Design Power BI Reports

Design the Analytical Report Layout
Design Visually Appealing Reports
Report Objects
Select Report Visuals
Select Report Visuals to Suit the Report Layout
Format and Configure Visualisations
Work with Key Performance Indicators
Exercise - Design a Report in Power Bi Desktop

Module 16: Configure Power BI Report Filters

Introduction to Designing Reports for Filtering
Apply Filters to The Report Structure
Apply Filters with Slicers
Design Reports with Advanced Filtering Techniques
Consumption-Time Filtering
Select Report Filter Techniques
Case Study - Configure Report Filters Based on Feedback

Module 17: Enhance Power BI Report Designs for the User Experience

Design Reports to Show Details
Design Reports to Highlight Values
Design Reports that Behave Like Apps
Work with Bookmarks
Design Reports for Navigation
Work with Visual Headers
Design Reports with Built-In Assistance
Tune Report Performance
Optimise Reports for Mobile Use
Exercise - Enhance Power BI Reports

Module 18: Perform Analytics in Power BI

Introduction to Analytics
Explore Statistical Summary
Identify Outliers with Power BI Visuals
Group and Bin Data for Analysis
Apply Clustering Techniques
Conduct Time Series Analysis
Use the Analyse Feature
Create What-If Parameters
Use Specialised Visuals
Exercise - Perform Advanced Analytics with Ai Visuals

Module 19: Create Paginated Reports

Introduction to Paginated Reports
Get Data
Create a Paginated Report
Work with Charts on the Report
Publish the Report

Module 20: Create and Manage Workspaces in Power BI

Distribute a Report or Dashboard
Monitor Usage and Performance
Recommend a Development Life Cycle Strategy
Troubleshoot Data by Viewing its Lineage
Configure Data Protection

Module 21: Manage Semantic Models in Power BI

Use a Power BI Gateway to Connect to On-Premises Data Sources
Configure a Semantic Model Scheduled Refresh
Configure Incremental Refresh Settings
Manage and Promote Semantic Models
Troubleshoot Service Connectivity
Boost Performance with Query Caching (Premium)

Module 22: Create Dashboards in Power BI

Introduction to Dashboards
Configure Data Alerts
Explore Data by Asking Questions
Review Quick Insights
Add a Dashboard Theme
Pin a Live Report Page to a Dashboard
Configure a Real-Time Dashboard
Set Mobile View
Exercise - Create a Power BI Dashboard

Module 23: Implement Row-Level Security

Configure Row-Level Security with the Static Method
Configure Row-Level Security with the Dynamic Method
Exercise - Enforce Row-Level Security in Power BI

More information

See dates & prices

Who should attend this Microsoft Power BI Data Analyst PL300 Course?

This Microsoft Power BI Data Analyst PL300 Training Course is designed for professionals who work with Data Analysis, Business Intelligence, and those responsible for creating insightful reports and dashboards for decision-making. This training course is especially beneficial for these professionals:

Data Analysts
Business Analysts
Data Engineers
Business Intelligence Professionals
IT Professionals
Managers
Team Leads

Prerequisites of the Microsoft Power BI Data Analyst PL300 Course

There are no formal prerequisites for the Microsoft Power BI Data Analyst PL300 Training Course. However, a basic understanding of cloud data experience can be beneficial.

Microsoft Power BI Data Analyst PL300 Course Overview

The Microsoft Power BI Data Analyst PL300 Course is designed to equip delegates with the skills required to effectively analyse data and create powerful visual reports. In today's data-driven world, the ability to interpret and present data clearly is crucial for informed decision-making. This course offers comprehensive training on Power BI, Microsoft's leading business analytics tool.

Proficiency in Microsoft Power BI Data Analyst PL300 is essential for Data Analysts, Business Intelligence Professionals, and any role involving data interpretation. Mastering this tool allows professionals to transform raw data into meaningful insights, aiding strategic decisions. Those aiming to enhance their data visualisation capabilities and report generation skills will benefit greatly from this course.

This 3-day Microsoft Power BI Data Analyst PL300 training by The Knowledge Academy provides delegates with hands-on experience in using Power BI to analyse data and create compelling visualisations. Through practical exercises and expert instruction, Delegates will learn to navigate the Power BI interface, connect to data sources, and design interactive dashboards, ensuring they can apply these skills immediately in their workplace.

Course Objectives

To understand the Power BI service and its components
To connect to various data sources and import data
To clean and transform data for analysis
To create and customise data visualisations
To design interactive reports and dashboards
To share and publish reports within an organisation

After completing the Microsoft Power BI Data Analyst PL300 Course, delegates will possess a robust understanding of Power BI. They will be able to create and share insightful reports, enabling their organisations to make data-driven decisions more effectively.

More information

See dates & prices

What’s included in this Microsoft Power BI Data Analyst PL300 Course?

World-Class Training Sessions from Experienced Instructors
Microsoft Power BI Data Analyst PL300 Certificate
Digital Delegate Pack

More information

See dates & prices

More information

See dates & prices

accredited by

Our Microsoft training course is accredited by Microsoft

Duration

Online Instructor-led (2 days)

Classroom (2 days)

Online Self-paced (16 hours)

Microsoft Power BI for End Users 55400AC course outline

Module 1: Introduction to Power BI

What is Business Intelligence?
Introducing Power BI
Power BI Tools and Services
Power BI Report Server
Power BI (Online)
Power BI Desktop
Case Studies
Support and Information
Data Stories

Lab: Getting Ready

Sign in to Office 365
Download Course Files
Place Data Sources into OneDrive
Create Further User Accounts

Module 2: Requirements Gathering

Welcome to House Rules Board Games
Beginning a BI Project
Grain Statements
Data Discovery
Refining Information
Modeling Data
Building Reports
Roles
Publishing
Creating Dashboards
Choosing Workspaces
Create and Share a Report in Power BI

Lab: Build a Report in Power BI Web

Connect to an Excel Spreadsheet
Build Visualisations
Create a Dashboard for Desktop and Mobile Users
Share Your Report and Dashboard

Module 3: Extract, Transform, and Load Data

Creating a Project with Power BI Desktop
Pinning an Active Project
What is in a PBIX File?
Connecting to Data Sources
Managing Data Connections
Refine Data with Power Query
Applied Steps
M Code Language
Designing Your Query
Choose Columns
Rename Columns
Moving Columns
Formatting Columns
Replacing Values
Expanding Related Columns
Star Schemas
Merge Columns
Split Columns
Custom and Conditional Columns

Lab: Starting a Project in Power BI Desktop

Creating a New PBIX Project File
Connecting to Data Sources
Use the Query Editor to Refine Data
Review the Table Relationships
Optional – Connect to an Azure SQL Database

Module 4: Creating a Data Model

Understanding Relationships
Cardinality
Cross Filter Direction
Hiding Tables and Columns
Formatting Columns
Introducing DAX
Designing Calculated Columns
Creating Measures
Adding Lookup Tables
Structuring Data with Dimensional Hierarchies
Roles and Row Level Security

Lab: Designing a Data Model

Hide and Format Table Columns
Create Calculated Columns
Create Measures
Using Geographical Data and Lookup Tables
Create a Dimensional Hierarchy

Module 5: Designing Reports

Adding Pages and Navigation Buttons
Using Shapes, Text, and Images
Creating a Theme
Adding Visualisations
Using Filters and Slicers
Controlling Filters
Adding Drill Throughs
Adding Custom Visualisations
Configure Phone Layouts
Report Tips

Lab: Creating a Report

Creating New Pages and Adding Basic Content
Adding and Configuring Visualisations
Design Slicers
Create Bookmarks and Navigational Buttons

Module 6: Using Workspaces and Dashboards

Power BI Licensing
My Workspaces
App Workspaces
Premium Capacity Workspaces
Creating a New Workspace
Publishing a Project
Parallels with Microsoft Excel
Publishers and Viewers
Reconnecting with Data Sources
Introducing the On-Premises Data Gateway
Scheduling the Data Refresh Frequency
Assigning Security Roles
Creating and Populating Dashboards
Customising Dashboard Tiles
Creating Tiles Using Q&A and Quick Insights
Creating a Mobile Dashboard
Sharing Reports and Dashboards
Publishing Apps in a Workspace
Publishing Content to Websites

Lab: Publish and Share Reports and Dashboards

Publish Your Project to Power BI
Create a Dashboard
Use Quick Insights and Pin Visuals
Use Q&A to Query Your Data Source
Share Your Dashboard with Marketing

More information

See dates & prices

Who should attend this Microsoft Power BI for End Users 55400AC Course?

The Microsoft Power BI for End Users 55400AC Training is designed to be accessible, making it a good fit for professionals at various levels of an organisation who are looking to make data-driven decisions. This course can be beneficial for a wide range of professionals, including:

Business Analysts
Data Enthusiasts
Excel Users
Non-technical Staff
Managers and Executives
Sales and Marketing Professionals
HR Professionals

Prerequisites of the Microsoft Power BI for End Users 55400AC Course

For the Microsoft Power BI for End Users 55400AC Training, prerequisites generally include basic computer literacy, familiarity with Microsoft Office applications like Excel, and an understanding of fundamental data concepts like tables and charts.

Microsoft Power BI for End Users 55400AC Course Overview

The Microsoft Power BI for End Users 55400AC Course is designed to provide a comprehensive introduction to Power BI, a powerful business analytics tool from Microsoft. This course is highly relevant for today's data-driven business environment, enabling users to transform raw data into meaningful insights and interactive visualisations.

Proficiency in Power BI is crucial for professionals who need to analyse and present data effectively. This course is ideal for Business Analysts, Data Analysts, Project Managers, and any professionals who work with data. Mastering Power BI allows these professionals to make data-driven decisions, enhance productivity, and gain a competitive edge in their respective fields.

This 2-day training equips delegates with practical skills to use Power BI efficiently. Participants will learn how to connect to various data sources, create interactive reports and dashboards, and share insights across their organisation. The hands-on approach ensures that delegates can apply what they learn immediately to their daily tasks, enhancing their data analysis capabilities.

Course Objectives

To understand the fundamentals of Power BI
To learn how to connect to various data sources
To create and manage data models
To design interactive and visually appealing reports
To use Power BI service for report sharing and collaboration
To implement data security and manage permissions

After completing the course, delegates will be proficient in using Microsoft Power BI to transform raw data into meaningful insights. They will be capable of creating interactive and visually appealing reports, empowering their organisations to make data-driven decisions.

More information

See dates & prices

What’s included in this Microsoft Power BI for End Users 55400AC Course?

World-Class Training Sessions from Experienced Instructors
Microsoft Power BI for End Users 55400AC Certificate
Digital Delegate Pack

More information

See dates & prices

More information

See dates & prices

accredited by

Our Microsoft training course is accredited by Microsoft

Duration

Online Instructor-led (3 days)

Classroom (3 days)

Online Self-paced (24 hours)

Introduction to SQL Databases Training 10985C Course Outline

Module 1: Introduction to Databases

Introduction to Relational Databases
Retrieve Data from a Relational Database by using SQL

Lab 1: Use the SELECT Statement to Retrieve Data from a Database

Use the SELECT Statement to Retrieve Data from a Database

Module 2: Database Tables

Identify and use Microsoft SQL Server Data Types
Create Tables with SQL
Altering and Dropping a Table
Collect Information from Multiple Tables by using Joins and Views

Lab 1: Create and Alter a Table in Microsoft SQL Server

Create and Alter a Table in Microsoft SQL Server
Create and Retrieve a View that Uses the JOIN Statement in Microsoft SQL Server

Lab 2: Create and Alter a Table in Microsoft SQL Server

Create and Alter a Table in Microsoft SQL Server

Lab 3: Create and Retrieve a View that Uses the JOIN Statement in Microsoft SQL Server

Create and Retrieve a View that Uses the JOIN Statement in Microsoft SQL Server

Module 3: Add and Update Data

Insert, Update, and Delete Information

Lab 1: Add and Change Data in SQL Server

Add and Change Data in SQL Server

Lab 2: Create and use Stored Procedures in Microsoft SQL Server

Create and use Stored Procedures in Microsoft SQL Server

Module 4: Entity Relationships

Model Entity Relationships
Design for Referential Integrity What is Referential Integrity?

Lab 1: Create an Entity Relationship Diagram in Microsoft SQL Server Management Studio

Create an Entity Relationship Diagram in Microsoft SQL Server Management Studio

Module 5: Normalisation and Denormalisation

Normalisation
Denormalisation

Lab 1: Denormalise a Table by Adding a Redundant Column of Computed Data

Denormalise a Table by Adding a Redundant Column of Computed Data

Module 6: Query Performance and Safety

Indexes
Write Performant SQL Code
Concurrency

Lab 1: Create Indexes and Consult Execution Plans in Microsoft SQL Server Management Studio

Create Indexes and Consult Execution Plans in Microsoft SQL Server Management Studio

More information

See dates & prices

Who should attend this Introduction to SQL Databases Training 10985C Course?

This Introduction to SQL Databases Training 10985C Course is designed for individuals who want to develop a foundational understanding of working with SQL databases and querying data. This training course is beneficial for the following professionals:

Administrative Assistants
Database Administrators
Data Analysts
IT Professionals
Clerical Staff
Web Developers
Researchers

Prerequisites of the Introduction to SQL Databases Training 10985C Course

There are no formal prerequisites for this Introduction to SQL Databases Training 10985C Course.

Introduction to SQL Databases Training 10985C Course Overview

The Introduction to SQL Databases Training 10985C Course provides a comprehensive foundation in SQL databases, a critical component for managing and querying data in today's data-driven world. This one-day training is designed to equip participants with essential skills to understand and utilise SQL databases effectively, ensuring they can handle data efficiently and accurately.

Proficiency in SQL databases is crucial for Data Analysts, Database Administrators, and IT Professionals. Mastering SQL ensures that professionals can efficiently retrieve and manipulate data, which is essential for decision-making and strategic planning. This course is particularly beneficial for those aiming to advance their careers in data management and analysis.

This 3-day training will empower delegates with hands-on experience in SQL databases. Participants will learn through practical exercises, enabling them to apply SQL commands and manage data effectively. The course is structured to provide a thorough understanding of SQL fundamentals, ensuring delegates gain the skills needed to work confidently with databases.

Course Objectives

To understand the basics of relational databases
To learn the SQL language and its practical applications
To execute data queries to retrieve specific information
To perform data manipulation, including inserting, updating, and deleting records
To create and manage database tables and schemas
To understand and implement data integrity and constraints

After completing the course, delegates will receive a certification, validating their SQL database skills and enhancing their professional credentials. This certification will open up new opportunities in data management and analysis fields.

More information

See dates & prices

What’s included in this Introduction to SQL Databases Training 10985C Training Course?

World-Class Training Sessions from Experienced Instructors
Introduction to SQL Databases Training 10985C Certificate
Digital Delegate Pack

More information

See dates & prices

More information

See dates & prices

accredited by

Our Microsoft Certifications (Microsoft Partner) course is accredited by Microsoft

Duration

Online Instructor-led (4 days)

Classroom (4 days)

Online Self-paced (32 hours)

Accelerated SQL Server 2016 Integration Services Course Outline

Module 1: SSIS Overview and Core Features

Introduction to Business Intelligence (BI)
Tools for Business Intelligence (BI)
Introduction to Data Integration
SSIS Features

Module 2: Data Warehousing

Dimensional Modelling
Dimensional Modelling Rules
Data Preparation for Advanced Analytics

Module 3: SSIS Control Flow Objects and Features

What is Control Flow?
Control Flow Concepts
Types of Control Flow Elements
SSIS Control Flow Tasks
SSIS Control Flow Containers
Control Flow Objects
Group or Ungroup Components
Using the Control Flow to Orchestrate SSIS Execution

Module 4: Extracting, Transforming and Loading Data Using SSIS Data Flows

SSIS Data Flow Task
Data Flow Path
Data Connection Managers
Data Flow Components

Module 5: Working with Files, Importing and Exporting File Data

File Connection Manager
Add and Configure a Flat File Connection Manager
Loading Data into Files
Excel Connection Manager

Module 6: Optimising Data Extraction and Data Loading

Change Tracking
Change Data Capture

Module 7: Data Quality and Cleansing

Data Quality
Data Profiling
Data Quality Services
Fuzzy Lookup Transformation
Data Profiling Task in SSIS

Module 8: Advanced Enterprise Information Management

Script Task and Script Component
Text Mining
Advanced Analytics

Module 9: Dimension ETL with SSIS

Dimension ETL Theory
SQL Server Temporal Tables
SSIS Slowly Changing Dimension Wizard
Slowly Changing Dimension Transformation

Module 10: Fact ETL with SSIS

Fact Table ETL Theory
Advanced Fact Table Techniques

Module 11: Project Deployment Model: Execution and Reporting

Pivot Transformation

Module 12: Processing SSAS Objects in SSIS

SSAS Tabular and Multidimensional
Tabular Model Designer
DAX for Multidimensional Models
Processing Methods in SSIS

Module 13: Project Deployment

Project Deployment Model
Create the SSIS Catalog
Monitoring
SSIS Master Package

Module 14: Package Deployment

Package Deployment Model
Deployment to the SSIS Package Store
Monitor Running Packages

Module 15: Transactions and Restartability

Using Breakpoints in SSDT
Implementing Transactions in SSIS
Using SQL Server Database Snapshots
Restart Packages by Using Checkpoints
Responding to Events

Module 16: Optimisation and Scalability

Leveraging the Data Flow
Transact-SQL
Data Flow Tasks
SSIS Optimisation Techniques
SSIS Performance Troubleshooting

More information

See dates & prices

Who should attend this Accelerated SQL Server 2016 Integration Services Course?

This Accelerated SQL Server 2016 Integration Services Training Course is designed for professionals who work with Data Integration, ETL development, and those who need to efficiently design and manage data workflows using SSIS (SQL Server Integration Services). This training course is beneficial for the following professionals:

ETL Developers
Data Engineers
Data Analysts
Database Developers
IT Administrators
Business Analysts
DevOps Engineers

Prerequisites of the Accelerated SQL Server 2016 Integration Services Course

There are no formal prerequisites for this Accelerated SQL Server 2016 Integration Services Training Course. However, a foundational understanding of database and SQL concepts would be beneficial for the delegates.

Accelerated SQL Server 2016 Integration Services Course Overview

The Accelerated SQL Server 2016 Integration Services Course is designed to provide an intensive, hands-on learning experience for database professionals. This one-day course delves into the core features and functionalities of SQL Server Integration Services (SSIS), enabling delegates to efficiently manage data integration and transformation tasks.

Proficiency in SQL Server Integration Services is crucial for Data Professionals seeking to enhance their capabilities in data warehousing, ETL (Extract, Transform, Load) processes, and data migration. This course is particularly beneficial for Database Administrators, Data Analysts, and Developers who aim to streamline their data management workflows and improve their technical proficiency in SSIS.

This 4-day training will provide delegates with comprehensive knowledge and practical skills through immersive exercises and real-world scenarios. Participants will learn how to design and implement SSIS packages, automate administrative tasks, and troubleshoot common issues. The hands-on approach ensures that attendees can apply their newly acquired skills directly to their professional roles.

Course Objectives

To understand the architecture and components of SQL Server Integration Services
To design and develop SSIS packages for data integration
To implement control flow and data flow in SSIS packages
To manage SSIS package execution and security
To troubleshoot and debug SSIS packages effectively
To integrate SSIS with other SQL Server features

After completing the course, delegates will receive a certification validating their expertise in SQL Server 2016 Integration Services. This certification will enhance their professional credentials and demonstrate their capability to manage complex data integration tasks efficiently.

More information

See dates & prices

What’s included in this Accelerated SQL Server 2016 Integration Services Course?

World-Class Training Sessions from Experienced Instructors
Accelerated SQL Server 2016 Integration Services Certificate
Digital Delegate Pack

More information

See dates & prices

More information

See dates & prices

accredited by

Our Microsoft training course is accredited by Microsoft

Duration

Online Instructor-led (3 days)

Classroom (3 days)

Online Self-paced (24 hours)

Writing Analytical Queries for Business Intelligence 55232A Training Course Outline

Module 1: Introduction to TSQL for Business Intelligence

Module 2: Turning Table Columns into Variables for Analysis: SELECT List Expressions, WHERE, and ORDER BY

Turning Columns into Variables for Analysis
Column Expressions Data Types and Built-in Functions
Column Aliases
Data Type Conversions
Built-in Scalar Functions
Table Aliases
The WHERE Clause
ORDER BY
Lab 2 - Write Queries

Module 3: Combining Columns from Multiple Tables into a Single Dataset: The JOIN Operators

Primary Keys Foreign Keys and Joins
Understanding Joins:
- Part 1: CROSS JOIN and the Full Cartesian Product
- Part 2: The INNER JOIN
- Part 3: The OUTER JOINS
- Part 4: Joining more than Two Tables
- Part 5: Combining INNER and OUTER JOINs
Combining JOIN Operations with WHERE and ORDER BY
Lab 3 - Write SELECT Queries

Module 4: Creating an Appropriate Aggregation Level Using GROUP BY

Identifying Required Aggregation Level and Granularity
Aggregate Functions
GROUP BY
HAVING
Order of Operations in SELECT Queries
Lab 4 - Write Queries

Module 5: Subqueries, Derived Tables and Common Table Expressions

Non-Correlated and Correlated Subqueries
Derived Tables
Common Table Expressions
Lab 5 - Write Queries

Module 6: Encapsulating Data Retrieval Logic

Views
Table-Valued Functions
Stored Procedures
Creating Objects for Read-Access Users
Creating Database Accounts for Analytical Client Tools
Lab 6

Module 7: Getting Your Dataset to the Client

Connecting to SQL Server and Submitting Queries from Client Tools
Connecting and Running SELECT Queries from:
- Excel
- Power BI
- RStudio
Exporting Datasets to Files using
Results Pane from SSMS
The bcp Utility
Import/Export Wizard
Lab 7

More information

See dates & prices

Who should attend this Writing Analytical Queries for Business Intelligence 55232A Training?

The Writing Analytical Queries for Business Intelligence 55232A Course is typically intended for individuals who work in roles that involve using data analysis and business intelligence tools to extract valuable insights from data. This training is suitable for:

Business Analysts
Data Analysts
Reporting Professionals
Business Intelligence Developers
Data Scientists
Data Enthusiasts
Database Administrators

Prerequisites of the Writing Analytical Queries for Business Intelligence 55232A Training

In the Writing Analytical Queries for Business Intelligence 55232A Course, delegates must have knowledge of data analysis and business intelligence, a basic understanding of the Windows operating system, and a fundamental grasp of relational database management systems like SQL Server.

Writing Analytical Queries for Business Intelligence 55232A Training Course Overview

The Writing Analytical Queries for Business Intelligence 55232A Course is designed to empower professionals with the skills to craft precise analytical queries for business intelligence. In today's data-driven world, the ability to extract meaningful insights from vast datasets is crucial for informed decision-making. This course provides foundational knowledge essential for leveraging business intelligence tools effectively.

Proficiency in Writing Analytical Queries is vital for transforming raw data into actionable intelligence. Mastery of this skill allows professionals to enhance organisational decision-making and strategic planning. This course is particularly beneficial for Data Analysts, Business Intelligence Professionals, and IT Specialists who aim to optimise their data query capabilities and contribute to their organisation’s data-driven initiatives.

This intensive 3-day training equips delegates with practical skills and knowledge to write effective analytical queries. Through hands-on exercises and real-world examples, participants will learn to optimise query performance, manage data efficiently, and generate accurate reports. By the end of the course, delegates will be well-prepared to handle complex data queries and support their organisation’s business intelligence needs.

Course Objectives

To understand the fundamentals of analytical querying
To learn the syntax and structure of SQL queries
To explore various data aggregation techniques
To improve query performance and efficiency
To manage and manipulate datasets effectively
To generate accurate and meaningful business reports

After completing this course, delegates will have the skills to write efficient analytical queries, significantly enhancing their ability to support business intelligence operations and drive data-informed decision-making within their organisations.

More information

See dates & prices

What's included in this Writing Analytical Queries for Business Intelligence 55232A Training?

World-Class Training Sessions from Experienced Instructors
Writing Analytical Queries for Business Intelligence 55232A Certificate
Digital Delegate Pack

More information

See dates & prices

More information

See dates & prices

accredited by

Our Microsoft training course is accredited by Microsoft

Duration

Online Instructor-led (5 days)

Classroom (5 days)

Online Self-paced (40 hours)

SQL Server Integration Services 55321AC Training Course Outline

Module 1: SSIS Overview

Import/Export Wizard
Exporting Data with the Wizard
Common Import Concerns
Quality Checking Imported/Exported Data
Lab

Module 2: Working with Solutions and Projects

Working with SQL Server Data Tools
Understanding Solutions and Projects
Working with the Visual Studio Interface
Lab

Module 3: Basic Control Flow

Working with Tasks
Understanding Precedence Constraints
Annotating Packages
Grouping Tasks
Package and Task Properties
Connection Managers
Favourite Tasks
Lab

Module 4: Common Tasks

Analysis Services Processing
Data Profiling Task
Execute Package Task
Execute Process Task
Expression Task
File System Task
FTP Task
Hadoop Task
Script Task Introduction
Send Mail Task
Web Service Task
XML Task
Lab

Module 5: Data Flow Sources and Destinations

Data Flow Task
Data Flow SSIS Toolbox
Working with Data Sources
SSIS Data Sources
Working with Data Destinations
SSIS Data Destinations
Lab

Module 6: Data Flow Transformations

Transformations
Configuring Transformations
Lab

Module 7: Making Packages Dynamic

Features for Making Packages Dynamic
Package Parameters
Project Parameters
Variables
SQL Parameters
Expressions in Tasks
Expressions in Connection Managers
After Deployment
How It All Fits Together
Lab

Module 8: Containers

Sequence Containers
For Loop Containers
Foreach Loop Containers
Lab

Module 9: Troubleshooting and Package Reliability

Understanding MaximumErrorCount
Breakpoints
Redirecting Error Rows
Logging
Event Handlers
Using Checkpoints
Transactions
Lab

Module 10: Deploying to the SSIS Catalog

SSIS Catalog
Deploying Projects
Working with Environments
Executing Packages in SSMS
Executing Packages from the Command Line
Deployment Model Differences
Lab

Module 11: Installing and Administering SSIS

Installing SSIS
Upgrading SSIS
Managing the SSIS Catalog
Viewing Built-in SSIS Reports
Managing SSIS Logging and Operation Histories
Automating Package Execution
Lab

Module 12: Securing the SSIS Catalog

Principals
Securables
Grantable Permissions
Granting Permissions
Configuring Proxy Accounts
Lab

More information

See dates & prices

Who should attend this SQL Server Integration Services 55321AC Training?

The SQL Server Integration Services 55321AC Course is designed for individuals and professionals interested in learning how to use SQL Server Integration Services (SSIS) to design, implement, and manage data integration solutions. This course is particularly suitable for:

Data Engineers
ETL (Extract, Transform, Load) Developers
Database Administrators
Business Intelligence Developers
Data Analysts
Data Integration Specialists
Database Architects

Prerequisites of the SQL Server Integration Services 55321AC Training

There are no formal prerequisites for attending this SQL Server Integration Services 55321AC Course.

SQL Server Integration Services 55321AC Training Course Overview

The SQL Server Integration Services (SSIS) 55321AC Course provides a comprehensive introduction to the powerful data integration and transformation tool from Microsoft. Designed for professionals who work with SQL Server databases, this course covers essential SSIS concepts, components, and techniques, equipping delegates with the skills to handle data migration, integration, and workflow applications efficiently.

Proficiency in SSIS is crucial for Database Administrators, Data Analysts, and IT Professionals involved in data management and business intelligence. Mastering SSIS enables these professionals to streamline data processes, ensure data quality, and support decision-making processes within their organisations. This course is particularly beneficial for those seeking to enhance their data handling capabilities and advance their careers in data-related roles.

This 5-day training course offers a hands-on approach, enabling delegates to gain practical experience in using SSIS. Participants will learn to create, deploy, and manage SSIS packages, automate data workflows, and troubleshoot common issues. By the end of the course, delegates will be proficient in utilising SSIS to address complex data integration challenges and optimise their organisation's data operations.

Course Objectives

To understand the core components and architecture of SSIS
To create and configure SSIS packages for data integration
To implement data flow tasks and transformations
To manage data sources and destinations effectively
To automate SSIS package execution and workflows
To optimise SSIS package performance for large data sets

After completing the course, delegates will have the confidence and skills to design, deploy, and manage robust SSIS packages, enhancing their ability to handle data integration tasks efficiently and effectively.

More information

See dates & prices

What's Included in this SQL Server Integration Services 55321AC Training?

World-Class Training Sessions from Experienced Instructors
SQL Server Integration Services 55321AC Certificate
Digital Delegate Pack

More information

See dates & prices

More information

See dates & prices

accredited by

Our Microsoft training course is accredited by Microsoft

Duration

Online Instructor-led (4 days)

Classroom (4 days)

Online Self-paced (32 hours)

SQL Server 2014 Performance Tuning and Optimisation 55144BC Training Course Outline

Module 1: SQL 2014 Architecture

New Cardinality Estimator
Memory-Optimised Tables
Understanding Performance for Developers
Understanding Start-up Parameters
Start-up Stored Procedures
Database Structures
Instant File Initialisation
How SQL Stores Data?
How SQL Locates Data?

Lab: SQL 2014 Architecture

Configuring Compatibility Level
Start-up Parameter
Start-up Stored Procedure
Instant File Initialisation

Module 2: Database Engine

Four Important Concepts
Temporary Tables Internals
Concurrency
Transactions
Isolation Levels
SQL Server Locking Architecture
SQL and Storage Area Networks (SAN)
SQL on VMs
SQLIO Utility
Partitioned Tables and Indexes

Lab: Database Engine

Table-Valued Parameter
Transactions
Vertically Partitioned Table Using Code
Partitioning with Filegroups
Partitioning Wizard

Module 3: SQL Performance Tools

Resource Governor
Activity Monitor
Live Query Statistics
Monitoring SQL with Transact-SQL
Dynamic Management Objects (DMOs) and Performance Tuning

Lab: SQL Performance Tools

Resource Governor
Activity Monitor
sp_who2
Performance DMVs

Module 4: Query Optimising and Operators

Tuning Process
Performance Monitor Tool
SQL Query Processing Steps
Understanding Execution Plans
SET STATISTICS TIME and SET STATISTICS IO
Data Access Operators
Troubleshooting Queries

Lab: Query Optimising and Operators

Performance Monitor (perfmon)
Estimated vs Actual Plans and XML Plans
Viewing a Non-Trivial Execution Plan
SET STATISTICS TIME and SET STATISTICS IO
Data Access Operators
DMVs
View Information About Extended Events
Wizard Templates
Creating a Session Without a Wizard
Configuring MDW and Viewing Reports

Module 5: Understanding Indexes

Introduction to Indexes
Index Types by Storage
Index Types by Column Designation
Creating and Altering Indexes
Metadata
Data Management Views for Indexing
Database Engine Tuning Advisor
Index Data Management Objects
SQL Server Fragmentation
Patterns
Index Storage Strategies
Indexed Views
Monitoring Indexes
Index Dynamic Management Objects (DMOs)

Lab: Understanding Indexes

Create Indexes
Index Metadata
Database Tuning Advisor
Missing Index DMOs
Fragmentation and Page Splits
Data Compression
Indexed Views
DMO Index Stats

Module 6: Understanding Statistics

Statistics
Cardinality Estimator
Incremental Statistics
Computed Columns Statistics
Filtered Statistics
Maintenance

Lab: Understanding Statistics

Statistics Objects
Histogram
New Vs Old Cardinality Estimator
Incremental Statistics
Computed Columns
Filtered Statistics
Ascending Keys
Statistics Maintenance Plan

Module 7: In-Memory Database

Architecture
Tables and Indexes
Natively Compiled Stored Procedures
Restrictions
Analyse Migrate Report Tool
In-Memory Data Management Views (DMV)

Lab: In-Memory Database

Creating an In-Memory Enabled Database with Code and SSMS
Creating a Memory-Optimised Table
View Hash Index
Natively Compiled Stored Procedure
AMR Tool
In-Memory DMVs

Module 8: SQL Profiler and SQL Trace

SQL Trace
SQL Trace Architecture
SQL Server Profiler

Lab: SQL Profiler and SQL Trace

SQL Trace Default Template
SQL Profiler Tuning Template
Create a Template for Slow Running Queries
Identifying Long Running Queries
Using Profiler to Detect Deadlocks
Use Profiler to Detect a Blocked Process

Module 9: Query Issues and Hints

Query Hints
Plan Guides
Plan To Freeze
Join Orders

Lab: Query Issues and Hints

Create and Test a Plan Guide
Allow SQL Profiler to Help Create a Plan
Plan To Freeze

More information

See dates & prices

Who should attend this SQL Server 2014 Performance Tuning and Optimisation 55144BC Training?

The SQL Server 2014 Performance Tuning and Optimisation 55144BC Course is intended for database professionals and IT personnel responsible for managing and maintaining SQL Server 2014 databases. This course is particularly suitable for:

Database Administrators
SQL Server Developers
Systems Administrators
Database Analysts
IT Managers
Database Architects
Application Developers

Prerequisites of the SQL Server 2014 Performance Tuning and Optimisation 55144BC Training

There are no formal prerequisites for attending SQL Server 2014 Performance Tuning and Optimisation 55144BC Course.

SQL Server 2014 Performance Tuning and Optimisation 55144BC Training Course Overview

The SQL Server 2014 Performance Tuning and Optimisation 55144BC Course provides an in-depth understanding of performance tuning and optimisation techniques crucial for managing SQL Server environments efficiently. This course is designed to equip participants with the skills necessary to enhance database performance, ensuring robust and reliable database systems.

Proficiency in SQL Server performance tuning is essential for Database Administrators, Developers, and IT Professionals who manage SQL Server databases. Mastering these skills is vital for optimising query performance, reducing server load, and ensuring high availability and scalability of database applications. Professionals in these roles should aim to develop expertise in SQL Server performance tuning to maintain efficient database systems.

This comprehensive 4-day training course will empower delegates with the knowledge and practical skills needed to identify and resolve performance issues in SQL Server 2014. Through hands-on exercises and expert-led discussions, participants will learn to apply best practices and advanced techniques to optimise SQL Server performance, leading to improved system efficiency and productivity.

Course Objectives

To understand the fundamentals of SQL Server performance tuning and optimisation
To identify and analyse performance bottlenecks in SQL Server 2014
To implement indexing strategies to enhance query performance
To utilise SQL Server Profiler and Extended Events for monitoring
To apply best practices for SQL Server configuration and maintenance
To optimise SQL Server memory and CPU usage

After completing this course, delegates will be able to effectively tune and optimise SQL Server 2014 for enhanced performance and efficiency, ensuring high availability and scalability of database applications, leading to improved overall system performance and reliability.

More information

See dates & prices

What's Included in this SQL Server 2014 Performance Tuning and Optimisation 55144BC Training?

World-Class Training Sessions from Experienced Instructors
SQL Server 2014 Performance Tuning and Optimisation 55144BC Certificate
Digital Delegate Pack

More information

See dates & prices

More information

See dates & prices

accredited by

Our Microsoft training course is accredited by Microsoft

Duration

Online Instructor-led (2 days)

Classroom (2 days)

Online Self-paced (16 hours)

Querying Data with Microsoft Transact-SQL DP080 Course Outline

Module 1: Introduction to Transact-SQL

Introduction
Work with Schemas
Explore the Structure of SQL Statements
Examine the SELECT Statement
Work with Data Types
Handle NULLs
Exercise - Work with SELECT Statements

Module 2: Sort and Filter Results in T-SQL

Introduction
Sort Your Results
Limit the Sorted Results
Page Results
Remove Duplicates
Filter Data with Predicates
Exercise - Sort and Filter Query Results

Module 3: Combine Multiple Tables with JOINs in T-SQL

Introduction
Understand Joins Concepts and Syntax
Use Inner Joins
Use Outer Joins
Use Cross Joins
Use Self Joins
Exercise - Query Multiple Tables with Joins

Module 4: Write Subqueries in T-SQL

Introduction
Understand Subqueries
Use Scalar or Multi-Valued Subqueries
Use Self-Contained or Correlated Subqueries
Exercise - Use Subqueries

Module 5: Use Built-In Functions and GROUP BY in Transact-SQL

Introduction
Categorise Built-In Functions
Use Scalar Functions
Use Ranking and Rowset Functions
Use Aggregate Functions
Summarise Data with GROUP BY
Filter Groups with HAVING
Exercise - Use Built-In Functions

Module 6: Modify Data with T-SQL

Introduction
Insert Data
Generate Automatic Values
Update Data
Delete Data
Merge Data Based on Multiple Tables
Exercise - Modify Data

More information

See dates & prices

Who should attend this Querying Data with Microsoft Transact-SQL DP080 Course?

This Querying Data with Microsoft Transact-SQL DP080 Training Course is designed for individuals who want to develop proficiency in querying and manipulating data using the Transact-SQL language within Microsoft SQL Server. This training course is especially beneficial for the following professionals:

Database Developers
Database Administrators
Data Analysts
Data Engineers
IT Administrators
DevOps Engineers
Business Analysts

Prerequisites of the Querying Data with Microsoft Transact SQL DP080 Course

There are no formal prerequisites for this Querying Data with Microsoft Transact SQL DP080 Training Course. However, basic familiarity with database concepts and SQL concepts would be beneficial for the delegates.

Querying Data with Microsoft Transact-SQL DP080 Training Course Overview

The Querying Data with Microsoft Transact-SQL DP080 Training Course provides a comprehensive introduction to querying data using Transact-SQL, the primary language used to manage and manipulate data in Microsoft SQL Server. This course is essential for anyone looking to understand the foundations of database querying, crucial for roles in Database Management, Data Analysis, and Software Development.

Proficiency in Transact-SQL is vital for professionals such as Database Administrators, Data Analysts, and Software Developers. Mastery of this language allows these professionals to efficiently query and manage data, ensuring data integrity and optimising database performance. By developing these skills, individuals can significantly enhance their career prospects in data-driven environments.

This 2-day training course equips delegates with the essential skills needed to query data using Microsoft Transact-SQL. Through a combination of theoretical knowledge and practical exercises, participants will learn to write efficient queries, retrieve and manipulate data, and understand key concepts such as joins, subqueries, and functions. This course ensures that delegates can confidently apply Transact-SQL in real-world scenarios.

Course Objectives

To understand the fundamentals of Transact-SQL
To learn how to write basic and advanced SQL queries
To retrieve data from single and multiple tables
To use filtering and sorting techniques to refine query results
To implement joins to combine data from multiple tables
To apply aggregate functions to summarise data

After completing the course, delegates will be proficient in writing and executing Transact-SQL queries, capable of retrieving and manipulating data efficiently, and prepared to apply these skills in real-world scenarios to enhance their professional capabilities.

More information

See dates & prices

What’s included in this Querying Data with Microsoft Transact SQL DP080 Course?

World-Class Training Sessions from Experienced Instructors
Querying Data with Microsoft Transact SQL DP080 Certificate
Digital Delegate Pack

More information

See dates & prices

More information

See dates & prices

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 +64 98874342 or Enquire.

Package deals for Microsoft SQL Server Training

Our training experts have compiled a range of course packages on a variety of categories in Microsoft SQL Server Training, to boost your career. The packages consist of the best possible qualifications with Microsoft SQL Server Training, and allows you to purchase multiple courses at a discounted rate.

Swipe for more. Don’t miss out!

Microsoft SQL Server Training FAQs

What is Microsoft SQL Server?

Microsoft SQL Server is a relational database management system developed by Microsoft. It supports a variety of transaction processing, business intelligence, and analytics applications in corporate IT environments, providing robust data storage and management capabilities.

What are the benefits of the Microsoft SQL Server Certification?

Microsoft SQL Server Training Certification enhances career prospects, validates expertise in database management, boosts earning potential, and improves job performance. It demonstrates proficiency in SQL Server technologies, leading to increased job opportunities and recognition in the industry.

Who should attend these Microsoft SQL Server Training Courses?

These Microsoft SQL Server Courses are ideal for Database Administrators, IT Professionals, Data Analysts, Developers, and anyone looking to enhance their skills in managing, maintaining, and optimising SQL Server databases in various business environments.

Are there any prerequisites needed for the Microsoft SQL Server Course?

Microsoft SQL Server Training Course typically requires a basic understanding of database concepts, familiarity with the Windows operating system, and some experience with programming or query languages. Prior exposure to SQL can be beneficial for better comprehension of course materials.

Do you provide self-paced training for these SQL Server Courses?

The Knowledge Academy provides flexible self-paced training for these Microsoft SQL Server Training Courses. Self-paced training is beneficial for individuals who have an independent learning style and wish to study at their own pace and convenience.

What are the levels of difficulties for these Microsoft SQL Server Training Courses?

Microsoft SQL Server Courses range from beginner to advanced levels, including foundational courses for novices, intermediate courses focusing on complex queries and database management, and advanced courses covering high-level administration, performance tuning, and security features.

Do you offer 24/7 support for the Microsoft SQL Server Training?

Yes, The Knowledge Academy offers 24/7 support via phone & email before attending, during, and after this course. Our customer support team is available to assist and promptly resolve any issues you may encounter.

What is the duration of the Microsoft SQL Server Training Course?

This Microsoft SQL Server Online Course is a 1-5 day training course during which delegates participate in intensive learning sessions that cover various course topics.

What is included in this Microsoft SQL Server Training?

In this Introduction to SQL Server Course, delegates will have intensive training with our experienced instructors, a digital delegate pack consisting of important notes related to this course, and a certificate after course completion.

What kind of jobs can I expect based on this Microsoft SQL Server Certification Training?

After completing this Microsoft SQL Server Training Course, you can pursue roles such as Database Administrator, SQL Developer, Data Analyst, Business Intelligence Developer, Data Engineer, IT Consultant, and System Administrator, focusing on database management, development, and data analysis.

Do you provide corporate training for Microsoft SQL Server Course?

Yes, we provide corporate training for this Microsoft SQL Server Training, tailored to fit your organisation’s requirements.

What are the best Microsoft SQL Server Training courses in New Zealand?

Please see our Microsoft SQL Server Training courses available in New Zealand

Which is the best training institute/provider of Microsoft SQL Server Training in New Zealand?

The Knowledge Academy is the Leading global training provider for Microsoft SQL Server Training.

What is the cost/training fees for Microsoft SQL Server Training in New Zealand

The training fees for Microsoft SQL Server Training in New Zealand starts from NZD3295

Why we're the go to training provider for you

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.

Trusted & Approved

We are accredited by PeopleCert on behalf of AXELOS

Many delivery methods

Flexible delivery methods are available depending on your learning style.

High quality resources

Resources are included for a comprehensive learning experience.

"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