Model Development Performance Improvements for Tabular Models Using Compatibility Level 1200+
It wasn’t until just a few weeks ago, while performing a design assessment on a very large tabular model, that I was finally able to appreciate one of the most underrated improvements made in SSAS Tabular 2016.
This particular client was on SSAS 2014 and had a very mature tabular model consisting of…
- 64 tables
- 284 relationships
- 1193 columns
- 480 measures
…which is quite large as far as tabular models go.
When working with a model this size (and a compatibility level of 1100 or 1103) you need a metric F#$K-ton of patience as even the most minor change, such as renaming an existing DAX measure, takes an unbelievably stupid amount of time. Thankfully this issue was fixed with SSAS 2016 for models created at (or upgraded to) the compatibility level 1200.
Models at the 1200 compatibility level are defined using JSON (which is much less verbose than XML) and leverage the native tabular object model (which is substantially less-complex than the multidimensional object mode). Both of these changes together result in a much smaller model.bim file which can be more efficiently (read: quickly) updated during development.
In smaller models (i.e. fewer tables, columns, measures) this performance bump was hardly noticeable. In larger models, however, the increase in speed alone is enough to justify an upgrade to 2016+.
Here are a few time-measurements using this client’s model: