Master On-Prem Tools

Data Engineering + Power BI + SQL + Automation
A Gateway for IT Professionals to Become BI & Reporting Experts

IT roles are evolving—BI and automation are now essential skills. This course helps IT professionals transition into BI reporting & automation while enabling BI professionals to expand into data engineering & IT infrastructure. Learn to build SQL-driven data pipelines, optimize BI systems, and automate reporting with SSIS, Power BI, and VBA—delivering real-time, enterprise-ready insights.

Tools You'll Learn

Visualization Tool

Power BI

Automation Tools

VBA, Python

Data Modeling Tools

Power Pivot, DAX

Data Transformation Tools

Power Query

Data Engineering Tools

SQL, SSIS, SSAS

In just 6 weeks, you'll be able to:

Build Scalable Data Pipelines to automate and streamline data flows.

Optimize BI Infrastructure and ensure seamless integration of data systems.

Automate End-to-End Reporting Workflows to save time and improve data efficiency.

Integrate, Transform, and Visualize Data for actionable business insights.

Create Self-Sustaining BI Systems that automate reporting and support decision-making.

Master SQL, SSIS, and Power BI for dynamic data analysis and reporting.

In just 6 weeks, you'll be able to:

Build Scalable Data Pipelines to automate and streamline data flows.

Optimize BI Infrastructure and ensure seamless integration of data systems.

Automate End-to-End Reporting Workflows to save time and improve data efficiency.

Integrate, Transform, and Visualize Data for actionable business insights.

Create Self-Sustaining BI Systems that automate reporting and support decision-making.

Master SQL, SSIS, and Power BI for dynamic data analysis and reporting.

Ideal For:

IT Professionals

looking to transition into business intelligence and data automation.

Data Engineers

aiming to enhance skills in data pipelines and scalable BI solutions.

BI Professionals

seeking to integrate data workflows for efficient reporting.

Operations Managers

wanting to optimize and automate reporting systems

Enterprise Architects

designing and optimizing BI systems for business intelligence.

Analysts

seeking to enhance their data engineering skills and integrate them into BI reporting.

A snapshot of what you'll be learning in 6-weeks.

Course Syllabus Overview

Advanced Charting Techniques

Quick Excel brush-up

Fundamentals Of Functions & Formulas Quick techniques to work with formulas & functions DATE AND TIME FUNCTIONS For data and time calculations

Today, Now
Day, Month, Year
Date, DateDif, DateAdd
EOMonth, Weekday
Workdays, NetWorkdays, Edate

TEXT FUNCTIONS For data transformations

Upper, Lower, Proper
Left, Mid, Right
Trim, Len
Concatenate
Find, Substitute

LOGICAL FUNCTIONS For conditional statement development

Nested If ( And Conditions , Or Conditions )
Alternative Solutions for Complex IF Conditions to make work simple
And, Or, Not

MATHEMATICAL FUNCTIONS For fundamental analysis

Sum, Count, CountA, Average, AverageA, Max & Min
SumIf, SumIfs
CountIf, CountIfs
AverageIf, AverageIfs
MaxIfs, MinIFs

LOOKUP FUNCTIONS For joins with multiple tables

Vlookup / HLookup
Match
Dynamic Two Way Lookup
Creating Smooth User Interface Using Lookup
Offset
Index
Dynamic Worksheet linking using Indirect

ERROR HANDLING FUNCTIONS For handling errors in functions

VBA

Understanding VBA Programming Environment

Overview

Project
Project Explorer
Menubar
Toolbar
Code Window
Module
UserForm

Programming Fundamentals Using VBA

Procedures
User Defined Functions
InputBox Function
MsgBox Function
Variables
Constants
Data Types
Working with Logical Operators
IF Statement
Nested IF Statement
IF with And Or Scenarios
Select Case
VBA Functions
Problem Solving Techniques Required for Programming
Saving Macro Enabled Files
For Loops
Do Loops
While...Wend Statement
Protecting Code
Error Handling

Running Code

Run mode as a developer
Running Code as end user
Running Code to debug procedures & Functions

Connecting to Ms-SQL via VBA

Working with MS-SQL Applications

Creating a Database
Understanding Tables and Creating Tables
Querying Data
Filtering Data
Grouping Data
Ordering Data
Column Aliases
Table Aliases
Retrieving data with VBA
Inserting, Updating and Deleting Data with VBA

Web Scrapping With VBA

Web Scrapping with VBA

Understanding & Installing Chrome Driver
Understanding HTML Tags
Understanding IDs, Class & Elements
Understanding basic Javascripts
Reading & Updating Websites using Element IDs, Class & Names
Reading Dynamic Elements in websites using VBA
Auto Filling Complex WebForms with VBA
Performing Web based actions using VBA

System Integration Using RPA

Integrating with other web based applications using API

Understanding APIs
Understanding JSON File Types and XML Files
Creating API calls with VBA
Reading, updating & deleting data with API
Working with Google APIs, Microsoft APIs, SalesForce APIs, etc

Power BI

Building Blocks of Power BI

Visualizations
Datasets
Reports
Dashboards
Tiles

Building Your First Power BI Report

Connect to Data Sources in Power BI Desktop
Clean and Transform Your Data With the Query Editor
Create a report in Power BI Desktop
Publish the report in the Power BI service

Data Modelling with Power BI

Fundamentals of Modelling
How to Manage Your Data Relationships
Create Calculated Columns
Optimizing Data Models for Better Visuals
Create measures and work with time-based functions
Create Calculated Tables
Explore Time-Based Data

Visualizations

Create and Customize Simple Visualizations

Building compelling data visualizations

Identify metrics and pair them with appropriate data visuals
Using slicers
Creating Map Visualizations
Creating Tables and Matrixes
Creating Waterfall and Funnel Charts
Using Gauges and Single Number Cards
Charting Options including Formatting with Colors, Shapes, Text Boxes, Images, etc.

Designing User-friendly reports

Customize themes
Create versatile layouts for your reports
Design principles to reduce noise and highlight data stories

Creating interactive reports for data exploration

Filtering & drilling for insights
Difference between filters & slicers
Filter pane for reporting needs

Power Query

Overview

Introduction
Loading & Refresh
Combine data from multiple data sources

Data Transformation

Editing Queries Created with Power Query
Editing Column Headers in Power Query
Splitting Column Data with Power Query
Sorting Data
Multi-Level Sorting
Filtering Data
Aggregate data from a column
Insert a custom column into a table
Merge columns
Remove columns
Remove rows with errors
Promote a row to column headers
Transforming Text Values
Replacing Data
Using the Fill command
Pivot and Unpivot Column
Transpose Query Data
Pivot Column Command in Action
Unpivot Columns Command
Grouping Data
Create a Duplicate Query
Group and Summarize Data
Advanced Data Grouping
Working with multiple sources in Power Query
Multiple Excel Tables
Expand a column containing an associated table
Understanding Table Relationships
Merging Queries

Loading Power Query Data to Destinations

Familiarity with the Load & Refresh Settings
Loading it to Workbook
Loading it to Data Model

DAX Functions

Overview

What is DAX?
Data Types
Table-Valued Functions
Building a Calendar Table
Date and Time Functions
Filter Functions
Information Functions
Logical Functions
Mathematical and Trigonometric Functions
Statistical Functions
Text Functions
Time Intelligence Functions
Creating Advanced DAX Measures With Advanced DAX Functions

Creating Advanced Dax Measures With Advanced Dax Functions

Calculate()
All()
Filter()
IF()
Switch()
SumX()

Evaluation Context

Filter Context
Row Context
Using RELATED in a Row Context
Filters and Relationships
USERELATIONSHIP

Hierarchies in DAX Querying with DAX Relationships

One-to-Many Relationships
Many-to-Many Relationships

Advanced DAX Functions

SQL Querying

Introduction to MS-SQL

Creating a Database
Understanding Tables and Creating Tables
Inserting, Updating and Deleting Data
Querying Data
Filtering Data
Grouping Data
Ordering Data
Column Aliases
Table Aliases

DDL INSIGHTS

CREATE TABLE
Dropping Objects
CREATE INDEX
TEMPORARY OBJECTS
Object Naming and Dependencies

SELECT STATEMENTS

Simple SELECTs
Calculated and Derived Fields
SELECT TOP / BOTTOM Records
Derived Tables
Joins
Predicates
Subqueries
Aggregate Functions
GROUP BY and HAVING
UNION
ORDER BY

SQL Programming

Power BI Administration

Objective: Get hands-on experience with advanced administration settings, permissions, Data refresh times etc.

Overview

Publishing Power BI Reports
Creating & Managing Workspaces and Its Access
Creating & Managing Dashboard and Its Access
Installing & Configuring Data gateway
Scheduling and configuring data refresh
Managing & Reusing Datasets
Scheduling Report Alerts
Setting up Row Level Permissions
Managing Users & Audit Log
Custom Branding Power BI For your Organization
Adding Custom Visuals for your Organization

Python for Automation

SSIS

SSAS

System Requirements

System Requirements:

Power BI Desktop

Free & Downloadable from Microsoft Store App

https://www.microsoft.com/store/productid/9NTXR16HNW1T?ocid=pdpshare
Excel 2016 & above with PowerPivot

Available with Office 365 subscriptions that include desktop versions of Excel for Windows.
MS-SQL Server

https://www.microsoft.com/en-in/sql-server/sql-server-downloads

Kindly install Developer Edition as it is full-featured free edition, licensed for use as a development and test database in a non-production environment.
SQL Server Management Studio (SSMS)

Download and install SSMS from https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16
Secondary Monitor (optional, but recommended)

Having a secondary monitor will greatly assist in following the pace of the trainer. It allows you to view instructions and your own workspace simultaneously, enhancing your learning experience.

Taught by Microsoft Certified Trainers

All our classes are live,
hands-on and with
real-trainers.

Data Engineering & BI Projects.

Real-time Projects (Practical Application)

This hybrid course sets you apart with hands-on experience across On-Prem & On-Cloud reporting. Work on 45+ projects covering Power BI dashboards, SQL queries, VBA automation, Python scripting, and app-driven workflows using Power Apps & Power Automate—ensuring you're job-ready.

Power BI

SQL

ETL

VBA & Python

Inventory & Supply Chain Dashboard

Create dynamic visualizations that track inventory levels, order fulfillment rates, and supplier performance. Learn to automate real-time data alerts to ensure optimal stock levels and timely deliveries.

Financial Health Dashboard

Build financial dashboards that display key metrics like revenue, expenses, and cash flow, with live updates. Automate reports that compare actual vs. budget figures and offer real-time insights for business decisions.

Customer Support Dashboard

Visualize customer service metrics such as ticket status, response times, and customer satisfaction. Learn how to automate ticket updates and generate insights to improve support efficiency.

Sales Performance Dashboard

Track sales revenue, regional performance, and top-selling products using dynamic visualizations. Create automatic reports to measure progress against sales targets.

Project Management Dashboard

Design a project management dashboard that tracks timelines, budgets, and milestones in real time. Automate status updates to identify risks or delays early.

Marketing Campaign Dashboard

Build dashboards that visualize key marketing metrics, such as conversions, impressions, and ROI. Automate reports to evaluate campaign success and optimize marketing strategies.

Database Design & Normalization

Design and normalize relational databases using primary keys,foreign keys, and normal forms to optimize storage and scalability.

Customer Data Analysis

Analyze customer data with JOINs, GROUP BY, and HAVING to identify trends, segment groups, and generate insights for marketing.

Sales Data Analysis

Use window functions and CTEs to track sales metrics, revenue, and product performance, generating actionable insights.

Inventory Management Queries

Leverage subqueries, UNION, and CASE to track inventory, product stock, and sales velocity for decision-making.

Employee Performance Tracker

Apply ranking functions (e.g., RANK(), DENSE_RANK()) to evaluate employee performance, track KPIs, and enhance workforce management.

Financial Reporting Queries

Retrieve financial data using aggregate functions and GROUP BY to automate real-time reports for balance sheets and P&L statements.

Sales & Customer Data Pipeline

Extract, clean, and load customer and sales data from multiple sources using Power Query and SQL, ensuring real-time reporting accuracy.

Employee Data Warehouse

Automate ETL workflows to consolidate employee records, payroll, and performance data, creating a structured HR analytics database.

Automated Data Migration & Cleansing

Transform and migrate legacy system data into modern databases, ensuring integrity and accuracy with Python and Power Query automation.

Marketing Data Consolidation

Integrate data from multiple marketing platforms (Google Ads, Facebook, LinkedIn) into a structured dataset, enabling real-time campaign performance tracking.

Inventory & Procurement Data Pipeline

Automate inventory tracking and supplier performance analysis with SQL and Python, setting up alerts for low stock levels and procurement optimization.

Automated Excel Report Generation

Automate the creation of complex reports with VBA macros and Python scripts. Generate dynamic pivot tables, charts, and formatted summaries at the click of a button.

Email & Report Automation

Use VBA and Outlook automation to send scheduled reports based on data conditions. Ensure timely delivery of financial, sales, or operational updates.

Web Scraping & Data Collection

Automate data extraction from websites using Python (BeautifulSoup, Selenium) to collect real-time insights for competitive analysis and trend tracking.

Data Cleaning & Transformation

Leverage Python and VBA to clean and standardize raw data, automate missing value handling, and prepare datasets for reporting.

Smart Financial Generator

Use VBA and Python to extract financial data, consolidate income statements, and generate automated balance sheets with dynamic updates.

Visualize customer service metrics such as ticket status, response times, and customer satisfaction. Learn how to automate ticket updates and generate insights to improve support efficiency.

Track sales revenue, regional performance, and top-selling products using dynamic visualizations. Create automatic reports to measure progress against sales targets.

Design a project management dashboard that tracks timelines, budgets, and milestones in real time. Automate status updates to identify risks or delays early.

Build dashboards that visualize key marketing metrics, such as conversions, impressions, and ROI. Automate reports to evaluate campaign success and optimize marketing strategies.

Design and normalize relational databases using primary keys, foreign keys, and normal forms to optimize storage and scalability.