Retrieving Cube Metadata in Reports
An interesting question popped up on the discussion list the other day about how to retrieve a list of the cube measures so the report author can display them in a report parameter.
Solution 1: If you just need a list of measures but not the measure metadata, such as display folder, etc., you can use the following (not trivial, ok hacky) query, which I borrowed from my esteemed colleague MVP and MDX guru Chris Webb:
WITH SET MYSET AS HEAD([Date].[Date].[Date].MEMBERS, COUNT(MEASURES.ALLMEMBERS)-1)
MEMBER MEASURES.MeasureUniqueName AS MEASURES.ALLMEMBERS.ITEM(RANK([Date].[Date].CURRENTMEMBER, MYSET)-1).UNIQUENAME
MEMBER MEASURES.MeasureDisplayName AS MEASURES.ALLMEMBERS.ITEM(RANK([Date].[Date].CURRENTMEMBER, MYSET)-1).NAME
SELECT {MEASURES.MeasureUniqueName, MEASURES.MeasureDisplayName} ON 0,
MYSET ON 1
FROM [Adventure Works]
Solution 2: This approach is especially useful if you need the measure properties. In this case, you must use the OLE DB Provider for Analysis Services instead of the SSRS Analysis Services Provider (the one that gives you the nice MDX Designer). This approach uses the MDSCHEMA_MEASURES rowset, which is one of the built-in SSAS schema rowsets. Darren Gosbell provides a great overview of the SSAS rowsets here and Vidas Matelis gives more insights here. Thanks to the schema rowsets, getting a list of measures can’t simpler.
SELECT * from $System.MDSCHEMA_MEASURES
The user will require Read Definition rights (on Role Properties General tab, check Read Definition) to obtain the metadata. If the user doesn’t have this right, an empty dataset will be returned.
There are of course additional schema rowsets, such as MDSCHEMA_KPIS if you want to get a list of all KPIs or DBSCHEMA_TABLES in case you want to discover the cube dimensions and measure groups. BOL provides a full list here. Aren’t SSAS schema rowsets cool?
