Analysis Services


There are lots of properties in SSAS that have no useful purpose, or whose purpose is somewhat obscure, and one property I’ve always wondered about is the DependsOnDimension property of a dimension. You can find it in the Dimension Editor in BIDS but there’s no indication in the description as to what it does, so in a moment of idle curiosity I Googled Binged it; the best information I found was this forums answer from 2007 from Matt Carroll:

DependsOnDimension is really just a hint for use when designing aggregations using the Aggregation Design Wizard or the Usage Based Optimization Wizard.  Normally the aggregation design algorithm estimates the size of aggregations assuming dimesions are independent, but this property lets it know that one dimension is strongly determined by another dimension.  This should not affect your browsing experience in any way.

In Analysis Services 2000, DependsOnDimension was also used as part of the definition of virtual dimensions as a way of specifying the source for virtual dimension’s levels.

I did some quick experiments in BIDS and couldn’t see any evidence that setting this property affected what aggregations were designed by the Aggregation Design Wizard, but they weren’t exhaustive tests by any means. If you’re taking the trouble to enter row counts and set the AggregationUsage property you should probably try setting it if you have any dimensions that are very closely linked, just in case it does have an impact.

4 thoughts on “DependsOnDimension

  1. Parikshit Savjani – United States – Parikshit Savjani is a Senior Program Manager with Azure Relational Database Services working on making the customers successful on Microsoft Data platform. He has more than 10 years of experience with Microsoft and SQL Server.
    Parikshit says:

    Hi Chris,

    Thanks for the info !!! Can you provide some examples of closely linked Dimensions if possible…..will it make sense to use DependsOnDimension for Referenced Dimensions ?

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      In Adventure Works, think of the Order Date, Due Date and Ship Date dimensions – one Order Date will only ever be associated with a few Due Dates and Ship Dates. In contrast if you think about Order Date and Product, there’s nowhere near the same level of dependency: any Product could be ordered on any Date.

  2. I think that if you have a sales fact, orders dimensions and customers dimensions the customers dimension depends on the order dimension because order 1 always belongs to customer A. I think there might be improvement if both dimensions are big (1 million+)

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.