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

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

The INFO.STORAGETABLECOLUMNS 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.STORAGETABLECOLUMNS 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]: SQL Server Analysis Services Tabular Protocol’ from Microsoft (which you may access here), this schema rowset contains information about the columns used for representing the columns of an in-memory table.

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

It will query the $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMNS and return an entire table with 19 columns:

• DATABASE_NAME: this is the name of the database
• CUBE_NAME: the name of the cube
• MEASURE_GROUP_NAME: this is the name of the measure group
• DIMENSION_NAME: this is the name of the dimension
• ATTRIBUTE_NAME: this is the name of the attribute
• TABLE_ID: this the ID of the table
• COLUMN_ID: this the ID of the column
• COLUMN_TYPE: this is the type of the column.  The values are as follows:
• BASIC_DATA – this column contains data provided by an external data source
• CALCULATED_DATA – this column contains data created by calculations
• RELATIONSHIP – this column contains information about relationship between tables
• HIERARCHY_POSITION_TO_DATAID – this column contains information mapping position of a value in the hierarchy to the Data ID
• HIERARCHY_DATAID_TO_POSITION – this column contains information mapping a Data ID to the position of a value in the hierarchy
• UNKNOWN – the column type is not known
• COLUMN_ENCODING: this is the encoding method used on the column.  The method can be one of the following:
• 0 – The system automatically selects a column encoding
• 1 – The column uses hash encoding
• 2 – The column uses value encoding.
• DATATYPE: this is the type of the column data.  The values are as follows:
• N/A – indicates that no data type information is available
• DBTYPE_EMPTY - indicates that no value was specified
• DBTYPE_I2 - indicates a two-byte signed integer
• DBTYPE_I4 - indicates a four-byte signed integer
• DBTYPE_R4 - indicates a single precision floating-point value
• DBTYPE_R8 - indicates a double precision floating-point value
• DBTYPE_CY - indicates a currency value.  Currency is a fixed-point number that has four digits to the right of the decimal point and that is stored in an eight-byte signed integer scaled by 10,000
• DBTYPE_DATE - indicates a date value.  Date values are stored as Double, the whole part of which is the number of days since December 30, 1899, and the fractional part of which is the fraction of a day
• DBTYPE_BSTR - indicates a null terminated character string (Unicode)
• DBTYPE_ERROR - indicates a 32-bit error code
• DBTYPE_BOOL - indicates a Boolean value
• DBTYPE_DECIMAL - indicates an exact numeric value with a fixed precision and scale.  The scale is between 0 and 28
• DBTYPE_I1 - indicates a one-byte signed integer
• DBTYPE_UI1 - indicates a one-byte unsigned integer
• DBTYPE_UI2 - indicates a two-byte unsigned integer
• DBTYPE_UI4 - indicates a four-byte unsigned integer
• DBTYPE_I8 - indicates an eight-byte signed integer
• DBTYPE_UI8 - indicates an eight byte unsigned integer
• DBTYPE_GUID - indicates a GUID
• DBTYPE_BYTES - indicates a binary value
• DBTYPE_STR - indicates a string value
• DBTYPE_WSTR - indicates a null terminated Unicode character string
• DBTYPE_NUMERIC - indicates an exact numeric value with a fixed precision and scale.  The scale is between 0 and 38
• DBTYPE_DBDATE - indicates a date value (yyyymmdd)
• DBTYPE_DBTIME - indicates a time value (hhmmss)
• DBTYPE_DBTIMESTAMP - indicates a date-time stamp (yyyymmddhhmmss plus a fraction in billionths)
• ISKEY: this indicates whether the column is a key column
• ISUNIQUE: this indicates whether the column contains unique values
• ISNULLABLE: this indicates whether the column can contain NULL values
• ISROWNUMBER: this indicates whether the column is a Row Number column
• DICTIONARY_SIZE: this indicates the amount of memory that is used by the dictionary data structure associated with the column, in bytes.  The dictionary data structure maps column data IDs to the actual values
• DICTIONARY_ISPAGEABLE: this indicates the Vertipaq Data Paging (VDP) state of the dictionary, which specifies whether the dictionary is pageable.  If the VDP state of the dictionary is unknown, the default value is NULL.  Otherwise, the value is a Boolean
• DICTIONARY_ISRESIDENT: this indicates the VDP state of the dictionary, which specifies whether the dictionary is resident.  If the VDP state of the dictionary is unknown, the default value is NULL.  Otherwise, the value is a Boolean
• DICTIONARY_TEMPERATURE: this indicates the scaled numeric value of the frequency of dictionary access.  The value is based on the most recent access time and usage.  This column has a numeric value only if the dictionary is pageable and has been loaded at least once; otherwise, the value is NULL
• DICTIONARY_LAST_ACCESSED: this indicates the most recent time the dictionary was accessed
• For non-pageable tables in the dictionary, the DICTIONARY_LAST_ACCESSED value is NULL
• For pageable tables in the dictionary, DICTIONARY_LAST_ACCESSED value NULL denotes the table has not been loaded since server startup.

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.