DMV to list table relationships in a Power Pivot model ?


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

    But would someone have a query that extracts table relationships ?



    • Edited by Bda75 Thursday, March 13, 2014 3:45 PM
    Thursday, March 13, 2014 3:45 PM


  • 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">


    <CATALOG_NAME>Adventure Works Tabular</CATALOG_NAME>

    <Catalog>Adventure Works Tabular</Catalog>

    </Discover> - please mark correct answers

    Monday, March 17, 2014 2:37 AM