Troubleshooting Dimension Hierarchy Errors: No Properties or Members in SQL Server?

Table of Contents

Encountering issues where SQL Server Analysis Services (SSAS) fails to return expected properties or members for a Dimension Hierarchy when queried via XML for Analysis (XMLA) script can be a perplexing problem for developers and administrators alike. This article delves into the specific cause of such a scenario within SSAS 2016 and provides comprehensive workarounds to resolve this frustrating issue, ensuring seamless data exploration and metadata retrieval.

Understanding Dimension Hierarchies and XMLA in SSAS¶

Before diving into the problem and its solution, it’s crucial to understand the fundamental components involved. SQL Server Analysis Services (SSAS) is a powerful analytical data engine used for online analytical processing (OLAP) and data mining. Within SSAS, Dimension Hierarchies are foundational structures that organize data along various attributes, enabling users to navigate, aggregate, and analyze information from different perspectives. They define paths for drilling down into data, for instance, from ‘Year’ to ‘Quarter’ to ‘Month’ in a time dimension, or ‘Product Category’ to ‘Product Subcategory’ to ‘Product’ in a product dimension. These hierarchies are vital for efficient data exploration and report building.

XML for Analysis (XMLA) serves as the primary communication protocol for SSAS. It’s an XML-based language used to issue commands and retrieve data from Analysis Services databases. Developers and administrators utilize XMLA for a wide array of tasks, including querying data (MDX), managing objects (DDL), and discovering metadata about the server, databases, cubes, dimensions, and hierarchies themselves. When troubleshooting, executing XMLA scripts directly in tools like SQL Server Management Studio (SSMS) or through programmatic interfaces is a common practice to inspect the underlying structure and properties of OLAP objects.

Symptoms: Unpacking the Issue - No Properties or Members Returned¶

The core symptom of this specific issue manifests when you attempt to retrieve the properties or members of a Dimension Hierarchy using an XMLA script, but the server returns an empty result set, even though the hierarchy is known to exist and contains data. This can occur when utilizing commands such as DISCOVER_DIMENSION_PROPERTIES or DISCOVER_MEMBERS within an XMLA query. For instance, you might execute a query targeting a specific dimension and hierarchy, expecting a list of attributes or members, but instead receive no rows.

This problem is particularly insidious because it often occurs without a clear error message. The query simply returns nothing, leading to confusion and the perception that the hierarchy itself might be empty or improperly configured. Users might encounter this when performing programmatic checks, integrating with reporting tools, or manually inspecting the metadata through SSMS. The lack of an explicit error often forces a deep dive into the SSAS configuration, making it a challenging issue to diagnose without prior knowledge of this specific behavioral nuance.

Consider a scenario where you have a well-defined ‘Product’ dimension with a ‘Category-Subcategory-Product’ hierarchy. You execute an XMLA query to list all members under ‘Category,’ expecting a comprehensive list of all product categories. However, the query completes successfully but yields an empty result. This unexpected blank output, despite the hierarchy being visibly populated and usable in other contexts (e.g., through MDX queries or direct browsing in Excel), is the tell-tale sign of this particular configuration conflict.

Cause: The Role of HierarchyUniqueNameStyle and ExcludeDimensionName¶

The root cause of this peculiar behavior lies in a specific server configuration property within SSAS known as HierarchyUniqueNameStyle. This property dictates how unique names for hierarchies are constructed within the Analysis Services instance. While its primary purpose is to define the naming convention, one of its legacy values can inadvertently conflict with modern XMLA query patterns, especially those that include DIMENSION_UNIQUE_NAME in their RestrictionList.

The problematic value for HierarchyUniqueNameStyle is ExcludeDimensionName. This setting was primarily introduced for backward compatibility, specifically to support older databases from SQL Server 2000 or 2005 Analysis Services, where the convention for unique hierarchy names did not always explicitly include the parent dimension’s unique name as part of the hierarchy’s unique identifier. When ExcludeDimensionName is set, the unique name generated for a hierarchy might simply be [Hierarchy Name] rather than [Dimension Name].[Hierarchy Name].

Here’s where the conflict arises: When an XMLA request is made, particularly with DISCOVER commands, and the RestrictionList includes DIMENSION_UNIQUE_NAME along with HIERARCHY_UNIQUE_NAME, the server internally attempts to match the provided DIMENSION_UNIQUE_NAME as part of the hierarchy’s fully qualified unique name. However, if HierarchyUniqueNameStyle is set to ExcludeDimensionName, the server’s internal representation of the hierarchy’s unique name does not include the dimension name. This mismatch between the query’s expectation (a fully qualified name) and the server’s internal naming convention (an abbreviated name) causes the server to fail to find any matching hierarchies, resulting in an empty result set.

Essentially, the query is asking for “Hierarchy X within Dimension Y,” but the server, due to the ExcludeDimensionName setting, only knows about “Hierarchy X” as an independent entity, unable to establish the programmatic link to “Dimension Y” through the unique name string. This subtle discrepancy in unique name parsing is the core reason for the “no results returned” symptom.

For clarity, consider the different HierarchyUniqueNameStyle settings:

Setting Value	Example Unique Hierarchy Name (for ‘Products’ Dimension, ‘Category’ Hierarchy)	Description
ExcludeDimensionName	[Category]	The unique name of the hierarchy does not include the dimension’s unique name. Primarily for backward compatibility with older SSAS versions (2000/2005).
IncludeDimensionName	[Products].[Category]	The unique name of the hierarchy includes the dimension’s unique name, separated by a period. This is the default and recommended setting for newer SSAS versions.
FullyQualified	[Cube Name].[Dimension Name].[Hierarchy Name]	The unique name includes the cube, dimension, and hierarchy names, providing the most explicit path.

When ExcludeDimensionName is active, and an XMLA query’s RestrictionList contains <DIMENSION_UNIQUE_NAME>...</DIMENSION_UNIQUE_NAME>, the server cannot reconcile the request with its internal, dimension-excluded hierarchy unique names, leading to the observed empty results.

Workaround: Resolving the Empty Results Issue¶

To resolve this issue, the most robust and recommended approach is to modify the HierarchyUniqueNameStyle server property. However, if that is not immediately feasible, adjusting your XMLA query can serve as a temporary alternative.

Option 1: Adjusting the Server Property HierarchyUniqueNameStyle (Recommended)¶

The best long-term solution involves changing the HierarchyUniqueNameStyle property on your SSAS instance from ExcludeDimensionName to a more appropriate setting, such as IncludeDimensionName or FullyQualified. For SSAS 2016 and newer versions, IncludeDimensionName is generally the default and preferred setting, as it aligns better with modern XMLA query practices and standard cube design patterns.

Method A: Changing HierarchyUniqueNameStyle via SQL Server Management Studio (SSMS)¶

This is the most common and user-friendly method for modifying SSAS server properties:

1. Connect to Analysis Services: Open SQL Server Management Studio (SSMS) and connect to your SQL Server Analysis Services instance.
2. Access Server Properties: In Object Explorer, right-click on the SSAS instance name (e.g., YOURSERVER\YOURINSTANCE) and select “Properties.”
3. Navigate to General Properties: In the “Analysis Services Properties” dialog box, select “General” from the “Select a page” pane on the left.
4. Locate HierarchyUniqueNameStyle: Scroll down the list of properties to find HierarchyUniqueNameStyle under the “General” group.
5. Change the Value: Click on the current value (likely ExcludeDimensionName) and select IncludeDimensionName from the dropdown list. You might also consider FullyQualified depending on your specific naming conventions and requirements. For most cases, IncludeDimensionName is sufficient.
6. Apply Changes: Click “OK” to apply the changes.
7. Consider Restart: While some SSAS property changes take effect immediately, for critical configuration settings, it’s often advisable to restart the Analysis Services service to ensure the change is fully propagated and recognized by all components. This can be done via SQL Server Configuration Manager or Windows Services.

Method B: Changing HierarchyUniqueNameStyle via XMLA Script¶

For automated deployments, scripting changes, or environments where SSMS GUI access is restricted, you can modify the HierarchyUniqueNameStyle using an ALTER XMLA command.

<Alter ObjectExpansion="ObjectAndChildren" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <Server />
  </Object>
  <ObjectDefinition>
    <Server>
      <Name>YOURSERVER\YOURINSTANCE</Name>
      <Properties>
        <Property>
          <Name>HierarchyUniqueNameStyle</Name>
          <Value>1</Value> <!-- 1 for IncludeDimensionName, 0 for ExcludeDimensionName, 2 for FullyQualified -->
        </Property>
      </Properties>
    </Server>
  </ObjectDefinition>
</Alter>

Note: The numerical values for HierarchyUniqueNameStyle are:
* 0: ExcludeDimensionName
* 1: IncludeDimensionName
* 2: FullyQualified

