DISCOVER_CALC_DEPENDENCY Rowset

Reports on the dependencies between calculations and on the objects referenced in those calculations. You can use this information in a client application to report on problems with complex formulas, or to warn when related objects are deleted or modified. You can also use the rowset to extract the DAX expressions used in measures or calculated columns.

Applies to: tabular models

Rowset Columns

The DISCOVER_CALC_DEPENDENCY rowset contains the following columns. The table also specifies the data type, indicates whether the column can be restricted to limit the rows that are returned, and provides a description of each column.

Example

Basic syntax

The following query is a simple DMV query that returns values for all of the columns in this rowset, using the default database on the current connection. You can run this query in an MDX query window and view its results in Management Studio. You can also follow the techniques described in Querying PowerPivot DMVs from Excel to view DMV query results in Excel.

SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY

Sort the results

Add an ORDER BY to sort the rowset by table or another column.

SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY ORDER BY [TABLE] ASC

Filter using a WHERE clause

The next query shows how to add a restriction using the WHERE clause. The following columns can be used as query filters in a WHERE clause: Database_Name, Object_Type, and Query.

SELECT * From $SYSTEM.DISCOVER_CALC_DEPENDENCY WHERE OBJECT_TYPE = 'RELATIONSHIP' OR OBJECT_TYPE = 'ACTIVE_RELATIONSHIP'

Filter on measures and calculated columns to view underlying DAX expressions

In this query, you can select just the measure or calculated column, and then view the DAX expression used in the calculation. The EXPRESSION column contains the DAX expressions. If you are using DISCOVER_CALC_DEPENDENCY to extract DAX expression used in the model, this query is sufficient for that purpose. It returns all expressions used in the model, in ascending order.

SELECT * From $SYSTEM.DISCOVER_CALC_DEPENDENCY WHERE OBJECT_TYPE = 'MEASURE' OR OBJECT_TYPE = 'CALC_COLUMN' ORDER BY [EXPRESSION] ASC

Filter using QUERY

Using the QUERY restriction, you can provide a DAX query to view all of the objects used in that query. Consider a simple query like ‘Evaluate Customer’. As written, this query returns rows of customer data, where row composition is based on the columns in the Customer table. If you now run DISCOVER_CALC_DEPENDENCY with a QUERY restriction of ‘Evaluate Customer’, you will get the columns (or objects) used in that query. In this case, it’s a list of the columns in the Customer table.

The next set of queries demonstrates syntax for the QUERY restriction. You can run these queries against the AdventureWorks Tabular Model SQL Server 2012 to view the result set.

The first query shows how to specify a QUERY restriction for object names that include spaces. The second query, borrowed from Execute DAX queries through OLE DB and ADOMD.NET, is a more complex query that includes objects from multiple tables.

SELECT * From $SYSTEM.DISCOVER_CALC_DEPENDENCY WHERE QUERY = 'EVALUATE ''Reseller Sales'''

SELECT * from $system.DISCOVER_CALC_DEPENDENCY WHERE QUERY = 'EVALUATE CALCULATETABLE(VALUES(''Product Subcategory''[Product Subcategory Name]), ''Product Category''[Product Category Name] = "Bikes")'

QUERY Restriction XMLA Example

You can use an XMLA Discover command to return the query objects in a table. XMLA returns results as raw XML. You can use ADOMD.NET to parse the results in a more readable format.

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
   <RequestType>DISCOVER_CALC_DEPENDENCY</RequestType>
     <Restrictions>
        <RestrictionList>
            <QUERY>Evaluate 'Reseller Sales'</QUERY>
        </RestrictionList>
    </Restrictions>
    <Properties />
</Discover>

Using ADOMD.NET to return the rowset

When using ADOMD.NET and the schema rowset to retrieve metadata, you can use either the GUID or string to reference a schema rowset object in the GetSchemaDataSet method. For more information, see Working with Schema Rowsets in ADOMD.NET.

The following table provides the GUID and string values that identify this rowset.

See Also

Reference

Analysis Services Schema Rowsets

Concepts

Use Dynamic Management Views (DMVs) to Monitor Analysis Services