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

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

The INFO.CATALOGS 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:

Now, we just need to write a simple DAX syntax to query those DBSCHEMA directly in DAX query view.  In this instance, we are using INFO.CATALOGS function to query the DBSCHEMA_CATALOGS.

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

This function has no parameters.

Based upon the ‘[MS-SSAS]: SQL Server Analysis Services Protocol’ from Microsoft (which you may access here), the DBSCHEMA_CATALOGS schema rowset the catalogues that are accessible on the server.

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

It will query $SYSTEM.DBSCHEMA_CATALOGS and return an entire table with 15 columns:

• CATALOG_NAME: this is the catalogue name
• DESCRIPTION: this is the catalogue description
• ROLES: this is a comma-delimited list of roles to which the current user belongs
• DATE_MODIFIED: this is the date that the catalogue was last modified
• COMPATIBILITY_LEVEL: this represents the compatibility level of the database
• TYPE: this is a mask with the following flags
• (0x0) Multidimensional: if the other bits are not set, the database is a Multidimensional database
• (0x1) TabularMetadata:  the Tabular model is built by using Tabular metadata
• (0x2) TabularModel:  this is a Tabular model, including those built using Tabular or Multidimensional metadata
• VERSION: this is a database that uses Tabular Metadata will return the current version of the database.  Otherwise, the value will be zero [0]
• DATABASE_ID: this is the ID of the database object
• DATABASE_GUID: this is not mentioned in the MS-SSAS document as at the time of writing (14 May 2024)
• DATE_QUERIED: this is unused
• CURRENTLY_USED: this is unused
• POPULARITY: this is a measure of how frequently the database is used.  The value is empty for the system tracker
• WEIGHTEDPOPULARITY: this is a measure of how frequently the database is used, expressed as a fraction with respect to the other databases.  The value is empty for the system tracker
• CLIENTCACHEREFRESHPOLICY: this is a hint to the client applications about when their data caches, if any SHOULD be refreshed after a Refresh command changes the data on the server.  The possible values are as follows:
• 0: client applications are notified to refresh their caches only if a user query/interaction needs newer data
• -1 (default): client applications are notified to allow all background cache refreshes
• ENCRYPTION_LEVEL: this is not mentioned in the current MS-SSAS document as at the time of writing (14 May 2024).

Here are two [2] remarks about function come with INFO prefix: 

• it is used for querying the DMV (Dynamic Management Views) from the $System schema.
• sometimes querying DMVs may fail if we do not have the appropriate permission. 

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.