Dimension Attribute Keys

A collection of keys must be defined for each dimension attribute.  This key collection must uniquely define each member of that attribute.

If we take a date dimension for example, the key for the [Year] level can be defined as the [Year] column in the database.  Every member with the same year will be aggregated together.  The story is different once we get to the [Quarter] level.  We have many members named [Q1] and they become unique in conjunction with the [Year] member.  That means that the key for the [Quarter] level will be [Year] and then [Quarter].  The same goes for the [Month] level as well.  The key would be [Year] and then [Month].  You could use the month name or the month number – whichever you like.  At the day level, you have a couple of options.  First of all, you can use the identity ID of your dimension table, and this is more desirable when you are using a smart date key or you can choose to use [Year], [Month], and [Day] as the key.

Now every member of each attribute and consequently any hierarchies built using these attributes has a unique way to reference each member.

After you are finished, you should make sure that your attribute hierarchies are set up correctly or SSAS won’t design [can’t calculate] aggregates at any level other than the [All] level and the leaf level.  More on that here.

Advertisements

One Response to Dimension Attribute Keys

  1. Dave Wickert [MSFT] says:

    Ref: “When you are finished, you should make sure that your attribute hierarchies are set up correctly or SSAS can’t calculate aggregates…”

    I think you mean “won’t design aggregates”; not calculate. Also, it isn’t attribute hierarchies that have to be set up correctly, it is *attribute relationships* and user-defined hierarchies. If you look at my SQL PASS presentation last year on “Everything you ever wanted to know about aggregations”, I explain what the algorithm is for how the aggregation design wizard decides what aggregates to design.

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

%d bloggers like this: