Full Stack BI Reporting & Automation Course

Project Management Data Professionals.

Ready to take your project management career to the next level? Our Full Stack BI course is designed to provide you with the latest data analysis and automation techniques, enabling you to enhance your value as a highly skilled project management data professional.

Prove you're human: Type the code shown.

By clicking any of the above buttons, I agree to the terms & conditions and privacy policy, and I consent to receive updates via SMS or email

Prove you're human: Type the code shown.

By clicking any of the above buttons, I agree to the terms & conditions and privacy policy, and I consent to receive updates via SMS or email

INTRODUCTION

In this course, you will learn how to harness the power of cutting-edge technologies like Power BI, MS-SQL, ETL, VBA, and SSAS to generate
advanced end-to-end project management reports. instructors bring real-world experience and industry insights to the course, ensuring that you learn the latest best practices and cutting-edge techniques.

In this course, you will learn how to harness the power of cutting-edge technologies like Power BI, MS-SQL, ETL, VBA, and SSAS to generate advanced end-to-end project management reports. Our expert instructors bring real-world experience and industry insights to the course, ensuring that you learn the latest best practices and cutting-edge techniques.

COURSE

We've designed this course specifically for project management data professionals and Project Management Office (PMO) teams, aimed at improving their data management and analytical skills.

BI CONSULTING

From improving project timelines to enhancing resource allocation, we'll assist you in leveraging your project management data to make data-driven decisions and maximize operational efficiency.

Our team of project management data experts will work closely with your PMO to understand your unique needs and develop customized solutions that align with your organizational goals and objectives.

Is a tailor-made course not what you're looking for? No problem! Join hundreds of other individuals in our vibrant regular Full Stack BI Reporting & Automation course

Is a tailor-made course not what you're looking for? No problem! Join hundreds of other individuals in our vibrant regular

Key Project Management Reports you'll be Working With

Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy

Key Reports You'll Learn

Project Status Report

Resource Allocation Report

Financial Performance Report

Risk Management Report

Project Change Report

Benefits Realization Report

Quality Control Report

Stakeholder engagement reports

Lessons Learned Report

Portfolio Performance Report

Tools You'll Learn

Visualization Tool

Microsoft Power BI

Data Modeling Tool

Power Pivot

Data Integrator Tool

ETL with SSIS

Data Transformation Tool

Power Query

Data Warehousing Tool

MS-SQL

Automation Tool

VBA & SSAS

Key Project Management Reports You'll Be Working with

Lorem ipsum dolor sit amet, consetetur sadipscing elitr,

Key Reports You'll Learn

Project Status Report

Resource Allocation Report

Financial Performance Report

Risk Management Report

Project Change Report

Benefits Realization Report

Quality Control Report

Stakeholder engagement reports

Lessons Learned Report

Portfolio Performance Report

Tools You'll Learn

Visualization Tool

Microsoft Power BI

Data Integrator Tool

ETL with SSIS

Data Transformation Tool

Power Query

Data Modeling Tool

Power Pivot

Data Warehousing Tool

MS-SQL

Automation Tool

VBA & SSAS

Course Content - Overview

Learn the entire suite of tools and technologies involved in building a complete BI solution.

Quick brush-up with Excel

Excel is to BI what wireframing is to application development. Brush up your Excel skills with 60+ formulas, pivots, charts & dashboards to quickly build a POC with small data before investing your time in building complex BI reports with big datasets.

Why should I learn Excel?

Visualization with Power BI

Getting started with Power BI is simple. Then, once you want to leverage its full potential, you need a different set of skills. Learn how to develop these skills, unlock more insights through better data analysis and visualization with this section.

Why should I learn Power BI?

Advanced Calculation with DAX

Add analytical power to your Power BI model that is not available in the data as-is, using DAX. The goal of this section is to teach you basic to advance features of DAX, providing the knowledge to write formulas for common and advanced business scenarios.

Why should I learn DAX?

M-Programming with Power Query

During this section, you'll learn how to perform advanced data transformations not available in the Query Editor. We will discuss how to leverage the embedded PowerBI programming language (M) to achieve this.

Why Should I learn M-Programming?

Model-building with Power Pivot

Explore the most powerful tool to build data models, forecast estimates, perform what-if-analysis scenario etc. If you are yet to have the Power BI license, you would still be able to create powerful & blended reports and share it internally using Power Pivot.

Why should I learn Power Pivot?

ETL with SSIS

Extract - Transform - Load. Extract Data from multiple data sources, transform it with SSIS inbuilt transformation options or use M-Programs with SSIS and, Load it to the Reporting Database or Data Warehouse for generating dynamic BI Reports.

Why should I know ETL with SSIS?

Data warehousing using MS-SQL

Data for Power BI can come from many sources, but often the data is hosted in SQL Server. This section will teach you how to write accurate, effective, and robust T-SQL code- the right way and also understand the logic behind the same.

Why should I learn MS-SQL?

Modeling & Analytics with SSAS

In this section, you'll master SSAS Tabular, crafting optimized data models for lightning-fast analysis. You'll seamlessly integrate these models with BI tools, enabling real-time insights and building robust reporting solutions.

Why should I learn SSAS Tabular?

Architect Power BI Solution

This section will help you put together Excel, VBA, Power BI, DAX, Power Query and SQL to architect the best BI solution. You'll also learn to publish, securely share reports including row level permissions along with BI administrative tasks like setting up data gateways etc.

Why should I learn to Architect a solution?

Course Snapshot

Duration

6-Weeks, 30 Sessions

Online live classes, Instructor-led.

Cohort Date

View Shedule

Certificate

9-Specialist Certificates

List of Certificates

1:1 Mentorship

15-hours | Project-based

View Details

COURSE SNAPSHOT

Duration

6-Weeks, 30 Sessions

Online live classes, Instructor-led.

Cohort Date

View Shedule

Certificate

9-Specialist Certificates

List of Certificates

1:1 Mentorship

15-hours | Project-based

View Details

Detailed Course Content

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

Objective: To prepare a quick POC with small data and get the concurrence on the output before investing time on huge datasets.

Overview

Core Fundamentals Of Spreadsheets
How System Interprets Your Data
Crucial Formatting Concepts
Understanding Core Formula Fundamentals & Advanced Referencing Concepts
60+ Advanced Excel Functions
Cleansing & Transforming Data
Data Summarization
Conditional Summarization
Working With Multiple Datasets
Working With Pivot Tables Including Advanced Pivot Tables Options
15+ Charts - When To Use What Chart?
Develop Data Models to Simulate Complex Business Scenarios for What IF Analysis

Objective: To do faster queries, simpler DAX code and enjoy easier maintenance.

Introduction To PowerPivot

Limitation Of Excel Functions
Limitation Of Excel Pivot table
Why Powerpivot?
Powerpivot Features – Overview

Powerpivot Environment

Opening Powerpivot Environment
Understanding External Data Section
Understanding Formatting
Powerpivot Views
Understanding Measures

Loading Data Into PowerPivot

Calculated Columns

Calculated Columns
Entering Formulas
Using Autocomplete Feature
Renaming Columns
Understanding Tables

Creating Measures

Creating Simple DAX Measures
Explicit Measure Vs Implicit Measure
Referencing Measures In Other Measures
Formatting Measures
Manage Data Relationships
Working With Multiple Tables
Disconnected Tables
Creating Custom Calendars
Using Advanced Filter()
Advanced Calculated Columns
Understanding Relationship Concept
Join Tables
Left Join Tables
Right Join Tables

Data Visualization Using

Charts, Score Cards And Dashboards
Slicers
Map Visualizations
Data Binding And Formatting

Creating Advanced Dashboards With PowerPivot

Creating Free Form Reports With PowerPivot & Excel

Develop Simple Models And Forecast Data Using PowerPivot & Excel

Objective: Getting started with Power BI and leverage it’s full potential to develop compelling visualizations.

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 Modeling with Power BI

Fundamentals of Modeling
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 Scatter Charts
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

Exploring Data

Use Quick Insights in the Power BI service
Create and Configure A Dashboard
Share Dashboards with your organization
Display Visuals and Tiles Full-Screen

Objective: To learn different methods to Transform Data before uploading it to the SQL Database.

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

Objective: To add analytical power to your Power BI model that is not available in the data as-is, using DAX.

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

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

Objective: To query MS-SQL Database (es) by writing accurate, effective, and robust T-SQL code - the right way.

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

Objective: Leverage DAX potential to perform advanced data analytics

Advanced Table Manipulation Functions

