Defining how aggregation is performed: Level metric grouping

Before you begin

This topic assumes a basic understanding of level metrics. See Level metrics: A practical overview for background information.

Grouping in a metric level determines how the metric aggregates. The grouping options for levels include:

• Standard, which groups by the attribute level of the target. That is, the metric calculates at the level of the target, if possible.

• None, which excludes the attribute in the target from the GROUP BY clause in the SQL statement. It also excludes any of the target attribute's children.

  None is not an option if the target is set to the report level.

Grouping is one element of a metric level. The others are the target, which provides the context for the calculation, and filtering, which defines how the level metric interacts with report filters.

Grouping options for non-aggregatable metrics

The remaining grouping options are only used for nonaggregatable metrics. A nonaggregatable metric is one that should not be aggregated across an attribute. An example is an inventory metric. While the data warehouse records the inventory every month, these monthly numbers are not added together to calculate the yearly inventory. Instead, you may want to use the end-on-hand and beginning-on-hand inventory numbers to see how the total inventory changed over the year. These grouping options, described below, are used in such cases:

• Beginning lookup uses the first value of the lookup table.

• Ending lookup uses the last value of the lookup table.

• Beginning fact accesses the first value of the fact table.

• Ending fact accesses the last value contained in the fact table.

Setting a metric level to one of the options listed above defines the metric as nonaggregatable. Whether you select a fact or lookup table largely depends on how the necessary information is stored. For example, to find the beginning-on- hand inventory for a particular item, you need to know how the inventory information is stored. If the inventory count is not taken on the first day of the week, as the lookup table requires, the inventory count should be taken from the fact table for the first recorded entry.

There is another important difference between accessing a fact table and a lookup table. If a value, such as April sales, is missing from a fact table, the row still exists in the table and is reported as null or zero. If that same value is missing in a lookup table, the April row does not exist. The previous or next value (March or May) is reported, depending on whether the level is set to beginning or ending value.

Level grouping example

In the report shown below, the % of All Revenue (by Product) metric divides the Revenue metric by a metric called Revenue (Dimensionality All Products). The Revenue metric is calculated at the report level, in this case, for each product. The level of the Revenue (Dimensionality All Products) metric has been set to product, with a grouping of none. This allows the calculation to total the revenue for all products, not just the single product on the particular row of the report. Therefore, the same number is calculated for each row of the report, producing the revenue percentage.

The grouping is indicated in the metric definition. If the grouping is standard, an additional indicator is not displayed. If the grouping is set to none, as in the Revenue (Dimensionality All Products) metric, an exclamation mark is placed before the target name, as shown below:

Revenue {~+, !Product+}

If you open this metric in the Metric Editor and do not see the exclamation mark, display the level properties. To do this, select Show level (dimensionality) properties on the View menu, or click the Show level (dimensionality) properties icon on the toolbar.

For additional examples, including examples with non-aggregatable metrics, see the Advanced Metrics chapter of the Advanced Reporting Help.

Related Topics

• Specifying metric levels

• About level metrics

• The context of a calculation: Level metric target

• Interaction with report filters: Level metric filtering

• About the report level of a metric

For examples of grouping in level metrics, see Level metrics: A practical overview and the Advanced Metrics chapter of the Advanced Reporting Help.