0

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

enter image description here

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

enter image description here

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

enter image description here

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?

Josh Sama
  • 33
  • 1
  • 5

1 Answers1

0

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.

TheRizza
  • 1,577
  • 1
  • 10
  • 23