Thursday, July 15, 2010

Querying Analysis Services For Cube Metadata

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


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.

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
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
} 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.

1 comment:

Sam Kane said...

Here are this and some other articles on SSAS Dynamic Management Views: