This is a blog-post in a series of articles about the new Compatibility Level 1200 of SSAS Tabular Models in SQL Server 2016. In this series, Daniel shares his experiences and recommendations from working with Compatibility Level 1200 in real-life scenarios.
Update April 25th 2017
SSDT and Tabular Editor has evolved a lot since the writeup of this article, so please note that a lot of the information here is no longer valid. For the latest updates of Tabular Editor, visit https://github.com/otykier/TabularEditor or follow the author on Twitter: @DOtykier.
If you have ever worked with a real-life SSAS Tabular project, you will inevitably have stumbled upon several interesting “features” of SSDT, when developing your Tabular Model.
Random Visual Studio crashes, sluggish GUI, editing measures in a messy grid and waiting impatiently for calculations to complete, is all part of the experience of developing Tabular Models. It is unfortunately something that has stuck to Tabular Models, ever since the first versions of PowerPivot in Excel. As of the newest release (version 16.3) of SSDT and even using SQL Server 2016’s new Compatibility Level 1200, most of this is still true. However, one new addition to SSDT in the 16.3 release, is a little tool window called “Tabular Model Explorer”:
While the Tabular Model Explorer definitely makes it easier to locate metadata objects in the model, there are still a lot of stuff we cannot do using the window. For example, the tool does not show Display Folders. This is not a problem for small projects with a limited number of measures, but in real life, our experience is that Tabular Models often have several tens and maybe even hundreds of measures. These measures are often put into just one table, and then organized by Display Folders to obtain a nice hierarchical structure in client tools.
To be really useful, a Tabular Model Explorer should be able to show this folder structure as well, so that the developer sees the metadata objects structured in exactly the same way as the end-user. Continuing this notion of having the Explorer show the model as it would appear to end-users, one would also expect the Explorer to be able to simulate perspectives and translations, which it also does not.
Furthermore, many of the objects in the Tabular Model Explorer cannot be interacted with. They are just there for visual reference. Hopefully, we will see a lot of improvements to this tool in the future.
Enter Tabular Editor
Thanks to the new Tabular Object Model in Compatibility Level 1200, it turns out to be very easy to modify the metadata of a Tabular model. Through the Tabular API, these changes can be done either in a Model.bim file, or directly to a Tabular database that has been deployed to a server. In fact, this is exactly what we did using PowerShell in last months blog post.
However, I decided to take this one step further. The Tabular Editor is a work-in-progress tool that solves many of the current shortcomings of the SSDT developer experience. It is a standalone executable, that can load a Model.bim file (Compatibility Level 1200) or connect directly to a deployed database. Once the model has been loaded, you will see a tree-like structure showing you most of the metadata objects in your model, not unlike the Tabular Model Explorer in SSDT:
By default, columns, measures and hierarchies are structured into Display Folders, just as they would appear to an end-user connecting to the model through Excel, Power BI or Report Builder. The Display Folders can of course be turned off, if you prefer to see a plain listing of the objects instead. Similarly, you can choose to show/hide hidden objects, columns, measures and hierarchies individually. Lastly, you can filter the objects by name.
The first release of the tool, available for download below, allows you to edit many, but not all, types of objects in a Tabular Model. Therefore, SSDT cannot be completely discarded (yet) for developing Tabular Models. For now, think of the Tabular Editor as a supplement to SSDT. Tabular Editor supports editing names, descriptions, visibility, perspective memberships, translations and display folders (where applicable), for the following objects:
- Columns and calculated columns
In addition, you can edit expressions and format strings for measures and calculated columns. Structural changes such as adding or removing tables, hierarchies and relationships are not supported yet, but will become available in a future release.
The primary strength of the Tabular Editor, compared to SSDT and the Tabular Model Explorer, is the ease of which the tool lets you organize objects in Display Folders. Especially in models containing translations. In the tree-view, you can multiselect objects and simply drag and drop them between folders.
In addition, a right-click context menu has been added to the tree, allowing you to easily add or delete measures and calculated columns, etc.
When selecting one or more measures, you can use the duplicate right-click option to quickly copy the selected objects, retaining their existing expressions, translations, format strings, perspective memberships, etc.:
Speaking of translations and perspectives, the property grid on the right side of the application, gives you an easy way to see which perspectives a given object is visible in, along with all translations defined for the object (for the object’s description, display folder and name).
Once again, multi-selecting several objects in the tree, lets you change these properties for all the selected objects at once. If you want to know how your model appears to end-users through a specific perspective and translation, simply choose the perspective and translation from the dropdown boxes in the top right corner of the screen, and the tree will be immediately updated to reflect this.
Tabular Editor lets you deploy any changes you have made to a model directly to a database. To do this, first open the dropdown next to the “Deploy” button, and choose “New deployment target…”. This opens a window in which you specify an Analysis Services server name. When you click “OK”, the server will appear in the dropdown list, and the “Deploy” button will become enabled.
Before you click “Deploy”, check that the Database ID of the model is correct, since any existing database with the same ID on that server, will be overwritten with your changes (Tabular Editor prompts you before deploying).
After the changes have been deployed, Tabular Editor will let you know if there are any errors in your model, such as invalid expression syntax, references to unknown objects, etc.
A word of warning: When using Tabular Editor to edit a workspace database directly, remember to update the Model.bim file of the SSDT project. Otherwise, your changes to the workspace database will be rolled back, once the Model.bim file is loaded in SSDT. By clicking “Save .bim” inside the Tabular Editor, you can save your changes to a new Model.bim file, or overwrite the one you had in your SSDT project (but consider taking a backup first). Having worked a lot with the Tabular Editor, it turns out that forgetting to save the Model.bim file from inside the tool is something that happens from time to time. For this reason, every time the “Deploy” button is clicked, Tabular Editor automatically saves a zipped .bim file in the current user’s AppData\Roaming\Tabular Editor folder, so that you can easily recreate the changes you have made in Tabular Editor, in case you forget to update the Model.bim file of your SSDT project.
Known issues and roadmap
The Tabular Editor tool is currently at a very early stage, so please read the disclaimer below before you use the tool. I would really like to hear your feedback on usability, bugs and useful features. In future releases of the tool, I expect to include features such as:
- DAX syntax highlighting and intellisense when editing expressions
- Tools to more easily manage translations and perspectives (for example to apply the base display folder strings of all objects to a translation)
- Modifying model structure – adding/removing tables, hierarchies, relationships
- Processing the model and getting calculation results from the server
Permission to use and distribute this software freely is hereby granted. This software is provided ”as-is”, without warranty of any kind. The author can not be held responsible for any claim, damages or other liability resulting from the use of this software. The software includes components under the following licenses, which the author also can not be held responsible for in any way or form: TreeViewAdv 1.7.0 BSD 2-clause, Copyright (c) 2009 by Andrey Gliznetsov. FastColoredTextBox 2.16.11 LGPLv3, Copyright (c) 2014 by Pavel Torgashov.
To keep the file size to a minimum, Tabular Editor requires that you have the Microsoft.AnalysisServices assemblies registered on the machine where you execute the tool. This should not be a problem when executing Tabular Editor on the same machine as SSDT or SQL Server Analysis Services is installed.
By downloading the Tabular Editor you accept the disclaimer above.