The Power BI Summit is a multi-day conference, the largest of its kind, run virtually across 5 days. It brings together the best and brightest including Microsoft’s Power BI team’s product group, community experts and MVPs.
With over 100 sessions and presenters, a huge range of topics were covered across technical, analytical, and business perspectives.
Below are some favourites from our consultants, Teresa Pankhurst and Emma Girvan:
- Empowering everyone to get started with Power BI
- Power BI Embedded
- Using What-if Parameters for Scenario Planning
- Must Have Custom Visuals
- Hybrid Tables
- Top Tips and Tricks
Empowering everyone to get started with Power BI
This session covered some very quick and simple ways to introduce new users to Power BI. My favourite was the Quick Creation feature (in Preview)…
By using the Quick Creation feature in Power BI Service, a user can create a report with charts and analysis with just a few clicks, without needing any prior knowledge or skills in reporting building or coding – all without even downloading PBI Desktop!
The Quick Creation feature is as simple as the user pasting data from their clipboard (or entering it in). From there Power BI will create a data model; after analysing the data to determine which columns work well together and will produce the best insights, Power BI will create a Quick Summary (see below). The Quick Summary is a pre-built report that you can either use for analysis, share as is or refine to meet your requirements.
Visit this site for more information.
Power BI Embedded
This session covered the end-to-end considerations of the when, why, what, and how of choosing to use Power BI Embedded, including licencing and security.
So, what is Power BI Embedded?
Firstly, it’s important to know that Power BI Embedded is not an addition or extension of Power BI Service but is a distinct offering within Azure. Power BI Embedded is a good choice when you want to share reports with users who do not have PBI accounts (such as customers) and/or you want to embed a report in a custom application.
There are two forms of embedding – Embedding for Organisations, which is for use internally within an organisation, requires users to be authenticated by Power BI and requires a monthly subscription commitment; and Embedding for Customers, which is primarily aimed at ISVs (Independent Software Vendors), it doesn’t require user authentication with Power BI, but works with RLS (Row Level Security) and has more flexible licencing (it can be switched off – pay by the hour).
Want to know more? Microsoft have some great resources including a tutorial on how to set up Embedding for Customers step by step:
Using What-if Parameters for Scenario Planning
By Emma and Teresa
Have you ever been in the modelling section of Power BI and wondered what the What-if parameters section is?
What-if parameters allows users to interact with a report to change the value of measures. They are particularly useful in scenario planning, allowing you to see what happens if… for example, a what-if scenario may be that you want to see how many additional staff are required to increase production by x% or what the optimal price may be for maximising profit? This is where a well-built What-if parameter can bring great benefit for little effort.
When generating a What-if parameter, a new calculated table, which includes a column and measure, is generated. The What-if measure is then applied to model measures (cost, sales, etc) and a user adjusts the What-if visual which the model measures will respond. For example, a revenue modifier will increase/decrease the revenue and cost measures, whereas a cost modifier will only increase/decrease the cost measure.
You can also get creative when using two What-If parameters to analyse different scenarios. This example illustrates how, by using the Cost and Revenue What-If modifiers as your row and column labels in a matrix, in conjunction with a blank measure (eg. BlankMeasure = “”), you can apply conditional formatting to produce a Modifier Matrix that looks like a heat map. This is a great way to allow users to analyse several possible outcomes.
The above chart illustrates the Power BI matrix visualisation which incorporates two What-If parameters plus a blank measure, with conditional formatting applied.
The above image shows how to apply the conditional formatting for the desired result.
The above chart shows 8 different scenarios selected using Ctrl+Click with corresponding figures displayed in a supporting table. It is easy to see what the Effect and Markup is for different Revenue and Cost What-If modifiers.
HOT OFF THE PRESS!
Look out for a new Power BI Desktop parameter feature coming soon (in the next couple of months) which will make life much easier when it comes to creating dynamic report parameters. No longer will we need to use complex DAX functions to create selection measures manually.
Here’s a sneak preview of how it will work.
There will be a new option called ‘Fields’ under the New parameter option from the Modelling menu.
Here you can add a bunch of measures that you want to use in your slicer.
This will create a new table with a new measure in it which can then be used as the Values attribute on your visualisations and slicers.
So easy….and no DAX! (Well apart from the original measures you plan to use)
Must Have Custom Visuals
Users of Power BI will be familiar with the standard set of visualisations that come with Power BI and the large variety of custom visuals available from the Microsoft Marketplace. In this session, the presenters took us through two custom visuals that are actually custom visual designers and they allow you to build your own custom visualisations!
Charticulator is a Power BI Certified, low/no code custom visual which provides a canvas and tools for you to build bespoke charts according to your requirements. If you know what you want and can drag, drop, and click then Charticulator is for you. Charticulator is a supported Microsoft product, visit the website for inspiration and information charticulator.com
Deneb is also a Power BI Certified custom visual and although it uses the Vega or Vega-lite languages to create custom visuals, there is a great community of templates and assistance including example visuals with associated code which you can find here. It requires more than the drag, drop, and click skill set, so is perfect for users who are keen to dip into the coding side of custom visuals without needing the skills of a web developer. Having said that, the Vega-lite language is very simple and the supporting website has extensive documentation on the properties available, including graphics on what they do.
Hybrid tables combine the performance of VertiPaq in-memory caches with the capabilities of DirectQuery, allowing users to unlock massive datasets for real-time, interactive analysis. This feature is only available to Premium or Premium Per User customers and is currently in public preview with no date set for general availability.
This session walked and talked us through how the new hybrid table feature leverages the incremental refresh technology.
It utilises one table partitioned into segments, similar to how incremental refresh works, however the difference is the most recent segment uses Direct Query method while the other segments use Import method.
It is recommended as best practice that all dimension tables that join to a hybrid table are set to Dual storage mode so that Power BI can generate efficient native SQL queries when querying the DirectQuery partition, therefore minimising performance issues.
A table configured as Dual storage mode is both Import and DirectQuery, and this setting allows the Power BI service to determine the most efficient mode to use on a query-by-query basis.
There are some limitations to be aware of though.
- For high cardinality columns you may experience poor performance when using;
- Distinct Count
- Min/Max over string columns
- Analyse in Excel
- The function ApproximateDistinctCount is not supported
- A hybrid table cannot have aggregation tables and cannot be an aggregation table itself
- A hybrid table cannot have calculated columns
Read up on the Microsoft documentation site for more information on hybrid tables.
Top Tips and Tricks
With Power BI always on the move bringing us new tools, features, functionality and all things shiny, it’s sometimes easy to forget some of the more…let’s say….less glamorous aspects of our daily BI lives. Remembering to follow best practices and good design principles from the get-go will make your life so much easier further along the development cycle.
Here’s just a few tips and tricks that may not be new to you but may have disappeared from the forefront of your mind while you’ve been building your awesome BI solutions recently.
- When performing your data preparation tasks
- Setup configuration parameters (i.e. Source data file path).
Connection strings can be used in many places so if that were to change in the future, you would then have to repeat that change in all those places where it is referenced. Avoid rework and duplication at all costs.
- Remove extra columns that are not needed. They take up valuable space and can impact performance. Listen out for the “give me everything now and I’ll work out what I want later” phrase that we’ve all heard before.
- Set the correct column data types, particularly number of decimals if relevant
- If not needing more than 4 decimal points, use Fixed decimal number
- Use business friendly column names – it will save you needing to change them in the visual layer or at the very least help the report developer decide the correct attributes to use in the visualisations.
- Setup parameters for Test/Production data so you can easily switch between environments and help with an efficient development and deployment process
- Setup configuration parameters (i.e. Source data file path).
- Turn off the global setting for date/time. Power BI creates a hidden date table for every date field in your dataset whether you need it or not. Since you should always have a Date Dimension in your dataset, there should be no need to use the auto date/time feature.
- Turn off Auto Relationship in the Current File settings because Power BI will often create an incorrect relationship and trip you up.
- Avoid using the ‘Both’ setting for cross filtering (when creating relationships). You need to be sure why you need a cross filter to both tables and fully understand the risks and nuances with doing so.
- Data Model Design
- Add a Date Table and mark it as a date table (or time intelligence won’t work)
- Set column categorisation (Web URL, Geography, etc.)
- Change summarisation of columns that don’t require it (set columns to Don’t Summarise). For example, you will never want to add up a post code.
- Create hierarchies to make it more efficient to add several levels of a set of attributes (i.e. Geographical attributes such as country, state, city, address)
- Hide unwanted columns (i.e. Those used in a hierarchy). These are most often the key columns used in creating relationships but could also be the fields used in hierarchies.
- Visualisation Design
- Create a theme file either via the user interface or JSON file to apply a corporate or preferred style guide. There are some good websites around that can help you create a colour palette to suit.
- Use report templates (for consistent look & feel)
- Use explicit measures – avoid using columns from Fact table to perform aggregations
- Create folders for measures to easily group them up together. It makes it much easier to find them when you’re building out the visualisations.
- Hide visual headers for slicers/text boxes/buttons, etc. where the user does not need to use any of the visual header features.
- Avoid using visual level filters where possible to allow for full transparency across commonly used slicers (use filters in the measures where possible).