Search code examples
sqlvisual-studiossasmdx

Is there a way to search all attributes in SSAS

I know in SQL there is usually some type of information schema table that you can search to find a specific column or table. In SSAS, is there a way to search through all attributes (MDX? Visual Studio Data Tools?).

Solution

  • Thanks user rlobban for providing the link. As Benny Austin shows in the link, you can search the attributes in SSAS:

        --All Attributes
SELECT [CATALOG_NAME] as [DATABASE],
CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],
HIERARCHY_DISPLAY_FOLDER AS [FOLDER],HIERARCHY_CAPTION AS [DIMENSION ATTRIBUTE],
HIERARCHY_IS_VISIBLE AS [VISIBLE]
 FROM $system.MDSchema_hierarchies
WHERE CUBE_NAME  ='Adventure Works'
AND HIERARCHY_ORIGIN=2
ORDER BY [DIMENSION_UNIQUE_NAME]

    This has proven to be a very useful query for me, and can be easily adapted to create more granular queries.