Metric types - Microstrategy

Metrics are report components that enable analytical calculations against the warehouse data. Metrics can be categorized as one of the following types, based on their formula:

  • The formula of a simple metric is a mathematical expression based on at least one group function, such as sum or average, applied to facts, attributes, or metrics. It can also contain non-group functions or arithmetic operators, in addition to the required group function. A simple metric always has a formula and a level. The entire metric can only contain one level.
  • The formula of a compound metric is based on arithmetic operators and non-group functions. Arithmetic operators are +, -, *, and /; non-group functions are OLAP and scalar functions such as running sum or rank. The expressions and functions can be applied to facts, attributes, or metrics.

Recall that a metric formula determines the data to be used and the calculations to be performed on that data.

Simple metrics

A simple metric does not restrict you to simple calculations; the term simple only refers to its structure. In its structure, a simple metric:

  • must include at least one group function
  • can include non-group functions or arithmetic operators, but not in place of the required group function
  • is based on either a fact column or an attribute
  • includes the specified level at which calculations are applied to the report
  • can include conditions for applying calculations
  • can include transformations to be done to the data prior to calculation.

The following are examples of simple metrics:

A simple metric consists of a formula and a level. A formula is a mathematical expression based on at least one group function, such as sum or average, applied to facts, attributes, or metrics. A simple metric can also contain a non-group function or arithmetic operator, in addition to the required group function. However, it must be placed inside the group function, as demonstrated by the previous examples.

The level, or dimensionality, is the level of calculation for the metric, such as year or customer. Simple metrics can also contain filtering, called a condition, or offset values, called transformations. These are not required components, as are the formula and level. All of these components are discussed in detail in the section Definition of simple metrics.

Simple metrics have been briefly addressed in the Metrics Essentials chapter of the Basic Reporting manual. These basic metrics are generally created early in a project life cycle. They can be used on their own or as building blocks for compound metrics. An example of such a metric that calculates revenue is shown as follows:

Sum(Revenue){~+}

The {~+}, which is set automatically when you create a metric, means that the metrics are calculated at the lowest level on the report. For example, if the report shows revenue by year and month, the numbers are calculated to reflect monthly sales data. If an attribute is added, then that attribute is considered when the data is calculated for the report.

Simple metrics can also contain multiple facts, as in this metric definition:

Sum(Revenue - Cost){~+}

Notice that the level, represented by {~+}, is set on the entire metric. This concept is important to distinguish between simple and compound metrics.

Nested metrics

A nested metric provides a convenient way to use metric functionality when fact tables in the warehouse do not include attribute data at the level desired for specific analysis purposes. By using the result of a metric calculation as a temporary fact table from which to calculate another metric, you can obtain and analyze data not immediately available. For example, if you need time data aggregated at the month level, but existing fact tables provide only day-level information, you can use nested aggregation to obtain the results you are looking for.

In their structure, nested metrics:

  • use the definition from another metric as part of the calculation.
  • include a level definition and may also have conditions and transformations, which are independent from those of metrics being used as part of their calculation.

    Although temporary tables built to calculate nested metrics are used in the same manner as other fact tables, they serve the purposes of a specific nested aggregation only; they cannot be shared.

The following is an example of a nested metric:

Avg(Sum(Fact){~+, month+}){~+, Year+}

The {~+, month+} dimensionality applied to the Sum metric means that the metric is calculated at the month level regardless of what appears on the report.

The {~+, year+} dimensionality applied to the Avg metric means that the metric is calculated at the year level.

Compound metrics

Compound metrics are made by combining one or more other metrics using one or more mathematical operators. The other metrics may be simple, nested, or other compound metrics. The most important distinction is that compound metrics cannot have a level placed on the entire metric, although the level can be set separately on each of the components. That is, the Revenue Metric is a simple metric defined as:

Sum(Revenue){~+}

A compound metric can contain the Revenue Metric as shown below:

Rank([Revenue Metric])

Note that no level is set and Rank is a non-group function.

Non-group functions are OLAP and scalar functions such as rank.

A compound metric can also include expressions that act as metrics, such as:

(Avg(Revenue) {Year+}) + (Avg(Cost) {Year+})

Notice that while both the average functions have a level (Year), the metric as a whole does not.

Compound metrics can contain prompts and constantnumerical values, but cannot include conditions, levels, or transformations except for those already part of the simple metric they contain.

Compound metrics are automatically updated when changes occur in the definitions of the metrics they include.

The parts of a compound metric are discussed in detail in the section Definition of compound metrics.

Derived metrics

Derived metrics are discussed in detail in the section Creating metrics in the Report Editor.

Distinguishing between simple and compound metrics

It is easy to distinguish between simple and compound metrics in the Metric Editor. Compare the following examples:

Distinguishing between simple and compound metrics

Only the last example is a compound metric. The others, regardless of the complexity of their formulas, are simple metrics. When you collapse everything on a simple metric, the components (formula, level, condition, and transformation) are still visible. Since a compound metric does not contain these components at the level of the entire metric, you cannot see them. When you expand each expression of a compound metric, the components of each are exposed.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Microstrategy Topics