Providing business context: Calculating metric levels

A metric's formula must be calculated within the context of a business concept. For example, a report with the metric called Revenue on it could show a list of company revenue — but in what context? Revenue generated by a particular salesperson, or by your company's top five best-selling products? Revenue earned last year, or lost due to lost sales leads last quarter? Revenue data alone is meaningless without at least one business attribute to provide context to the metric data. If you add the attribute Year, revenue for the current year makes sense.

A metric, therefore, must be calculated in relation to some attribute if the metric is to have meaning on the report. Therefore, every metric definition must include a reference to the attribute that you want to provide context to the metric's calculation of data.

For an introduction to attributes, see Providing business context to a report: Attributes.

Level of calculation for a metric

As noted above, a metric must be calculated within the context of a business attribute - but which attribute? Most reports contain more than one attribute. For example, on a report containing the Revenue metric and the attributes Month and Year, is revenue calculated and displayed by month? Or is it calculated and displayed by year?

Another way to ask this question is, at what level is the Revenue metric calculated? Is it calculated at the higher-level Year attribute or the lower-level Month attribute? To understand an attribute's level, picture a hierarchy of related business attributes. An example is shown below:

The example above shows a hierarchy of all the attributes that relate to the business concept of Time. (These attributes and this Time hierarchy are part of the sample Tutorial project.) The attribute Year is higher than Quarter, Month, or Day, because it appears above those other attributes. The highest level attribute is usually the attribute that reflects the most-inclusive business concept. In this hierarchy, Day is the lowest-level attribute and reflects the least-inclusive business concept.

In another example, suppose that your company is an Internet-based retailer and has its call centers all over the U.S. Your company therefore stores its employee data in your data source within the concept of geographical regions within the U.S. The related business attributes within this idea of geographical region become part of the Geography hierarchy. An example using sample data from the Tutorial project is shown below:

In the Geography hierarchy above, Country is the highest-level attribute and Employee is the lowest-level attribute.

A metric's level (sometimes called dimensionality) determines the level at which the metric is calculated. Therefore, every metric must have a level as part of its definition. By default, a metric is calculated at the report level, which means it is calculated at the level of the attribute on the report in which the metric is placed.

When more than one attribute is on a report, as is generally the case, a metric is calculated by default at the level of the lowest-level attribute that is on the report. The lowest level is usually the attribute that reflects the least-inclusive business concept. For example, a report shows your company's revenue listed by month and year. The report therefore contains the Revenue metric and the attributes Year and Month. Is the Revenue metric going to be summed up and displayed by year? Or is it going to be summed up and displayed by month? The metric results by default are calculated to reflect monthly sales data, since Month is a less-inclusive, or lower-level, concept than Year.

Determining calculation level

In MicroStrategy, the level at which a metric is calculated is displayed in both the Function Editor and the Formula Editor.

In the Function Editor, the level appears in the Level area, as shown below, where it is listed as Report Level:

In the Formula Editor, the level appears within curly braces, as shown below.

• {~} denotes the default metric level. If the default level is changed, the name of the attribute that represents the new level appears between the curly braces.
• {~+} denotes that the metric is calculated at the level of the lowest attribute on the report, which is the default calculation level for a metric.

Following are examples of metric formulas with the level displayed at the end of the formula:

Sum(Revenue - Cost){~+}

Sum(Abs (Revenue - Cost)){~+}

The level at which a metric is calculated can be changed. In a report that shows Revenue, Year, and Month, which means the metric on the report measures revenue by year and month, you might change the Revenue metric's level from the default of Month (which is the level of the lowest attribute on the report,) and redefine the level as Year. The metric then calculates at the Year level, and the report results display yearly sales data. This metric appears in the Formula Editor as:

Revenue {Year}

For examples of more complex metrics with various levels, see the Advanced Metrics chapter of the Advanced Reporting Help.

Metrics that do not use a level

Certain types of metrics can only have a level applied to their constituent parts, not to the metric as a whole. These restricted metrics are called compound metrics. See Metrics made up of metrics: Compound metrics for a description and examples of these types of metrics.