Using Microsoft Power BI astutely and efficiently gets harder the more you aim to achieve with it. Once you have established it as the tool of choice to deliver business insights to your organisation, it will quickly grow and its use proliferate across departments.
At first glance MS Power BI makes it very easy to import large amounts of data from disparate sources and create fabulous reports, without involving IT or other infrastructure. However, before long the ease of use that enabled such a fast development becomes its own undoing.
Without careful planning and awareness of the consequences of decisions made early on, a Power BI dataset can soon become slow and hard to maintain.
Luckily there are a few tools out there that can help with performance tuning and other tasks that will bring datasets and models back under control.
1. DAX Studio, What Is It?
DAX Studio is a free 3rd party tool, released under a Microsoft Permissive Licence, that can connect to local and published datasets in Power BI, as well as SQL Server Analysis Services and PowerPivot models in Excel.
As the name suggests its focus is on the formula language for MS Power BI – DAX (Data Analysis Expressions) – but it also helps with analysis of dataset size, design, query performance and other features of connecting to and manipulating data.
The home menu of DAX Studio
It is downloaded from https://daxstudio.org and is updated often, to keep pace with developments in MS Power BI itself.
2. Analysing DAX with DAX Studio
As its name implies, DAX Studio is very useful for writing and editing DAX formulas/statements. It comes with an updated list of all DAX functions in an easy to browse library and as you type it gives you the same hints and tips that MS Power BI’s Intellisense does.
DAX Studio function library
You can use the query builder in DAX Studio to write new DAX statements or to analyse existing ones from your data model.
Running DAX formulas and checking their performance
Once you have DAX formulas you are happy with and that run well in DAX Studio you can copy them back to the MS Power BI model and then update the metadata in DAX Studio to see them as part of the model.
3. Analysing Queries in DAX Studio
Beyond DAX there is also the opportunity to analyse and tune query performance in DAX Studio. Using the Query Builder panel you can create the same sorts of queries that your visualizations will contain in MS Power BI and then check their performance.
Output of a query in DAX Studio
Timings of the query performance
This sort of analysis helps immensely with identifying long-running queries and will provide insight into their causes. Slow-to-load reports and visuals will turn off users and prevent them from engaging with your analysis.
4. Other Performance Analysis
Aside form DAX and query performance, DAX Studio can also give you insight into the overall health of the dataset you are connected to, and also import performance data from MS Power BI’s own Performance Analyzer tool.
Summary of a dataset’s metadata
The VertiPaq Analyzer tool shed light on the size of and potential problems with the dataset and data sources, showing details of the tables columns and relationships between them.
Summary of table sizes
At its heart MS Power BI Desktop is keeping all of the imported data and calculations in a compressed in-memory database while you work on it. So this can place great strain on your PC or laptop’s resources if you are trying to make updates to a large dataset.
Using DAX Studio to identify the size and cardinality of the data you are working with can flag areas where resources will be stretched.
Summary of table relationships
It can also help identify data issues such as missing keys or null values.
In summary DAX Studio is an easy to use tool that will be invaluable for more sophisticated users of Power BI.
Not all MS Power BI issues or questions can be solved with DAX Studio though. If you have a question or are seeking advice then get in touch with us at Codestone – we’re experts in data, analytics and BI solutions!