The same DMVs that provide information about SSAS Tabular databases are also available for Power BI datasets. Querying these DMVs can provide schema information, such as the columns used to define relationships, the definitions of DAX Measures, and the memory usage of columns and other structures. From a memory analysis standpoint, the two most important DMVs are DISCOVER_STORAGE_TABLE_COLUMNS and DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS.

In the following query from DAX Studio, the dictionary size of each column of a Power BI dataset is retrieved via the DISCOVER_STORAGE_TABLE_COLUMNS DMV:

Dictionary size by Column

With the Power BI dataset (the PBIX file) open on the local machine, the DAX Studio application can connect to the dataset and SQL queries can be executed against the DMVs, just like normal DAX queries.

The DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS DMV contains information on four separate memory structures: user hierarchies, system hierarchies, relationships, and the compressed data segments per column. Dataset designers are generally most interested in the size and distribution of data segments by column and this can be retrieved with the following SQL query:

Data size per Column Segment

The first two characters of the Table_ID column identify the data structure represented by the row. For example, H$ refers to system column hierarchies, U$ refers to user-defined hierarchies, and R$ refers to relationships. All other rows of the DMV, the rows in which the second character is not a dollar sign, refer to column data segments. In this query, the WHERE clause containing the LEFT() and RIGHT() text functions and the <>'$' condition is used to retrieve only the column data segments.

The Dictionary_Size column and the Used_Size column from the two respective DMVs are stored in bytes. For a more intuitive analysis of this data, particularly with large datasets, it can be helpful to convert from bytes to megabytes by dividing by 1,048,576.

Fact and dimension tables with over a million rows will contain more than one segment with each segment representing approximately one million rows. To analyze the DMV query results with multiple segments, it's necessary to group the result set by column and use aggregation functions (sum, average) against the Used_Size column. Analyzing the memory usage data from SSAS DMVs is generally performed outside of DAX Studio in tools such as Excel or Power BI.

A separate Power BI dataset (the PBIX file) exclusively dedicated to analyzing the memory usage of Power BI datasets can be an effective method of streamlining the data retrieval and visualization process. A detailed example of developing and maintaining one of these datasets is included in Chapter 10 of the Microsoft Power BI Cookbook (https://www. packtpub. com/big-data-and-businessintelligence/microsoft-power-bi-cookbook). At a high level, this solution involves executing M Queries against a running Power BI dataset to retrieve DMVs, such as the two DMVs identified, and then model and visualize this data.