Daily DAX : Day 198 INFO.RELATIONSHIPSTORAGES

 The INFO.RELATIONSHIPSTORAGES DAX function in Power BI is part of the INFO family of functions, introduced to provide metadata about a Power BI semantic model. Specifically, INFO.RELATIONSHIPSTORAGES returns information about the storage properties of relationships in the model, such as how relationships are managed in terms of data storage and query performance. This function is particularly useful for advanced users, administrators, or developers who need to document, analyze, or optimize the structure of their Power BI data models.

Syntax

DAX


INFO.RELATIONSHIPSTORAGES()


    No parameters: The function takes no arguments and returns a table with details about the storage properties of relationships in the semantic model.

    Return value: A table containing columns that describe the storage-related metadata for each relationship in the model.


Columns Returned

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


    RelationshipID: A unique identifier for the relationship.

    FromTableID: The ID of the table on the "from" side of the relationship.

    FromColumnID: The ID of the column on the "from" side of the relationship.

    ToTableID: The ID of the table on the "to" side of the relationship.

    ToColumnID: The ID of the column on the "to" side of the relationship.

    StorageMode: Indicates the storage mode of the relationship (e.g., Import, DirectQuery, or Direct Lake).

    IsActive: A boolean indicating whether the relationship is active (TRUE) or inactive (FALSE).

    CrossFilterDirection: Describes the direction of filter propagation (e.g., Single, Both).

    JoinType: Specifies the type of join used for the relationship, such as INNER or OUTER, which is particularly relevant for DirectQuery models.

    ReferentialIntegrityAssumed: Indicates whether the model assumes referential integrity for the relationship (TRUE/FALSE), which can affect query performance in DirectQuery or Direct Lake modes.


These columns provide a detailed view of how relationships are configured and stored, which can impact query performance and model behavior.

Use Cases

The INFO.RELATIONSHIPSTORAGES function is primarily used for model introspection, documentation, and optimization. Below are some practical use cases:


    Model Documentation:

        Scenario: A Power BI developer needs to create a comprehensive report or documentation of the data model, including details about relationships and their storage properties.

        How to Use: Run EVALUATE INFO.RELATIONSHIPSTORAGES() in DAX query view to retrieve a table of all relationships and their storage metadata. This can be exported to Excel or visualized in a Power BI report to share with stakeholders.

        Example:

        DAX


        EVALUATE

        INFO.RELATIONSHIPSTORAGES()


        This query returns a table listing all relationships, their associated tables and columns, and storage properties, which can be used to document the model’s structure.

    Performance Optimization:

        Scenario: In a DirectQuery or Direct Lake model, queries are slow, and you suspect inefficient relationship configurations, such as OUTER joins or lack of referential integrity.

        How to Use: Use INFO.RELATIONSHIPSTORAGES to inspect the JoinType and ReferentialIntegrityAssumed columns. For DirectQuery models, enabling "Assume Referential Integrity" (where appropriate) can change joins from OUTER to INNER, improving performance by leveraging columnstore indexes.

        Example:

        DAX


        EVALUATE

        FILTER(

            INFO.RELATIONSHIPSTORAGES(),

            [JoinType] = "OUTER"

        )


        This query identifies relationships using OUTER joins, which may be candidates for optimization by ensuring referential integrity.

    Troubleshooting Relationships:

        Scenario: A report is returning unexpected results due to misconfigured relationships (e.g., inactive relationships or incorrect cross-filter directions).

        How to Use: Query INFO.RELATIONSHIPSTORAGES to verify the IsActive and CrossFilterDirection properties of relationships. This can help identify whether a relationship is inactive or if bi-directional filtering is causing ambiguity.

        Example:

        DAX


        EVALUATE

        SELECTCOLUMNS(

            INFO.RELATIONSHIPSTORAGES(),

            "RelationshipID", [RelationshipID],

            "FromTableID", [FromTableID],

            "ToTableID", [ToTableID],

            "IsActive", [IsActive],

            "CrossFilterDirection", [CrossFilterDirection]

        )


        This query provides a simplified view of relationships, focusing on their active status and filter direction, to diagnose potential issues.

    Auditing DirectQuery or Direct Lake Models:

        Scenario: In a DirectQuery or Direct Lake model, you need to ensure that relationships are optimized for performance and that storage modes are correctly configured.

        How to Use: Use INFO.RELATIONSHIPSTORAGES to check the StorageMode and ReferentialIntegrityAssumed columns. This helps confirm that relationships align with the storage mode of the model and that referential integrity is enforced where possible to reduce query overhead.

        Example:

        DAX


        EVALUATE

        FILTER(

            INFO.RELATIONSHIPSTORAGES(),

            [StorageMode] = "DirectQuery" && [ReferentialIntegrityAssumed] = FALSE

        )


        This query identifies DirectQuery relationships that do not assume referential integrity, which could be optimized for better performance.

    Self-Documenting Models:

        Scenario: You want to embed metadata about relationships directly within the Power BI model for other developers or analysts to reference.

        How to Use: Create a calculated table using INFO.RELATIONSHIPSTORAGES to store relationship metadata within the model. This table can be hidden from report view but used for reference.

        Example:

        DAX


        RelationshipsMetadata = 

        INFO.RELATIONSHIPSTORAGES()


        This calculated table can be queried or visualized by advanced users to understand the model’s relationship structure.


