BI Tools
Dr. Akhter Raza
Tableau
Dr Akhter Raza
Informatica
Dr Akhter Raza
Informatica
Dr Akhter Raza
MSBI
Dr Akhter Raza
MSBI
Dr Akhter Raza
IBM Cognos
Dr Akhter Raza
Power BI
Dr Akhter Raza
MSBI
Dr Akhter Raza
Power BI Tools and Building Blocks?
Dr Akhter Raza
Course Elements
Dr Akhter Raza
Workflow of power BI Desktop
Dr Akhter Raza
Power BI?
Dr Akhter Raza
What is Power BI?
• New Microsoft Business Intelligence product line
• Aimed at business power users and end-users
• Rich interactive reports, dashboards, charts, graphs, and maps
• Minimize clicks to complete tasks
• Open source/custom visualizations
• Remove Office 365 & SharePoint Online Requirements
History of Power BI
• PowerPivot – Excel add-in
• SQL Server 2012 SSAS Tabular Model
• Microsoft BI Semantic Model (BISM)
• PowerView
• Re-branding to Power BI
• SharePoint and Office 365 integration
• Power BI (December 2014)
• Power BI Service & Desktop GA (July 2015)
What is Power BI Desktop?
According to Microsoft:
Power BI Desktop puts visual analytics at your fingertips
with intuitive report authoring. Drag-and-drop to place
content exactly where you want it on the flexible and fluid
canvas. Quickly discover patterns as you explore a single
unified view of linked, interactive visualizations.
Source: https://powerbi.microsoft.com/en-us/desktop
What is Power BI Desktop?
• Standalone design/development/rendering app with
deployment to Power BI Service
• Currently integrates:
• Power Query
• Power Pivot
• Power View
• New Power BI Desktop file extension .pbix
What is Power BI?
• PowerBI.com & Power BI Desktop
• Access on-prem sources:
SQL Oracle Teradata
MySQL PostgreSQL Sybase
Analytics Platform Service (APS, includes PDW)
IBM DB2 HDFS And more!
• Online data sources – Azure
• Built-in connectors for SaaS:
Salesforce Google Analytics GitHub
Zendesk SAP And more!
Two versions
PowerBI.com
Power BI Desktop
Introduction to Power BI
Ch. 1
Dr Akhter Raza
what exactly PBI Do?
1. Import data
2. Model data
3. Create reports and dashboards
4. Publish Reports
Dr Akhter Raza 21
1. ETL—Extract transform and load
• Import data from a wide variety of sources. This covers
corporate databases to desktop files, social media to big
data.
• Merge data from multiple sources and shape it into a
coherent structure.
• Cleanse your data to make it reliable and easy to use.
• Break down the data into the rows and columns that suit
your requirements.
Dr Akhter Raza 22
2. Modeling Your Data
• Create a data model by joining tables to develop a coherent data
structure from multiple separate sources of data. This data model is
then used in dashboards.
• Enrich the data model by applying coherent names and data types.
• Create calculations and prepare the core metrics that you want to
use in your analyses and presentations.
Dr Akhter Raza 23
3. Creating Reports and Dashboards
• Tables
• Matrices
• Charts
• Maps
• Gauges
• Text and Images
• KPIs
Users can “slice and dice” the data “on the fly” in real
time using • Slicers and • Filters 24
Dr Akhter Raza
Power BI Desktop Files
Power BI Desktop lets you create multiple pages in a single file.
Each collection of pages that is based on the same underlying
data is called a report.
A Power BI Desktop file contains all the dashboards and all the
data that is needed by each element (called a visualization) on
each page. So, a Power BI Desktop file is completely self-
contained.
Dr Akhter Raza 25
The Power BI Universe
• PowerBI.Com: An Azure-based service where you can create
and share data and dashboards.
• Power BI Desktop: Core tool use to create reports. This can
involve connecting to multiple data sources and modelling
and cleansing the source data.
• Power BI Report Server: An on-premises server that allows
you to distribute and deliver reports inside a corporate
firewall. Dr Akhter Raza 26
The Power BI Universe
• Power BI Mobile Apps: allow you to view and interact with
Power BI reports and dashboards on Windows, iOS, and
Android devices.
• Power BI Apps: a method of collecting and deploying
purpose built dashboards and reports for tailored groups of
users in the cloud.
• Power BI Embedded: An Azure service that enables
application developers to add interactive Power BI reports
into their own applications.
Dr Akhter Raza 27
The Power BI Universe
• Power BI Gateway: Allows you to connect the Azure-based
Power BI service to on-premises data sources and automate
data refresh.
• Third-party visuals: A collection of visual elements, often
created by third parties. You can add these visuals to your
Power BI reports and dashboards.
Dr Akhter Raza 28
Installing Power BI Desktop
Go to the Power BI Desktop download page on the Microsoft
web site.
https://www.microsoft.com/en-us/download/details.aspx?id=45331
Dr Akhter Raza 29
Power BI Desktop
Dr Akhter Raza 30
First PBI Desktop Dashboard
Dr Akhter Raza 31
First PBI Desktop Dashboard
Dr Akhter Raza 32
First Power BI Desktop Dashboard
1. Click Get Data in the startup screen and
load CarSales.Xlsx file and choose
BaseData worksheet
2. a sigma (Σ) indicates the field is numeric
Dr Akhter Raza 33
First Power BI Desktop Dashboard
3. In the Visualizations pane, click the matrix
icon
4. Leaving created matrix selected, click the
CountryName in the Fields list
Dr Akhter Raza 34
First Power BI Desktop Dashboard
5. Drag the ReportingYear field into the Visualizations pane over
the Columns fields area (this is called the field well)
6. click the check box to SalePrice in the Fields list. The aggregated
sale price for all vehicles sold by country and by year will appear
in the matrix.
7. Drag the corner handle of the matrix to resize it so that there is
no spare whitespace inside the matrix itself.
Dr Akhter Raza 35
First Dashboard
Dr Akhter Raza 36
Adding a Column Chart of DeliveryCharge
1. Click an empty area of the dashboard canvas to unselect any
visualizations
2. Drag the Model field onto an empty area of the dashboard
canvas it automatically creates a table displaying all the vehicle
models sold.
3. Drag the DeliveryCharge field onto the table just created. Power
BI Desktop will calculate the total DeliveryCharge for each
available make
Dr Akhter Raza 37
Adding a Column Chart of DeliveryCharge
Dr Akhter Raza 38
Adding a Column Chart of DeliveryCharge
4. Leaving table selected, click the clustered column chart icon in
the Visualizations pane. Power BI Desktop will switch the table
to a chart.
5. Drag the corner handle of the chart to resize it
Dr Akhter Raza 39
Adding a Map of LaborCost by Country
1. Click any empty part of the dashboard
2. Click the map icon in the Visualizations pane
3. click the CountryName field this will display a map of the world
4. drag the LaborCost field onto the map. This will highlight any
countries where there are laborcosts relating to vehicles sold.
5. Drag the colored European countries to the center of the map
6. Zoom in to the colored European countries
Dr Akhter Raza 40
Adding a Map of LaborCost by Country
7. Hover the mouse over a country you will see the labor cost for
that country
Dr Akhter Raza 41
Adding Card Showing TotalCost of
SpareParts
1. Click the dashboard canvas to unselect any visualizations
2. Click the card icon in the Visualizations pane
3. Leaving the (slightly clunky) empty card
4. click SpareParts field. This displays the spare parts total in the
source data.
4. Drag the corner handle of the matrix to resize it
Dr Akhter Raza 42
Adding a Slicer by Make
1. Drag the Make field to a blank area on the dashboard canvas.
Power BI Desktop will create a list of vehicle models.
2. Click the slicer icon in the Visualizations pane2. Click the card
icon in the Visualizations pane
3. test the slicer by selecting—or deselecting—any car model that
is listed in the slicer. The other visualizations on the dashboard
will instantly be updated to reflect the choice of models.
4. This slicer can be used to filter data
Dr Akhter Raza
43
Final look of dashboard
1. Arranging the Dashboard
2. Moving a Visualization
3. Resizing a Visualization
4. Check the Interactivity in Dashboards
Dr Akhter Raza
44
Final look of dashboard
Dr Akhter Raza
45
Formatting Reports
1. Power BI Desktop allows you to create
reports with multiple visuals in record
2. Select an existing visual
3. Click the “paint roller” icon
4. Click Border and click the color palette
and select a color for the border
5. Click the color palette and select a color
for the background color
Dr Akhter Raza
46
Formatting Reports
6. Change the title text to Delivery Charges
7. Click table and Expand the Grid section of the Format Pane
Switch on the vertical and horizontal grids
8. Expand the Data colors section of the Format Pane and change
table text color
Dr Akhter Raza
47
Creating and Modifying Reports
1. Power BI Desktop makes it easy to add, copy, and delete the
pages in your original file so that you can create complex data
reports
48 Dr Akhter Raza
Creating and Modifying Reports
1. You should try Adding Pages.
2. Renaming Pages
3. Deleting Pages
4. Moving Pages
5. Duplicating Pages
6. Scrolling Through Collections of Pages
49 Dr Akhter Raza
End of Ch. 1
Extracting data from files
Ch. 2
Dr Akhter Raza
Data Sources
Fetching data from different variety of sources
• File: Includes Excel files, CSV (comma-separated values) files, text
files, JSON files, and XML files. Power BI Desktop can even load
entire folders full of files.
• Database: A comprehensive collection of relational databases that are
currently in the workplace and in the cloud, including MS Access,
SQL Server, and Oracle and many many more
Dr Akhter Raza 52
Data Sources
• Azure: This option lets you see an immense range of data types that is
hosted in the Microsoft Cloud. This covers data formats from SQL
Server through to big data sources.
• Online services: These sources range from SharePoint lists to
SalesForce, Dynamics 365 to Facebook—and many, many
others.
• Other: A considerable and ever-growing range of data sources, from
Facebook to Microsoft Exchange.
Dr Akhter Raza 53
File Sources
Dr Akhter Raza 54
A CSV File
• Comma separated values
• May contain header row which actually have column name
• Have a .csv extension
• Use a comma to separate the elements in a row. This, too, is a
default that can be overridden by selecting a delimiter
• End with a line feed, carriage return
Dr Akhter Raza 55
Extracting from CSV Files
• Open Power BI Desktop and close the splash screen
• In Home ribbon, click the Get Data button
• Click File on the left
• Click Text/CSV on the right of the dialog
• Click Connect. The Open dialog will appear
• Navigate to the folder containing the file and choose Countries.csv
• Click Open
Dr Akhter Raza 56
Extracting from CSV Files
• Click the Edit button. The Power BI Desktop Query window
appears
• First 200 rows
• Entire data set
• Do not detect data type
• Close the query editor window
Dr Akhter Raza 57
Text Files
• Can have something other than a comma to separate the
elements in a list. Delimiter can be specified at loading
• Should normally have the extension .txt
• must be perfectly formed; that is, every record (row) must
have the same number of elements as every other record
58
Dr Akhter Raza
Text Files
• Must not contain anything other than the dataset if you want a
flawless data load
• If encounters difficulties, it should import the data as a single
column that we can then try and split up into multiple columns
59
Dr Akhter Raza
Opening Text Files in Power BI
• Click Get Data ➤ Text/CSV. The Open dialog will be
displayed
• Navigate to the folder containing the file CountryList.txt
• Click Open double-click the file name rather than click Open.
• Click the Cancel button because we do not really need it
60
Dr Akhter Raza
File Delimiters Data Type Detection
• Colon Read the first 200 rows
• Comma Read the entire file
• Equals sign No data type detection
• Semicolon
• Space
• Tab character
61
Dr Akhter Raza
XML Files
• XML, or Extensible Markup Language
• Click the small triangle on the Get Data button, and then click
More in the menu that appears. Select File and XML.
• Click Connect. The Open dialog will appear.
• Navigate to the folder containing the file and ColoursTable.xml
• Click Open. The Navigator dialog will open.
• Click the Colours dataset The contents of XML file will be
displayed
Dr Akhter Raza 62
XML Files
• Click the check box to the left of the Colors dataset on the left.
The Load and Edit buttons will be enabled.
• Click the Edit button. The Power BI Desktop Data window will
display the contents of the XML file.
• Click the Close and Apply button in the Power BI Desktop
Data window. You will see that the Colors dataset appears in
the Fields list on the right of the screen
Dr Akhter Raza 63
Importing Excel file
• Open a new, blank Power BI Desktop file.
• In the File menu, select Import ➤ Excel Workbook
• Select an existing Excel file containing Power View or Power
Pivot items. Select CarSalesForPowerBI.xlsx
• Click Open
• Power BI Desktop will import any compatible items and
display the import screen Click Start.
• Power BI Desktop will begin to load and convert data and
elements from Excel. Dr Akhter Raza 64
Importing Excel file
• Once the import process has successfully finished, Power BI
Desktop will display the summary dialog
• Click Close. The items become a Power BI Desktop report
Dr Akhter Raza 65
Importing Excel file
Dr Akhter Raza 66
Microsoft Access Databases
• In the Power BI Desktop ribbon, click Get Data ➤ More ➤
Database and select Access Database in the Get Data dialog.
• Click Connect ClientsDatabase.accdb
• Select the Access file and click OK. The Navigator dialog
appears; it lists all the
• tables and queries in the Access database.
• Select ClientList dataset
Dr Akhter Raza 67
Microsoft Access Databases
• Click Load. The Power BI Desktop window opens and displays
the table in the Fields list in the Report window
Dr Akhter Raza 68
Error to connect 32 or 64 bit version
Dr Akhter Raza 69
Connecting to JSON file
• JavaScript Object Notation
• In the Power BI Desktop ribbon, click Get Data ➤ File and
select JSON
• Click Connect and navigate to the folder containing the JSON
file Colors.json
• Click Open
• Click the Apply Changes button at the top of the Power BI
Desktop window
Dr Akhter Raza 70
Loading Multiple Files from a Directory
• Open a new Power BI Desktop file
• Click Get Data ➤ File, and select
• Click Connect. The Folder dialog will appear
• Click the Browse and navigate to folder MultipleIdenticalFiles
• Click OK
• Click Combine ➤ Combine and Load
• Select one of the source files to serve as a model for the
structure of all the files to load
• Click OK Dr Akhter Raza 71
Loading Multiple Files from a Directory
72
Dr Akhter Raza
Loading the Contents of a Folder
• Open a new Power BI Desktop file.
• Click Get Data ➤ File, and select Folder
from the options on the right of the dialog.
• Click Connect. The Folder dialog will appear.
• Navigate to the folder containing several identically structured
text files MultipleIdenticalFiles
• Click OK. The contents of the folder will be displayed.
• Click Load. A series of predefined fields will appear
Dr Akhter Raza 73
Adding Your Own Data
• Click Enter Data. The Create Table dialog will appear.
• Click the asterisk to the right of Column1 to add a column.
• Enter the data that you need.
• Enter a name for the table
• Click Load to load the data
Dr Akhter Raza 74
End of Ch. 2
Extracting Data from Databases &
Data Warehouses
Ch. 3
Dr Akhter Raza
Relational Database vendors
SQL Server Access database
SQL SSAS database Oracle database
IBM DB2 database IBM Informix database
IBM Netezza MySQL database
Sybase database Teradata database
SAP HANA database
Dr Akhter Raza 77
Connecting with SQL Server
1. Open a new PBI application
2. Click Get Data button SQL Server
3. Enter Server name
4. Enter atabase name BikeStores
5. Click the Import button
6. Click check boxes for selecting
tables to load
7. Press load button
Dr Akhter Raza 78
Sel
ect
the
dat
ab
ase
tab
les
Dr Akhter Raza 79
Database Connection Options
Dr Akhter Raza 80
Connect to SQL server using a SQL
Statement
1. Get Data SQL Server
2. Server name DESKTOP-T94NDVF
& database name BikeStores
3. Enter the SQL command that you
want to enter in this case
SELECT * FROM sales.orders
4. Click OK
Dr Akhter Raza 81
Conn
ect to
SQL
server
using
a
SQL
State
ment
Dr Akhter Raza 82
Extracting from CSV Files
• Click the Edit button. The Power BI Desktop Query window
appears
• First 200 rows
• Entire data set
• Do not detect data type
• Close the query editor window
Dr Akhter Raza 83
End of Ch. 3
Using Data by
Direct Query and Connect Live
Ch. 4
Dr Akhter Raza
What is DirectQuery or ConnectDirect
Connect directly to the data source and avoid having to download the
data. This technique is called DirectQuery
Dr Akhter Raza 86
How DirectQuery different from data load
• Don't load data into PBI. Instead, use it directly from database server
• As we don't load a copy of the data therefore we can‘t work offline
• Connection to source database is usually extremely fast
• Data is fetched specifically when required on new visual
• It do not need to refresh data source if ever the data is updated in source
database or data warehouse
• All the data is available from source database or DW
• Data is refreshed every time you apply a slicer or a filter
• At a time only one DirectQuery can be used to connect to database or DW
Dr Akhter Raza 87
Microsoft SQL Server Data
1. Open a new PBI Desktop application
2. Get Data SQL Server click the small triangle at the bottom of dialog
3. Enter server name used by your organization DESKTOP-T94NDVF
4. Enter the database name BikeStores
5. Select the DirectQuery button
6. If you keep Sql Statement box blank then all tables are linked otherwise the
tables specified in the query are associated
SELECT * FROM sales.orders
7. Click load or Transform
Dr Akhter Raza 88
DirectCo
nnect
with
Microsof
t SQL
Server
Data
Dr Akhter Raza 89
DirectCo
nnect
with
Microsof
t SQL
Server
Data
Dr Akhter Raza 90
End of Ch. 4
Loading Data from Cloud
Ch. 5
Dr Akhter Raza
Creating an account on AZURE for free
1. Click on the link below
https
://www.google.com/aclk?sa=l&ai=DChcSEwjEg-TJrK_lAhVFh9UKHb
Q9D4AYABAAGgJ3cw&sig=AOD64_0EaAvQtVinab4RbMf8nQ0iUR
MzWA&q=&ved=2ahUKEwioltzJrK_lAhUGUhoKHb9lC4IQ0Qx6BA
gQEAE&adurl
=
2. Click Start for Free
Dr Akhter Raza 93
Creating an account on AZURE for free
Dr Akhter Raza 94
AZURE free Services
Dr Akhter Raza 95
Connect to AZURE
database (DirectQuery)
1. Open a new Power BI Desktop application.
2. Click on Get Data button
3. Click Azure Azure SQL Database
4. Click Connect (an Azure SQL database is a
SQL Server database—but in the cloud)
5. Click Direct Query
6. Enter Azure SQL Database server name. The
SQL Server Database dialog will look like as
shown on next slide
7. Select desired tables and press Load
Dr Akhter Raza 96
Connect to AZURE
database (DirectQuery)
Dr Akhter Raza 97
End of Ch. 5
