While some cloud BI tools such as Amazon QuickSight do all their authoring in the browser, Microsoft chose to develop a dedicated client creatively named Power BI Desktop. Its initial release as “Power BI Designer” back in early 2015 was underwhelming and unstable. But Microsoft stuck to its guns, and by combining Power Query for ETL, the great vertiPaq engine to handle data models in memory, and a decent if uninspired visualization layer, Power BI Desktop gradually grew into a must-use tool for BI developers and analysts working in the Microsoft ecosystem. The relentless stream of monthly updates – even if the pace has slowed down in the past year or so – has been a sight to behold.
Safe-to-Skip Introductory Musings on Product Positioning and Historical Developments
Microsoft program managers have stated that they intended to add more authoring tools to the Power BI service eventually, but I believe running the data model on a desktop tool – locally or in a virtual machine – will remain relevant for the foreseeable future, provided you have enough RAM (in my experience, getting to 32GB makes a big difference). It’s really cool to be running Analysis Services on your own PC if you stop to think about what’s under the hood.
Power BI Desktop however suffers from the jack-of-all-trades curse in that it doesn’t excel (bad pun? say it ain’t so!) at anything:
- The ETL user experience is good for “regular end users” thanks to Power Query’s macro-recorder like GUI, but it can be extremely slow and is not very developer friendly. The “Advanced Editor” eventually graduated from obnoxious to lackluster with the addition of color-coding and intellisense, but it remains underwhelming for more demanding use.
- DAX authoring in the formula bar can be aggravating because of silly usability snags.
- Application Lifecycle Management remains a weak point.
Whether you’re looking at the M side or the DAX side, there’s a general lack of a “big picture” perspective of your code base, with things such as global search-and-replace or dependency tracking being obvious gaps. The maturity seen in traditional development tools is nowhere to be seen, which is a bit frustrating coming from a vendor with such a rich history of working with and for developers. I understand Microsoft has business users in mind with this product, but even “regular people” eventually need this type of support.
Instead, in the past couple of years it’s become clear that Microsoft has been more focused on turning Power BI Desktop into the “clicky draggy PowerPoint of data” than making it a very developer-friendly tool. The user community made a concerted effort to fill in the gaps, and with its July 2020 update, Power BI Desktop became officially aware of third-party tools and gave them the ability to write back to the model.
I have mixed feelings about this as I feel it’s a cop out from making improvements that should be native to the core tool, but if Microsoft is not going to focus on developer productivity, I’ll take it. Interestingly, this ribbon was contributed by third-party developer Daniel Otykier. To be fair, that openness to community tools makes Microsoft looks much better than old school enterprise vendors such as Oracle, as Paul Turley posted here.
I’ll also note that Power Query is really a separate product, and in my opinion it would be better off as its own desktop tool that can go back and forth with Excel files, Power BI datasets, Power BI dataflows, PowerApps dataflows, Azure Data Factory wrangling dataflows, you name it… but I digress.
OK Olivier, Nice History Notes, But Why Should I Bother Again?
If you don’t feel like you’re outgrowing Power BI Desktop, you’re probably using Power BI at a fairly small scale, which is entirely fine, but the Power BI platform has grown far beyond basic self-service BI. And even at a small scale, the effects of time alone make the need for auditing and documentation tooling ever clearer.
While you might wonder what the fuss is about if you’re just getting started with a new, small model from scratch, the need for more powerful tooling becomes more apparent as you:
- Add more models
- Use more advanced functionality, from row level security to aggregations to calculation groups
- Run into performance issues
- Have more than one developer
- Need to maintain all this as your Power BI use becomes more and more complex over time: more data, more measures, more users, more workspaces, higher expectations, personnel rotation, etc. etc.
Trust me on this, Power BI Desktop alone is not enough to do sustained serious work on the Power BI platform.
Enough Already, I’m Convinced! Getting Started with 3rd-Party Tools in the Power BI Desktop Ribbon
First off, external tools will only show up in the ribbon if specific JSON files are present on your PC, which requires the latest version of these tools (as of July 2020) to be installed on your PC. So start by installing the latest DAX Studio et. al. if you haven’t already done so.
Another significant dependency is that you need to enable the enhanced dataset metadata preview, otherwise the icons will be displayed as disabled. I ran into issues with this feature when I tested it, so like some other consultants I follow, I’ve since then been staying away from it for fear of wreaking production models. I’ll update this entry as we learn more about how these integrations work in practice and whether it’s safe to use the enhanced metadata.
Without further ado, here’s an overview of what these tools are about, focusing on ETL and data modeling for now.
1. DAX Studio: Diving Deep Into your Data Models, with a Side Dish of VertiPaq Analyzer
If you’re going to use a single third-party tool to complement Power BI Desktop, DAX Studio is it. It lets you see under the hood of your data model in ways that Power BI Desktop can’t. There’s a learning curve to it, mainly because there’s a steep a long learning curve to DAX. Don’t just EVALUATE it, adopt it (bad pun intended).
Here’s how to get started with it, including how to connect to the ever-changing IP port if you’re not going to use the new integration with Power BI Desktop. There’s a lot going on here, and explaining DAX Studio in detail is beyond the scope of this entry.
Years ago SQLBI created another tool to analyze storage structures in PBI and Analysis Services models called VertiPaq Analyzer. It’s very useful in case you’ve added many tables to your model and are now faced with a big PBIX file, which can lead to performance issues or even push you beyond what’s allowed in shared (capping datasets at 1GB each) or even dedicated capacities.
The metrics returned by VertiPaq Analyzer are now available in DAX Studio, and can be output to small .vpax files that contain just metadata and stats, but not actual data, and can be opened in Excel to see further details. This will help you diagnose that, say, using GUIDs takes a lot of room in your model that could be saved by using surrogate keys instead.
2. Tabular Editor: Unlocking Calculation Groups & Working at Scale
2.1. Cut Down Redundant Measures and Control Formatting with Calculation Groups
Tabular Editor was originally built with Analysis Services in mind, however it has become very relevant to Power BI which now supports calculation groups but lacks a user interface to create them. Sidebar: here’s hoping that calc groups for Power BI Pro will emerge from the backlog, but I haven’t seen that mentioned in the 2020 release wave 2 roadmap. Come on Microsoft, differentiate Premium based on capacity rather than productivity features! [Edit: I’m told calculation groups work in shared capacity too, I’m investigating and will report my findings.]
A DAX limitation that’s been frustrating me to no end is the fact that FORMAT turns numbers into strings, making it useless to control how chart axes, labels etc. look. Calculation groups however let you apply a format string expression to measures without that side effect. This unlocks much more flexible visualizations, as explained by Business Intelligist in the next video.
2.2. Other Efficiency Gains with Tabular Editor
There’s much more to the tool than just support for calculation groups, with many other features that can accelerate large scale work. Its measure dependency tree is a good example of functionality that I feel should be native to Power BI Desktop. Where DAX Studio is the tool of choice within a model, Tabular Editor might be strongest across models. Perhaps its most limiting factor is the lack of IntelliSense while authoring DAX, though it does know when a measure is incorrect. On the other hand it can format measures to make them more readable, as per DAX Formatter best practices.
Scripting and custom actions in particular deserve to be explored as an entirely new way to approach DAX development beyond the confines of Power BI Desktop. See how Matt Allington uses it to edit/replace code inside all DAX measures, among other useful script snippets. See all its advanced features here, and this Introduction to Tabular Editor by Alex Powers.
3. Visual Studio Code: M and TOM Advanced Work
3.1. Syntax Highlighting
The first and most straightforward use for the free and nimble Visual Studio Code is to install its Power Query / M extension and use it as an ETL editor. It’s not going to be recognized by Power BI Desktop as a substitute for its advanced editor, so you’ll have to copy and paste. At first I thought I had to save a query to a .pq file for the M code to be recognized as such, but kind souls on Twitter pointed me to the “Select Language Mode” menu (shortcut: Ctrl+K M) in the bottom right corner:
3.2. PowerShell Scripting Black Arts
But wait! Undeterred by the lack of ribbon integration, the fearless Ben Grubaudo came up with a solution using PowerShell to write back from VS Code to Power BI and Excel files. Bananas, as they say in Power BI lingo! You’ll need to install PowerShell 7 if you don’t have it already (here’s how to check).
If you want to execute PS scripts from within VS Code, you’ll want to select it as the shell you’re executing in VS Code (otherwise you might be running commands against PowerShell 5, which won’t work). If you’ve never done development before, well, welcome to dependency hell (wait till you try npm or Python)! In the case of Edit-Datamashup, it needs to be executed from outside of VS Code with VS Code closed, but other scripts such as Search-DataMashup can be launched from within VS Code.
Incidentally, Microsoft just released Pylance, a language server for Python, to provide autocomplete, error-checking, jump-to-definition, code formatting, and other goodies while writing and reviewing code. When you realize how sophisticated is the tooling of more mature general-purpose languages, the DAX authoring experience in Power BI Desktop then looks downright primitive. Microsoft’s design choice, centered on an Excel-like formula bar, is both a blessing to ease entry for a mass audience, and a curse when you undoubtedly eventually need to work beyond its confines.
3.3. Tabular Object Model: Advanced Sorcery
VS Code can also be connected to a Power BI data model via the Tabular Object Model (TOM), as described by Phil Seamark here. While you probably won’t want to go through that rather involved process for day-to-day work, this is a promising approach for bulk work such as copying measures between reports or migrating from Azure Analysis Services to Power BI Premium.
Watch Phil’s blog for follow-ups on this, starting with Automatically create measures in Power BI using VS Code (C# code sample).
3.4. Notepad++: The Original Option for Syntax Highlighting M Code
Before this existed in any Microsoft tool, Matt Masson and Lars Schreiber added support for M as a user-defined language for the beloved Notepad++. From a code-formatting perspective, it’s functionally equivalent to the VS Code option above, so it’s more about which code editor you prefer to use.
Note however that Lars stopped supporting the tool as Power BI and Excel improved their PQ editors, so you might be missing out on newer M functions. I still thought I’d mention it if only for the historical record or in case you can’t/won’t run VS Code.
4. Visual Studio: for SSAS Die-Hards and Custom Connector Developers
The full-fledged Visual Studio is a very distant grandfather to the open source VS Code. I’ve used it a couple times to work against Power BI – namely to migrate a Power BI model to SSAS – but like Paul Turley details in this post, it’s a bit of a bloated product so good luck with that. I’m mentioning it here for completeness, in practice it sees little use in the Power BI community.
For a completely separate use case there’s the Power Query SDK that’s designed to help with the development of custom connectors and is overkill for “regular” M ETL.
5. ALM Toolkit: A substitute for Git
A PBIX file is really a zip file with XML and JSON files in it. To the best of my knowledge this has been reverse engineered by the community but is not officially documented for the public, and “hacking” these internals, while useful in a number of scenarios, is not supported. A drawback of how this file format is structured is that it’s just a binary file to traditional source control tools such as Git.
To alleviate that, Microsoft’s Christian Wade and MAQ Software developed the ALM toolkit (guys, get an SSL certificate for your website!).
6. Analyze in Excel
I’ve always been frustrated by Analyze in Excel’s reliance on jumping through several hoops and using the old .odc office data connectors as opposed to loading Power BI datasets from Get Data in Excel. SQL BL strikes yet again by bringing Excel much closer to Power BI Desktop with the AnalyzeInExcel external tool.
7. Power BI Cleaner and Power BI Comparer
The always helpful Imke Feldmann created two pointed tools well worth mentioning here:
- Power BI Cleaner lets you identify unused measures, calculated columns, relationships, and pretty much everything else in a model except incremental load policies. If you’ve grown a large model over months or years, it’s very likely you have some cruft in there.
- Power BI Comparer is an Excel template that counts and tracks down changes between two PBIT files. “Git blame” for Power BI if you will.
8. Power BI Helper to Fix That Mess!
This tool from Radacad feels a bit less single-minded in its focus than everything I’ve listed so far, but if you inherit messy, undocumented Power BI entities it may prove useful in getting a handle over what’s going on. Features run the gamut from exporting and beautifying M script to auditing columns, tables, and relationships, to publishing and copying across workspaces. I don’t think it’s “ribbon ready” yet.
9. Beyond M and DAX: R and Python with Power BI
R and later Python were the original third-party tools recognized in some capacity by Power BI, namely to generate visuals by sideloading the respective engines and IDEs. You can also use R or Python in Power Query, but that’s done via the native Power BI UI, not in third-party editors. These capabilities have existed for years, and their core purpose is to bring the world of R/Python into Power BI.
The new external tool functionality opens up opportunities for additional ways to use these languages directly on top of Power BI Desktop. David Eldersveld lists some scenarios in this entry:
- Code model documentation.
- Export as much data as your local RAM will allow (as an alternative to using DAX Studio for that purpose)
- Visualize the Power BI data in a fully interactive way with Python libraries such as Altair or Bokeh by loading the data into a panda dataframe. R/Python visuals in Power BI are turned into static images so they can be the recipient of cross filtering but not the emitter.
- Blend Power BI data with other sources in Python.
- Run DAX queries from a Python script or Jupyter notebook. I can see how I might use this for tests, training, and documentation.
Some of these ideas are very intriguing as they might help expose and “unlock” Power BI data models beyond the confines of the Power BI service. I’ll have to think more about how deployment would work, but this could be big for embedded scenarios without having to shell out big bucks for Power BI Embedded. David plans to post more on this, and I for one will pay close attention.
10. Build Your Own External Tools
See these entries for examples of what can be built and how to do so:
- Kasper de Jonge’s: Creating a Time intelligence “External Tools” app for Power BI Desktop
- Erik Svensen: External tool to connect Excel to the current PBIX file
- Marc Lelijveld: Document your Power BI Model
11. Monkey Tools for (Excel) Data Models
Technically Monkey Tools is an Excel add-in, but some of its more advanced features are also valuable to Power BI modelers. Namely, QuerySleuth gives you a dependency/precedent tree view of your queries, while ModelSleuth helps document data models.
Unlike the aforementioned tools, this one is a paid product.
BI-Related Tools Beyond This Entry’s Scope
As I said I left aside for now the broader BI ecosystem, which I may expand upon in a future entry to include:
- Data access, e.g. Azure Storage Explorer, Azure Data Studio, or SQL Server Management Studio
- Administration and governance, e.g. BI Sentinel, Power Update
- Write-back and planning: Power Planner, Acterys (available as an External Tool)
- Visualization, e.g. Charticulator, Power BI Report Builder (which will be added to the external tool ribbon)
- Custom visual development (that’s a completely different type of project and skillset, closer to web development than to BI)