The tale of Tabular Editor

I never like to brag, but after almost 7 years of developing and maintaining Tabular Editor 2, I feel a blog post with a little history and my perception of the tools' impact, is merited. In Danish, we have the proverb "Ananas i egen juice", directly translated as "Pineapple in its own juice", which is sort of the thing you say when people speak positively about something they themselves created. I guess this is that kind of blog post.

The beginning

Tabular Editor started as a hobby project back in August 2016. At the time, I was working as a consultant, building SQL Server Analysis Services Tabular models for a large Danish company. This was in the very early days of Power BI, mind you, so on-premises SSAS was still very much the go-to solution for analytics, for many companies. We used Analysis Services Projects for Visual Studio, or SQL Server Data Tools, as it was called back then, for all our Tabular model development. Some of the models used by this client, were pretty big and complex, having some 50 tables and some 1200 or so measures.

Let's just say, the development experience in Visual Studio at the time, was not the best. This was probably because Visual Studio used a tabular model designer that was based on PowerPivot for Excel - a tool, which was never intended for advanced developers, but rather for business analysts, who were used to working with Excel. For example, DAX had to be edited in a very small formula bar, which didn't use a monospaced font, and in the beginning, we didn't even have syntax coloring of keywords/functions. In fact, this is the very reason why the SQLBI team invented www.daxformatter.com, and the rules for DAX code formatting - even slightly complex DAX would be more or less impossible to read, otherwise.

But we made do, because the client had gigabytes of data, and the query-time performance we got from a SSAS Tabular-based solution, simply blew the alternatives away.

A small task?

One day, however, the client requested that the measures in the model be reorganized. Of course, we were already using a hierarchy of Display Folders (otherwise, browsing 1200+ measures in Excel would be impossible), but now the client needed one of the folders at the root level to be renamed. As the experienced reader already knows, the Display Folder is a property on each individual measure - and back in 2016, there was no batch editing of measures (or other Tabular model objects, for that matter) in Visual Studio. One had to go through each and every measure within that root folder (some 800 measures in our case), update the Display Folder property, and wait for Analysis Services to sync up the workspace database, after the change. This sync would take on the order of 30 seconds, due to the complexity of the model (and, I imagine, the enormous XMLA statements generated during each change). And this was the happy path. If you clicked somewhere you weren't supposed to, Visual Studio might crash, and you had to start over from the last time you saved.

To make matters worse, there was no way to view the actual Display Folder structure in Visual Studio. All measures were displayed in a GRID, and the only place you could view the Display Folder, was in the Properties view, after you selected a measure...

So, a task, that seemed to be stupidly simple and small, was estimated to take 5 days of work...

Work smarter, not harder!

To my surprise, the customer accepted the 5-day estimate without any kind of objection. I guess they were used to small changes taking a long time on their models. I'm lazy by nature, so not only did it not sit well with me, to bill the client for 5 days of monkey-see-monkey-do repetitive grunt work, I also really didn't feel like doing all that unforgiving, mindless work. There had to be a better way.

The first thing I considered, was opening the model.bim file (which is a JSON document that contains the definition of the entire model structure, including all measures and their properties) in a text editor, in order to simply perform a search-and-replace. Unfortunately, the folder that the client wanted renamed, had a very generic name (if I recall correctly, it was the word "Sales" that had to be changed to something else, but similar). By performing a search-and-replace through the JSON, I would inevitably replace something not related to Display Folders (perhaps an object name or a DAX expression), which would most likely break the entire model (and this was a long time before any kind of CI/CD or automated testing of tabular models, was viable). Even with a regex search-and-replace, it was just too likely that I would accidentally rename something I wasn't supposed to.

Having a background as a C# developer, I remembered that a few months prior to this, Microsoft had released the Tabular Object Model (TOM) extension for Analysis Management Objects (AMO), and luck would have it that the client had recently upgraded to SQL Server 2016 and Compatibility Level 1200, which was a prerequisite for using the TOM.

So, I fired up Visual Studio, this time to create a C# console application project, I referenced the TOM DLL's, and wrote something like 10 lines of code, which would loop through all measures of the model, that resided in or beneath the "Sales" folder, and updated their Display Folder property, to use the new name. Creating this script took maybe 20 minutes, including the time I spent looking at the TOM documentation. I hit F5, and the console application ran and finished the job in less than a second.

I need a UI for this application!

I immediately realized that this might not be the first time that such a request came from a client, and that it would be useful to have an application that would visualize the tree structure of the Display Folders and measures within, letting the user interactively modify the structure, for example by dragging and dropping, just like when you move files around folders in the Windows Explorer. Since I'd only spent 20 minutes on this task so far, and the client thought it would take 5 days, I figured, I might as well spend some more time on this.

An hour or so later, I had a small WinForms application running, which would let you load a model.bim file, visualize the Display Folders as a tree, rename folders and drag-and-drop measures around within.

