Daily DAX : Day 345 INFO.ATTRIBUTEHIEARACHYSTORAGES

INFO.ATTRIBUTEHIERARCHYSTORAGES DAX Function

Overview

The INFO.ATTRIBUTEHIERARCHYSTORAGES is an advanced INFO function in DAX (Data Analysis Expressions) used in Power BI, Analysis Services, and Power Pivot. It retrieves detailed metadata about the storage configurations for attribute hierarchies in the semantic model. Attribute hierarchies are internal structures in tabular models that define how dimension attributes are organized for efficient querying and aggregation.

Note: This function is undocumented by Microsoft and may be unsupported in future releases. It corresponds to the underlying TMSCHEMA_ATTRIBUTE_HIERARCHY_STORAGES Dynamic Management View (DMV). Use with caution, as it requires semantic model admin permissions and is intended for advanced model inspection.

Syntax

INFO.ATTRIBUTEHIERARCHYSTORAGES(
    [<RestrictionName> [, [<RestrictionValue>] [, <RestrictionName> [, [<RestrictionValue> [, … ] ] ] ] ] ]
)

Parameters:

• RestrictionName (Optional, Repeatable): Name of the filter criterion (e.g., 'ID' or 'AttributeHierarchyID').
• RestrictionValue (Optional, Repeatable): Value to filter by (e.g., a specific hierarchy ID).

These parameters allow narrowing the results, similar to DMV queries.

Return Value

Returns a table with metadata columns about storage for attribute hierarchies. Key columns include:

Column Name	Data Type	Description
ID	Integer	Unique identifier for the storage entry.
AttributeHierarchyID	Integer	ID of the associated attribute hierarchy.
SortOrder	Integer	Order for sorting in the hierarchy.
OptimizationLevel	Integer	Level of query optimization applied.
MaterializationType	Integer	How the hierarchy data is materialized (e.g., compressed or indexed).
DistinctDataCount	Integer	Number of unique values in the hierarchy.
HasStatistics	Boolean	Whether statistics are maintained for the storage.
MinValue / MaxValue	String	Minimum and maximum values in the hierarchy.

This table can be queried further with DAX functions like SELECTCOLUMNS or FILTER.

Use Cases

This function is primarily for model developers and administrators performing advanced diagnostics:

• Performance Tuning: Analyze storage efficiency (e.g., DistinctDataCount or OptimizationLevel) to identify hierarchies with high cardinality that may slow queries, and optimize by reducing levels or compressing data.
• Model Documentation: Export hierarchy storage details to a report or Excel for auditing the semantic model's internal structure.
• Troubleshooting: Investigate issues in DirectQuery or composite models where attribute hierarchies affect aggregation performance.

Example Query (in DAX Query View):

EVALUATE
SELECTCOLUMNS(
    INFO.ATTRIBUTEHIERARCHYSTORAGES(),
    "Hierarchy ID", [AttributeHierarchyID],
    "Distinct Count", [DistinctDataCount],
    "Optimization Level", [OptimizationLevel]
)

This returns a simplified table of key storage metrics for all attribute hierarchies.

Remarks & Limitations

• Can only be used in DAX queries (e.g., DAX Query View in Power BI Desktop), not in measures, calculated columns, or tables.
• May require elevated permissions; errors occur if the feature is unsupported.
• For friendlier alternatives, consider documented INFO functions like INFO.VIEW.HIERARCHIES for basic hierarchy info.