INNER JOIN between 2 $SYSTEM.TMSCHEMA_ tables using MDXQuery

Question

I'm doing some queries in a SSAS model using SSMS and MDXQuery . I'm able to run a single select on any table as below:

SELECT[TableID], [Name] FROM $SYSTEM.TMSCHEMA_PARTITIONS

SELECT [ID], [Name] FROM $SYSTEM.TMSCHEMA_TABLES

The first select gives me the partitionNames assign to each table and the table ID, now I want to know what is the table name behind that TableID but I've tried a JOIN between both tables but I'm getting an error

SELECT[TableID], [Name] FROM $SYSTEM.TMSCHEMA_PARTITIONS a

JOIN $SYSTEM.TMSCHEMA_TABLES b on a.TableId = b.ID

I am more a SQL developer so I though the JOIN might work but is not , how can I join these 2 tables to get all needed columns?

Answer 1

You can't do joins via SQL with Dynamic Management View tables. The SQL used for DMVs is a subset of T-SQL. The normal approach is to load the data into tables and then do the joins. I do this all the time in Power Query from Excel and Power BI Desktop.

Here are my sources:

https://isura777.blogspot.com/2016/03/dynamic-management-views-for-ssas.html

SQL statements can be used to query the row sets, but have following limitation in SQL 2008 version.

• SELECT DISTINCT does not return DISTINCT values
• ORDER BY clause accepts just one field to order by. Only one order expression is allowed for TOP Expression at line 1, column 1?
• COUNT, SUM does not work
• ORDER BY does not ORDER, but no error
• JOINS appear not to work
• LIKE does not work
• IN doesn't work, but [col] = '1' OR [col] = '2' does work
• String functions like LEFT do not work

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/hh230820(v=sql.110)

Although DMV query syntax is based on a SQL SELECT statement, it does not support the full syntax of a SELECT statement. Notably, JOIN, GROUP BY, LIKE, CAST, and CONVERT are not supported.