Answered by:

DMV to list table relationships in a Power Pivot model ?

Question

I have used different queries based on DMVs which let you extract data from a Power Pivot model in order to create a design documentation: list of tables, columns, calculated columns formulas, measures ...etc.
Yet, I have not found the way to extract a list of all tables relationships in the model. Sure enough, I can copy/paste it from the PowerPivot window.

Answers

I do not believe that there is any $system based DMV which returns relationship information.

You can get this information from an SSAS Tabular instance using a DISCOVER_CSDL_METADATA call, but that returns an XML document with the all the metadata and I don't know of any easy way to call that against a PowerPivot model (unless it's deployed to sharepoint)

The following is an example query that I ran against a tabular SSAS instance.

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

<RequestType>DISCOVER_CSDL_METADATA</RequestType>

<Restrictions><RestrictionList><CATALOG_NAME>Adventure Works Tabular</CATALOG_NAME><PERSPECTIVE_NAME>Model</PERSPECTIVE_NAME><VERSION>1.2</VERSION></RestrictionList></Restrictions>

<Properties><PropertyList><Catalog>Adventure Works Tabular</Catalog><FORMAT>Tabular</FORMAT></PropertyList></Properties>

Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.