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

The way I used to query without data was:

WITH MEMBER Measures.Amount AS 0 
SELECT { 
   [-dimensionName-].[-hierachyName-].Members 
} ON COLUMNS 
FROM [-cubeName-]

But after watching BIDS work in SQL Profiler I learned about

SELECT { 
   [-dimensionName-].[-hierachyName-].Members 
} ON COLUMNS 
FROM [$-dimensionName-]

e.g.

SELECT { Organization.Organization.Members } ON COLUMNS FROM [$Organization]

Not sure if there are unintended side-effects of this route but if you just want to dump the contents a hierarchy without worrying about data, it's another option.

Answer 3

You can use the ADOMD Catalog object to interrogate a cube, and find out what measures/dimensions it has etc. This does not involved MDX at all.

Answer 4

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.