Daily DAX : Day 185 INFO.STORAGEFOLDERS

 The INFO.STORAGEFOLDERS DAX function in Power BI is part of the INFO family of functions introduced to provide metadata about the semantic model. Specifically, INFO.STORAGEFOLDERS returns information about the storage folders used in the model, which can be useful for understanding how data is organized and managed behind the scenes.

Syntax

INFO.STORAGEFOLDERS()

    No arguments: This function does not require any input parameters.

    Return value: It returns a table with columns that describe the storage folders in the model, such as:

        ID: A unique identifier for the storage folder.

        Name: The name of the storage folder.

        Path: The path or location of the storage folder within the model’s storage structure.

        Other columns may include additional metadata, depending on the model and environment.

Purpose

The INFO.STORAGEFOLDERS function is primarily used to retrieve metadata about the storage structure of a Power BI semantic model. Storage folders are internal constructs that organize data, such as tables, partitions, or other objects, in the model’s storage engine (typically the Tabular engine in Analysis Services). This function is particularly relevant for advanced users, such as data modelers or administrators, who need to inspect or document the internal organization of a model.

Use Case

Here are practical scenarios where INFO.STORAGEFOLDERS can be useful:

    Model Documentation:

        A data modeler wants to document the internal structure of a Power BI model for auditing or compliance purposes. By running a DAX query with INFO.STORAGEFOLDERS, they can retrieve a list of storage folders and their paths, providing insight into how data is segmented and stored.

        Example query:

        EVALUATE

        INFO.STORAGEFOLDERS()

        This query returns a table listing all storage folders, which can be exported to Excel or used in a Power BI report for documentation.

    Performance Optimization:

        Understanding storage folder organization can help optimize a model’s performance. For instance, if a model has many storage folders due to excessive partitioning, it might indicate opportunities to simplify the model structure for faster query execution.

        A modeler can use INFO.STORAGEFOLDERS to identify folders associated with specific tables or partitions and assess whether the current setup is efficient.

    Troubleshooting:

        When debugging issues related to data refresh or query performance, INFO.STORAGEFOLDERS can reveal how data is distributed across folders. For example, unexpected folder structures might point to misconfigured partitions or data sources.

        This is particularly useful in DirectQuery or composite models where storage locations can impact performance.

    Integration with External Tools:

        Developers using tools like DAX Studio or SQL Server Management Studio (SSMS) can leverage INFO.STORAGEFOLDERS to extract metadata programmatically. For instance, they might use the output to automate model analysis or generate reports about storage usage.

Example in Power BI

To use INFO.STORAGEFOLDERS in Power BI, you typically write a DAX query in DAX Query View or an external tool like DAX Studio. Here’s an example:

DEFINE

    MEASURE 'Model'[StorageFolders] = 

        CONCATENATEX(

            INFO.STORAGEFOLDERS(),

            [Name] & " (" & [Path] & ")",

            ", "

        )

EVALUATE

    INFO.STORAGEFOLDERS()

    Explanation:

        The query retrieves the table of storage folders.

        The optional measure uses CONCATENATEX to create a comma-separated list of folder names and paths for easier readability.

        The result is a table showing all storage folders, which can be visualized in a Power BI report or exported for analysis.

Notes and Limitations

    Preview Feature: The INFO functions, including INFO.STORAGEFOLDERS, were introduced in public preview around December 2023, so you may need to enable preview features in Power BI Desktop to use them.

    DirectQuery Compatibility: Check the compatibility of this function with DirectQuery mode, as some INFO functions may have limitations in certain model types.

    Advanced Use: This function is geared toward advanced users familiar with the Tabular engine and model metadata. Casual Power BI users may not need it for typical reporting tasks.

    No Modification: INFO.STORAGEFOLDERS is read-only and provides metadata; it cannot be used to modify the model’s storage structure.

Practical Example

Suppose you’re managing a large Power BI model with multiple tables and partitions. You notice slow refresh times and suspect the storage structure might be overly complex. You run the following query in DAX Query View:

EVALUATE

INFO.STORAGEFOLDERS()

ORDER BY [Name]

The result shows a table with folder names and paths, revealing that some tables have dozens of storage folders due to fine-grained partitioning. You use this insight to consolidate partitions, reducing the number of folders and improving refresh performance.

Conclusion

The INFO.STORAGEFOLDERS function is a specialized tool for inspecting the storage structure of a Power BI model. Its primary use cases include model documentation, performance optimization, and troubleshooting. While not essential for everyday reporting, it’s valuable for advanced users who need to understand or manage the internal workings of a semantic model.

For more details, you can refer to Microsoft’s official documentation on DAX functions or explore the model in tools like DAX Studio to see INFO.STORAGEFOLDERS in action.