Key Considerations


    Permissions: Using INFO.RELATIONSHIPSTORAGES requires semantic model admin permissions, as it accesses metadata about the model. Some columns may also require workspace admin permissions.

    Compatibility: The function is supported only in Power BI semantic models and not in SQL Server Analysis Services, Azure Analysis Services, or Power Pivot models.

    Joining with Other INFO Functions: To make the output more user-friendly, you can join INFO.RELATIONSHIPSTORAGES with other INFO functions like INFO.TABLES or INFO.COLUMNS to replace table and column IDs with their names.

        Example:

        DAX


        EVALUATE

        ADDCOLUMNS(

            INFO.RELATIONSHIPSTORAGES(),

            "FromTableName", 

            LOOKUPVALUE(

                INFO.TABLES()[Name], 

                INFO.TABLES()[TableID], 

                [FromTableID]

            ),

            "ToTableName", 

            LOOKUPVALUE(

                INFO.TABLES()[Name], 

                INFO.TABLES()[TableID], 

                [ToTableID]

            )

        )


        This query enhances the output by adding the names of the "from" and "to" tables, making it easier to interpret.

    DirectQuery and Direct Lake: The JoinType and ReferentialIntegrityAssumed columns are particularly relevant for DirectQuery and Direct Lake models, where query performance depends on efficient SQL joins. Ensuring referential integrity can reduce the use of OUTER joins, which are less efficient.

    Limitations: The function provides metadata but does not allow you to modify relationships. Changes to relationships must be made in the model view or via tools like Tabular Editor.


Practical Example

Suppose you are managing a Power BI model with a DirectQuery connection to a SQL database. You notice that some reports are slow, and you suspect that relationship configurations are causing inefficient queries. You can use INFO.RELATIONSHIPSTORAGES to audit the model:

DAX


EVALUATE

SELECTCOLUMNS(

    FILTER(

        INFO.RELATIONSHIPSTORAGES(),

        [StorageMode] = "DirectQuery"

    ),

    "RelationshipID", [RelationshipID],

    "FromTable", 

        LOOKUPVALUE(

            INFO.TABLES()[Name], 

            INFO.TABLES()[TableID], 

            [FromTableID]

        ),

    "ToTable", 

        LOOKUPVALUE(

            INFO.TABLES()[Name], 

            INFO.TABLES()[TableID], 

            [ToTableID]

        ),

    "JoinType", [JoinType],

    "ReferentialIntegrityAssumed", [ReferentialIntegrityAssumed]

)


This query returns a table listing all DirectQuery relationships, their associated tables, join types, and whether referential integrity is assumed. You can use this information to identify relationships that use OUTER joins and check if enabling "Assume Referential Integrity" in the relationship properties is feasible (ensuring all rows in the "from" table have matching rows in the "to" table).

Conclusion

The INFO.RELATIONSHIPSTORAGES DAX function is a powerful tool for inspecting the storage and configuration details of relationships in a Power BI semantic model. Its primary use cases include model documentation, performance optimization, troubleshooting, and auditing, especially in DirectQuery or Direct Lake scenarios. By providing metadata about relationship storage properties, it enables developers and administrators to make informed decisions to improve model efficiency and maintainability. For best results, combine it with other INFO functions to create a comprehensive view of the model’s structure.

Comments

Popular posts from this blog

Daily DAX : Day 65 INFO.TABLEPERMISSIONS

Daily DAX : Day 55 PV