SESSION Details

Cracking Power BI Performance Tuning in a day!

Congratulations! You've built your shiny fancy Power BI report and your charts look terrific. But, your data refresh takes forever...Or, your report is utterly slow when interacting with visuals...You switch to DirectQuery mode to make things better, just to discover that the report performance sucks even more...

And, if you feel lost, I don't blame you - I've also been there!

That's why this workshop is for you: we will examine potential bottlenecks in your Power BI report - starting with defining the best practices for data model size optimization, understanding the importance of query folding feature, all the way to identifying culprits for your slow report rendering. We will wrap it up with learning when and why (not) to use DirectQuery mode, and how to apply improvements on the data source side to make things faster in the Power BI.

Feedback Link - https://sqlb.it/?6201

Starts: 09:00 9th Mar 2022
Ends: 17:00 9th Mar 2022

Q&As

Short Description
- Creating a Power BI report may look like an easy task, but there is a huge difference between the report that works and the report that works efficiently!
Should delegates bring a laptop with them?
- Yes
If so what Spec?
- Min 8GB RAM, 256 GB HDD
What software and licences do they need?
- Power BI Desktop (free) DAX Studio (free)
What is the structure of the day?
- Agenda 1. Introduction 2. Data Modeling best practices 3. Data Model Size 4. Data Refresh Process 5. Aggregations 6. DAX Calculations 7. Visualization rendering time 8. Storage Mode choice 9. Summary 1. Introduction • Understanding the importance of having a high performant solution • Where do I start? How to identify the poorly performing area(s) 2. Data Modeling best practices • Why is data modeling so important? • Star-schema vs flat table • Costs of not having a star-schema in place • Role-playing dimensions • Bi-directional filters 3. Data Model size • Understanding the core components of the VertiPaq engine • Describing different data compression encoding types • Understanding the concept of cardinality levels • Describing Auto Date/Time and calculated columns caveats • Demo: Reducing Data model size applying various techniques (splitting columns, changing data types, reducing the cardinality levels, etc.) 4. Aggregations • Understanding the concept of aggregations in Power BI • User-defined vs Automatic aggregations • Demo: Create user-defined aggregations, make Power BI “aware” of it, and unleash the full power using the Composite model; Check if the aggregations are being used by Power BI 5. Data refresh process • Introduction to the data transformation process • Best practices for transforming the data • Using Power Query Editor for data transformation • Understanding the concept of Query folding • Describing the most common scenarios for breaking Query folding • Visualizing data refresh process • Demo: understand the importance of Query folding by showing the difference in performance between the queries that return exactly the same results: one with query folding, one without 6. DAX Calculations • Introduction to DAX calculations as part of enriching semantic model • Understanding how little nuances in the DAX query can produce a significant difference in the performance • Describing most common mistakes when writing DAX calculations 7. Visuals rendering time • Understanding what affects the report rendering time • Using Performance Analyzer feature to identify the slowest report elements • Best practices for visuals rendering time • Demo: redesign Power BI report page to perform 5x faster, without violating the original report design 8. Storage Mode decision • Understand the difference between Import and DirectQuery mode • Understand use-case scenarios for DirectQuery mode • Best practices for using DirectQuery mode • Things to avoid when using DirectQuery mode 9. Summary
What internet requirements do the delegates have?
- No specific internet requirements
What will the delegates get out of the day?
- Delegates will walk out well equipped to identify the potential Power BI development areas that need improvement. By understanding specific important concepts and features, and the way Power BI works internally, delegates will be able to create highly efficient reporting solutions.

Slidedeck Link

Cracking Power BI Performance Tuning in a Day.pptx

Speakers

Nikola Ilic

Tags

AzureMoving to the CloudOn PremisesIntelligence and AnalyticsVisualising and Actioning DataMonitoringModellingOptimisingData Transformation and IntegrationDatabase EngineModern AnalyticsModern Reporting and InsightsPower BIIntermediateHow

The SQL Bits Story

SQLBits was formed in 2007 by a group of volunteers who were passionate about the SQL Server product suite and wanted to provide much-needed community-driven education to the data community.

As one of the largest data platform conferences in the world, we offer more opportunities to a wider audience.

15 Years

We’ve grown and expanded a lot since 2007.

2500 Participants

SQLBits is the best place to meet fellow data professionals.

82 Countries

We welcome data professionals from all over the globe.

1140 recorded sessions

All the live sessions are recorded and offered for free, year round.

Experience the SQLBits Conference

Previous
Next

Want to be part of the SQLBits community?

Attend the London conference in-person or virtually on 

March 8-12, 2022 at ExCel London, UK.