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