SSAS MemberNamesUnique Property
Reposted from Thomas Ivarsson blog with the author's permission.
In the SSAS newsgroup there was a question about removing the & character that will appear when you work with attributes in Management Studio/directly in MDX.
The reason why the & character appears is about the MemberNamesUnique-property of the dimension attribute that you can find in BIDS here. That property is set to false as a default and that is why you will see the & before the attribute in the MDX code.
I have choosed to use the Sales Reason dimension in the Adventure Works demo cube and the Sales Reason Type attribute since it only has a few members.
In the Adventure Works cube I have changed that property to false in the two last examples when you drag an attribute member to the MDX query window in Management Studio.
The two first examples(1-2) shows the default behaviour, MemberNamesUnique = False. The two last(3-4) what will happen if you set that to True.
1. [Sales Reason].[Sales Reason Type].&[Promotion]
2. [Sales Reason].[Sales Reason Type].&[Other]
3. [Sales Reason].[Sales Reason Type].[Marketing]
4. [Sales Reason].[Sales Reason Type].[Other]
I would recommend you to keep the default behaviour with that property set to False because it is harder to have unique member names than key. City names in Uinited States are not unique on the country level.
Thomas Ivarsson has been working with the MS BI platform since SQL Server 7 in 1999. Presently he is working in the telecom industry in Sweden, with a data warehouse based on SQL Server 2005. From 1999 to 2007 he worked as a consultant also on the three SQL Server BI platforms. During the latest years he has spent most of time on SSAS, Reporting Services, ProClarity and Performance Point. He also has several years experience of the ETL process with DTS and SSIS. During 2008 and 2009 he has been working with introducing data mining in his daily business to see patterns in a service network behaviours. His blog can be found here: http://thomasianalytics.spaces.live.com