ssasssas-tabulardmv

SSAS Tabular DMV to get source query for a table

Need to get the source query and connection details for measures used in Tabular Model. Have tried below to get measure names:

SELECT * from $system.TMSCHEMA_MEASURES

We get TableID and measure calculations from above query executed against Analysis Server in SSMS. How do we get the Source query for the table and connection details.

Note: Below query gives Table Names but not the query and connection details:

select * from $System.TMSCHEMA_TABLES
Solution

  • You can do the following:

    select * from $System.TMSCHEMA_TABLES

    Note the ID from the table you are interested in. Then:

    select * from $SYSTEM.TMSCHEMA_PARTITIONS where TableID=<ID>

    will show you query definition(multiple definitions if you have partitions) for SSAS Tabular Model table. Note the DataSourceID, then run:

    select * from $SYSTEM.TMSCHEMA_DATA_SOURCES

    This will show you connection details.