Benefits of Using Tabular Editor 2 for Working with Tabular Data Models

July 8, 2024

7:10

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?

What is Tabular Editor 2?

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.

Benefits of Tabular Editor 2

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:

Offline capability and can be used without a workspace server
Edit a BIM file without accessing data from the model
Make quick changes to the BIM file, without automatic processing
Advanced shortcuts and filtering
Ability to create and use custom scripts
Create folders and calculation groups to organize measures
Best Practice Editor - set rules and automatically flag rule violations

Tabular Editor 2 vs. Tabular Editor 3

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:

DAX experience - write and execute DAX queries, use scriptsm, view the DAX call tree and more.
UI and organizational improvements - customize UI and set advanced configurations.
Productivity improvements - queue background refresh operations, preview tables with infinite scrolling, and other productivity tweaks.

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.

Tabular 2 Best Practices

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.

Best Practice Rules

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.

DAX Expressions

Use the DIVIDE function for division
Avoid using the IFERROR function
Column references should be fully qualified
Measure references should be unqualified
Measures should not be direct references of other measures
No two measures should have the same definition
Use the TREATAS function instead of
INTERSECT for virtual relationships

Error Prevention

Data columns must have a source column
Calculated columns must have an expression

Formatting

Add data category for columns
Do not summarize numeric columns
First letter of objects must be capitalized
Hide fact table columns
Hide foreign keys
Mark primary keys
Month (as a string) must be sorted
Objects should not start or end with a space
Percentages should be formatted with thousands separators and one decimal
Format flag columns as Yes/No value strings
Provide format string for “Date” columns
Provide format string for “Month” columns
Provide format string for measures
Relationship columns should be of integer data type
Whole numbers should be formatted with thousands separators and no decimals

Maintenance

Ensure tables have relationships
Objects with no description
Remove data sources not referenced by any partitions
Remove roles with no members
Remove unnecessary columns
Remove unnecessary measures

Naming Conventions

Use CamelCase for hidden columns
Partition name should match table name for single partition tables
Object names must not contain special characters

Performance

Avoid bi-directional relationships against high-cardinality columns *
Avoid excessive bi-directional or many-to-many relationships
Avoid snowflake schema architecture
Do not use floating point data types
Large tables should be partitioned *
Limit row level security (RLS) logic
Many-to-many relationships should be single direction
Minimize Power Query transformations
Model should have a date table
Model using Direct Query and no aggregations
Reduce number of calculated columns
Reduce usage of calculated columns that use the RELATED function
Reduce usage of calculated tables
Reduce usage of long-length columns with high cardinality **
Remove auto-date table
Remove redundant columns in related tables
Set IsAvailableInMdx to false on non-attribute columns
Split date and time ***
Date/calendar tables should be marked as a date table
Unpivot pivoted (month) data

* In order to run this rule, you must first run this script as documented here.

** In order to run this rule, you must first run this script in the same fashion as the note above.

*** In order to run this rule, you must first run this script in the same fashion as the note above.

Note: Each of the asterisked (*) rules requires Tabular Editor version 2.12.1 or higher.

How to Load the Rules

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:

You can disable any of the above rules that may not apply. Within Tabular Editor 2, go to the “Manage Best Practices Rules” window and simply uncheck the rule to disable it. You can also disable rules for particular objects when in the Best Practice Analyzer.
You can also set severity levels per rule. This won’t change how Best Practice Analyzer flags violations, but it will sort those violations so your team can prioritize manual follow-up.

How it Works

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

Final Thoughts

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.

Looking for additional support, training, and staff augmentation?

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.

Tags: