SSAS: Listing Attribute Relationships

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\Attribute|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\Attribute|Name,Usage\AttributeRelationships\AttributeRelationship"
, "DimensionName='Product'")

Print | posted on Wednesday, November 26, 2008 10:58 PM

Comments on this post

# re: SSAS: Listing Attribute Relationships

Requesting Gravatar...

Hi
Try to get the join structure from a dataSourceView, i had some problems with the function call
assp.DiscoverXmlMetadata("\Database\DataSourceViews\DataSourceView\Schema\xs:schema\xs:element\xs:complexType\xs:choice\xs:element|Name")

Any ideas, how to pick it up ?
Reg. Jakob Gehl
Left by Jakob Gehl on Feb 12, 2009 7:30 PM

# re: SSAS: Listing Attribute Relationships

Requesting Gravatar...
Sorry, but most of the rest of the XMLA has it's data stored in elements and I built the function to extract elements not attributes. Unfortunately the Schema has a dataset serialized inside and that has all it's information in attributes. There is no way of extracting information from elements with the current function and there are some assumptions in the code that will make it hard to add this capability.
Left by Darren Gosbell on Feb 17, 2009 6:41 AM

# re: SSAS: Listing Attribute Relationships

Requesting Gravatar...
I've been having trouble with these for a while. Good thing I chanced upon your post. Thanks!
Left by Best Time to Cruise Alaska on May 10, 2010 8:25 PM

# re: SSAS: Listing Attribute Relationships

Requesting Gravatar...
Thanks. I've heard a lot about DiscoverXmlMetadata. It seems to be pretty useful! I'll keep it in mind.
Left by Mp3hounddog on Jan 03, 2011 10:15 PM

# re: SSAS: Listing Attribute Relationships

Requesting Gravatar...
Here are this and some other articles on Attribute Relationships:

http://ssas-wiki.com/w/Articles#Attribute_Relationships

Left by Sam Kane on Mar 19, 2011 12:54 AM

Your comment:

 (will show your gravatar)