SSAS Tabular - Do "isUnique", "isKey" properties improve performance?

43 Views Asked by At

Columns have a Is Unique and Is Key property. I'm trying to identify if these provide any kind of performance improvement during query time and if there is any performance overhead with setting these.

I've not been able to find any documentation regarding the benefits (pros and cons) of using these fields.

enter image description here https://learn.microsoft.com/en-us/analysis-services/tmsl/tables-object-tmsl?view=asallproducts-allversions

1

There are 1 best solutions below

0
TheRizza On

I've never heard of anyone tweaking these for performance. If you find anything helps, share it, but generally speaking, your best performance gains are not at this level, but how you have the data modeled, your DAX, and your queries and visuals. You could stumble on a new performance technique, but if SQLBI hasn't blogged about it, I doubt you are going to find a new technique that they haven't discovered. I haven't looked, but I assume you searched first and didn't find anything.

Reducing memory size of the model is worth doing. Connect to your model from DAX Studio and go to Advanced > View Metrics and check out table and column sizes. Row reduction and column reduction = memory reduction, and always helps performance. I also have seen cases if you change the sort order of the table, you can get drastically better compression and memory usage.

Another good start for performance tuning is the Best Practice Analyzer from Tabular Editor. Most of their Best Practices are about naming and supportability issues, but I believe they have some tuning recommendations too.

Then I would find problems areas (visuals/queries/etc.) and tune those. Using Power BI Desktop's Performance Analyzer in combination with DAX Studio is where to start there.

I would stick to these conventional areas of performance tuning and stay away from the exotic options you are looking at. If you change something off the beaten path, you could run into strange issues later and have support issues because no one else messes with these options.