ADDCOLUMNS
CROSSJOIN
DATATABLE
DISTINCT column
EXCEPT
FILTERS
GENERATESERIES
INTERSECT
NATURALINNERJOIN
NATURALLEFTOUTERJOIN
ROW
SELECTCOLUMNS
SUMMARIZE
SUMMARIZECOLUMNS
Constructor
TOPN
UNION
VALUES

Advanced Filter Functions

ADDMISSINGITEMS
ALL
ALLCROSSFILTERED
ALLEXCEPT
ALLSELECTED
CALCULATE
DISTINCT
FILTER
FILTERS
RELATED
REMOVEFILTERS
SELECTEDVALUE
USERELATIONSHIP
VALUES

ADVANCED USAGE OF

CALCULATE
SUMX
COUNTX
AVERAGEX
MAXX
MINXX
DATATABLE

Objective: To perform advanced and complex data transformations not available in the Query Editor

Understanding M Programming Code

M LANGUAGE Specifications
Lexical Structure
Basic Concepts
Values
Types
Operators
Let
Conditionals
Functions
Error Handling
Creating Simple M Expressions
Creating Simple M Functions
Working with List, Record & Tables
Understanding Operators and Evaluation Methods
Data Types & Type Conversions

Working with M Functions

ACCESSING DATA FUNCTIONS - From Excel, SQL, CSV, Tab-Delimited, Webpage, XML, JSON, SharePoint, etc.
COMBINER FUNCTIONS
COMPARER FUNCTIONS
DATE FUNCTIONS
DATETIME FUNCTIONS
DURATION FUNCTIONS
LIST FUNCTIONS
LOGICAL FUNCTIONS
NUMBER FUNCTIONS
RECORD FUNCTIONS
REPLACER FUNCTIONS
SPLITTER FUNCTIONS
TABLE FUNCTIONS
TEXT FUNCTIONS
TIME FUNCTIONS
TYPE FUNCTIONS
VALUE FUNCTIONS

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

Objective: To collect data from multiple ad-hoc sources and prepare it for loading it to SQL Database for BI Reporting purpose.

Overview

Core Fundamentals of VBA
Understanding VBA Environment
Writing simple VBA Codes
Concept of Variables & Data Types
Testing & Debugging Code
Understanding Fundamental Coding Blocks
IF Block
Select Case
And / Or Conditions
Understanding Fundamentals of Looping
For Loop
Do While
Understanding Object Based Programming
Advanced Excel Automations
Word Automation
Outlook Automation
Working with Database
Working with Web Browsers - Web Scrapping
Creating User Interfaces with VBA Forms
Handling Run Time Errors & Securing Code

Objective: Developing expertise in SSAS Tabular for creating high-performance data models vital for real-time analysis and integrated BI reporting solutions.

SSAS Tabular Modeling - Introduction

Understanding SSAS
Installing and configuring a development environment
Installing Visual Studio
Configuring Tabular Mode Environment
Importing sample datasets
Configuring a workspace server
SSAS project properties

Creating & Working with Simple SSAS Tabular Models

Adding data into tabular models
Creating Calculated Columns
Creating Simple Measures
Creating Calculated Tables
Creating Calendar Tables
Creating and Working with Hierarchies
Working with Model Views
Working in Tabular Models & KPIs
Setting Relationships
Creating and deploying Simple Model
Use the models via SSMS, Excel & Power BI

Administering SSAS Tabular Models

Working with tabular model properties
Creating and working with partitions
Working with Roles
Server Properties
Managing SSAS Memory

SSAS Tabular Model - Security

Understanding Tabular Models Security
Set up row-level security
Set up dynamic filter security

Working with DAX Functions in SSAS Tabular Models

Creating Measures
Creating Calculates Columns
Using the Power BI DAX functions learnt in SSAS Tabular
Working with Calendar Tables
Date & Time Intelligence Functions

Connecting & Working with SSAS Tabular Models in Power BI

Objective: Extract Data from multiple data sources, transform them with SSIS or M-Program transformation options and, Load it to Reporting Database / Data Warehouse

Understanding SSIS Packages

Creating & Managing Connections with Connection Manager

Importing Data With ADO.NET
Importing Data From Excel
Importing Data From Flat Files
Importing Data From FTP
Importing Data From SMTP
Working with Multiple Connections
Working with Control Flows
Working with Data Flows
Working with Variables & Expressions

