SSAS Dimension Properties : Misc and Source

Previously I’ve blogged about both Basic dimension properties (here) and Advanced dimension properties (here), so today I’d like to finish off this initial set by blogging about the misc and source sections of the properties of an attribute in a dimension. The source section, above everything else, is where you define your key for that attribute and the name and value properties, so it’s definitely one of, if not the, most used property sections.

 

Source

 

image

 

KeyColumns

Specifies what is the key of that attribute. This is the table in the source that binds to that attribute.

If you don’t put anything in the name column, AS uses the key column to as the name column as well.

NameColumn

This property determines the name that is displayed for the attribute. In cases where your key property is set to more than 1 column, you must put something in this field. Another nice thing about this field is that you can set say, your surrogate key attribute with the name column in your dimension and have it process on the key but display the name, so you can combine 2 attributes into 1 and improve performance while you’re doing it.

ValueColumn

This property determines what is actually displayed on the screen. If you have something in the name property, that value will be used by default, otherwise the key column is used. Or you can populate the value property with a value of your choosing if say, you want the key value to display or an entirely different attribute.

 

CustomRollupColumn & CustomRollupPropertiesColumn

These 2 properties are most commonly used in parent-child hierarchies and can be used if you want to apply a custom formula to your attribute as it’s being processed. You can also use this to track something like depreciation, as an example. There is an excellent post on how to set this up from Hennie located here.

 

Misc

image

 

AttributeHierarchyOrdered

Specifies whether or not to order this attribute. When you select “True” (also the default), Analysis Services creates an index during processing that it uses to ensure that members are displayed in order. (which is also defined by your key attribute property, see above). To save processing time, for certain attributes you can set this to “False” which tells Analysis Services it does not need to be displayed in a certain order. Ironically, the surrogate key of the dimension can be set to “False,” because you hardly, if ever, care in what order a key is displayed.

GroupingBehavior

This property only has 2 choices: EncourageGrouping or DiscourageGrouping. Essentially, it tells the client application if members of that attribute can be grouped. Something like Gender, or education, can be grouped as the same property could potentially apply to lots of different rows (there are a lot of individuals with Bachelor’s degrees, for example). Likewise, something that will always have a 1:1 relationship with the key attribute isn’t a good candidate for grouping (phone number, name, etc.)

InstanceSelection

You can use this property to tell client applications (namely, Excel), how to display the list of members in each attribute hierarchy. You have a couple of choices here:

  • None – This is the default. Like none in other cases, pretty much says do whatever you feel is best, Mr. Client Application
  • DropDown – there aren’t many records in here, so you can always display all of the items in a drop down list
  • List – too many for a drop-down list, but not so many that it’s unwieldy.
  • FilteredList – there are a lot of records, so it always needs to be filtered before it can be displayed.
  • MandatoryFilter – there are tons of records, the list must always be filtered before it can be displayed (think millions)

MemberNamesUnique

This property tells AS if each member in this attribute hierarchy must be unique.

 

 

By the way, I haven’t forgotten about the parent-child section, but in my opinion it deserves it’s own blog post, so that should be coming soon (hopefully later this week). 🙂

Advertisements

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