Replace YOURSERVER\YOURINSTANCE with the actual name of your SSAS instance. Execute this XMLA script against your SSAS instance in SSMS or through a programmatic client. After executing, a service restart might still be beneficial.

Impact of Changing the Property:
Changing HierarchyUniqueNameStyle primarily affects how SSAS constructs unique names for hierarchies internally and how it interprets incoming XMLA requests that rely on these names. It generally does not require a full re-processing of your cubes or dimensions, nor does it typically break existing MDX queries or client applications that rely on logical object names, as long as they don’t explicitly hardcode unique names in the ExcludeDimensionName style when IncludeDimensionName is expected. However, any client applications or scripts that previously relied on the ExcludeDimensionName unique name format for XMLA DISCOVER commands might need to be updated to expect the new, fully qualified names.

Option 2: Modifying the XMLA Query (If property change is not feasible)¶

If for some reason you cannot or do not wish to change the HierarchyUniqueNameStyle server property (e.g., due to legacy dependencies or strict configuration management), you can adapt your XMLA DISCOVER query to work with the ExcludeDimensionName setting. This workaround involves omitting DIMENSION_UNIQUE_NAME from your RestrictionList and relying solely on HIERARCHY_UNIQUE_NAME with the abbreviated style.

However, this approach comes with significant caveats:

• It makes your XMLA queries less robust, as they become dependent on knowing the hierarchy’s unique name without its parent dimension context.
• It can lead to ambiguity if different dimensions happen to have hierarchies with the same name.
• It’s generally not recommended for new development.

Here’s an example of how you might adjust your XMLA script for DISCOVER_MEMBERS if HierarchyUniqueNameStyle is ExcludeDimensionName:

Original (Failing) XMLA Request:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
  <Request>
    <Properties>
      <PropertyList>
        <Catalog>YourDatabase</Catalog>
      </PropertyList>
    </Properties>
    <Restrictions>
      <RestrictionList>
        <CUBE_NAME>YourCube</CUBE_NAME>
        <DIMENSION_UNIQUE_NAME>[Product]</DIMENSION_UNIQUE_NAME>
        <HIERARCHY_UNIQUE_NAME>[Category]</HIERARCHY_UNIQUE_NAME>
      </RestrictionList>
    </Restrictions>
    <ObjectType>DISCOVER_MEMBERS</ObjectType>
  </Request>
</Discover>

Modified (Working) XMLA Request for ExcludeDimensionName:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
  <Request>
    <Properties>
      <PropertyList>
        <Catalog>YourDatabase</Catalog>
      </PropertyList>
    </Properties>
    <Restrictions>
      <RestrictionList>
        <CUBE_NAME>YourCube</CUBE_NAME>
        <!-- Omit DIMENSION_UNIQUE_NAME here -->
        <HIERARCHY_UNIQUE_NAME>[Category]</HIERARCHY_UNIQUE_NAME>
      </RestrictionList>
    </Restrictions>
    <ObjectType>DISCOVER_MEMBERS</ObjectType>
  </Request>
</Discover>

Notice that DIMENSION_UNIQUE_NAME has been removed from the RestrictionList. This works because, with ExcludeDimensionName set, the server only expects the simple hierarchy name. This approach can be applied to DISCOVER_DIMENSION_PROPERTIES or any other DISCOVER command suffering from the same issue.

Best Practices and Prevention: Avoiding Future Issues¶

To prevent encountering this issue in future SSAS deployments and ensure robust metadata retrieval:

• Default to IncludeDimensionName for New Deployments: For all new SQL Server 2016 Analysis Services instances and later versions, it is highly recommended to configure the HierarchyUniqueNameStyle property to IncludeDimensionName (or FullyQualified). This aligns with modern SSAS architecture and client expectations.
• Consistency in Naming Conventions: Maintain consistent naming conventions across your dimensions and hierarchies to simplify XMLA queries and reduce ambiguity.
• Thorough Testing of XMLA Scripts: Always test your XMLA scripts, especially those used for automated metadata discovery or management, in development and staging environments before deploying to production.
• Document Server Configurations: Keep clear documentation of your SSAS server properties and their justifications. This aids in troubleshooting and onboarding new team members.
• Understand XMLA Schema Rowsets: Familiarize yourself with the various XMLA schema rowsets (DISCOVER_SCHEMA_ROWSETS, DISCOVER_DIMENSION_PROPERTIES, DISCOVER_MEMBERS, etc.) and their respective restriction lists. Understanding these schemas is key to writing effective and accurate XMLA queries.

Advanced Troubleshooting and Diagnostics: When Things Go Deeper¶

If the workarounds mentioned above do not resolve your issue, or if you suspect a more complex problem, consider the following advanced troubleshooting steps:

• Verify SSAS Processing Status: Ensure that your SSAS database, cubes, dimensions, and partitions are fully processed. Unprocessed or partially processed objects will not return complete metadata or data.
• Utilize SQL Server Profiler: SQL Server Profiler is an invaluable tool for diagnosing SSAS issues. You can capture Query End and Command End events to see the exact XMLA commands received by the server and their outcomes. Pay attention to events related to Discover and Execute operations. You can also filter for specific users or applications to narrow down the trace. This helps verify if the XMLA request is being sent correctly and how the server is responding internally.
• Examine SSAS Logs: Check the Analysis Services log files (msmdsrv.log) for any errors or warnings that might provide clues. These logs often contain detailed information about server operations, configuration issues, or processing failures. The log file location is usually found within the SSAS instance’s data directory.
• Review User Permissions: Confirm that the user account attempting to execute the XMLA query has sufficient permissions on the Analysis Services database and relevant objects (cubes, dimensions). Lack of appropriate read permissions on the dimension or its hierarchies will result in empty or restricted result sets.
• Network Connectivity and Firewall: Although less common for this specific issue, ensure there are no network connectivity problems or firewall rules blocking communication between your client tool and the SSAS instance.

Visualizing a Dimension Hierarchy Example (Mermaid Diagram)¶

To further illustrate the concept of a dimension hierarchy, consider this simple example using a Mermaid graph, which represents a typical Product Dimension:

mermaid graph TD A[Product Dimension] --> B(Product Category) B --> C(Product Subcategory) C --> D(Product Name)

In this diagram, ‘Product Category’ is a higher level than ‘Product Subcategory,’ which in turn is higher than ‘Product Name.’ Each of these levels forms part of the ‘Product’ hierarchy. When querying, you might want to discover properties or members at any of these levels. The HierarchyUniqueNameStyle setting dictates how the unique identifier for Product Category, Product Subcategory, and Product Name is constructed relative to the Product Dimension itself.

Example: Understanding HierarchyUniqueNameStyle in Practice¶

Let’s assume we have a Product dimension and a Category hierarchy within it.

• If HierarchyUniqueNameStyle is ExcludeDimensionName:
  • The unique name for the Category hierarchy would be: [Category]
• If HierarchyUniqueNameStyle is IncludeDimensionName:
  • The unique name for the Category hierarchy would be: [Product].[Category]

This distinction is precisely why an XMLA RestrictionList expecting [Product].[Category] when the server is configured for [Category] leads to no match and empty results.

Watch a Video on SSAS Dimension Hierarchy Best Practices¶

For those who prefer a visual explanation or want to deepen their understanding of SSAS Dimension Hierarchies and best practices, consider watching a video tutorial. While this article doesn’t link to a specific external video, a search for “SQL Server Analysis Services Dimension Hierarchy Best Practices” on platforms like YouTube can provide valuable insights into designing and managing effective OLAP structures.

Example of an embedded video (conceptual):

Title: Understanding and Designing Dimension Hierarchies in SSAS

This conceptual video would cover topics like attribute relationships, defining hierarchies, and best practices for performance and usability in SQL Server Analysis Services.

Conclusion¶

The “no properties or members returned” issue for Dimension Hierarchies in SSAS 2016, particularly when using XMLA scripts with DIMENSION_UNIQUE_NAME in the RestrictionList, is a specific problem rooted in the HierarchyUniqueNameStyle server property. By understanding the historical context of the ExcludeDimensionName setting and its conflict with modern XMLA querying patterns, administrators and developers can effectively resolve it.

The recommended and most robust solution is to change the HierarchyUniqueNameStyle property to IncludeDimensionName or FullyQualified via SSMS or an XMLA ALTER script. This aligns the server’s internal naming convention with standard XMLA expectations. If a server-wide change isn’t immediately feasible, modifying the XMLA query to omit DIMENSION_UNIQUE_NAME can provide a temporary workaround, though it is less robust. Adhering to best practices for SSAS configuration and consistent naming conventions will significantly reduce the likelihood of encountering such issues in the future.

Have you encountered this particular issue in your SSAS environment? What troubleshooting steps did you find most effective? Share your experiences and insights in the comments section below!