Performing Transformations

Aggregation Transformations
Audit Transformations
Conditional Split Transformations
Data Conversion Transformations
DQS Cleansing Transformations
Derived Columns Transformations
Export Column Transformation
Fuzzy Grouping Transformation
Fuzzy Lookup Transformation
Lookup Transformation
Merge Transformation
Merge Join Transformation
Pivot Transformation
Unpivot Transformation
Row Count Transformation
Sort Transformation

Loading Data to SQL Server

Advanced SQL Concepts

DML INSIGHTS

INSERT
UPDATE
DELETE
Detecting DML Errors

Understanding Data Types

Dates
Strings
Numeric
BLOBs
Bits
UNIQUEIDENTIFIER
Cursor Variables
Timestamps

DDL INSIGHTS

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

SQL VIEWS

Create & Manage VIEWs
Getting a VIEW's Source Code
Updatable VIEWs
Derived Tables
Dynamic VIEWs
Partitioning Data Using Views
View Restrictions

Stored Procedures

Stored Procedure Advantages
Internals
Creating Stored Procedures, Executing Stored Procedures
Environmental Concerns
Parameters for Procedures
Important Automatic Variables
Flow Control Language
Errors
Nesting
AutoStart Procedures
User Defined Functions
Scalar UDFs
Table-Valued UDFs
Per-Row UDFs

Working with Date & Time

Date and Time Data Types
Date and Time Manipulation
Date and Time Functions
Literals
Identifying Weekday
Handling Date-only or Time-only Data Prior to SQL Server 2008
Examples of Date and Time Calculations
Rounding Issues
Date- and Time-Related Querying Problems
Age Problems
Overlaps
Grouping by the Week
Working Days
Generating a Series of Dates

SETS

Unions
Differences
Intersections
Subsets

Transactions

Transactions Defined
How SQL Server Transactions Work
Types of Transactions
Avoiding Transactions Altogether
Automatic Transaction Management
Transaction Isolation Levels
Transaction Commands and Syntax
Debugging Transactions
Optimizing Transactional Code

Objective: To put our entire learning to use and arrive at the best possible BI solution -using the right combination of available technologies.

Overview

Case Study 1
Case Study 2
Case Study 3

System Requirements

Power BI Desktop

Free & Downloadable at https://powerbi.microsoft.com/en-us/get-started/
Excel 2016 & above with PowerPivot

Available with Office 365 subscriptions that include desktop versions of Excel for Windows. Also available with certain onetime office purchases. Refer https://support.office.com/en-us/article/where-is-power-pivot-aa64e217-4b6e-410b-8337-20b87e1c2a4b for more information
SQL Server Development Tools (SSDT)

Refer https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver15
Visual Studio 2017 & above

Visual Studio Community Edition is Free and can be downloaded fromhttps://visualstudio.microsoft.com/vs/
MS-SQL Server

Free & Downloadable at https://www.microsoft.com/en-us/sql-server/sql-server-editions-express/
SSIS

It comes along with your SQL Server License.

Course Fee

$ 1999^*

What is included?

8 Excel Reports & Models
12 Power BI + DAX Dashboards
6 Power Query Projects
6 Power Pivot Models
6 Power Query Projects
7 ETL Projects
5 MS-SQL Projects
14 VBA Automation Scenarios
4 Power BI + DAX + PQ Based Dashboards

3 Power BI + DAX + PQ + SQL Based Dashboards
9 Master Projects Using Power BI, DAX, PQ, SQL VBA, Web-Scrapping, API Integration, etc.

Course Content

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

Objective: To prepare a quick POC with small data and get the concurrence on the output before investing time on huge datasets.

Overview

Core Fundamentals Of Spreadsheets
How System Interprets Your Data
Crucial Formatting Concepts
Understanding Core Formula Fundamentals & Advanced Referencing Concepts
60+ Advanced Excel Functions
Cleansing & Transforming Data
Data Summarization
Conditional Summarization
Working With Multiple Datasets
Working With Pivot Tables Including Advanced Pivot Tables Options
15+ Charts - When To Use What Chart?
Develop Data Models to Simulate Complex Business Scenarios for What IF Analysis

Objective: To do faster queries, simpler DAX code and enjoy easier maintenance.

Introduction To PowerPivot

Limitation Of Excel Functions
Limitation Of Excel Pivot table
Why Powerpivot?
Powerpivot Features – Overview

Powerpivot Environment

Opening Powerpivot Environment
Understanding External Data Section
Understanding Formatting
Powerpivot Views
Understanding Measures

Loading Data Into PowerPivot

Calculated Columns

Calculated Columns
Entering Formulas
Using Autocomplete Feature
Renaming Columns
Understanding Tables

Creating Measures

Creating Simple DAX Measures
Explicit Measure Vs Implicit Measure
Referencing Measures In Other Measures
Formatting Measures
Manage Data Relationships
Working With Multiple Tables
Disconnected Tables
Creating Custom Calendars
Using Advanced Filter()
Advanced Calculated Columns
Understanding Relationship Concept
Join Tables
Left Join Tables
Right Join Tables

Data Visualization Using

Charts, Score Cards And Dashboards
Slicers
Map Visualizations
Data Binding And Formatting

Creating Advanced Dashboards With PowerPivot

Creating Free Form Reports With PowerPivot & Excel

Develop Simple Models And Forecast Data Using PowerPivot & Excel

Objective: Getting started with Power BI and leverage it’s full potential to develop compelling visualizations.

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 Modeling with Power BI

Fundamentals of Modeling
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 Scatter Charts
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

Exploring Data

Use Quick Insights in the Power BI service
Create and Configure A Dashboard
Share Dashboards with your organization
Display Visuals and Tiles Full-Screen

Objective: To learn different methods to Transform Data before uploading it to the SQL Database.

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

Objective: To add analytical power to your Power BI model that is not available in the data as-is, using DAX.

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

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

Objective: To query MS-SQL Database (es) by writing accurate, effective, and robust T-SQL code - the right way.

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

Objective: Leverage DAX potential to perform advanced data analytics

Advanced Table Manipulation Functions

ADDCOLUMNS
CROSSJOIN
DATATABLE
DISTINCT column
EXCEPT
FILTERS
GENERATESERIES
INTERSECT
NATURALINNERJOIN
NATURALLEFTOUTERJOIN
ROW
SELECTCOLUMNS
SUMMARIZE
SUMMARIZECOLUMNS
Constructor
TOPN
UNION
VALUES

Advanced Filter Functions

ADDMISSINGITEMS
ALL
ALLCROSSFILTERED
ALLEXCEPT
ALLSELECTED
CALCULATE
DISTINCT
FILTER
FILTERS
RELATED
REMOVEFILTERS
SELECTEDVALUE
USERELATIONSHIP
VALUES

ADVANCED USAGE OF

CALCULATE
SUMX
COUNTX
AVERAGEX
MAXX
MINXX
DATATABLE

Objective: To perform advanced and complex data transformations not available in the Query Editor

Understanding M Programming Code

M LANGUAGE Specifications
Lexical Structure
Basic Concepts
Values
Types
Operators
Let
Conditionals
Functions
Error Handling
Creating Simple M Expressions
Creating Simple M Functions
Working with List, Record & Tables
Understanding Operators and Evaluation Methods
Data Types & Type Conversions

Working with M Functions

ACCESSING DATA FUNCTIONS - From Excel, SQL, CSV, Tab-Delimited, Webpage, XML, JSON, SharePoint, etc.
COMBINER FUNCTIONS
COMPARER FUNCTIONS
DATE FUNCTIONS
DATETIME FUNCTIONS
DURATION FUNCTIONS
LIST FUNCTIONS
LOGICAL FUNCTIONS
NUMBER FUNCTIONS
RECORD FUNCTIONS
REPLACER FUNCTIONS
SPLITTER FUNCTIONS
TABLE FUNCTIONS
TEXT FUNCTIONS
TIME FUNCTIONS
TYPE FUNCTIONS
VALUE FUNCTIONS

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

Objective: To collect data from multiple ad-hoc sources and prepare it for loading it to SQL Database for BI Reporting purpose.

Overview

Core Fundamentals of VBA
Understanding VBA Environment
Writing simple VBA Codes
Concept of Variables & Data Types
Testing & Debugging Code
Understanding Fundamental Coding Blocks
IF Block
Select Case
And / Or Conditions
Understanding Fundamentals of Looping
For Loop
Do While
Understanding Object Based Programming
Advanced Excel Automations
Word Automation
Outlook Automation
Working with Database
Working with Web Browsers - Web Scrapping
Creating User Interfaces with VBA Forms
Handling Run Time Errors & Securing Code

