Defining Analysis Services Dimensions - SQL Server Analysis Services 2012 Cube Development - page 46

Setting essential attribute properties
While running the Dimension Wizard to create the Promotion dimension, you learned
that each attribute has name and key properties. Choosing appropriate columns for
these properties is essential for having the correct attribute design, so we will discuss
them in greater detail here.
How to do it...
The steps for setting essential attribute properties are as follows:
1. The key property must uniquely identify the attribute and could consist of
multiple columns. For example, if we have a date dimension that includes
the quarter number column, with values quarter 1, quarter 2, and so on, this
column alone won't be sufficient to uniquely identify each quarter because
quarter names would be duplicated for each year. Instead, the key property
should be set to a combination of quarter and year columns. If we don't expli-
citly specify the attribute key, Analysis Services will use the same column as
the attribute key and name, but attribute names might not always be unique. If
we check the properties of the Promotion Type attribute, we find the following
settings:
2. However, a quick check of the DimPromotion relational table will show du-
plicate promotion type values. For instance, the Excess Inventory promotion
type exists for both Reseller and Customer promotion categories. If we leave
the dimension design at its current state, Analysis Services processing will fail
reporting duplicate values. Instead, let's specify a composite key consisting of
the promotion type and promotion category columns. Click on the ellipsis but-
ton to the right of the Key Columns property; this will open the Key Columns
dialog. Note from the screenshot that you could choose columns from multiple
source tables (if we were using a snowflake schema). Add the EnglishPro-
motionCategory column to the Key Columns list and click on OK :