About this task

In some cases, you may have dimension data that you want to aggregate. For example, a product dimension may have a list price column, that you want to aggregate to determine the average list price for all of the products you carry.

Note that this would not help you determine the average list price of all products sold. This is because a secondary metrical attribute can only be calculated for the attributes local to the dimension where it resides. Queries that reference an unrelated dimension generate errors like this:

Query is not possible: metrics (xxx, yyy) are not defined over the product of these dimensions: (AAA, BBB, CCC)

Restrictions

• Queries that use secondary metrical attributes together with metrics on fact datasets will return unexpected results.

  To work around this restriction, add dimension datasets in which secondary metrical attributes are defined to the canvas as fact datasets; then; model the secondary metrical attributes as metrics in those fact datasets. For steps, see Adding a Secondary Metrical Attribute as a Metric in a Fact Table.

• If you want to write a query that uses a secondary metrical attribute and groups the results by a hierarchy level other than the level that the attribute is defined on, you must define the attribute on the lowest level of the hierarchy.

• If you define a secondary metrical attribute on a level higher than the lowest level, queries that use the attribute can reference only that hierarchy.

  However, you can avoid this restriction if each hierarchy that you want to reference in the same dimension has a level that uses the same key column. For example, suppose you have these hierarchies in a single dimension:

  Hierarchy A	Hierarchy B	Hierarchy C
  Level 1	Level 1	Level 1
  Level 2 (secondary metrical attribute)	Level 2	Level 2
  Level 3	Level 3	Level 3

  If Level 1 in each of these hierarchies uses the key column KeyColA, then queries that use the secondary metrical attribute can also reference levels in hierarchies B and C.

• If you use a secondary metrical attribute in a calculated metric formula, it can be used only with other secondary metrical attributes that come from the same dimension.

  For example, to create an average of product list price, first create two secondary metrical attributes: one for the sum and one for the non-distinct row count. Then, add a calculated metric on the main model canvas that refers to these metrics by their query name, as in this example: ([Measures].[product_list_price] / [Measures].[product_nd_count])

Procedure

You can add secondary metrical attributes to dimensions by including the metrics property in the dimension's underlying SML. For more information, see Dimensions.