Default Members on Analysis Services Tabular models
In multidimensional, we can set the "IsAggregatable" property to False on an attribute hierarchy and choose a Default Member. This has the advantage that end-users will not be able to do multiselects on the hierarchy, and additionally the "All" member will disappear from MDX clients. This is very useful when building functionality for Currency handling, Budget Version selection, etc.
We can simulate the same functionality in Tabular using DAX (ISFILTERED), but this is not optimal for several reasons:
- The client tool will still display the hierarchy as if multiselect is possible
- There will still be an "All" member on the hierarchy when browsed from an MDX client
- The same DAX logic must be created and maintained on all measures intended to be used with the non-aggregatable attribute
- The formula engine seems to become slow, when implementing many such measures
- In Compatibility Level 1100 or 1103, this could be handled through the MDX Script (although unsupported and only working with MDX clients). This option is gone in Compatibility Level 1200.
Upvotes: 5<=-=Oct 4 2016 2:46PM=-=>
Thank you for the suggestion, Otykier.
We have a corresponding item on our backlog for the next release of SQL Server, but no promises. We’ll see if we can fit this in, but it may not make it given that there are other higher priority items.
The Analysis Services Team
+1 … Non-aggregatable hierarchies and snapshot fact tables are much more cumbersome to query in tabular vs. multidimensional. Adding in the “IsAggregatable” flag and allowing for default members would be a huge improvement to the product imho.<=-=Dec 20 2017 5:08AM=-=>
Anything new regarding this ‘Default Member’ feature?
Martin A commented
I fully agree with this suggestion and below comments.
The lack of Default Member/IsAggregetable-functionality in SSAS tabular can in itself be a showstopper for many organizations to be able to take the step and migrate from multi-dimensional solutions. The available work-arounds are not good enough either - too many drawbacks (as clearly pointed out in suggestion).
Hello - C'mon guys... clearly you want multidimensional to die. I think a lot of us will come on board the tabular express, but you need to keep working to address all of those things that make it almost impossible to do so. Default members are pretty important feature of MDX that needs to be replicated in tabular. Give us a workaround. Give us something.
Adding the “IsAggregatable” flag to tabular model would be a very useful for the product because it was previously in multidimensional model and when you migrate your cubes from multidimensional model to tabular model you a expecting to have it there.
In other way it is not an upgrade but downgrade.
Please add it.