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

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

The INFO.STORAGETABLECOLUMNSEGMENTS 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.STORAGETABLECOLUMNSEGMENTS  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 returns information about the column segments used for storing data for in-memory tables.

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

It will query the $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS and return an entire table with 20 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
• PARTITION_NAME: this is not mentioned in the documents
• DIMENSION_NAME: this is the name of the dimension
• TABLE_ID: this the ID of the table
• COLUMN_ID: this the ID of the column
• SEGMENT_NUMBER: this is the numeric value of the segment
• TABLE_PARTITION_NUMBER: this is the numeric value of the partition table
• RECORDS_COUNT: this represents the number of records
• ALLOCATED_SIZE: this is the size of allocated data
• USED_SIZE: this is the size of the data used
• COMPRESSION_TYPE: this is the type of compression.  Currently, this value is always "NOSPLIT".  The compression value is intended for internal server use only
• BITS_COUNT: this is the count of bits required to store the Data IDs
• BOOKMARK_BITS_COUNT: this is the bookmark count of BITS
• VERTIPAQ_STATE: this is the state of the VertiPaq compression for this column segment.  The value is one of the following:
• COMPLETED: this is the VertiPaq compression completed successfully
• TIMEBOXED: this is he VertiPaq compression was timeboxed
• SKIPPED: this is the VertiPaq compression was skipped
• ISPAGEABLE: when true, MAY indicate that the segment is pageable; otherwise, false.  If the paging feature is not supported on the server, the value is NULL
• ISRESIDENT: when true, MAY indicate that the segment is resident; otherwise, false.  If the paging feature is not supported on the server, the value is NULL
• TEMPERATURE: when the segment is pageable and is resident, MAY indicate the scaled numeric value of the frequency of segment access considering the last access time and usage; otherwise, NULL
• LAST_ACCESSED: For a pageable segment, MAY indicate the last access time of a segment if it has been paged in at least once; otherwise, NULL.  For a non-pageable segment, the value is always NULL.

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.