List dimension members with MDX query

Question

Is there a way to list dimension members without fetching default Measure for each member?

Answer 1

You could SELECT nothing on the opposite axis:

SELECT
  { } on 0,
  { DESCENDANTS([Dimension].[Hierarchy]) } on 1
FROM [Cube]

SELECTing an empty set prevents SSAS from adding the default measure on the opposite axis.

Answer 2

You can access the catalog views which Magnus mentions (which by the way are documented here), from SQL Server 2008 using the following SQL syntax instead of MDX:

SELECT *
  FROM $system.MDSCHEMA_MEMBERS
 WHERE ...

The SQL understood by Analysis Services is limited: There are no joins possible, and the WHERE condition may only contain clauses like [HIERARCHY_UNIQUE_NAME] = '[Date].[Order Date]' connected via AND. GROUP BY and ORDER BY are not supported. But nevertheless, you can query the cube meta data.

Depending on the interface you are using to access Analysis Services there might be some issues, as these metadata are returned in resultset format, not in cellset format.