I’ve had a long-term love affair with Tabular Editor (TE). With my roots in SQL Server Analysis Services (SSAS) development, I first started using the open-source version of Tabular Editor 2 way back in 2016.
The early days
At that time the default tooling for SSAS was Visual Studio (VS). And in all honesty, it was a bit pants. Having to be connected to an in-memory version of your data model often made operations and navigation slow. There were outrageous limitations, like there being a finite number of measures you could add to a table, a particular problem if you had a large model and preferred to use a measure table. The DAX editor at the time was an Excel-esque one line formula pane (though it did improve with later releases). VS had always been notoriously buggy, especially the BI utilities, so freezing and crashing was a common issue.
A new dawn
Tabular Editor was a breath of fresh air. Navigating the data model objects in the explorer pane was far easier, the spacious expression pane made reading and authoring DAX measures a much better experience and because you were editing the .bim file that stored the code for your model “offline” it performed far quicker than VS and with fewer moving parts going on, I found it far more reliable.
And then discovering the scripting capabilities unlocked huge productivity gains. I was able to create dozens of time intelligence measures based on a set of core measures in just a few clicks.
Game changer
The game changer for me came in 2019, when I attended SQL Bits in Manchester and was able to watch TE creator Daniel Otykier give a presentation on development lifecycle using Tabular Editor.
Business Intelligence as a discipline has been getting better and better at adopting software development principles and this session was a real eye opener. It introduced me to serialised source control, finally solving the issue of having multiple developers working on the same .bim file. It was also the first time I became aware of Best Practice Analyzer, which I’m sure already existed at this point, but had thus far escaped my notice. Now myself and my team could validate models against an agreed set of design principles. In time, I’d even bake all of this in to CI\CD pipelines.
External Tools
The next level of Tabular Editor love was achieved in July 2020, when Microsoft enabled the use of external tools with Power BI. Now I could bring all those awesome productivity hacks to my PBIX development. This also opened up new capabilities, such as being able to use calculation groups, a feature that until October 2023 you still couldn’t achieve using native Power BI desktop.
There are other obscure properties you can set in Tabular Editor that can’t be set in Power BI that can give huge performance boosts too. Setting AvailableInMDX to false for hidden columns can help reduce your data model size and processing time. I’ve also seen some stunning results when using Direct Query on top of Databricks SQL by adjusting the Max Parallelism Per Query property. So Tabular Editor doesn’t just aid productivity, it’s a hugely important tool for ensuring the performance of your Power BI models, especially at Enterprise scale.
Tabular Editor 3
All the above advantages can all be realised using Tabular Editor 2, the open-source version. I haven’t even begun to wax lyrical on why I think Tabular Editor 3, the licensed and fully supported version, is well worth the fee.
I’m very fortunate that the folks at Advancing Analytics fund an Enterprise TE3 licence for me, but I really do think that it’s a worthwhile investment.
The DAX authoring experience is on steroids, with syntax highlighting, semantic checking and auto complete. You can connect your model to a Power BI workspace and test your DAX live (no need to fire up DAX Studio separately) and there’s also an integrated debugging tool.
My C# scripting has been taken to the next level, as I’m able to record actions using the macro recorder, helping me to explore areas of the Tabular Object Model (TOM) that I’m unfamiliar with quickly and easily.
The workflow for creating relationships is far better, and the addition of a model view is also valuable to help me validate the structure of my models easily.
And did I mention that can all be done using Dark Mode!
Tabular Editor 3 has played a huge role in the development of Aggreg8, the Power BI x Databricks accelerator that we’ve been building out at Advancing Analytics.
In terms of development tools for working with Microsoft’s ground-breaking Fabric platform, TE3 is also leading the way.
The elephant in the room
But when discussing the merits of Tabular Editor, there is an elephant in the room that needs to be addressed. That is the perception of third-party tools in the Power BI ecosystem. I hear countless tales from people who say they are not allowed to use third-party tools. Twitter celebrity Jeff Weir is currently bemoaning this attitude, and there are questions around whether Microsoft could do more to aid the adoption of this tooling.
I personally feel that developing Tabular Models at scale without using Tabular Editor would be like doing it with one arm tied behind my back (or as Joe Reis recently referenced “you are like a one-legged man in an ass-kicking contest”).
If your organisation is serious about using Power BI as an enterprise tool, you should absolutely be using Tabular Editor. Should Microsoft just buy Tabular Editor out and bake all this goodness into their own tooling? Chris Webb provides some pretty compelling reasons why that probably isn’t a good idea.
If you need to seek approval for Tabular Editor installation, if you need to go through some kind of process to ratify its usage in your business; do it! Yes, it might be a painful process, and you may have to jump through hoops to get there, but that’s a better alternative to just complaining about it. Write that business case and go through that pain. It will be worth it. Maybe the best time to have done this was months ago, but the second-best time is now.
The case for Tabular Editor
Need some help to support your case?
Microsoft themselves have issued comms on the issue and this is backed up by some of the leading lights in the industry. SQLBI’s own guidance on the matter was recently updated for 2023. The Microsoft Reference architecture for Advanced Data Model Management and Enterprise Content Publishing both include Tabular Editor in them, whilst you will also see references to Tabular Editor scattered throughout the MS Learn content and it certainly features in the learning path for the DP-500 Azure Enterprise Data Analyst certification. Heck, maybe you can even use this blog as a reference!
Advocacy
In the meantime, I’ll continue to be a huge advocate for Tabular Editor. Is this a sponsored post? I am the lucky recipient of various pieces of Tabular Editor swag, having met the team at a couple of different conferences now. And I’ve had the pleasure of chatting over a beer with Søren, David and Morten from the TE team too. But no, no paid association here. Just genuine affection for a product which has sincerely had a profound influence on my own career.
Next chapter
More great news on the Tabular Editor front is that they’ve just launched an online training platform to accompany the product. With content developed in conjunction with Kurt Buhler (for whom I have constant content envy because all his stuff is so good) the training resources are totally free and a great way to familiarise yourself with the product. You should check it out!
0 Comments