DATA ANALYTICS

TRAINING USING
MICROSOFT POWER
BI & SQL

Upscale Your BI Career With Easy To Learn

BI Tools.
© 2017-2023 The Tools. Private & Confidential.
 Power BI Training Outline

Power BI Desktop Power BI Services Power BI DAX Power BI Gateway

© 2017-2023 The Tools.

Understanding Fundamentals of BI

1. What is BI and the use case of BI?

2. What is managed and self service BI?
3. Comparison of Power BI with other Self service BI tools
4. Introduction to Data Warehouse

© 2017-2023 The Tools. 01

Power BI Architecture and Licensing

1. Power BI Architecture
2. Power BI Service Pro Accounts
3. Power BI Premium accounts
4. Office365 and Power BI Deal

© 2017-2023 The Tools. 02

Creating your First Power BI Desktop Report

1. Installation of Power BI Desktop

2. Data source connection
3. Connection to Databases and files
4. Dimensions and measure

© 2017-2023 The Tools. 03

Cleansing and Transformation Power Query Editor

1. Data quality and Profiling

2. Error rectification and error removal
3. Data quality Statistics
4. Removing rows and columns
5. Column split and column merge

04
6. Pivot the data
7. Moving the column position
© 2017-2023 The Tools.
Understanding Technical aspects of Visualizations

1. Scenarios to use BAR chart and Pie Chart

2. Scenarios to use line and area chart
3. Use of CARD, MULTIROW CARD and GUAGE
4. Understanding KPIs
5. KPI Power BI Visualization

05
6. Donut chart
7. Line and Area chart
© 2017-2023 The Tools.
Filters & Sorting

1. Visual level filter

2. Page level filter
3. Report level filter
4. Drill through filter
5. TOP N elements

06
6. Advanced Filters (Text matching filters)

© 2017-2023 The Tools.

Test Your Knowledge Series
Hands on Project 1

07
Understanding Power BI Service

1. Creating Power BI pro account with Business email address

2. Publishing the report from desktop
3. Sharing the report with end user
4. Custom workspaces
5. Managing access control of datasets

© 2017-2023 The Tools. 08

Data Modeling: Use of DAX for calculation

T
1. Calculated measure, column and tables
2. Difference between sum() and SUMX()
3. Relationship between the tables of same source
4. Relationship between the tables from different source systems

e Series: Project 2
5. One to one, one to many, many to one and many to many
relationships

09
6. Cross filter direction both
7. Active and inactive relationship
© 2017-2023 The Tools.
Test Your Knowledge Series
Hands on Project 2

10
Custom visualization

T
1. Importing the custom visualization from App Source
2. Importing the custom visualization from file
3. Import of Histogram, bullet chart and text filter

e Series: Project 2
© 2017-2023 The Tools. 11
Sharing the Power BI content and deployment

1. Sharing the content with write access through workspace

2. Export to PDF and Power point
3. Embed- Publish to web
4. Embed- secure embed in portal and application

© 2017-2023 The Tools. 12

Test Your Knowledge Series
Hands on Project 3

13
Working with Databases

T
1. Connecting with Oracle and SQL server
2. Import and Direct Query(Live Connection)
3. Fetching the data based on SQL statement

e Series: Project 2
4. Managing Relationship
5. Managing active and inactive relationships

© 2017-2023 The Tools. 14

Slicers

1. Difference between Slicers and Filters

2. Synchronization of slicers
3. Edit interaction in slicers
4. Use of slicers in dashboard
5. Limitation of slicers as in 2020 version as compare to Filters

15
6. Difference between Slicers edit interaction and Page level filter

© 2017-2023 The Tools.

Hierarchy and Drilldown

T
1. Date Hierarchy and drilldown
2. Creating custom hierarchy
3. Matrix Visualization

e Series: Project 2
4. Drill Down using Bar and Line chart
5. Applying filters in hierarchy
6. Difference between matrix visualization and Hierarchy

© 2017-2023 The Tools. 16

Advanced Visualization

1. Stacked column and Clustered column chart

2. Dual axis chart and mixed chart
3. Treemap and Funnel chart
4. Scatter plot
5. Donut chart

17
6. Waterfall chart
7. Gantt Chart

© 2017-2023 The Tools.

Working with Dashboards

