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

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

The INFO.TABLES 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.TABLES function is one of the system functions.  It employs the following syntax:

There are two [2] main arguments in this function (excluding numbering):

• RestrictionName: this argument is optional and repeatable this represents the restriction name
• RestrictionValue: this argument is optional and repeatable this represents the restriction value.

Based upon the ‘[MS-SSAS-T]: SQL Server Analysis Services Tabular Protocol’ from Microsoft (which you may access here), this is object defines the security rules of the Role object on the Table object.  It is a child of a Role object.

We can write this INFO.TABLES function in DAX query view to get the same information on the TMSCHEMA_TABLES:

It will query the $SYSTEM.TMSCHEMA_TABLES and return an entire table with 20 columns:

• ID: this is a reference to the object
• ModelID: this is an ID-based reference to a Model object
• Name: this is the name of the object
• DataCategory: this is a string that specifies the category of the data.  The values automatically map to the DIMENSION_TYPE column.  The possible values are as follows:
• Unknown (0) – all unknown strings are returned in the Contents attribute of the EntityType element of Conceptual Schema Definition Language with Business Intelligence annotations (CSDLBI).  For more information, see [MS-CSDLBI].  (Maps to UNKNOWN)
• Regular (1) – standard dimension (Maps to OTHER)
• Time (2) – time dimension (Maps to TIME)
• Geography (3) – geography dimension (Maps to GEOGRAPHY)
• Organization (4) – organization dimension (Maps to ORGANIZATION)
• BillOfMaterials (5) – bill of materials dimension (Maps to BILL OF MATERIALS)
• Accounts (6) – accounts dimension (Maps to ACCOUNTS)
• Customers (7) – customers dimension (Maps to CUSTOMERS)
• Products (8) – products dimension (Maps to PRODUCTS)
• Scenario (9) – scenario dimension (Maps to SCENARIO)
• Quantitative (10) – quantitative dimension (Maps to QUANTITATIVE)
• Utility (11) – utility dimension (Maps to UTILITY)
• Currency (12) – currency dimension (Maps to CURRENCY)
• Rates (13) – rates dimension (Maps to RATES)
• Channel (14) – channel dimension (Maps to CHANNEL)
• Promotion (15) – promotion dimension (Maps to PROMOTION)
• Description: this is the description of the object
• IsHidden: this is a Boolean that indicates whether the table is treated as hidden by client visualisation tools.  If the table is treated as hidden by client visualisation tools, it is "true"; otherwise, it is "false"
• TableStorageID: this is an ID-based reference to a TableStorage object.  The TableStorage object is reserved for internal use only
• ModifiedTime: this is the time that the object was last modified
• StructureModifiedTime: this is the time that the structure of the object was last modified
• SystemFlags: this is a bitmask that is used to identify the type of object.  The possible values are as follows:
• Bit 0 is set to 1: the object is a system table that is defined and built internally by the system
• Bit 1 is set to 1: the object is a user-created calculated table
• ShowAsVariationsOnly: this is a Boolean that dictates whether the table is shown only when referenced as Variation.  If it is "true", the table is shown only when it is referenced as a variation; otherwise, it is "false".  Compatibility level 1400 or higher is required
• IsPrivate: this is a Boolean that dictates whether the table is to be hidden for all clients.  If it is "true", the table is hidden for all clients; otherwise, it is "false".  Compatibility level 1400 or higher is required
• DefaultDetailRowsDefinitionID: this is an ID-based reference to a DetailRowsDefinition object.  This property defines the default Data Analysis Expressions (DAX) expression to apply when drilling through to the detail rows for measures in this table.  Compatibility level 1400 or higher is required
• CalculationGroupID: this is an ID-based reference to a CalculationGroup object.  A calculation group reduces the number of redundant measures by grouping common measure expressions as calculation items.  Compatibility level 1500 or higher is required
• RefreshPolicyID: this is an ID-based reference to a RefreshPolicy object.  A refresh policy allows partitions to be refreshed and maintained by the server automatically.  Compatibility level 1500 or higher is required
• ExcludeFromModelRefresh: this is a Boolean that indicates whether the table is excluded when the model is refreshed.  When the value is "true", a refresh operation on the model does not trigger a refresh on any partitions of the table that were already processed.  Compatibility level 1500 or higher is required
• AlternateSourcePrecedence: this is the ranking or precedence that is used to select the aggregation table in case more than one match is found.  The table with higher precedence is preferred over the table with lower precedence if more than one aggregation table matches for a query.  Compatibility level 1500 or higher is required
• LineageTag: this is an optional tag that can be used to define the lineage of a table across different versions of a model.  Compatibility level 1600 or higher is required
• SourceLineageTag: this is an optional tag that can be used to define the lineage of a referenced table across different versions of a model.  As opposed to LineageTag, SourceLineageTag can be used to define the lineage of a referenced table rather than a table itself.  SourceLineageTag is useful when a model references other models by using a Direct Query connection.  Compatibility level 1600 or higher is required
• SystemManaged: this is a Boolean that indicates whether the table is managed by the system.  Compatibility level 1600 or higher is required.

It should be noted that:

• it is used for querying the DMV (Dynamic Management Views) from the $System schema called TMSCHEMA where TM stands for ‘Tabular model’ and TMSCHEMA provides information from the tabular model
• 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.