Advanced Customized SQL Server and Business Intelligence Course

This course is design to cover advance topics in MS SQL Server.

SQL Server Architecture

Fast-track T-SQL

  • CRUD Queries, Joins, Groups etc
  • Filtering, Grouping, Subqueries etc

 

SQL Server Database Development

  • SQL View and Indexes
  • Introduction to Stored Procedures
  • User-Defined Functions
  • Working with Triggers

 

SQL Server High Availability(HA)

  • Backup and Restore
  • Configuring Replication
  • Log Shipping
  • Mirroring
  • Clustering

 

Administration and Security

  • Creating Users and Logins
  • Assigning Roles and Permissions
  • Working with Schemas.

 

SQL Server Query Performance and Optimizatin

  • Creating Execution Plans
  • Monitoring Databases
  • SQL Server Monitoring Components
  • Using Activity Monitor
  • Execution Plans
  • Tuning
  • Using Tuning Advisor
  • Introduction to SQL Server Profile

 

Microsoft Business Intelligence

Microsoft provides a suite of tools for data analytics, reporting and integration. These tools are collective called Microsoft Business Intelligence(MSBI). We would cover them in this tutorial.

SQL Server Analysis Services (SSAS)

SSAS is an analytical data engine provided by Microsoft and using for modelling and analysis of data for business intelligence(BI). SSAS is used to develop Online Analytical Processing Solutions(OLAP).  The following are covered under SSAS

  • Configure Schema n BI Development Studio
  • Creating dimensions, hierarchies and cubes
  • Read data from multidimensional datasets
  • Process a data cube
  • Data cube deployment
  • Create calculated measures and named state with MDX
  • Browse the data cube using a client tool(MS Excel)

 

SQL Server Reporting Services (SSRS)

SSRS is a set of tools and services provided by Microsoft for creating, deploying and managing reports. SSRS is deployed on premise. This tutorial would cover the following:

  • Setting up Report Building on Windows
  • Introduction to Report Designer
  • Creating a Basic Report using Report Designer
  • Crating a Data-Driven Subscription
  • Creating a Drillthrough Report using ReportViewer
  • Introduction to Report Builder
  • Creating s Simple Chart Report
  • Creating a Data Connection
  • Basic Reports(Basic table, matrix and free form)
  • Working With Plain Text
  • Adding Charts(Pie, Bar, Sparkling)
  • Adding Maps
  • Parameterized Reports
  • Introduction to Web Portal
  • Creating and Branding a Web Portal
  • Using Shared Datasets
  • Creating Paginated Reports
  • Managing Snapshots
  • Working with Data Subscriptions
  • Using KPIs
  • Integrating with Power BI

 

SQL Server Integration Services (SSIS)

SQL Server Integration Services (SSIS) is platform created by Microsoft for developing enterprise-grade data integration as well as data transformation solutions. With SSIS, you can handle complex business challenges by downloading and loading datasets into data warehouse, data cleansing and data mining.

SSIS can be used tor extract, transform and load(ETL) of data from different data sources including XML data files, relational databases, flat files. Then these data can be consolidated into one or more destinations.

Additionally, SSIS has a graphical integration services(GIS) set of tools you can used to build solutions without having to write codes. In this tutorial, we would cover the following:

  • Setup SQL Server Management Studio(SSDT)
  • Setup SQL Server Data Tools(SSDT)
  • Installing Integration Services
  • Creating SSIS Projects and Solutions
  • Creating an Integration Services Project and Basic Package
  • Adding Looping
  • Adding Logging
  • Adding Error Flow Redirection
  • Creating Package Configuration for Deployemnt
  • Working with parameterised deployment
  • Deploying the Package

 

Introduction to Microsoft Power BI

Power BI is a business analytics service developed by Microsoft. The objective is to provide interactive visualisation and BI features that is simple enough for different kinds of end-users to create reports and dashboard easily.

According to the Microsoft Documentations, Power BI can be used to creating amazing  data experiences, connect to model and creating nice reports with KPIs and brand.

This tutorial would cover the following:

  • Overview and sign up to Power BI
  • Creating your first online report and dashboard
  • Downloading the Power BI Designer for desktop
  • Loading, transforming and filtering data using Power BI
  • Creating tables and reports
  • Applying filters and slicers
  • Using graphs and visualizations
  • Creating relationships between tables
  • Creating calculations, key metrics and KPIs
  • Creating interactive Dashboard
  • Publishing dashboards to Power BI

 

Introduction to Azure Database Services

  • SQL Servers
  • SQL Elastic Pools
  • SQL Server Registries
  • Azure Database Migration Services