Here’s a “fun” exercise: ask any database developer how they like modeling in Visual Studio. To put it charitably, VS has a lot of limitations. There has to be a better way…and there is.
Tabular Editor 2 is a fantastic tool for working with tabular data models at scale. It can be used with tabular models for SQL Server Analysis Services, Azure Analysis Services or Power BI Premium. We’ll explore how to use this powerful tool and how to set best practice rules that automatically flag violations.
Note: This resource is intended for developers who actively use VS for work with tabular data models. For business owners or managers looking to improve their organizations’ data reporting, check out our blog: Why Do You Need Microsoft Consulting Services?
Tabular Editor 2 is a powerful, open-source editor that allows you to more easily work with tabular data models. The biggest advantage of Tabular Editor 2 is that you can edit a BIM file without accessing data from the model AND you can edit in offline mode. Additionally, multiple BIM files can be accessed at once.
Tabular Editor 2 offers a few major benefits over Visual Studio. Automatic processing in Visual Studio often creates delays and can interfere with collaborative creation - as it often creates different versions which then create conflicts. Additionally, updates to table query definitions trigger reprocessing which causes further delays. These delays can also often cause freezes or crashes.
Tabular Editor 2 resolves both of these issues by allowing editing of a BIM file without accessing data. This means you can create your reporting and modeling structures, without the system constantly processing data. The key here is speed - you can work on creating modeling structures without constantly having to wait for the system to process and save.
That’s the biggest advantage overall of Tabular Editor 2, but there are other useful features designed for developers that make it even better. The advanced scripting functionality allows you to create and deploy custom scripts. Additionally, there are a few quality of life organizational tools that make a big difference.
Rundown of benefits in Tabular Editor 2:
Tabular Editor 3 is the commercial version of Tabular Editor 2. It offers the same features as the open source version, in addition to some more powerful productivity features. For developers who regularly work within a team environment and/or need even more customization options, Tabular Editor 3 is worth the cost.
Additional features in Tabular Editor 3:
If you’re new to Tabular Editor, start with the open source version to discover how it works and then consider upgrading to Tabular Editor 3.
A core feature of Tabular Editor is its ability to analyze a set of rules against the metadata that it ingests, and report back on violations it finds. Microsoft has complied a list of rules that it considers best practices, and that Tabular Editor allows. Below, you’ll find the full list of rules as well as how to load the rules into Tabular Editor.
The following rules are taken from Microsoft’s Power BI Blog. We recommend using these rules, as they will be effective for a vast majority of organizations and applications. These will also help ensure smooth syncing.
Once you have Tabular Editor 2 installed, updating your rules is easy.
1. Launch Tabular Editor
2. Open Tabular Editor and run the following code in the Advanced Scripting window:
System.Net.WebClient w = new System.Net.WebClient();
string path = System.Environment.GetFolderPath(System.Environment.SpecialFolder.LocalApplicationData);
string url = "https://raw.githubusercontent.com/microsoft/Analysis-Services/master/BestPracticeRules/BPARules.json";
string downloadLoc = path+@"\TabularEditor\BPARules.json";
w.DownloadFile(url, downloadLoc);
3. Close and relaunch Tabular Editor.
4. Start modeling!
Some Tips:
Once you’ve updated these rules in Tabular Editor 2, you can activate the Best Practice Analyzer. This will automatically review your metadata and flag errors. Depending on the error, it can be an easy fix or a more complex one.
For a majority of errors, you can right-click on the object and use the “Generate fix script” functionality within Best Practice Analyzer to generate a script that will resolve the violation. In other cases, a more in-depth fix may be required. You can find a detailed breakdown of error types here: Tabular Editor 2: Command Line output and Exit Codes
Tabular Editor 2 brings much-needed speed and organizational improvements to modeling data.
For a more detailed plan regarding your databases, contact a data analyst at SCS to discuss your project.
Superior Consulting Services (SCS) is a Microsoft-centric technology firm with one goal: helping you solve your business problems. Whether recommending a technical direction, architecting a solution or turning a design into reality, we work to aid your brand and ensure your success. For data development teams, our consultants serve as additional members of your staff to fill a limited-term need for a particular skill set or to provide additional bandwidth to meet a temporary increase in demand. Explore our services or contact us for more information.
Superior Consulting Services (SCS) is a Microsoft-centric technology firm providing innovative solutions that enable our clients to solve business problems. We offer full-scale data modeling, analytics, and custom app development.