Microsoft Power BI Performance Best Practices: A comprehensive guide to building consistently fast Power BI solutions

312 Pages·2022·7.19 MB·english

by  B. Merchant

Microsoft Power BI
Performance
Best Practices
A comprehensive guide to building consistently fast
Power BI solutions
Bhavik Merchant
BIRMINGHAM—MUMBAI
Microsoft Power BI Performance Best Practices
Copyright © 2022 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system,
or transmitted in any form or by any means, without the prior written permission of the
publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the
information presented. However, the information contained in this book is sold without
warranty, either express or implied. Neither the author, nor Packt Publishing or its dealers
and distributors, will be held liable for any damages caused or alleged to have been caused
directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the
companies and products mentioned in this book by the appropriate use of capitals.
However, Packt Publishing cannot guarantee the accuracy of this information.
Publishing Product Manager: Ali Abidi
Senior Editor: David Sugarman
Content Development Editor: Priyanka Soam
Technical Editor: Devanshi Ayare
Copy Editor: Safis Editing
Project Coordinator: Aparna Ravikumar Nair
Proofreader: Safis Editing
Indexer: Rekha Nair
Production Designer: Jyoti Chauhan
Marketing Coordinator: Priyanka Mhatre
First published: April 2022
Production reference: 1240322
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham
B3 2PB, UK.
ISBN 978-1-80107-644-9
www.packt.com
"Like so many other authors, I dedicate this book, first and foremost, to my
wife and 5-year-old son. The little one especially, for being a real trooper
and reluctantly allowing me many hours on weekends to write this book
instead of spending time with him. I didn't realize how critical their support
was until I reached the final chapters, as the long months with COVID
were even more challenging for us with some personal and professional
hurdles to overcome. Despite being isolated and in a new country, they kept
encouraging me and celebrating my small wins each time a chapter draft or
review was done. My deepest, heartfelt gratitude goes out to them both.
I also want to thank everyone I worked with during my time at Microsoft
in the Power BI product team. I learned a lot from many experts, such
as the CAT team, architects, engineering managers, and deep subject
matter experts in areas such as reports. The list is long, and I prefer not to
name anyone for fear of missing someone out! I really hope that all their
knowledge, coupled with my own experiences serving innovative customers
around the world, will help you take your Power BI solutions to
the next level."
Foreword
Ask anyone who has ever presented at a database conference, written blog posts about
databases, or recorded videos about them, what the most popular database-related topic
is and they will all tell you the same thing: performance tuning. While a skilfully crafted
conference presentation on database design best practices will draw a good number of
attendees, a basic presentation on performance tuning will draw the crowds. Why is this?
I think it's because the goal of performance tuning is so simple: you have something slow
and you need to make it fast. It's the struggle that every DBA, report developer, or business
analyst faces every day of their professional life. Speed represents usability, faster decisions,
happy users, and, ultimately, success. Some people have gained minor celebrity status and
undertaken lucrative careers by being able to make your queries and calculations go faster.
Power BI is no different from any other BI tool or database in this respect. One of the
most common causes of failure, and certainly the most common cause of complaints, for
any BI project is poor report performance. In general, Power BI is extremely fast, even
when you're working with relatively large amounts of data, but if you make a mistake
somewhere, write a complex calculation incorrectly, or fail to model your data in the right
way, for example, you will end up in trouble. As a Power BI professional, it's essential that
you know how to design for performance, how to troubleshoot your reports when things
go wrong, and how to rectify your errors.
All of this is why Bhavik's book is so important. Even though performance tuning is such
an important and popular topic, I believe this is the first book that has ever been dedicated
to performance tuning Power BI. It brings together hints, tips, and best practices that have
been scattered around the official documentation, blog posts, videos, and training courses
and is enriched by the author's years of personal experience as a program manager on the
Power BI development team working with some of the largest Power BI customers in the
world. Rather than focusing on one particular aspect of performance tuning, such as DAX,
it looks at the subject holistically. As a result, you're holding in your hands (or maybe
viewing on your screen) an invaluable resource that could make the difference between
the success or the failure of your Power BI project. Study it carefully and follow its advice!
Christopher Webb
Principal Program Manager, Power BI CAT Team; 13 year MVP and author of multiple
SSAS and Power BI titles
Contributors
About the author
Bhavik Merchant has nearly 18 years of in-depth BI experience. He is a director of
product analytics at Salesforce. Prior to that, he was at Microsoft, first as a cloud solution
architect, and then as a product manager in the Power BI engineering team. At Power
BI, he led the customer-facing insights program, being responsible for the strategy and
technical framework to deliver system-wide usage and performance insights to customers.
Before Microsoft, Bhavik spent years managing high-caliber consulting teams, delivering
enterprise-scale BI projects. He has delivered much technical and theoretical BI training
over the years, including expert Power BI performance training that he developed for top
Microsoft partners globally.
About the reviewers
Suresh Datla has been in the IT Industry for over two decades and has vast experience
across multiple Business and Technology domains. He is an Architect, Adviser, Evangelist,
and Trainer. He has been working on Azure and Power Platform since their inception and
he also works very closely with the Microsoft team developing industry vertical solutions.
He is a speaker at Microsoft-sponsored events on Power Platform, Power BI, Power
BI Premium, Security, and Performance. He organizes the Southern California Power
Platform User Group every month and strongly believes that the success of a platform
lies in the strength of the community. Suresh is the principal at Synergis Consulting and
leads a group of Data Architects, Designers, Engineers, and Developers.
Vishwanath Muzumdar has more than 8 years' experience in information technology
consulting, business analysis, business development, and business process management
in the BI space.
He is an MS Power BI developer (champion) in the creation of powerful visual reporting
for clients. His goal is to utilize his strong prioritization skills, analytical ability, team
management skills, and expertise in the Microsoft Power BI reporting tool in order to
achieve organizational objectives.
Table of Contents
Preface
Part 1: Architecture, Bottlenecks,
and Performance Targets
1
Setting Targets and Identifying Problem Areas
Defining good performance   5 The Power BI enterprise gateway   9
Report performance goals   5 Network latency   10
Setting realistic performance targets   6 The Power BI service   10
Considering areas that can slow Which choices affect
you down   7 performance?   11
Connecting data sources   8 Summary   12
2
Exploring Power BI Architecture and Configuration
Understanding data How gateways work   21
connectivity and Good practices for gateway
storage modes   14 performance   22
Choosing between Import and
General architectural guidance   31
DirectQuery mode   15
Planning data and cache refresh
When DirectQuery is more appropriate    17
schedules   31
LiveConnect mode   19
Summary   33
Reaching on-premises data
through gateways   20
viii Table of Contents
3
DirectQuery Optimization
Data modeling for DirectQuery   36 Power BI Desktop settings   43
Optimizing DirectQuery relationships   40 Optimizing external data sources   46
Configuring for faster Summary   48
DirectQuery   43
Part 2: Performance Analysis, Improvement,
and Management
4
Analyzing Logs and Metrics
Power BI usage metrics   52 Analysis Services server traces with
the XMLA endpoint   67
Customizing the usage metrics report   55
Integration with Azure Log Analytics   68
Power BI logs and engine traces   66 Monitoring Azure Analysis Services
Activity logs and unified audit logs   66 and Power BI embedded   68
Summary   70
Further reading   72
5
Desktop Performance Analyzer
Technical requirements   74 Achieving consistency in tests   81
Overview of Performance Understanding Performance Analyzer's
Analyzer   74 strengths and limitations   85
Interpreting and acting on
Actions and metrics in Performance
Performance Analyzer data   86
Analyzer   76
Determining user actions   77 Exporting and analyzing
performance data   91
Spotting and mitigating
Summary   96
performance issues   81
Table of Contents ix
6
Third-Party Utilities
Technical requirements   98 Using Tabular Editor's Best Practice
Analyzer   102
Power BI Helper   98
Identifying large columns in the dataset   99 DAX Studio and VertiPaq
Identifying unused columns   100 Analyzer   106
Identifying bi-directional and inactive Analyzing model size with VertiPaq
relationships   101 Analyzer   107
Identifying measure dependencies   101 Performance tuning the data model
and DAX   109
Tabular Editor   101
Summary   115
7
Governing with a Performance Framework
Establishing a repeatable, Leveraging professional developers   123
pro-active performance Approaching performance
improvement process   118 improvement collaboratively   123
The performance management cycle   119 Applying the performance
management cycle to different usage
Knowledge sharing and scenarios   124
awareness   122
Summary   128
Helping self-service users   122
Part 3: Fetching, Transforming, and
Visualizing Data
8
Loading, Transforming, and Refreshing Data
Technical requirements   132 Improving the development
experience   135
General data transformation
guidance    132
Folding, joining,
Data refresh, parallelism, and and aggregating   140
resource usage   133