18

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

Žygimantas
  • 725
  • 3
  • 7
  • 13

4 Answers4

33

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.

Tullo_x86
  • 2,573
  • 25
  • 27
  • 1
    This is a great way to populate an SSRS parameter without making the user wait for the primary query to reload. – Justin Lange Apr 05 '13 at 17:38
  • 3
    You could even make this slightly shorter: The `null` is redundant and will be removed by the MDX parser anyway, resulting in an empty set. – FrankPl Jul 30 '13 at 08:07
  • @FrankPI, when you don't select an opposite axis in SSAS, it adds the default measure. This query returns a different result - just the axis you query for, and none of the measures. The `null` is most certainly *not* redundant. – Tullo_x86 Aug 06 '13 at 04:44
  • 2
    @Tullo The `null` is redundant. But the empty set on the columns axis is not. You can write (and this is more clear from my point of view) `SELECT { } on 0, ...`. – FrankPl Jun 18 '14 at 08:19
1

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.

FrankPl
  • 13,205
  • 2
  • 14
  • 40
1

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.

Zoltan Grose
  • 51
  • 6
-1

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.

Magnus Smith
  • 5,895
  • 7
  • 43
  • 64
  • do you have a link somewhere that explains how to do it? or could you post a code example? – Ahmed Jul 26 '11 at 14:01
  • I don't know what language you're using! I used classic ASP to do this years ago. For .NET try starting here http://msdn.microsoft.com/en-us/library/microsoft.analysisservices.adomdclient.cubedef.aspx – Magnus Smith Aug 07 '11 at 17:16
  • This answer does not correspond to the question. – Matthias Aug 09 '18 at 12:30