Benefits of Using Tabular Editor 2 for Working with Tabular Data Models
July 8, 2024
![Benefits of Using Tabular Editor 2 for Working with Tabular Data Models](https://www.teamscs.com/hubfs/Blog%20Images/a-bunch-of-cubes-that-are-on-a-table.jpeg)
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
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.