MDX or DMX Query to get DimensionId

Question

Id like a query to get the DimensionId from an SSAS database, which is required when scripting the processing of a dimension using XMLA script: The following query selects the DIMENSION_CAPTION which is not the correct name that should go in an XMLA script to process the dimension. What I need is the DimensionId.

SELECT DISTINCT [CATALOG_NAME] as [DATABASE],
      DIMENSION_CAPTION AS [DIMENSION]
 FROM $system.MDSchema_Dimensions
WHERE (   [CUBE_NAME] = 'myCube1'
         OR [CUBE_NAME] = 'myCube2'
      ) 
  AND DIMENSION_CAPTION <> 'Measures' 
ORDER BY DIMENSION_CAPTION

Is there a way I can select all the DimensionId's used by a set of cubes so I can use it to create an XMLA script to process them?

Answer 1

I think the most convenient way to get it for a Multidimensional model is to install ASSP and use the following query that leverages the XmlaDiscover function:

CALL ASSP.discoverXmlMetaData("\Database\Dimensions\Dimension");

For a Tabular model, the easiest way is to just use this built-in DMV:

SELECT TABLE_ID
FROM $system.DISCOVER_STORAGE_TABLES
WHERE mid(TABLE_ID,2,1)<>'$'

Excluding H$... and R$... "TABLE_ID" values excludes hierarchies and relationships, etc. I don't think it's allowed to have a $ as a character in a table ID so I think that's a safe filter.

Or if you would prefer to get a list of dimensions in C#, then you could use AMO to accomplish that.