In this blog, Scott Hietpas and Cory Cundy – two principal consultants on Core BTS’ Data team – explore the advantages of using Tabular Editor for tabular model development.
This blog is about using Tabular Editor as part of the development process and adding it to how you’re using Visual Studio. We’ve found that it solves some specific development challenges that show up when developers collaborate on tabular models in Visual Studio.
Development Challenges in Visual Studio
Overall, Visual Studio has gotten us quite far with tabular model development, but there have been some challenges along the way. If you’ve been doing development within Visual Studio to build tabular models, or Analysis Services models in general, you know you work with a single .bim file. That can create version challenges and general editing delays. Automatic processing in Visual Studio also results in delays as changes are made.
Version Control
Whether you’re changing a single measure or a table, all changes are captured in that one file and it’s easy to have conflicts when multiple developers are working on the same model. That .bim file also captures other details like unique IDs that might not clearly indicate what changed.
Moving Measures
Measures in Visual Studio are entered in the grid table or the actual table definition. If you want to move them to a different table, you might have to copy and paste them cell-by-cell.
Editing DAX Queries
Editing DAX queries for the measures can also be clunky and lack easy formatting for more complex queries.
Editing Table Queries (Reprocessing Demands)
In general, any updates to table query definitions will require reprocessing. Depending on how much data you have, that can bog down your system and take a lot of time.
Where Does Tabular Editor Come into the Picture?
It’s important to note here that Tabular Editor is not a Microsoft product. It is an open source tool created primarily by Daniel Otykier and available at tabulareditor.com or GitHub.
In years past, a third-party tool like Tabular Editor would not have been as easy to integrate into products like Visual Studio and Power BI. However, Microsoft has gradually embraced more and more external tools to round out their stack’s overall capabilities. Visual Studio Code is another good example of this expansion. These days, even the new Power BI update has a ribbon dedicated to external tools, and Tabular Editor is one of those. Instead of a competitor or an underground add-on, we’re seeing resources like Tabular Editor becoming a fully embraced toolset.
Though we’re focusing on some of the high-level best practices and advantages of this tool, there’s already a strong community of resources available for this tool – including input from folks like Marco Russo and Christian Wade.
General Features of Tabular Editor
Below we have listed out some of the high-level advantages of Tabular Editor.
Shortcuts and Filtering
Tabular Editor allows you to easily hide and show certain aspects of the object hierarchy. There are also advanced filtering and finding capabilities.
Advanced Scripting Pane
The advanced scripting page is a powerful feature that lets you use C#-type language to leverage the Tabular Object Model (TOM). It lets you create scripts to generate measures, iterate through your objects, and do many different other tasks efficiently. If you have time to invest in creating those scripts, it really can pay off.
DAX Formatter
Before Tabular Editor, if you were using Visual Studio and wanted your code formatted nicely, you had to view it outside the product and then copy and paste it back in. With the DAX Formatter tool integrated into Tabular Editor, we can click a button and have that formatting done right in line. Additionally, you can click on that button or hit F12 on a DAX command and it will automatically open a browser to dax.guide. That is a nice single source for all the different DAX functions, their parameters, and links to supplementary articles.
Managing the Tree
Tabular Editor also gives you the ability to manage the tree in many ways. For example, you can move multiple measures at one time, drag and drop measures to another table, and easily duplicate them. It can also show dependencies – which is a very useful feature.
You can see the entire hierarchy using this tool, and that’s useful when cleaning up a model. If you want to delete an object, you can see the impact that will have across the whole model.
CTRL + Z
There’s the ability to undo by clicking CTRL + Z, which you don’t have in every tool. Maybe this seems like a small feature, but it’s always good to know that if you make a mistake or if you click the wrong thing, you can undo it.
Best Practices Analyzer
This is a nice way to automatically check your model for places where some pre-defined best-practices may not have been followed. We are going to go into this feature more in another blog focused just on the Best Practices Analyzer.
Managing Models in a Folder Structure
We talked a little about the challenges of handling that single .bim file using just Visual Studio. It does the job, but if you have multiple people trying to edit that file at the same time during a project, you’ll often run into merge conflicts when committing code to a version control repository.
So that is where Tabular Editor (and its ability to save your object model in more granular files) excels. You can break your model into different levels with full control of the granularity and have those sections of JSON stored in separate files. That can get down to having every column, partition, and measure stored in different files in the folder structure.
Benefits of the Folder Structure for Multi-Development Scenarios
How is this folder structure helpful in a multi-development scenario? If your developers are working with the same folder structure but with separate files, it allows them to make independent changes to a couple measures or columns on the table while limiting merge conflicts. The only way you run into merge conflict is if the developers change the same object (in the same spot) at the same time and try to merge it back into source control. Even then, it’s much easier to resolve a merge issue in the 10-20 lines of code from one file versus the thousand lines of code in one of the large .bim files you are ultimately generating.
Because that folder structure is not a .bim file, you can’t deploy it in the same manner. However, when you are ready to deploy, Tabular Editor can take that folder structure and reverse the process to output a .bim for deployment. It does the heavy lifting for you.
Deploying After Using Tabular Editor
One of the nice things about Tabular Editor is that you retain that whole DevOps process. The process of converting the folder structure into a .bim file for deployment can be easily incorporated into a DevOps build process. The remainder of the DevOps process remains almost the same. Whatever your branch policy is, whether you’re using the deployment wizard, or deployment utility command line, the steps look almost the same. The main difference is the value-added features Tabular Editor offers on the build side to keep your overall model cleaner and better.
