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

    But would someone have a query that extracts table relationships ?

    Regards,

    Bertrand 


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

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>

    </Discover>


    http://darren.gosbell.com - please mark correct answers

    Monday, March 17, 2014 2:37 AM