CODEWITHSUNDEEP
powerbidaxstudiodmvtabular-editor
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6940

What is the difference in TMSCHEMA vs MDSCHEMA in DMV of Power BI models

For example, what is the difference between in those 2 when querying DMV?

select * from $SYSTEM.MDSCHEMA_MEASURES

select * from $SYSTEM.TMSCHEMA_MEASURES

When those queries are going to return different results?

Upvotes: 0

Views: 1450

Answers (1)

TheRizza
TheRizza

Reputation: 2062

The TM tables are generally the better ones to use for Tabular models, though you can use either for Tabular. The columns and sometimes the rows are different though. For example measures, I get an extra row on the MD query for a hidden measure: MDSCHEMA_MEASURES

You can see the columns for each using these links.
MDSCHEMA_MEASURES
TMSCHEMA_MEASURES

I do a lot with the TM DMV tables, and I think the only MD table I ever use is MDSchema_Dimensions to get row counts.

SELECT DISTINCT
  [CATALOG_NAME] as [Database],
  DIMENSION_CAPTION AS [Table],
  FORMAT(DIMENSION_CARDINALITY,'###,###,###') AS [RowCount]
FROM $system.MDSchema_Dimensions
WHERE DIMENSION_CAPTION <> 'Measures'  --table name
AND CUBE_NAME = 'Model'
ORDER BY DIMENSION_CARDINALITY DESC  --RowCount

You can also get row counts from DISCOVER_STORAGE_TABLES, but because you can't write full SQL (eg. GROUP BY) when using DMVs, you have to jump through hoops after the query to get the right answer.

Upvotes: 0

Related Questions