Objective: Extract Data from multiple data sources, transform them with SSIS or M-Program transformation options and, Load it to Reporting Database / Data Warehouse

Understanding SSIS Packages

Creating & Managing Connections with Connection Manager

Importing Data With ADO.NET
Importing Data From Excel
Importing Data From Flat Files
Importing Data From FTP
Importing Data From SMTP
Working with Multiple Connections
Working with Control Flows
Working with Data Flows
Working with Variables & Expressions

Performing Transformations

Aggregation Transformations
Audit Transformations
Conditional Split Transformations
Data Conversion Transformations
DQS Cleansing Transformations
Derived Columns Transformations
Export Column Transformation
Fuzzy Grouping Transformation
Fuzzy Lookup Transformation
Lookup Transformation
Merge Transformation
Merge Join Transformation
Pivot Transformation
Unpivot Transformation
Row Count Transformation
Sort Transformation

Loading Data to SQL Server

Advanced SQL Concepts

DML INSIGHTS

INSERT
UPDATE
DELETE
Detecting DML Errors

Understanding Data Types

Dates
Strings
Numeric
BLOBs
Bits
UNIQUEIDENTIFIER
Cursor Variables
Timestamps

DDL INSIGHTS

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

SQL VIEWS

Create & Manage VIEWs
Getting a VIEW's Source Code
Updatable VIEWs
Derived Tables
Dynamic VIEWs
Partitioning Data Using Views
View Restrictions

Stored Procedures

Stored Procedure Advantages
Internals
Creating Stored Procedures, Executing Stored Procedures
Environmental Concerns
Parameters for Procedures
Important Automatic Variables
Flow Control Language
Errors
Nesting
AutoStart Procedures
User Defined Functions
Scalar UDFs
Table-Valued UDFs
Per-Row UDFs

Working with Date & Time

Date and Time Data Types
Date and Time Manipulation
Date and Time Functions
Literals
Identifying Weekday
Handling Date-only or Time-only Data Prior to SQL Server 2008
Examples of Date and Time Calculations
Rounding Issues
Date- and Time-Related Querying Problems
Age Problems
Overlaps
Grouping by the Week
Working Days
Generating a Series of Dates

SETS

Unions
Differences
Intersections
Subsets

Transactions

Transactions Defined
How SQL Server Transactions Work
Types of Transactions
Avoiding Transactions Altogether
Automatic Transaction Management
Transaction Isolation Levels
Transaction Commands and Syntax
Debugging Transactions
Optimizing Transactional Code

Objective: Developing expertise in SSAS Tabular for creating high-performance data models vital for real-time analysis and integrated BI reporting solutions.

SSAS Tabular Modeling - Introduction

Understanding SSAS
Creating Calculated Columns
Installing Visual Studio
Configuring Tabular Mode Environment
Importing sample datasets
Configuring a workspace server
SSAS project properties

Creating & Working with Simple SSAS Tabular Models

Adding data into tabular models
Installing and configuring a development environment
Creating Simple Measures
Creating Calculated Tables
Creating Calendar Tables
Creating and Working with Hierarchies
Working with Model Views
Working in Tabular Models & KPIs
Setting Relationships
Creating and deploying Simple Model
Use the models via SSMS, Excel & Power BI

Administering SSAS Tabular Models

Working with tabular model properties
Creating and working with partitions
Working with Roles
Server Properties
Managing SSAS Memory

SSAS Tabular Model - Security

Understanding Tabular Models Security
Set up row-level security
Set up dynamic filter security

Working with DAX Functions in SSAS Tabular Models

Creating Measures
Creating Calculates Columns
Using the Power BI DAX functions learnt in SSAS Tabular
Working with Calendar Tables
Date & Time Intelligence Functions

Connecting & Working with SSAS Tabular Models in Power BI

Objective: To put our entire learning to use and arrive at the best possible BI solution -using the right combination of available technologies.

Overview

Case Study 1
Case Study 2
Case Study 3

System Requirements

Power BI Desktop

Free & Downloadable at https://powerbi.microsoft.com/en-us/get-started/
Excel 2016 & above with PowerPivot

Available with Office 365 subscriptions that include desktop versions of Excel for Windows. Also available with certain onetime office purchases. Refer https://support.office.com/en-us/article/where-is-power-pivot-aa64e217-4b6e-410b-8337-20b87e1c2a4b for more information
SQL Server Development Tools (SSDT)

Refer https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver15
Visual Studio 2017 & above

Visual Studio Community Edition is Free and can be downloaded fromhttps://visualstudio.microsoft.com/vs/
MS-SQL Server

Free & Downloadable at https://www.microsoft.com/en-us/sql-server/sql-server-editions-express/
SSIS

It comes along with your SQL Server License.

Course Fee

$ 1999^*

650-591-3131

What is included?

8 Excel Reports & Models
12 Power BI + DAX Dashboards
6 Power Query Projects
6 Power Pivot Models
6 Power Query Projects
7 ETL Projects
5 MS-SQL Projects
14 VBA Automation Scenarios
4 Power BI + DAX + PQ Based Dashboards
3 Power BI + DAX + PQ + SQL Based Dashboards
9 Master Projects Using Power BI, DAX, PQ, SQL VBA, Web-Scrapping, API Integration, etc.

Training Schedule

Week 1 - Quick Excel Brush-up – Advanced Formulas & Functions

Thu, Oct 10 & Fri, Oct 11

01:00 PM to 03:00 PM ET
( Project 1: Project Management )

Week 2 - Power BI & DAX Fundamentals Advanced DAX Function, Power Query & Power Pivot

Tue, Oct 15 through Fri, Oct 18

01:00 PM to 03:00 PM ET
( Project 2: Project Management )

Week 3 - Power BI & DAX Fundamentals Advanced DAX Function, Power Query & Power Pivot

Mon, Oct 21 through Fri, Oct 25

01:00 PM to 03:00 PM ET
( Project 3: Project Management )

Week 4 - T-SQL & Power BI Administration

Mon, Oct 28 & Tue, Oct 29

01:00 PM to 03:00 PM ET
( Project 4: Project Management )

Week 5 - Ad-hoc Automations using VBA Macro

Tue, Nov 12 through Fri, Nov 15

01:00 PM to 03:00 PM ET
( Project 5: Project Management )

Week 6 - Ad-hoc Automations using VBA Macro, ETL with MS-SQL - SSIS

Mon, Nov 18 through Fri, Nov 22

01:00 PM to 03:00 PM ET
( Project 6: Project Management )

Week 7 - SSAS and Architect BI Solution

Mon, Nov 25 & Tue, Nov 26

01:00 PM to 03:00 PM ET
( Project 7: Project Management )

NEW

Upcoming Batch

Starts On

Tuesday, 11 JULY

Time

11:00 AM - 2:00 PM ET

Course Fee

$1999*

*($1299 for Excelgoodies Power BI Students)

Or Click here to view next cohort dates.

CORPORATE
Training 5 or
more people?

Avail additional 10% Corporate Benefit^*
on the total course fee for 5+ participants.
Get you team BI ready, today.

*Available until Apr 30, 2024.

Training Schedule

Week 1 - Quick Excel Brush-up – Advanced Formulas & Functions

Thu, Oct 10 & Fri, Oct 11

01:00 PM to 03:00 PM ET
( Project 1: Project Management )

Week 2 - Power BI & DAX Fundamentals Advanced DAX Function, Power Query & Power Pivot

Tue, Oct 15 through Fri, Oct 18

01:00 PM to 03:00 PM ET
( Project 2: Project Management )

Week 3 - Power BI & DAX Fundamentals Advanced DAX Function, Power Query & Power Pivot

Mon, Oct 21 through Fri, Oct 25

01:00 PM to 03:00 PM ET
( Project 3: Project Management )

Week 4 - T-SQL & Power BI Administration

Mon, Oct 28 & Tue, Oct 29

01:00 PM to 03:00 PM ET
( Project 4: Project Management )

Week 5 - Ad-hoc Automations using VBA Macro

Tue, Nov 12 through Fri, Nov 15

01:00 PM to 03:00 PM ET
( Project 5: Project Management )

Week 6 - Ad-hoc Automations using VBA Macro, ETL with MS-SQL - SSIS

Mon, Nov 18 through Fri, Nov 22