T
1. Types of Dashboard
2. Creating dashboards on Power BI Service
3. Pin a visual or page to dashboard

e Series: Project 2
4. Adding Buttons and navigation
5. Adding transparent button
6. Image background in Dashboard
7. Bookmarks and actions

© 2017-2023 The Tools. 18

Test Your Knowledge Series
Hands on Project 4

19
Time Intelligence DAX Function
Year(), Quarter(), Month(), DATE(), CALENDER(), CALENDERAUTO(), FORMAT(),
TOTALYTD(), TOTALQTD(), TOTALMTD(), SAMEPERIODLASTYEAR(), DATEDIFF(), TODAY(),
NOW(), DATESBETWEEN(), WEEKDAY()

Aggregation DAX Function

COUNT(), COUNTA(), COUNTROWS(), COUNTBLANK(), COUNTX(), SUM(), AVERAGE(),
MAX(), MIN(), SUMX(), AVERAGEX(), MAXX(), MINX(), DISTINCTCOUNT()

Advanced DAX Function

20
RELATED(), CALCULATE(), IFFERROR(), FILTER(), SUMMERIZE(), ALL(), SELECTCOLUMNS(),
NATURALINNERJOIN(), NATURALLEFTOUTERJOIN(), UNION(), INTERSECT(), DISTINCT(),
VALUES(), FLOOR(), CEILING(), ROUND(), TRUNC(), IF(), userprincipal(), username()

© 2017-2023 The Tools.

Test Your Knowledge Series
Hands on Project 5

21
Data Combining Operations

T
1. Set Operations: Union, Union all, intersect, except
2. Append
3. Merge (Joins)

e Series: Project 2
4. Inner join, left outer join, right outer join, full outer join
5. Left anti join, right anti join

© 2017-2023 The Tools. 22

Aggregated Table and Ranks

1. Aggregated table (Group by Table)

2. Summarize DAX function
3. Rank and Dense_rank Examples
4. RankX DAX function

© 2017-2023 The Tools. 23

Advanced Concepts Part 1

T
1. Refresh Architecture
2. Gateway Installation and Configuration
3. Adding Data source to Gateway

e Series: Project 2
4. Scheduling the data refresh in Power BI service
5. Managing dynamic row level security

© 2017-2023 The Tools. 24

Advanced Concept Part 2

1. Features Added in 2020

2. Connecting to file from Share point
3. Connecting to file on one drive
4. AI – Quick Insight
5. NLP – Q and A visual
6. Apps
7. Importing apps from cloud applications

© 2017-2023 The Tools.

25
Basic SQL understanding

T
1. What is Oracle and SQL server
2. What is Oracle SQL developer and SSMS
3. Simple select statement

e Series: Project 2
4. Use of SQL in Power BI

© 2017-2023 The Tools. 26

SQL queries
1. Select queries
2. Aggregation and distinct
3. Group BY
4. Where
5. Joins
6. Arithmetic on numeric columns

27
7. Validation of Power Bi reports using SQL

© 2017-2023 The Tools.

Data Warehouse Introduction

T
1. Data flow in companies
2. What is Data warehouse
3. What is ETL
4. Difference between Data engineering and Data Analytics

e Series: Project 2
5. Star Schema
6. Snowflake Schema

28
7. Next steps in career as a BI developer
8. What is SSIS, SSAS and SSRS
© 2017-2023 The Tools.
Power BI Preview 2022

1. DAX NLP
2. Embed in Powerpoint
3. Directquery in pbi datasets

© 2017-2023 The Tools. 29

Test Your Knowledge Series
Hands on Project 6

On demand Projects and Interview

30
Preparation
Power BI Architecture

T
e Series: Project 2
© 2017-2023 The Tools. 31
Contact Us
+(91) 9607584765 / 9309044273 / 8767044745
Highly qualified and
experienced faculty at The
Tools, help candidates learn
and grow with complete
www.thetools.co.in
knowledge from both
theoretical and on-site
perspective. Our all round dual
contact@thetools.co.in
learning mode of classroom
and live project training goes a
long way in making our
candidates aware of the live
project environment in leading MUMBAI PUNE NAGPUR BANGALORE
organisations.
OUR PRESENCE
IN INDIA
THANK YOU

© 2017-2023 The Tools. Private & Confidential.