Power Pivot Principles: The A to Z of DAX Functions – INFO.LINGUISTICMETADATA

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at INFO.LINGUISTICMETADATA.

The INFO.LINGUISTICMETADATA function

Dynamic Management Views (DMVs) are specialised queries provided by SQL Server Analysis Services (SSAS), Azure Analysis Services (AAS), and Power BI that offer an administrative view into the internal state of these systems.  DMVs are used to retrieve metadata, monitor health and performance, and diagnose problems within the database or data model.  They serve as a powerful tool for administrators and developers to gain insights into the workings of the database engine and the tabular data model, covering aspects like performance metrics, configuration settings and the structure of database objects.

The $System schema DMVs in SQL Server Analysis Services (SSAS), Azure Analysis Services (AAS), and Power BI are categorised into four [4] types, each serving specific purposes:

• DISCOVER: requires admin privileges and provides information about the model, including details on connected sessions and environment configuration
• DMSCHEMA: focused on data mining, offering insights for predictive analytics and pattern recognition, mainly used in SSAS/AAS
• MDSCHEMA: targets multidimensional models, delivering metadata and structure from an MDX perspective, relevant for OLAP cubes and dimensions
• TMSCHEMA: designed for tabular models, it provides detailed metadata about tables, columns, measures, etc., using Tabular Model Scripting Language (TMSL) information, crucial for Power BI and tabular SSAS/AAS models.

In the past, if we wanted to query those $System schema DMVs we used external tools like Tabular Editors or DAX Studio to query them:

The INFO.LINGUISTICMETADATA function is one of the system functions.  It employs the following syntax:

This function has no parameters.

Based upon the ‘[MS-SSAS-T]: SQL Server Analysis Services Tabular Protocol’ from Microsoft (which you may access here), the LinguisticMetadata object is used to hold synonym information for the Tabular model. It is a child of a Culture object.

We can write this INFO.LINGUISTICMETADATA function on DAX query view to get the same information on the TMSCHEMA_LINGUISTIC_METADATA:

It will query the $SYSTEM.TMSCHEMA_LINGUISTIC_METADATA and return an entire table with five [5] columns:

• ID: this is a reference to the object
• CultureID: this is an ID-based reference to a Culture object
• Content: this is a string that contains the natural language synonyms
• ModifiedTime: this is the time that the object was last modified
• ContentType: this specifies the type of the linguistic metadata based on the Content property.  Compatibility level 1500 or higher is required.  The possible values are as follows:
• XML (0) – XML content
• Json (1) – JSON content.

It should be noted that:

• it is used for querying the DMV (Dynamic Management Views) from the $System schema called TMSCHEMA where TM stand for ‘Tabular model’ and TMSCHEMA provides information from the tabular model
• sometimes querying DMVs may fail if we do not have the appropriate permission.