Daily DAX : Day 217 INFO.COLUMNPARTITIONSTORAGES

 The INFO.COLUMNPARTITIONSTORAGES DAX function in Power BI is part of the INFO family of functions, which provide metadata about a semantic model. Specifically, INFO.COLUMNPARTITIONSTORAGES returns detailed information about the storage characteristics of column partitions in a Power BI semantic model. This function is primarily used for advanced model analysis, optimization, and documentation purposes, especially by administrators or developers working with large or complex datasets.

Syntax

INFO.COLUMNPARTITIONSTORAGES()

    No parameters: This function does not take any arguments and returns a table with metadata about column partition storage.

    Output: A table containing columns that describe storage-related properties for each column partition in the model.

Returned Columns

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

    TableID: The unique identifier for the table in the model.

    ColumnID: The unique identifier for the column within the table.

    PartitionID: The identifier for the partition associated with the column.

    SegmentCount: The number of segments used to store the column’s data.

    DictionarySize: The size (in bytes) of the dictionary used for encoding the column’s values.

    DataSize: The size (in bytes) of the actual data stored in the column.

    LastRefreshed: The timestamp of when the partition was last refreshed.

    Other metadata: Additional fields may include information about compression, encoding methods, or storage modes (e.g., DirectQuery, Import, or Dual).

Note: The exact columns returned may vary slightly depending on the Power BI version or the model’s configuration.

Key Characteristics

    Table Output: Like other INFO functions, INFO.COLUMNPARTITIONSTORAGES outputs a table, which can be used in DAX queries or combined with other DAX functions (e.g., FILTER, SELECTCOLUMNS, ADDCOLUMNS) for further analysis.

    Admin Permissions: Using this function typically requires semantic model admin permissions because it accesses sensitive metadata about the model’s structure.

    Power BI Exclusive: This function is supported in Power BI semantic models but not in SQL Server Analysis Services, Azure Analysis Services, or Power Pivot models.

    Dynamic Management Views (DMVs): The function is based on the library of DMVs in Analysis Services, adapted to work as a DAX function, offering more flexibility than traditional DMV queries.

Use Cases

The INFO.COLUMNPARTITIONSTORAGES function is particularly useful in scenarios involving model optimization, troubleshooting, and documentation. Below are some practical use cases:

    Model Optimization:

        Identify Storage Inefficiencies: By analyzing the DictionarySize and DataSize, you can identify columns that consume excessive storage due to high cardinality (e.g., columns with many unique values) or inefficient encoding. For example, a column with a large DictionarySize might benefit from data type optimization (e.g., converting a string to a numeric ID) or splitting into multiple columns.

        Partition Management: The SegmentCount and PartitionID columns help you understand how data is segmented across partitions. This can guide decisions on partition strategies, such as splitting large tables into smaller partitions for faster refresh times.

        Compression Analysis: You can assess whether the model’s compression is effective by comparing DataSize to the raw data volume. This might prompt adjustments to data types or relationships to improve compression ratios.

    Performance Troubleshooting:

        Diagnose Slow Queries: Columns with large DataSize or high SegmentCount may contribute to slow query performance. You can use this information to prioritize indexing, aggregations, or query optimization.

        Refresh Performance: The LastRefreshed column helps track partition refresh frequency and timing, allowing you to identify bottlenecks in data refresh processes.

    Model Documentation:

        Self-Documenting Models: You can use INFO.COLUMNPARTITIONSTORAGES in a calculated table to document the storage characteristics of a model. For example, combining it with INFO.VIEW.TABLES or INFO.VIEW.MEASURES provides a comprehensive view of the model’s structure and performance metrics.

        Audit and Compliance: Administrators can use the function to generate reports on data storage and refresh history, which may be required for compliance or auditing purposes.

    Capacity Planning:

        Storage Forecasting: By summing the DataSize and DictionarySize across columns, you can estimate the total storage footprint of the model. This helps in planning resource allocation for Power BI Premium or Azure Analysis Services.

        Scalability Analysis: Understanding partition and segment counts can inform decisions about scaling the model as data volume grows.

Example DAX Query

Here’s an example of how to use INFO.COLUMNPARTITIONSTORAGES in a DAX query to analyze storage usage:

dax

EVALUATE

ADDCOLUMNS(

    SELECTCOLUMNS(

        INFO.COLUMNPARTITIONSTORAGES(),

        "TableID", [TableID],

        "ColumnID", [ColumnID],

        "PartitionID", [PartitionID],

        "DataSize", [DataSize],

        "DictionarySize", [DictionarySize],

        "LastRefreshed", [LastRefreshed]

    ),

    "TotalSize", [DataSize] + [DictionarySize]

)

Explanation:

    SELECTCOLUMNS filters the output to include only the specified columns.

    ADDCOLUMNS adds a calculated column (TotalSize) that sums DataSize and DictionarySize for each row.

    The query returns a table showing storage details for each column partition, with the total storage size for easier analysis.

Practical Example

Suppose you’re managing a Power BI semantic model for a retail company with a large Sales table containing millions of rows. You notice that data refreshes are slow, and storage costs are high. You can use INFO.COLUMNPARTITIONSTORAGES to:

    Run the query above to identify columns with high DataSize (e.g., a CustomerNotes column storing free-text data).

    Optimize by replacing high-cardinality text columns with numeric keys or moving them to a separate table.

    Check SegmentCount to evaluate whether partitioning the Sales table by year or region could reduce refresh times.

    Monitor LastRefreshed to ensure partitions are updating as expected.

Limitations and Considerations

    Permissions: Requires admin access, limiting its use to model administrators or developers.

    Complexity: Interpreting the output requires a good understanding of the model’s structure and storage mechanics.

    Not for End Users: This function is designed for technical users, not business analysts creating reports.

    Power BI Only: It’s not available in other Analysis Services platforms, which may limit its use in hybrid environments.

Related Functions

    INFO.VIEW.TABLES: Provides table-level metadata, such as storage mode and table names.

    INFO.VIEW.MEASURES: Returns information about measures, useful for documenting calculations.

    INFO.VIEW.RELATIONSHIPS: Details relationships between tables, complementing storage analysis.

    INFO.TABLES, INFO.COLUMNS: Provide additional metadata about tables and columns, which can be joined with INFO.COLUMNPARTITIONSTORAGES for deeper insights.

Conclusion

The INFO.COLUMNPARTITIONSTORAGES DAX function is a powerful tool for Power BI administrators and developers to analyze and optimize the storage and performance of semantic models. Its primary use cases include model optimization, performance troubleshooting, documentation, and capacity planning. By providing detailed metadata about column partition storage, it enables informed decisions to improve efficiency and scalability. However, its use requires admin permissions and technical expertise, making it most suitable for advanced users working on large or complex models.

For more details, refer to the Microsoft Learn documentation on INFO functions: https://learn.microsoft.com/en-us/dax/info-functions-dax[](https://learn.microsoft.com/en-us/dax/info-functions-dax)