Secrets of Power BI Performance: Power BI Dataflows
Author: Tom Hoblitzell | 5 min read | February 3, 2021
Microsoft Power BI is a mature, feature-rich business analytics solution used by thousands of companies to get cutting-edge data-driven insights. However, there are a number of challenges with using Power BI straight out of the box (as we discuss in our white paper “Power BI for Mid-Market Companies”).
As a Microsoft Gold Partner, Datavail knows Power BI like the back of our hand—and we’re here to help. In this series, we’ll take a look at some of the Power BI “secrets” that savvy users know about, starting with one of the most powerful and useful features of the platform: Power BI dataflows.
What Are Power BI Dataflows?
Power BI data flows are a construct for organizing and persisting self-service data in Power BI, similar to but distinct from a dataset. Users can employ dataflows to collect, clean, combine, and enrich their enterprise data, automating much of the process.
Dataflows enable you to eliminate some services and bring data directly into the Power BI data service. By creating an abstraction away from your data sources, dataflows help provide users access to the information they need, without them needing to worry about the underlying implementation in terms of data warehouses and tools.
The use cases of dataflows include:
- Creating transformation logic that can be shared and reused for Power BI datasets and reports.
- Establishing a single source of truth by forcing analysts to connect to dataflows themselves, instead of connecting to the underlying systems.
- Limiting access to an underlying data source for only a few select people, while letting analysts build on top of the dataflows.
Power BI Dataflows: Tips and Tricks
Power BI dataflows, like any other software feature, require a bit of adjustment as you get used to how they work within your IT environment. To lower the learning curve and boost your productivity, below are just a few of the Power BI dataflow tips and tricks you should know about.
- Power BI enhanced compute engine
Released in October 2020, the Power BI enhanced compute engine loads data into an SQL-based cache for faster querying. According to Microsoft, this enhanced compute engine has the potential to improve Power BI dataflow performance by up to 20 times. This new engine is now enabled by default for all new dataflows in Power BI, so be sure to turn it on for older dataflows as well.
- Reusing dataflows
Reusing your Power BI dataflows across multiple environments and workspaces can save users a great deal of time and effort. Some of the best practices here include:
- Separating data transformation workflows and staging/extraction workflows. Decomposing dataflows into smaller pieces makes them more modular and easier to reuse in different places.
- To use the output of a dataflow in one workspace for dataflows in other workspaces, you need to set the correct workspace access levels. (See Microsoft’s article “Organize work in the new workspaces in Power BI.”)
For the full story, check out Microsoft’s article “Best practices for reusing dataflows across environments and workspaces.”
- Implementing error handling
Unexpected errors can derail your Power BI deployment and bring down your entire analytics workflow, depriving you of the insights you need. It’s highly recommended that you make your Power BI dataflows robust by implementing error handling so that you can handle errors and recover from them gracefully.
The two types of common errors in Power BI are step-level errors (which prevent a query from loading) and cell-level errors (which do not prevent the query from loading, but display “Error” in cells with errors). Microsoft provides guidance for how to deal with these in the article “Dealing with errors in Power Query.” More sophisticated error handling in Power BI is possible by applying your own custom conditional logic (e.g. the “try” and “otherwise” syntax).
Final Thoughts
Using the enhanced compute engine, reusing your dataflows in multiple places, and implementing error handling are just a few ways for Power BI power users to optimize the software’s performance. Power BI dataflows are a tremendously powerful and flexible construct—at least when you follow the Power BI best practices that we’ve outlined above.
However, dataflows are just one way for companies to get the Power BI performance they need. Want to learn more? Check out the Datavail blog for more Power BI insights, or download our white paper “Power BI for Mid-Market Companies” for all the details. Also talk to one of our Power BI experts.
