Elements of a Metric Level

The following elements are needed to specify a level for a metric:

• Target: the Context of a Calculation
• Grouping: How Aggregation is Performed
• Filtering: How the Report Filter Interacts with the Metric Calculation

These elements are set in the Metric Editor. The window displays the complete metric definition, including its formula, level, condition, and transformation. In the metric definition area, selecting Level (Dimensionality) causes the Level (Dimensionality) component window to appear below.

Clicking Reset changes the level unit back to the default of report level for the target and standard for filtering and grouping. The Add Report Level button becomes available if the report level is removed from the metric level.

The Advanced button accesses the Level (Dimensionality) advanced options dialog box, which is discussed in Level Metrics: Applying the Metric Condition to the Metric Calculation and Level Metrics Accepting Dimensionality Units to Emulate MicroStrategy 6.x Behavior.

Target: the Context of a Calculation

The target is the attribute level at which the metric calculation groups. It determines the table to use to calculate the metric. Any set of attributes or a hierarchy can be the target. A special case is the default target, which is at the report level. For a more detailed description of the report level, including an example, see Report Level: Interaction with the Context of Reports. For specifics on using a hierarchy as a target, see Using a Hierarchy as the Target of a Metric Level.

The following examples use attributes as the target; for an example using a hierarchy, see Level Metrics Review: Standard Grouping.

In a Revenue (Dimensionality All Products) metric, the target is Product.

The target is indicated between braces in the metric definition, as shown below:

Sum(Revenue) {~, Product}

Grouping: How Aggregation is Performed

Groupingdetermines how the metric aggregates. The result of this setting is reflected in the GROUP BY clause of the SQL command. The grouping options for levels include:

• Standard: groups by the attribute level of the target. The metric calculates at the level of the target, if possible.
• None: excludes the attribute in the target from the GROUP BY clause. It also excludes any of the target attribute's children.

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

select a11.[SUBCAT_ID] AS SUBCAT_ID,
max(a12.[SUBCAT_DESC]) AS SUBCAT_DESC,
sum(a11.[TOT_DOLLAR_SALES]) as REVENUE
from [CITY_SUBCATEG__SLS] a11, 
[LU_SUBCATEG] a12
where a11.[SUBCAT_ID] = a12.[SUBCAT_ID]
group by a11.[SUBCAT_ID]

insert into TEMP_TABLE 
select a11.[SUBCAT_ID] AS SUBCAT_ID,
sum(a11.[TOT_DOLLAR_SALES]) as REVENUE
from [YR_CATEGORY_SLS] a11
group by a11.[CATEGORY_ID]
select a11.[SUBCAT_ID] AS SUBCAT_ID,
a11.[SUBCAT_DESC] AS SUBCAT_DESC,
pa1.[REVENUE] as REVENUE
from [TEMP_TABLE] pa1, 
[LU_SUBCAT] a11
where pa1.[CATEGORY_ID] = a11.[CATEGORY_ID]

select a12.[CATEGORY_ID] AS CATEGORY_ID,
sum(a11.[TOT_DOLLAR_SALES]) as REVENUE
from [CITY_SUBCATEG_SLS] a11, 
[LU_SUBCATEG] a12,
[LU_CATEGORY] a13
where a11.[SUBCAT_ID] = a12.[SUBCAT_ID] and a12.[CATEGORY_ID] = a13.[CATEGORY_ID]
group by a12.[CATEGORY_ID]

insert into TEMP_TABLE 
select sum(a11.[TOT_DOLLAR_SALES]) as REVENUE
from [CITY_SUBCATEG_SLS] a11
 
select a12.[ITEM_ID] AS ITEM_ID,
a12.[ITEM_DESC] AS ITEM_NAME,
pa11.[REVENUE] as REVENUE
from [TEMP_TABLE] pa11, 
[LU_ITEM] a12
 
drop table TEMP_TABLE

Grouping Options for Nonaggregatable 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.

Grouping in the Products - By Top 10% Customers Report

Review the Products - By Top 10% Customers report described at the beginning of Level Metrics: Modifying the Context of Data Calculations. In the report, 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 and do not see the exclamation mark, select Show level (dimensionality) properties from the View menu.

Filtering: Interaction with Report Filters

Filtering in the Products - By Top 10% Customers Report

Review the Products - By Top 10% Customers report described at the beginning of Level Metrics: Modifying the Context of Data Calculations. In the report, 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 filtering set to standard. The report filter contains a prompt for quarter and a filter for the top ten percent of customers based on revenue. Standard filtering means that revenue is calculated for only the quarter selected in the prompt.

The filtering is indicated in the metric definition. Standard filtering is indicated by a plus sign after the target, as shown in the definition of the Revenue (Dimensionality All Products) metric shown below:

Revenue {~+, !Product+}

If you open this metric and do not see the plus sign, select Show level (dimensionality) properties from the View menu.