3

Assume that a cube named MyCube has 2 dimensions, and I want to build an MDX SELECT statement without knowing the name of the dimensions but I have a list of elements with one element from each dimension:

SELECT [ELEM X from first dimension] ON 0,  [ELEM Y from second dimension] ON 1
FROM [MyCube]

Alternatively is there a function that will return the list of all dimensions and that I can use to built my MDX?

Roland
  • 7,525
  • 13
  • 61
  • 124
  • 1
    I don't know enough about MDX to answer this, but I think [this thread](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/75c3601c-81de-40b2-b5d3-5f0993a043e4/mdx-query-to-get-the-all-dimension-names-in-a-cube?forum=sqlanalysisservices) may be helpful. Looks like you could maybe just select everything (`*`) and then filter to get just the dimensions. – Max von Hippel Jul 08 '17 at 00:49

1 Answers1

1

Yes, you can query a dimension without knowing its exact name. You don't even have to know the names of any member, as this example shows. Just be careful in case you are returned a grid of 10000 x 10000 results!

SELECT 
{Dimensions(0).Levels(0).members} ON ROWS, 
{Dimensions(1).Levels(0).members} ON COLUMNS 
FROM [Sales]

It may also be useful to use .members(0) or [Measures].allMembers to specify items where the name is not known.

Magnus Smith
  • 5,895
  • 7
  • 43
  • 64