Querying Analysis Services For Cube Metadata

SSAS DMVs can be very useful in providing metadata information. Ie.

$SYSTEM.MDSCHEMA_CUBES
$SYSTEM.MDSCHEMA_DIMENSIONS
$SYSTEM.MDSCHEMA_FUNCTIONS
$SYSTEM.MDSCHEMA_HIERARCHIES
$SYSTEM.MDSCHEMA_INPUT_DATASOURCES
$SYSTEM.MDSCHEMA_KPIS
$SYSTEM.MDSCHEMA_LEVELS
$SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS
$SYSTEM.MDSCHEMA_MEASUREGROUPS
$SYSTEM.MDSCHEMA_MEASURES
$SYSTEM.MDSCHEMA_MEMBERS
$SYSTEM.MDSCHEMA_PROPERTIES
$SYSTEM.MDSCHEMA_SETS

SQL Server Enterprise Manager's Query editor supports "Select * From " these view. Interestingly, a lot of this information is also available through MDX. For example, here is an easy way of getting a list of all attributes (strictly speaking, hierarchies) and their level names.

WITH
member measures.HierarchyCount as dimensions.count
set HierarchySet as tail([Date].[Year Month Day].[Day],measures.HierarchyCount)
member measures.MyHierarchyNo as rank([Date].[Year Month Day],HierarchySet)-1
member measures.DimensionName as
left(dimensions(measures.MyHierarchyNo).dimension.unique_name,
instr(dimensions(measures.MyHierarchyNo).dimension.unique_name,".")-1)
member measures.MyHierarchy as dimensions(measures.MyHierarchyNo).name
member measures.HierarchyLevels as dimensions(measures.MyHierarchyNo).levels.count
member Measures.Level1Name as
case when Measures.HierarchyLevels>1 then dimensions(measures.MyHierarchyNo).levels(1).name else null end
member Measures.Level2Name as
case when Measures.HierarchyLevels>2 then dimensions(measures.MyHierarchyNo).levels(2).name else null end
member Measures.Level3Name as
case when Measures.HierarchyLevels>3 then dimensions(measures.MyHierarchyNo).levels(3).name else null end
member Measures.Level4Name as
case when Measures.HierarchyLevels>4 then dimensions(measures.MyHierarchyNo).levels(4).name else null end
member Measures.Level5Name as
case when Measures.HierarchyLevels>5 then dimensions(measures.MyHierarchyNo).levels(5).name else null end
member Measures.Level6Name as
case when Measures.HierarchyLevels>6 then dimensions(measures.MyHierarchyNo).levels(6).name else null end
member Measures.Level7Name as
case when Measures.HierarchyLevels>7 then dimensions(measures.MyHierarchyNo).levels(7).name else null end
member Measures.Level8Name as
case when Measures.HierarchyLevels>8 then dimensions(measures.MyHierarchyNo).levels(8).name else null end
SELECT NON EMPTY
{measures.DimensionName
,Measures.MyHierarchy
,Measures.HierarchyLevels
,Measures.Level1Name
,Measures.Level2Name
,Measures.Level3Name
,Measures.Level4Name
,Measures.Level5Name
,Measures.Level6Name
,Measures.Level7Name
,Measures.Level8Name
} ON Columns,
HierarchySet on Rows
FROM EasternMining

Just replace the Date hierarchy name with a hierarchy from your own cube. This hierarchy is used to list out the attributes, so just ensure there are at least a few hundred members. These members are used to drive the query.

Not sure when this would be useful. Perhaps if you only had browser access to a cube, it did not support DMVs, and you needed to see hidden attributes.