01:00 PM to 03:00 PM ET
( Project 6: Project Management )

Week 7 - SSAS and Architect BI Solution

Mon, Nov 25 & Tue, Nov 26

01:00 PM to 03:00 PM ET
( Project 7: Project Management )

9 Specialist Certificates

Take exam and get certified for in-demand data tools.

Certificate : MS-Excel Associate

Level :

Exam ID : EG-901

Eligibility : On clearing post-training assessment

Certificate : MIS Reporting & Business Modeling Specialist using MS-Excel

Level :

Exam ID : EG-902

Eligibility : On clearing post-training assessment

Certificate : VBA Developer

Level :

Exam ID : EG-903

Eligibility : On clearing post-training assessment

Certificate : Report Automation Specialist using Excel VB Programming

Level :

Exam ID : EG-904

Eligibility : On implementing a Live VBA project

Certificate : Data Analysis & Dashboard Associate using Power BI

Level :

Exam ID : EG-905

Eligibility : On clearing post-training assessment

Certificate : Data Analytics & BI Developer using MS-SQL & Power BI

Level :

Exam ID : EG-906

Eligibility : On clearing post-training assessment

Certificate : Data warehouse & ETL Specialist using MS-SQL, SSIS & Power BI

Level :

Exam ID : EG-907

Eligibility : On clearing post-training assessment

Certificate : Business Reporting Specialist using SSAS & Power BI

Level :

Exam ID : EG-908

Eligibility : On clearing post-training assessment

Certificate : Fullstackbi Reporting & Automation Specialist

Level :

Average Rating

4.9

Average rating this year

Scott A. Reifsynder

Power BI Training

Chief Financial Officer

Nexterus

United States

This class was extremely helpful in my knowledge and growth in these database, analysis and reporting tools. I really enjoyed the hands-on approach and got lots of “goodies” out of all the classes. Your approach, repetitiveness and patience is much appreciated and sincerely helped me learn how to accomplish these important tasks. Doing these activities again and again embedded these processes into my routines while I began strategizing how to best utilize these tools in my company’s reporting and analyses. I’d be happy to consider additional classes that you offer on related, important topics to continue enhancing my skills. Thank you very much for your teaching and training methods. I am also very grateful that you provided the daily sample dashboards, instruction steps and how-to descriptions.

Wendy Flowers

Power BI Training

Financial and Business Analyst

First Light

United States

This Power BI course was an exceptional training experience. It was very fast paced -- necessary as it covered Power Pivots, Power Query, DAX, Power BI, and SQL. Sami ran the training smoothly, staying after to help individuals with questions. We also are able to schedule up to two 30-minute sessions after the end of the course for guidance on projects. Highly recommend!

Tim Palacios

Power BI Training

WFM Data Developer

World Connection

United States

This course was excellent. Sami had a systematic approach to the course curriculum which I feel is key to most if not all learning experiences. He was very knowledgeable and very quick with troubleshooting and helping support when we had any challenges with our work. I am definitely going to take more courses from Excelgoodies. If you are an IT professional or require Data Analytics for your job, this is a great course and I highly recommend it. I think my only critique is that I would have like to have more time devoted to the database side of the class, but that is just a personal preference.

Learner stories around
the world

Total Reviews

4583

Overall growth till now

Average Rating

4.9

Average rating this year

Graph

2.0K

1.0K

500

200

Star Rating

Trainer's Expertise

Course Structure

Course Content

Assignments

Certification Process

Scott A. Reifsynder

Power BI Training

Chief Financial Officer

Nexterus

United States

Wendy Flowers

Power BI Training

Financial and Business Analyst

First Light

United States

Tim Palacios

Power BI Training

WFM Data Developer

World Connection

United States

Esteemed Clientele

BI Reporting Consulting for Project Management Data Professionals or PMO.

Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam

Why hire more staff when you can achieve
300% growth with your existing workforce?

From improving project timelines to enhancing resource allocation, we'll assist you in leveraging your project management data to make data-driven decisions and maximize operational efficiency.

Our experienced consultants specialize in data warehousing, modeling, advanced analytics, and workflow optimization, allowing you to optimize your resources and drive significant growth while reducing operational costs and improving your bottom line.

Our team of project management data experts will work closely with your PMO to understand your unique needs and develop customized solutions that align with your organizational goals and objectives.