Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

DependsOnDimension

with 4 comments

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:

http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/bd289ca5-0cb0-4279-8947-ffa2690bcd1e/

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.

Written by Chris Webb

February 2, 2012 at 11:30 pm

Posted in Analysis Services

Tagged with

4 Responses

Subscribe to comments with RSS.

  1. 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 ?

    Parikshit

    February 3, 2012 at 5:32 am

    • 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.

      Chris Webb

      February 3, 2012 at 10:50 am

  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+)

    Enders

    February 3, 2012 at 11:30 pm

  3. [...] Chris Webb Covers DependsonDimension Property [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,072 other followers

%d bloggers like this: