With TM1 you'll have more time for analysis and other valuable activities without having to give up Excel. Cubewise will show you how.
We all know that attributes are a very useful feature of TM1 with many uses for lookups in modeling as well as providing context for reporting. In a recent post, we talked about the Caption attribute as a potential default attribute for all dimensions and how the attribute is linked to the intrinsic MEMBER_CAPTION MDX member property.
So what are member properties? Essentially “member properties” are element attributes. Member property is just the industry standard terminology used by Oracle Essbase and Microsoft SSAS among others. As TM1 was invented first (prior to the existence of MDX or ODBO standards), its own pre-existing terminology has survived.
MDX and TM1
TM1 has supported MDX for a long time, but TM1’s own “dialect” of MDX can be a little quirky. Compared to standard MDX it’s very concise, but it is also ambiguous which can be a problem. Consider a dimension “Product” which has a text attribute called “Brand” assigned to each leaf product. In TM1’s implementation of MDX this would be referred to as:
[Product].[Brand] (or to be more correct to account for hierarchies as [Product].[Product].[Brand])
However, this can be problematic due to ambiguity, as from the statement above we could also be referring to an element called “Brand” or a subset called “Brand”. Where there is a name collision and the “TM1 shorthand” MDX is used then TM1 will first check for an element, then for an attribute (member property), and finally for a subset.
Of course, TM1 doesn’t just speak its own dialect of MDX, it also understands standard MDX. Using Standard MDX resolves the ambiguity. If we use standard MDX then it is clear we are referring to the Brand attribute:
Ideally to avoid confusion and make our code transportable we should always use standard MDX. However, there are two potential issues:
- Member properties are typed as string
- The potential for name conflict between implicit member properties and TM1 attributes
Member properties are implicitly typed as string. This can create a problem as TM1 both supports numeric attributes but doesn’t support re-typing a member property as numeric. To work around this limitation in the standard MDX we can use the TM1 shorthand, but a better and more universal solution is to revert to a data query:
This is again unambiguous as to what is being referred to and the numeric or string type of the cell will be resolved automatically.
The second case of a name conflict with intrinsic member properties can only be dealt with by developers being aware of the problem during the design and implementation phase. (Follow the link to the Microsoft MDX documentation to see a full listing of implicit member properties). TM1 attributes are by definition custom member properties. TM1 won’t prevent us creating an attribute with the same name as an implicit property, e.g. “DESCRIPTION”, “MEMBER_TYPE” or “MEMBER_NAME”. However, if we do this it is extremely problematic as when using the standard MDX member property notation the intrinsic property will have priority over TM1 attributes of the same name. Thus, if we created an attribute called “MEMBER_NAME” and set values against elements and then tried to filter members based on a value, our query would return empty or an unexpected result as the intrinsic MEMBER_NAME property is not writeable and is fixed to the element principal name.
We can, of course, access our custom “MEMBER_NAME” property via querying the ElementAttributes cube directly as explained above. However, a much better solution is to avoid such name conflicts in the first place!
Some intrinsic member properties can be set. We previously discussed how the “Caption” attribute in TM1 will set the “MEMBER_CAPTION” MDX property. Another member property which allows itself to be set by TM1 attribute is “DESCRIPTION”, in which case values of the attribute of the same name are used (of course not case sensitive for TM1). In fact, this is the one exception to the rule of never having conflicting names between intrinsic member properties and custom TM1 attributes!
And now for some summary points to remember.
- Be aware of intrinsic member properties and avoid giving TM1 attributes the same names!
- Although standard MDX member property syntax is the preferred way of addressing TM1 attribute values, due to TM1 supporting numeric attributes and the potential for name conflicts, the only 100% reliable way to address a TM1 attribute in MDX is via a cube query e.g.