SSAS: Listing Attribute Relationships

[Darren Gosbell]

Occasionally questions come up about how to extract certain pieces of metadata from Analysis Services. In general all the metadata that you would need on a day to day basis is pretty well covered by the standard schema rowsets. And in SSAS 2008 you can use the system DMVs to get at most of this data.

For example, if you want to get a list of the current user sessions on the server you can do the following...

SELECT * FROM $System.DISCOVER_SESSIONS

...and in SSAS 2005 you can use the same syntax with the DMV() function that is part of ASSP.

call ASSP.DMV("SELECT * FROM $System.DISCOVER_SESSIONS")

But there are some details which can only be accessed through the DISCOVER_XML_METADATA command which returns a hierarchical result similar to what you get when you script an object from SSMS and both the DMV's in SSAS 2008 and the DMV() function in ASSP does not handle this data. Unfortunately the hierarchical information is not the easiest thing to read quickly and is even harder to try to incorporate into a reports.

This is where the DiscoverXmlMetadata() function comes in handy. I wrote this function to use a syntax similar to XPath in order to extract certain nodes. By default the function lists all of the properties of the node it finds which matches the specified path, however you can also add a pipe character (|) after any node and list extra properties that you would like returned

The following call will return a list of all the attribute relationships in the current database:

call assp.DiscoverXmlMetadata("\Database\Dimensions\Dimension|Name\Attributes\Att ribute|Name,Usage\AttributeRelationships\AttributeRelationship")

And if you want to view the relationships for just a single dimension you can use the optional parameter to pass in a predicate in the same form that you would use in an SQL query (provided that you compile the code yourself or use a version greater than the current 1.2 release - as I only recently added this filter parameter)

call assp.DiscoverXmlMetadata("\Database\Dimensions\Dimension|Name\Attributes\Att ribute|Name,Usage\AttributeRelationships\Attribute Relationship"
, "DimensionName='Product'")

More...