CODEWITHSUNDEEP
mdxssas
Žygimantas
Žygimantas

Reputation: 725

List dimension members with MDX query

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

Upvotes: 18

Views: 34057

Answers (4)

Zoltan Grose
Zoltan Grose

Reputation: 51

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.

Upvotes: 1

Tullo_x86
Tullo_x86

Reputation: 2673

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.

Upvotes: 33

FrankPl
FrankPl

Reputation: 13315

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.

Upvotes: 1

Magnus Smith
Magnus Smith

Reputation: 5963

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.

Upvotes: -1

Related Questions