What email address or phone number would you like to use to sign in to Docs.com?
If you already have an account that you use with Office or other Microsoft services, enter it here.
Or sign in with:
Signing in allows you to download and like content, and it provides the authors analytical data about your interactions with their content.
Embed code for: Power BI Workshop
Select a size
Open source course for Power BI Desktop. Accompanying files can be found on https://github.com/KoenVerbeeck/PowerBI-Course.
Power BI Workshop
by Koen Verbeeck
Copyright © 2017 Koen Verbeeck
Permission is hereby granted, free of charge, to any person
obtaining a copy of this software and associated documentation
files (the "Software"), to deal in the Software without
restriction, including without limitation the rights to use,
copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the
Software is furnished to do so, subject to the following
The above copyright notice and this permission notice shall be
included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
OTHER DEALINGS IN THE SOFTWARE.
getting data into Desktop
explore data & other features
Power BI Desktop
1 – data analysis for your company
2 – multi-tenant app
3 – anonymous users on the web
e.g. Analysis Services cubes
e.g. SQL Server database
e.g. everything else
--> Power Query!
Power BI Desktop – different sources
Power BI Desktop – creating queries
Power BI Desktop – creating relationships
Power BI Desktop – creating visuals
Power BI Desktop – explore data
expression calculated for every row
calculated during processing
take up space in the model
expression calculated for current context
dynamic! (and easy reusable)
calculated during query time
doesn’t take up space in the model
another measure. Inside a measure!
evaluated for every row of the table
YTD, MTD, LASTDATE, CLOSINGBALANCEYEAR, PARALLELPERIOD…
needs a date table
continuous range of dates
column with date data type
most powerful function in DAX
can alter filter context
calculate(expression, filter1, filter2, …)
6 evaluation steps of DAX
detect Pivot coordinates (initial filter context)
CALCULATE alters filter context
apply filter context
filters follow the relationships
“current row” concept
easy to understand for calculated columns
no row context in measures
except with “iterator” functions
calculate can create filter context in calculated columns
SemiAverage = AVERAGEX(VALUES(Employee),SUM(Hours))
calculate in calc column
to the cloud!
Power BI Service
row level security
analyze in Excel
updates updates updates
Online book (blog)
Essentially means you need to keep this page in your slidedeck.
Server & Tools Business
© 2012 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.
Introduce yourself briefly. You can change this of course to your own details. I keep this slide in as an example.
The workshop focuses on Powr BI Desktop.
First a small intro, then we’ll go over the architecture of the whole thing and what you can use Power BI for.
We’ll talk mostly about Power BI Desktop : getting data in, modelling it and building visualisations on top of it. We’ll also discuss the DAX language and the Power BI service.
Ask the audience for their experience with Power BI. Demo only, or actual project experience?
This is the most complete architecture picture I could find. I’ll give you guys 10 minutes.... It’s already out of date though, I think Analyze in Excel is missing, as well as solution templates.
(go over this slide, but not too long)
(this slide is not mine, but made by Melissa Coates and falls under the Attribution-NonCommercial-NoDerivative Works 3.0 Common License)
Power BI Desktop is no longer a side-kick to Excel, but a fully fledged development environment. The Power BI v2 service has replaced the Office 365 version and its capabilities are growing each month. Updates cycles are insane. You can add custom visuals, integrate R code, have streaming data, embed Power BI or publish it anonymously to the web. RLS has been added and Q&A is brought to on-premise Tabular. The sky is the limit...
The most obvious use case – and the most frequent one – is when you use Power BI for self-service BI in your company. You (IT or the end user) create some reports and dashboards and they can consume those:
in the Power BI Service
or in Power BI Desktop (locally, in the future through SSRS and Pyramid Analytics)
or you can integrate it in your own website using REST APIs
In all cases, users need a Power BI license and sign in through Azure AD.
Or in other words: Power BI Embedded.
This is an Azure Service allowing you to embed Power BI visuals in your own application.
Azure Active Directory is not required, you can use app tokens.
For example, integrating Power BI in ERP/CRM systems. Not to be confused with the tile apps for Office or SharePoint.
You can publish Power BI reports to the web where they can be viewed by everyone. You can compare this with Tableau Public. Ideal for blog posts, web sites, ...
It only works with data models published to the cloud service, obviously.
Walk through the finished IMDB.pbix file and show off all the cool features.
Lotsa sources... If you import data, file size limit for free account is 1GB, paying is 10GB.
Some sources support more than one option.
If possible, connect live to a model.
If possible, add with DirectQuery to SQL Server and add a measure into the model.
Pull some data in from Excel and do some easy examples in Power Query. Load the data into the model.
Don’t forget to mention how PBI Desktop changes (the different sections you can access: report, model, diagram)
Very user friendly tool with lots of functionality build into the box. For advanded stuff, you can use the M language.
Demo Power Query. You can make this as easy or as complex as you want. You can start with reading simple Excel files, but you can also loop over files in a folder or use some M functions.
Definitely show the built-in transformations and how simple it is to modify/filter the data. Don’t forget to mention query folding and how it affects performance.
See 01 - Exercises - Load Data.docx.
The skeleton of your whole solution. This should be refined and be made user friendly.
One big limitation: you can only link one column to another. No multi-column relationships (you can do this in Power Query though). Also, there can only be one active relationship between two tables. The others need to be set with DAX.
If you use row level security, you need to apply the “apply security filter in both directions” checkbox. Be careful with cross filter direction, it can sometimes lead to unexpected results (if for example there are many tables/relationships and every relationship is bi-directional).
Assume referential integrity: if you use DirectQuery, the model will generate INNER JOINS instead of LEFT OUTER JOINS, which might speed up queries. The model assumes referential integrity is done in the DWH.
Don’t overdo bi-directional cross-filtering!
This slide explains the concept of many-2-many relationships and their bridge tables. The red arrows around the actors bridge table indicate the issue we had with Tabular before SQL Server 2016: the filters only flow in one direction. This means special DAX measures had to be constructed to solve the M2M problem. Now, with bi-directional cross filtering, the whole M2M problem is solved with a simple setting on a relationship.
To set up security:
Enable directquery cross filtering (even though it doesn’t seem to make sense)
Create your role
Don’t forget to apply the security filter.
Add users in service.
Calculated columns, simple example. The slide here is used to show you can enrich your data model, something that is not possible when using live connections. More on DAX later on.
explain all the options you have to enrich a model (columns, measures, synonyms, roles and groups). Most of these are explained later on. Don’t dive into DAX, that’s for later.
Create all those bridge tables! You can only show one, that should be enough :)
See 02 - Exercises - Modelling Data in Power BI Desktop.docx.
Halfway through the exercise, you should open a new .pbix file, that has all the queries and bridge tables done.
The reason for this is that it’s 4 times the same work – kind of tedious – but also that refreshing the data becomes quite long due to inefficient caching in Power Query and a relative slow API.
Finally creating us some data viz!
Power BI Desktop is an easy to use application allowing you to quickly create reports. It kind of replaces Power Query, Power Pivot and Power View. This means you have advanced data integration, transformation and modelling techniques available. Depending on the source, you can create sophisticated models using DAX (even better than in Power Pivot, since M2M is supported). Currenty this tool is meant as the first step in publishing your report to the cloud service, but over time this tool becomes more independent (--> in SQL Server vNext we can publish .pbix files to SSRS!)
Use this to set if a viz filters or highlights another viz.
Available on matrix and tables.
New functionality. Editing groups is not that straightforward.
Variant on grouping, but on measures. Ideal for creating histograms.
Quite new in Power BI Desktop. Will probably be refined in the coming months.
(note that the IMDB demo doesn’t have a forecasting graph. The reason is an error that is thrown by the visual: not enough data points to create a forecast).
Forecast is quite rudimentary on this screenshot. Only seems to work with continuous dates and no NULL values. Doesn’t work on all sources.
(separate demo, not in IMDB but on AdventureWorks cube (imported data))
If the built-in visuals don’t satisfy you, there’s a wide array of custom visuals (open source) that you can download and use. They are not supported everywhere in the lifecycle though.
If that’s not enough, you can also use R code in PBD. You can either use it as a source of data, or you can use it to create even more types of advanced visualizations.
Templates are useful to give someone else a starting point for their own analyses.
When they open the template, data has to be loaded (which means RLS security can kick in).
You can also define parameters that need to be filled in at startup.
See 03 - Exercises - Explore Data in Power BI Desktop.docx.
Explain that it’s basically Excel formulas, only better. Instead of referring to cells, you refer to column names or measures.
This workshop is a Power BI workshop, intended for people with limited coding skills. Thus, the DAX intro is really basic and just shows some capabilities. For full-on DAX, contact Marco Russo and Alberto Ferrari :D
Only keep columns that are absolutely necessary, drop others.
If you can choose between measure or calculated column, go for the measure.
They are the real benefit of using PowerPivot / Power BI over regular Excel.
No naked column references. You can reference other measures though.
(inception!) Or. Yo dawg, I heard you like measures, so I built you a measure that’s inside another measure!
A decent date table is needed to do time intelligence. Remark that you cannot mark a table as date table, like you can in PowerPivot. I believe the consequence is that your relationship column needs to be an actual date, not a surrogate key.
A date dimension can be generate using DAX, or in Power Query.
Determines how every cell is calculated.
The queen of all DAX functions. Very important.
Note that the result is still influenced by slicers.
The golden rules
Transfer row context to filter context.
Hopefully expanded in the near future.
Optional part of the workshop. No exercises are made for this part.
Once you’re done in PBI Desktop, you can publish your reports to the cloud service. Or pyramid analytics since very recently, but I’m not discussing that in this session.
In the future SSRS will also be possible.
The Power BI Service allows you to:
share your reports from Desktop
either directly, or through content packs
but also Excel reports and SSRS content
enhance models with RLS
create and edit reports
create dashboards (service feature only)
set up data refresh
online or through gateways
organize content through groups
Doesn’t work on all sources (yet?)
Android tables have just been added.
Extra: resource materials for learning Power BI, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
Once you’re done in PBI Desktop, you can publish your reports to the cloud service. Or pyramid analytics since very recently, but I’m not discussin