power-bi-complete-guide-slides.pdf

PowerBI guidelines

1. Power BI
The Complete Guide

2. Power BI Desktop
WhattheDesktop applicationisperfectfor

3. Workflow ofPower BI Desktop
Power BI Desktop
Query
Editor
Data
View
Report
View
Data
preparation
Data modelling
Data
visualization
Relationship
View

4. The Query Editor
Howweimportandprepareourdata

5. Power BI Desktop –Query Editor
Power BI Desktop
Query
Editor
Data
View
Report
View
Relationship
View
Data
preparation
Data modelling
Data
visualization

6. The Star Schema
FACT TABLE DIM TABLEVS

7. The Star Schema
Sales
Customers
SalesPointTime
•IdentifierCust
•FirstName
•SecondName
•Age
•Gender
•IdentifierGeo
•Continent
•Country
•City
•IdentifierProd
•IdentifierDate
•IdentifierCust
•IdentifierGeo
•UnitsSold
•TotalSales
•TotalCost
Products
•IdentifierProd
•ProductType
•PricePerUnit
•CostperUnit
•IdentifierDate
•Year
•Quarter
•Month
•Week
•Day
DIM TABLE DIM TABLE
FACT TABLE

8. OurProject –Currentstructure
Population-Combined
•Country-ID
•Country
•Year
•AgeGroup
•Gender
•Population

9. Out Project turnedintoa Star Schema
Population
Age
•AgeGroup-ID
•AgeGroup
•Category
•Country-ID
•AgeGroup-ID
•Year
•Gender
•Population
Region
•Country-ID
•Country
•Region
DIM TABLE DIM TABLE
FACT TABLE

10. Query: Duplicatevs. Reference
Source
file
Query Editor
Query 2
(DuplicateofQuery 1)
Query 2
(Reference toQuery 1)
A
B
C
Query 1
(Createdin Query Editor)
A
B
A
B

11. MergeQueries-JoinKind
Outer
Inner
Anti
IDSales
A10
B50
C20
Query 1
LEFT
Query 2
RIGHT
IDSales Region
A 10 USA
B 50 n/a
C 20 Asia
IDRegion Sales
A USA 10
BBEurope n/a
C Asia 20
IDSales Region
A 10 USA
B 50 n/a
C 20 Asia
BBn/a Europe
IDSales Region
B 50 n/a
IDRegion Sales
BBEurope n/a
IDSales Region
A 10 USA
C 20 Asia
LEFT RIGHT FULL
IDRegion
A USA
BBEurope
C Asia
Separate Queries
MergedQueries

12. Import dataintothedatamodel
Data preparation
Query Editor
Data model
Data View/Report View
Source files
Data preparation
Query Editor
Data model
Data View/Report View
Import data
Query 1
Query 2 Default =
Enableloadis
setforall
queries
Import data
Query 1
Query 2
Enableloadis
onlyselected
forQuery 1
Query 1 &
Query 2 are
loadedintothe
datamodel
Query 1 is
loadedintothe
datamodel

13. Data View & Relationships
Howwemodelourdata

14. Power BI Desktop –Data Model
Power BI Desktop
Query
Editor
Data
View
Report
View
Relationship
View
Data
preparation
Data modelling
Data
visualization

15. Query Editor vs. Data Model
Query Editor Data Model
Connect tosourcefiles
Clean data
Shape data
Structure+ preparedata
Add relationships
Add calculatedcolumns
Add measures
Analyse data

16. Power BI Desktop –Data Model
Power BI Desktop
Query
Editor
Data
View
Report
View
Relationship
View
Data
preparation
Data modelling
Data
visualization

17. Let‘sbring ourData Model tolive
Cardinality Cross Filter Direction ActiveProperties
= „Type ofrelationship“

18. Onetomany(1:*) & Many toone(*:1)
Customers Orders
ID-Customer FirstName SecondName
1 Maximilian Schwarzmueller
2 John Meyer
3 Linda Belle
4 Manuel Lorenz
ID-Order OrderDate ID-Customer
A 01 Jan 2017 1
B 08 Jan 2017 2
C 15 Jan 2017 1
D 25 Jan 2017 1
E 05 Feb 2017 3
F 15 Feb 2017 4
Eachcustomerisunique Eachcustomercanhave
multiple orders

19. Onetoone(1:1)
Passport Person
ID-Passport ValidIssuedFirstNameSecondName Country
1 20252005 MaximilianSchwarzmuellerGermany
2 20191999 John Meyer USA
3 20171997 Linda Belle Japan
ID-Passport FirstNameSecond Name Country
1 MaximilianSchwarzmuellerGermany
2 John Meyer USA
3 Linda Belle Japan
ID-Passport ValidIssued
1 20252005
2 20191999
3 20171997

20. Power BI Desktop –Data Model
Power BI Desktop
Query
Editor
Data
View
Report
View
Relationship
View
Data
preparation
Data modelling
Data
visualization

21. Onetool-Twolanguages
M-Language
DAX-Language
Power Query FormulaLanguage
Data Analysis Expression Language
Description Applicationareas
Independent from
eachother
Prepareyourdatabeforeyouload
themintothedatamodel
Create formulasforan in-depth
analysisin theData View
Data transformation
Analytical datacalculation
ComparabletoExcel functions

22. Course interimconclusion
M DAXOR
This course

23. CalculatedColumns vs. Measures
Return a singleresultofa calculationoran aggregatedvalue(e.g. Averages)
Perform an operationthatgeneratesresultsforeachrowofyourtableCalculatedColumn
Measure

24. Report View
Let‘screatebeautifulchartsandtables

25. Power BI Desktop –Report View
Power BI Desktop
Query
Editor
Data
View
Report
View
Relationship
View
Data
preparation
Data modelling
Data
visualization

26. Power BI Service & Power BI Mobile
Wefinishedourworklocally, whatnow?

27. Waystocontinue
Power BI Desktop
Power BI Service
Share
YOU
Publish
ITYOU
Collaborate
Marketing
Power BI
Service
Power BI
Mobile
-
-
OrganizationSingle User
YOU
Power BI Desktop
STOP Publish-
Power BI Service
Access-
Power BI
Mobile
YOU
YOU

28. Questions tobeanswered
HowcanwepublishourdatatoPower BI Service?
Howcanwecollaboratein Power BI Service?
Howcanwesharedataandspecifywhatwewanttoshare?

29. Changesin 2017
Power BI Free Power BI Pro
Power BI Premium
Large ScaleBI
deployments
Personal users Collaboration
Until
31 May
01 June
2017
Functionalalignmentwithremainingdifferencesin
sharingandcollaboration
•Access to all Pro
Databases
•IncreasedWorkspace
Storage
•Improvedrefresh-
rates
+

30. Publishing ourprojectdatatoPower BI Service
Power BI Desktop
Dataset & Report
Yourcomputer
Server
Publish/
Connect to
File
Personal
Gateway
Power BI Service
On-Premises
Gateway
Power BI Service

31. Collaboration
Power BI Service
Create Dashboards
Create Dashboards
YOU
ITYOU
App Workspace
Dataset & Report from
Power BI Desktop

32. HowcanweshareourresultsfromtheApp workspace?
Power BI Service
Dashboard, Report &
Dataset
Dashboard
Report
Report
PROData createdusingProfeatures, canonlybesharedwithPower BI Pro Users!
PublishApp
PublishtoWeb
Dataset