I played around with this app for a little while and shared it with some colleagues. One of them asked me if it would also be possible to change the name of a measure or edit its DAX expression. And then I realized: This UI really ought to let the user edit, well, EVERYTHING, in the Tabular Object Model.

Tabular Editor 1

September 1st, 2016, I posted a blog on my employers’ website: "A new way to work with SQL Server Tabular models: The Tabular Editor". This was the first time the tool was made available to the public. The blog is no longer public, but you can still find it on the wayback machine. The tool itself was a 102 kb executable, which required you to manually download and copy the TOM DLLs into the same folder where tabulareditor.exe was located. And, as can be seen from the screenshot below, the groundwork for what is now Tabular Editor 2 (and 3), was already in place. The DAX editor used a monospaced font, which was an improvement over Visual Studio's formula bar, but the true benefit of Tabular Editor was that all changes were made offline with no workspace database synchronization in between.

The tool quickly gained popularity, as Kay Unkroth of the Analysis Services product team (who I'd previously been in contact with over a blog post that used TOM in PowerShell to migrate metadata translations to models on SQL Server 2016), promoted Tabular Editor in a presentation on the advantages of TOM, in early September. A few days later, even Marco Russo reached out, and offered to promote the tool in SQLBI's newsletter. I was completely star struck!

At this stage, we were still on the fence about the future of the tool, having kept the source code closed, and releasing it on Kapacity's website under a permissive, free software license. Kapacity wanted me to have all the credit, as they were a small consulting company at the time, with no interest in tackling the business of delivering software. The year before, my wife had given birth to our first child, and our second child was due in December of 2016, so naturally, I was concerned about the future of the tool, considering that I still had my day job as a consultant, to put bread on the table.

GitHub to the rescue?

In late 2016, I ultimately decided that going open source, would probably be best for the continuation of the tool. Tabular Editor 2.0 was released on GitHub on November 21st. This release included the familiar UI that is still in use today, and added support for undo/redo, C# scripting and macros (then known as "custom actions"), a deployment wizard, syntax highlighting, and more. My hope was that a release on GitHub, would attract other BI professionals with C# coding experience, to help maintain the tool and build new features over time.

While a user community quickly grew around the tool, with weekly and even daily bug reports and feature requests becoming the norm, I was disheartened to discover that code contributions from other people, were very minor and rare. I am still not sure why open-source contributions never picked up; it could be a number of things: Tabular Editor was made using WinForms, which was already sort of outdated in 2016. Sexier UI frameworks, using web technology, TypeScript, etc. were (and still somewhat is) all the rage. It could also be that I never got around to set up proper CI/CD for TE2, that there was no "how to contribute" guide, or that people plain and simple weren't able to debug and understand my spaghetti-style C# code (not to be confused with Italian-style DAX, which is actually a good thing!). Or the reason could be, that there was simply very few Microsoft BI Professionals out there, who also had C# coding skills.

In any case, I tried to make do, spending what little free time I had working on the tool. When working on client projects during the day, I was always on the lookout for tasks that would be easier and faster to solve using Tabular Editor - that way, I could justify building X new feature on client time. Still, this was not sustainable, and I had a hard time keeping up with the tool's popularity.

Power BI Contribution

In 2019, I was invited to Redmond for the Power BI Contributor Program. Amir Netz, who is the CTO of Power BI, wanted to provide a way for 3rd party developers, to integrate their tools with Power BI Desktop. In July of 2020, the Power BI External Tools ribbon was released, which was the culmination of this effort. Naturally, as Tabular Editor was one of the three "featured tools" in this blog post, adoption skyrocketed. Not only could Power BI Desktop now benefit from the productivity-enhancing features of Tabular Editor, but the tool would even unlock certain advanced modelling capabilities, that were not otherwise accessible: Things like Calculation Groups, Metadata Translations and Object-Level Security, could not be authored through Power BI Desktop at the time, and Tabular Editor provided an easy way to add these objects, without having to deal with SQL Server Management Studio (SSMS) and Tabular Model Scripting Language (TMSL).

I may have had a hard time keeping up with community support and feature requests before the release of External Tools, but this was nothing compared to the influx of Power BI Desktop users, who had now added Tabular Editor to their toolbelt. To make matters worse, Power BI Desktop initially only had limited support for external tools making modifications to the model, so questions from users who didn't understand why they couldn't add calculated columns or rename tables, or why adding a calculation group disabled implicit measures, came in frequently, sometimes even multiple times per day (I tried to limit my activity to the questions that came in on Tabular Editor's GitHub issues page, however, figuring that experienced Tabular Editor users would be happy to help the newbies on other forums).

Simultaneously, I had been working secretly on creating my very own DAX parser, which was going to be the foundation of a much better DAX editing experience within Tabular Editor, as well as the key component in another project. At this point, my daily routine, looked like this: Wake up, go to work for 7-8 hours, come home, spend a little time with the family, then work on Tabular Editor and the DAX parser for an additional 3-4 hours every night and most of the weekends. No more time for gliding or hanging out with friends. This was very unsustainable, not just for me, but also for my family, and it started to stress me out.

The present

I finished the DAX parser in early 2020 and acquired a trial license of the DevExpress WinForms Components, in an attempt to push the limits of the WinForms technology stack, and see if I would be able to create an even more productive user interface for Tabular Editor. Upon demonstrating an early prototype of Tabular Editor 3 to my boss, we toyed with the idea of commercializing it, in an attempt to generate enough income for me to drop my day job as a consultant. Fast forward to June 1st 2021, Tabular Editor 3 was released, and that idea has now become reality, tremendously reducing my level of stress, as I could now provide support for all Tabular Editor users during office hours - and get paid doing so! But that's a story for another blog post.

Tabular Editor 2 is still going strong. Version 2.19 came out just last month, adding support for PBI Project folders and adjusted restrictions when working against Power BI Desktop, as Desktop recently added support for many additional modelling operations through external tools. Tabular Editor 2 is still very popular. I don't have any idea about the actual number of active users (since the tool does not employ any type of telemetry) but judging from the total number of downloads of version 2.18, which was released in April, it's a pretty high number: As of July 10th, 2023, Tabular Editor 2.18 was downloaded 67.186 times. Of course, the actual number of users could be lower (for example, if one person downloads the same version multiple times), or it could be higher (for example, if one person distributes the download to multiple people within a company, or if users decide to skip this version).

It's also worth mentioning that an entire community has grown around Tabular Editor. Many Microsoft Data Platform MVPs and other community members, regularly post articles, videos, or submit conference and user group sessions, that involve Tabular Editor in some way. Moreover, entire GitHub repositories have been dedicated to community sharing of Tabular Editor scripts and Best Practice Rules. People have created advanced plug-ins and even games in Tabular Editor. I've also seen job adverts that explicitly list Tabular Editor as a required or recommended skill, when companies are hiring new BI team members. I think it's pretty safe to say, that Tabular Editor 2 has been a game changer, not only for SQL Server and Azure Analysis Services model development, but also for Power BI dataset authoring.

Now that we've covered the pineapple juice, let's take a look ahead.

The future

Today, I'm no longer working as a consultant. Instead, every hour I spend in the office, is spent on Tabular Editor 3: Feature design, sprint planning, customer support, etc. Not much has changed in regard to Tabular Editor 2 though: It's still a hobby project, and it will remain open-source on GitHub, as long as there is a need for the tool. With the release of Tabular Editor 3, a lot of people mistakenly thought that TE2 was being replaced, and in hindsight, we should probably have given TE3 a different name, just to indicate that it is not a sequel, but rather a premium alternative, for users who prefer an all-in-one tool that has a company behind it, offering support and liability.

For proprietary reasons, we cannot copy TE3 features over to TE2, but this wouldn't really be viable anyway, since I have a philosophy that TE2 should be kept as lightweight as possible. For these reasons, some of the more heavy stuff, like a customizable UI, a DAX parser, an M parser, or the Roslyn C# code compiler, will be kept out of TE2. But rest assured I have plenty of ideas for new features that will benefit TE2 users. For example, I'm working on a new C# scripting API, that will make it possible to easily load and save models, spawn various UI elements, etc. from within the script. I'm also working on getting TE2's script compiler to support interpolated strings, in order to remove friction when migrating TE3 scripts to TE2 (ideally, scripts should be able to work on both versions of Tabular Editor, without any modifications).

Moreover, new TOM or AS engine features may often be released within TE2 sooner than they will in TE3. I can make a TE2 release whenever I have something that is release worthy, whereas with TE3, we are locked down to a fixed release cycle, where new features have to be planned in advance and thoroughly tested. Essentially, this means that TE2 users may sometimes get access to the latest features, before their TE3 counterparts, as was the case, when TMDL support was added a couple of months ago.

All of this is still a hobby effort from my side - but since I'm no longer day jobbing as a consultant, I get to spend more time each day researching features and interacting with users online - which benefits both TE2 and TE3.

Someday, I may rebrand TE2 as "Tabular Editor Lite", or something like that, just to make it absolutely clear that the tool is not going away, that it is not being deprecated, that it will always be available for free, and that I will continue to support it and ensure that it always includes the latest TOM and AS engine features, whatever they may be.

For now, let me just say, that I am incredibly proud of Tabular Editor 2. Proud because of the community that have emerged around the tool, watching it grow and proliferate in online fora, and at user groups and conferences world wide. Proud because "Tabular Editor" has become an attractive skill one might list on a resumé. But most of all, I'm proud because I can honestly say that this is my creation - a creation that I know is helping several thousand Microsoft BI professionals be more productive every day. I'm thankful for the support and encouragement I've gotten from users of Tabular Editor throughout the years, and I look forward to continue this journey for many years to come.

Thanks for reading.