Daily DAX : Day 168 INFO.STORAGETABLES

 The INFO.STORAGETABLES function in Power BI's Data Analysis Expressions (DAX) is part of the INFO family of functions introduced to provide metadata about a semantic model. Specifically, INFO.STORAGETABLES retrieves information about the storage tables within the model, which are the underlying structures used to store data in a tabular model. This function is particularly useful for advanced users, such as data modelers or administrators, who need to inspect or optimize the internal workings of their Power BI models.

Syntax

The INFO.STORAGETABLES function does not take any arguments. Its syntax is simply:

INFO.STORAGETABLES()

When executed, it returns a table with details about the storage tables in the current semantic model.

Returned Columns

The table returned by INFO.STORAGETABLES typically includes columns such as:

    ID: A unique identifier for the storage table.

    Name: The name of the storage table.

    TableType: Indicates the type of storage table (e.g., whether it’s a data table, metadata table, or another type).

    Rows: The number of rows stored in the table.

    Size: The size of the table in memory or storage (often in bytes).

    LastUpdated: The timestamp of the last update or refresh of the table.

The exact columns may vary slightly depending on the version of Power BI or the underlying engine (e.g., Analysis Services Tabular), but these are the core pieces of information you can expect.

Use Case

The primary use case for INFO.STORAGETABLES is model introspection and optimization. Here’s a detailed explanation of when and how it might be used:

1. Understanding Data Storage

    Scenario: You’re managing a large Power BI model with multiple tables and want to understand how the data is physically stored.

    Application: By running INFO.STORAGETABLES in DAX Query View, you can see the number of rows and size of each storage table. This helps you identify which tables are consuming the most resources, potentially guiding decisions about data partitioning or aggregation.

2. Performance Optimization

    Scenario: Your Power BI report is slow to refresh or interact with, and you suspect inefficient storage or compression.

    Application: Use INFO.STORAGETABLES to inspect the size and row count of storage tables. If a table has an unusually high size relative to its row count, it might indicate poor compression due to high cardinality columns (e.g., many unique values). You could then adjust the model by removing unnecessary columns, reducing cardinality, or applying aggregations.

3. Troubleshooting Refresh Issues

    Scenario: A data refresh fails or takes longer than expected, and you need to pinpoint the problematic table.

    Application: Check the LastUpdated column to see which tables were recently refreshed and compare their sizes or row counts to historical data (if tracked). This can help identify tables that grew unexpectedly or failed to update.

4. Documentation and Auditing

    Scenario: You need to document the structure of your Power BI model for compliance or team handover.

    Application: Execute INFO.STORAGETABLES and export the results to a table or Excel file. This provides a snapshot of the model’s storage layout, which can be paired with other INFO functions (e.g., INFO.MEASURES or INFO.TABLES) for a comprehensive overview.

Example in Power BI

To use INFO.STORAGETABLES, you would typically run it in DAX Query View in Power BI Desktop. Here’s an example query:

EVALUATE

INFO.STORAGETABLES()

This returns a table with the storage table metadata. You could then visualize this in a report or export it for further analysis.

Practical Example

Imagine you have a Power BI model with sales data across multiple regions. You notice performance degradation and suspect the issue lies in the storage of a large fact table. Running INFO.STORAGETABLES reveals that one storage table (corresponding to your fact table) has 10 million rows and a size of 2 GB, while another related table is much smaller. You investigate further and find that a text column with high unique values (e.g., detailed comments) is bloating the storage. You decide to remove or summarize that column, reducing the table size and improving performance.

Key Notes

    Availability: The INFO functions, including INFO.STORAGETABLES, were introduced in late 2023 as part of the DAX Query View enhancements in Power BI. Ensure your Power BI Desktop is updated to a version that supports them (post-December 2023).

    Context: This function operates at the model level and doesn’t require a specific table or filter context, making it straightforward to use.

    Limitations: It’s not intended for end-user reporting but rather for developers and analysts working behind the scenes. The data it returns is technical and may require familiarity with tabular model concepts.

In summary, INFO.STORAGETABLES is a powerful tool for digging into the internals of a Power BI model, offering insights that can drive optimization and troubleshooting efforts. It’s especially valuable in complex models where performance and resource usage are critical concerns.