Power BI Summit is a multi-day event bringing speakers from the Microsoft Power BI team’s product group and community experts and MVPs from all around the world. This year’s virtual conference contained more than 100 sessions, covering all topics from Power Query, DAX, Dataflow, Visualization, Power BI Desktop, Power BI report server, Mobile experience, Embedded, Power BI Premium, Architecture, governance.
For those that did not get the chance to attend MDW, which is specialized in Power BI, have summarized a few interesting sessions for you.
Sessions covered in this article:
Govern your Power BI Environment with Azure Purview
Craig Bryden presented an overview of Azure Purview, the unified data governance service that helps you manage and govern your on-premises, multi-cloud and software-as-a-service (SaaS) data. He highlighted the importance of having a data catalog, as it allows:
Central repository for data assets
Data asset curation
Data asset discovery
Data lineage
Data quality
With this service, companies can:
Automate and manage metadata from hybrid sources.
Classify data using build-in and custom classifiers and Microsoft Information Protection sensitivity labels.
Label sensitive data consistently across SQL Server, Azure, Microsoft 365 and Power BI
Easily integrate all the data systems using Apache Atlas APIs.
Even though Power BI already has an integrated feature that allows you to visualize data lineage, Purview brings thigs to a next level:
Full end to end Lineage
Data asset discovery across workspaces
Asset curation beyond what is available in Power BI
Power BI Premium - Practical Tips for making the most of it
Power BI Dedicated Capacity requires some attention but doing so will result in the ability to do great things. During this session, Benni De Jagere touched base on some of the key activities’ organizations need to perform, to make sure their investment is one that pays off. He showed how using the Premium Capacity Metrics App as our base of operation, auditing, licensing, and some common dataset performance patterns, can make sure you're up to the task and using your premium capacity to its max!
Note that for this session Benni focused on Gen 1 (as it will still be for the next months, and most of it will be taken to gen 2).
What is Power BI Premium?
The Power BI Premium capacity is a dedicated capacity for your organization that provides a more complete feature set (some feature even release premium first, pro later (or not…). It allows for users to access artefacts without a Power BI subscription and it supports data residency by region (multi-geo) and customer-managed encryption keys for data at rest (BYOK). Note that it is not a shortcut to improve performance of your content in the workspaces.
Backend vs. Frontend v-cores:
Backend = everything that is related to refreshes, datasets and dataflows (regardless they are manual or triggered refreshes). Working with the actual data in the actual model
Frontend = resource that we use for rendering visuals, for getting query out of the data models, everything that is done for that interactive usage inside of power BI itself
RAM: working set of memory (e.g. P1 the highest point of memory we can use at one single point is 25 GB, for P2 50…). PBI Premium has the multimodal management (model management) where it balances the memory capacities itself, meaning that we can dump 50 GB of datasets into a P1 capacity and PBI will manage this for us.
Benni went through the Power BI Pro vs. Premium feature comparison list highlighting the features:
XMLA endpoint read/write connectivity
Application lifecycle management
Autoscale add-on availability (preview)
XMLA endpoint (the way for you to connect to the actual instance of AS that is working in the background and connect for example to Management Studio or any other tool that we may have). For this to work we need to have a READ WRITE enabled.
Tip: Enable Enhanced Dataflows Compute Engine (incredible difference in the performance when using dataflows and in the features that you can use). Make sure you limit the compute engine memory that this can use
You can check the complete list in the Microsoft official page, under the pricing section.
Here are the usable set of tips to assist daily tasks that he mentioned:
Check (and adjust) the capacity workload settings
Check the default capacity workload settingsà TIP: Do not overcommit the allocated resources
Questions to ask? Are all the workloads needed/used? Can we offload some workloads to other capacities?
How to set it up: Power BI Service > Settings > Admin portal
Question to ask when configuring the settings:
What is the current usage?
What is the potential growth that I may have?
How does this translates to the settings that I may need to have: Set the settings
Advice: Do not change the query timeout, max row counts… unless you have a very good reason to do so.
How do I know what is happening in my tenant? The only way right now is with the Premium Capacity Metrics Apps. Premium Capacity Metrics App v2 is your Ops base.
Good for Short term analysis
Cannot do real time connection
Not always as stable as we want to be
What to watch in your Capacity Metrics App
Memory evictions: eviction process: unload a dataset from a memory and load something else.
o There are perfectly normal, do not stress!
o Is there a threshold for when the evictions are too much? No, based on the models that you have, the usage that you have… but usually when the number goes to the high hundreds/thousands yes potentially you have an issue.
Hourly query distribution: What is happening with our queries? How long are they taking?
o We need to look at the distributions of our query.
o We want as max of the results to be either blue, black or orange, to see that we are not getting the long duration times.
Wait time distribution: How long does a query have to wait for other processes to finish before it gets its own going? What does the wait time distribution conclude?
o High wait times = busy reports on its own (prime candidates to be checked)
o What is the distribution for your wait time? As soon as they start diverging away from the blue, ALERT!
Benni is a firm believer in the value of Power BI Auditing. Here are the arguments he used to convince us of the power of this information.
Artefacts
Usage
User information
Licensing
Correlate these for insights into your capacity usage. Are users actively using their Pro Subscription? Are we having usage spikes at certain moments? Are we seeing behavior that need to be investigated?
Scheduled Refreshes
Check for congestions in the Capacity Refresh Summary [Admin portal > Capacity settings > Refresh summary]. Content Builders do not have access to this info. Extract refresh schedules and History and Build your report to provide them with this info so they know when they have a slot to set their own refresh.
Set up auditing to have a full overview of what is happening and correlate it to the usage
Activate large model support for model > 1GB
Offload non-production workloads to A Sku / PPU
Power BI Premium per User (PPU)
· Subscription model based on Premium Gen 2
· General availability on April 2021
· Aimed at smaller organizations, or offloading scenarios
Power BI Premium Gen 2
2 big changes apart from Premium per User: performance boost and scale capacity (autoscale needs to be enabled manually).
Metric Apps has changed a lot (improvement, allow us to do an in-depth analysis, but missing some features from the Gen1)
Autoscale needs to enable it manually. Ensure to set up consumption limits
Usage patters are still key, although the need is less pressing
Row-level security in Power BI
Reza Rad presented several ways of securing Power BI reports with simple examples that help better understand each case.
RLS (static): adding a Role with a static filter.
o For example, creating an ‘Australia Sales Manager’ Role with a Country = Australia filter and adding corresponding Salespersons to that role.
o Disadvantage: This can become difficult to manage if you have often new Roles to add; you will have to open the .pbix file each time you need to add new thigs
RLS (dynamic): add a Role with a dynamic filter, based on the user reading to the report. You can achieve that by using the USERPRINCIPALNAME DAX function.
o How can you implement that? By having a table containing users’ e-mails.
o For example, you have a ‘Sales Rep’ table with a list of employees and a ‘Sales Transaction’ one. Employees’ related sales records will be filtered using the Email field and the relationship between these 2 tables. So, if you implement this logic, your report’s data will be based on the current logged user who will see only his sales records.
Dynamic RLS Patterns
Manager access
Use a table with an indicator to check if your user is a manager or not
2. Implement a role based on that value
Hierarchy
1. Use a table describing the hierarchy by implement the PATH feature
2. Implement a role based on that value using the ‘PATHCONTAINS’ function
Users and profiles
If your company uses Profiles, you can implement security by managing the filtering options based on the relationships between the tables. This will need some DAX expressions to be implemented.
What’s new and what's next in Power BI embedded analytics
This session presented by Alon Baram and Amit Shuster showed an overview of Power BI embedded, a powerful tool that allows you to embed fully interactive reports and dashboards into your applications.
Its advantages include:
Easily embed with the JavaScript SDK
Ensure data privacy in single and multi-tenant deployments with row-level security
Monitor performance and automate with REST APIs
Provide the best user experience by scaling to meet demand
Alon demonstrated its power in a simple and intuitive demo.
Integrated to a company's application, it allows, besides the classic Power BI features, to create / edit a record directly within the app. This is an extremely powerful feature! No longer need to switch between apps, everything is in one place.
You can even directly qualify a lead within your report!
By clicking on a cell or button, you can easily access:
ML algorithm
Start a campaign
Open a dialog
Go to a different page / report
Finally, you also have the possibility to export a View in different extensions:
You have 2 options of integrating it:
This gives you choice in implementing the solution that will best fit your company and needs.
Amit presented the Power BI Embedded Analytics Playground. You can use it to learn and explore how to implement embedded analytics in your different apps.
You have access to a developer Sandbox, that allows you to get hands-on experience with the different APIs.
Don’t know where to start? Take a look at the 'Showcase' part that demonstrates what you can achieve with the proposed APIs.
Want to know more about Power BI embedded? check out the Learning Center. There you can dive into the documentation, learn about the different APIs, find developer samples and videos, and learn where to get help.
Amit also talked about the Power BI Embedded Generation 2 (preview), that brings:
1. Enhanced performance
2. Greater scale
3. Lower entry level for paginated reports and AI workloads
4. Scaling a resource instantly
5. Scaling without downtime
6. Improved metrics
Power BI Tips, Tricks & Hacks
Even if you already use Power BI in a day-to-day activity, maybe for a couple of years now, there are always some interesting tips that you can learn from others!
Rui Romano presented some useful features that will help you gain time and effort in deploying and implementing Power BI Solutions.
Bar code scanner integrated to the Power BI mobile app:
When you scan a product barcode with the scanner in the Power BI app on your device, you'll get a list of the reports that have barcode data. You can open the report you're looking for, automatically filtered to the information you need. This can be especially useful for inventory purpose.
Copy Report Layout
Have you already faced the following situation? You have a beautiful Power BI report, with some themes, filters, pages… And you would like to create another report, with the same elements. How can you accomplish that? Rui has a solution for you:
1. Go to your Power BI .pbix source file and unzip it (right-click -> use your computer’s default Extract tool).
2. Locate the 'Report folder' and copy entire content.
3. Unzip your destination .pbix file and go to the 'Report' folder.
4. Replace its content with the source's files.
5. Delete the 'SecurityBindinds' file.
6. Zip the folder back to a pbix file.
And that's it.
This will work even if your destination Datasource is not 100% same as the source one.
But how about copying a template from a report located on PBI services? Without downloading it
1. Create a blank Power BI report and save it to your local machine.
2. Unzip the .pbix file.
3. Locate the Report folder -> Layout file. Open it with a text editor, like Notepad.
4. Go to Power BI Service -> tap the F12 key on your keyboard.
5. Locate the Network tab and write ‘exploration’ in the search bar.
6. Refresh the page.
7. The ‘exploration’ file will be shown. You can copy the content from the “Response” tab and replace the one from your ‘Layout’ local file. Save file.
8. Delete the ‘SecurityBindings’ file.
9. Zip the file.
And that’s it!
Creating powerful Power BI Apps
1. Use emojis on pages' titles ✨. This can be fun! How?
o Create / modify an app from a Workspace.
o On the ‘Navigation’ pane, select the ‘Report Details’ / Name.
o Hit the Windows + semicolon keys on your keyboard. You will be presented a screen with emojis. Just pick one / several that you like.
o Publish your app and access it!
2. Embed a power app in the power bi application
Create a new ‘link’ item in the navigation pane of your app and specify the link for accessing the Power Apps application. Chose the “Open in Content area” to show the Power Apps app within the Power BI App. Publish / update.
3. By using the same ‘link’ feature, you can as well:
o Trigger a Refresh by implementing a Power Automate flow.
o Embed a power bi report from another workspace.
o Collect feedback from users by adding a “Microsoft Form” directly to your App.
A Low-code/No-code Analytics approach for Microsoft Power BI
Inforiver provides a template-based approach to rapidly build data stories the way you want, using a low-code / no-code approach right within Power BI.
Its user-friendly menu allows us to rapidly find the features we neeed.
Jay Anantharaman presented some interesting capabilities, like:
Formatting options that allow you to highlight important data and consequently help make better and faster decisions.
Reordering lines. This is an awesome feature as it avoids you to add an order column in your data set. Just drag and drop the lines and organize it the way you want!
Adding new rows, columns and measures to the integrated report. This gives you a wide range of capabilities in designing and personalizing your work.
Adding comments. A powerful feature that allows you to collaborate with others.
Power BI Governance-Tips and Trick
In this session, Gabi Münster & Oliver Engels walked through important settings you should know concerning Power BI Governance. This will help you monitor and maintain your landscape and help you retrieve control over your users and workload.
The presenters highlighted the difference between the Objectives of Administration vs the Objectives of Governance, as often people are confused when using these concepts.
When implementing Power BI solutions, it is important to find the right balance between the Self-service BI and Centralized BI. On one hand you will have Speed, Freedom, Creativity and Change, represented by the Business. And on the other hand – Rules, Control, Stability maintained by the IT team.
It is essential to put in place the right processes and tools to monitor what happens with your data in your company.
Gabi and Oliver also talked about securing report content by implementing RLS / OLS:
Building great data experiences in Power BI – tips from the product team
In this session Will Thompson, Sujata Narayana, Rien Hu from the product team made us understand the importance of building a high-quality data experience, shared 8 essentials to building great data experiences and showed how the Power BI features can help achieve this.
Let’s dive into the 8 essential steps they shared for building a great data experience (not in a particular order, all are as important) and the recommendations and/or tips for each and features that will help you achieve this.
Essential # 1: Think about your end-user’ needs
Get the end users more involved in the process and think of it in a more iterative way (have a good understand of what the end users care about).
Get feedback as soon as possible to minimize wasted effort and continue the feedback loop.
Essential # 2: Design the layout with the user in mind
Identify which is the subject or topic for each page on your report
Ensure that each page is clearly communicating that subject
Title and description on the top of the report and minimize clutter and distractions
Ensure consistency of the layout across all pages
How do you give importance to a visual? Give them more space, position them closer to the top left.
You do not need to be a designer to create a great report, help you get started or inspired from the demo files and Power BI free templates (PowerBI.Tips Layouts).
Essential # 3: Use the right charts for the right purpose
Common mistakes from users:
Comparison vs. composition use cases!
Use clustered column charts or stacked area charts to show multi serial trends overtime (use line chart)
Essential # 4: Allow interactivity and exploration
Features to help when it comes to report interactivity and data exploration:
Enable ad-hoc exploration with Personalized visual feature: to allow users explore the data on the fly and customize the report to suit their needs
o Top right of the visual […] > Personalize > choose different visual > change fields
o Save it as your personal Bookmark to save the new view
o You can undo all the changes to the point you started the change. Do not be afraid to play around!
Support your natural language queries with QA visuals
Allow for progressive disclosure by adding drill through pages to the report
Essential # 5: Help users go from insights to action
Upcoming feature* Build in a Power Automate flow in a button
Power automate gives you the flexibility to hook up with context from the report being sent through to a flow you have created to for example go and launch campaigns, write information into different data sources…
Essential #6: Accessibility
Configure your report to be interfaced with in multiple ways (Alt text, Tab order, visual sort order)
Label and differentiate data in accessible ways (color contrast, friendly color palette, data labels, markers, images and tooltips)
Consider user with disabilities such us low vision, colorblindness, blind users, motor disabilities, dyslexia, and other cognitive impairments
Essential #7: Mobile
Benefit from the mobile layout feature
Design for vertical/scrolling layout
Design for a touch interface
Essential #8: Performance
Something very important to the quality of interactivity of your reports the investment in performance optimizations. Performance is a function of volume
Reduce amount of data
Reduce number of visuals
Reduce interactions between visuals (by default all interactions activated)
DAX/ model/ query optimization
In summary, make it easy for the users
To interpret the data correctly and quickly
To explore the data and get personalized insights they are looking for
To act on those insights
Brilliant Power BI Report. Can I print it?
If you’re a BI specialist, you have probably been asked these 2 questions (maybe a lot of times):
How can I export it to Excel?
How can I print it?
In this session Laura Graham-Brown gave several ways of obtaining a printable copy of a report, that can be a PDF or a PowerPoint document.
For each solution, Benefits and Problems are also aborded, so you can choose the best option that suits your needs.
Export your report directly from Power BI Service
Benefits
o Self Service
Problems
o Slow
o Custom Uncertified Visuals
o Scrolling Tables
Subscribe
You have plenty of options to personalize your subscription: E-mails (you can add other users), specify frequency, message, add a link to the report or include a copy of the report.
You then receive the following e-mail:
Benefits
o Self Service
o Automated
Problems
o File hidden in an email
o Fixed email format
Implement a Power Automate flow
This flow allows you to directly save your report to a SharePoint folder.
Benefits
o Choose file location
o Self Service
Problems
o Screen Shot – No Scrolls
o Certified Visuals Only
o Power BI Premium Subscription required
Paginated reports
Designed to be printed or shared as they are formatted to fit well on a page. These reports display all the data in a table, even if the table spans multiple pages. They're also called pixel perfect because you can control their report page layout exactly.
Benefits
o Laid out to Print
Problems
o New tool to learn
o More technical than Power BI
Inside the VertiPaq Engine
The VertiPaq engine used by SQL Server Analysis Services Tabular (AS), Power BI (PBI), and Power Pivot, is a columnar database capable of incredible performances, both in speed and compression ratio. In this session, Marco Russo performed a deep dive in the internals of the database architecture, discovering how Vertipaq stores information, in order to gain better insights into the engine and understand the best way to model your data warehouse to leverage the features of VertiPaq.
This session was super interesting and with a lot of content, including the common and useful techniques to increase the compression ratio and obtain better performances from your Tabular data model. Let’s dive directly into it!
VertiPaq has its own language to query the model, and it doesn’t know anything about DAX or MDX, it is just a storage engine, and it stores it in memory (same engine as when you create aggregations). The physical organization of the data is different; the data is stored column by column; and the data in these columns is stored with compression. Why?
reduces the amount of memory required, and
improves the speed of the queries
Compression uses two techniques:
Value encoding
Integers, decimals
When there is a pattern in the values this compression can be considered.
In this example 3/8= 37,5% of space saved
Hash encoding
Strings can only be stored in hash encoding but hash encoding can be used also for numbers.
When we have a small number of unique values in the column.
Dictionary: list of unique values
Every column can have either a value or hash encoding. But every column that has an encoding type can also have an additional compression: Run Length Encoding (RLE). It is only used when it is an advantage, when reduces the size of the columns by removing the duplicates.
The encoding says how the value is represented for each position in the column, and the RLE can reduce the size that is needed to store the data in memory.
There are cases, especially when processing large tables (normally those managed in PBI premium or AS) when the engine must spend more time repeating the compression of the data that was already processed: Re-encoding
*Use Encoding Hint must be set in AS, there is no access to this optimization feature in Power BI desktop.
Compression is the Key feature from Vertipaq: on average an acceptable compression ratio is 1:8 or 1:10. The compression ratio depends on:
· The distribution of the data
· The data types= number of unique values in each column (the smaller number of unique values the better the compression)
CPU can process more data if it doesn’t have to access to much RAM.
Marco also highlighted reducing the column size; “reducing the memory usage is the most important task to optimize tabular. Smaller model means faster scans”. And presented the following table to understand and summarize the memory usage.
Another important element he mentioned was the Segmentation. The data is not processed all at once, the table is divided into segments. AS is supposed to run on larger servers that have more RAM, hence can have larger number of rows in each segment. The more rows we can run at a time, that we include in each segment, the better the compression. Logic: Read in memory segment by segment, stored in memory segment by segment.
How is the data processed? What happens when you read the data from your data source?
If we have a small table only containing 2 segments (in Power BI up to 2 million rows) at the beginning for small tables, the amount of memory needed is the required for 2 segments. Out of memory error when you process the data? It could mean that you don’t have enough memory in the RAM for 2 million rows in PBI or for 16 million rows in AS (unless you change the parameter).
Segment size and compression the larger, the better the compression, hence better query performance.
DefaultSegmentRowCount: 8M (need to be power of 2, at least 1M)
ProcessingTimeboxSexPerMRow: -1 = 10 sec (increase for large number of columns ~>200)
Suggestion: don’t consider changing these settings unless you have very large tables and you can experiment with this by changing the segment size you get better compressions
Oher two structures stored by VertiPaq he mentioned: Hierarchies
How much data do you need to process a table? It depends on many factors. These are the ones mentioned:
How can we estimate how much memory you need in a table?
And, what happens when we query a model?
Materialization is very important in query execution. It happens in complex joins, complex iterators and temporary data spooled for further processing. It might require more memory than the database size and note that the spooled data is not compressed.
Early Materialization
Issue that we can face
The data stored in memory compressed
Late Materialization
Optimal execution for the storage engine
The storage engine can provide the final outcome without generating additional tables
The session concluded with some best practices on how to reduce the query and improve performance. A practice that we should all include in our development model.
Good to know: VertiPaq Analyzer embed within DAX Studio. Download Vertipaq and test it 😊
Power BI Tips to take performance to the next level
During this session presented by the guys in a cube (Adam Saxton and Patrick LeBlanc) it was shared some practical and common performance problems that customers encounter when using Power BI. From report design to your data, they covered different aspects of Power BI to get your report working at the level that you expect. Let’s check out the different settings and techniques they listed so you can use to maximize your data and report within Power BI.
Name of the game: Name of the game: keep it clean, keep it simple
Aim of the game: To build a healthy power bi report and healthy data model to get the most out of it
Tools of the trade (free tools, but your enterprise organization might have them blocked):
Tabular editor & Best Practice Rules: Run BPA rule (tools > best practice analyzer). List of what is violating the rules in the model. For those using it the first time, you will need to configure the BPA rules (here the link to Michael Kovalsky’s article with full details of the complete list and how to configure it under the “Loading the rules” section, which is as simple as running a script he provides in the Advanced Scripting window and restarting Tabular Editor)
Most common mistakes:
o Do not use the DIVIDE functionlumns (such as the primary keys)
o Do not use the DIVIDE function
How to fix it: right click on object
DAX Studio and vpax analysis: how to create a vpax and analyze it (DAX Studio > Advanced tab > View metrics). Things to watch out (fundamentals that you need to understand)
o Cardinality (do you need the primary key of the table? Ditch the column!)
o Column size
o Data type (avoid double unless you have a decimal number with more than 4 decimals)
o Specify encoding (using VALUE) so the dictionary does not need to be created
Use a STAR SCHEMA (do not use a flat model). Why?
o Usability (easier navigation)
o Refreshes (will be so much faster)
o Simpler DAX (e.g. time
o Performance (to diagnose the problem. Performance analyzer. Check for the expensive DAX, analyze it in DAX studio.
Subscribe to MDW
You want to know more about MDW, a company specialized in Data, AI and BI projects 100% Cloud based? Feel free to subscribe to our Linkedin page, to contact us at contact@mdwpartners.com or to book one hour free consultation here: free consultation.