How to get the name of invisible attributes and user defined Hierarchies using MDX?

Question

I have a cube with some dimensions. There I have a dimension 'Product' which has some attributes and user defined hierarchies hidden. I do not know which attributes are hidden. Is there a way to write an MDX to get the invisible attributes and user defined hierarchies? I can get the name by other ways. But I want to know the way to get using MDX.

Answer 1

http://msdn.microsoft.com/en-us/library/ms145613.aspx gives an example query that reveals a member property:

WITH MEMBER [Measures].[Product List Price] AS
[Product].[Product].CurrentMember.Properties("List Price")
SELECT 
   [Measures].[Product List Price] on COLUMNS,
   [Product].[Product].MEMBERS  ON Rows
FROM [Adventure Works]

I cannot test it myself right now, but I assume that you could also write .Properties(0) or .Properties(1) to refer to properties by index, since you don't know the names. I'm not sure if there is a way to then discover the property name from the resulting cellset or not, sorry.

Answer 2

I'd start with looking into the DMVs for this, as you're really after metadata, not data.

http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/

They look like SQL, but run in the MDX window of SQL Management Studio, so will also run in an MSOLAP connection.

SELECT * FROM $system.mdschema_properties

That looks to be the query, a full list of members and a column identifying which are visible.

See how that works out for you.