Skip to content

Metrics

Source - https://www2.microstrategy.com/producthelp/Current/manuals/en/AdvancedReporting.pdf

  • Types of Metrics
    • Simple metrics
    • Nested metrics
    • Compound metrics
    • Smart metrics
  • Total / SubTotal


Fact

Facts are values that represent business performance.

Characteristics

  • Are numeric
  • They can be aggregated to provide meaningful results.
  • They point to columns in data warehouse.

Metrics

Metric is an object that you create to perform a calculation on a fact.

Types of Metrics

Simple metrics

  • Sum (Revenue) {~} – sum (cost) {~}

Nested Metrics

  • Avg(sum(Profit) {~, Month}) {~, Year}

Compound Metrics

  • ([Region Revenue] / [Company Revenue])

MSTR

MSTR

Total Subtotal

Smart Metric

When the Allow Smart Metric check box is selected for a metric, it changes the manner by which its metric valus, including any of its subtotals, are calculated. We should select it when strictly summing the metric values doesn’t produce a meaningful Total subtotal value. eg. Sum of percentage.


Advanced metrics

  • Level metrics
  • Non-aggregatable metrics
  • Transformation metrics
  • Base formulas
  • Conditional metrics
  • Nested metrics
  • Advanced functions
  • Advanced subtotals


Level metrics

Level or dimensionality, enables to determine the attribute level at which a metric is calculated. By default, all metrics are calculated at report level.

MSTR

Level metrics settings:

  • Target
  • Filtering
  • Grouping

Target is the attribute level at which metric is calculated.

Filtering governs the relationship between the report filter and calculation of metrics. Determine how the metric interacts with the report filter (WHERE clause).

Grouping determines how the metric aggregates. Effects the GROUP BY clause of SQL.


Filtering options:

  • Standard
  • Absolute
  • Ignore
  • None

MSTR

Standard - the metric calculates only for the elements included in the report filter definition.

Absolute - raised the level of the report filter to that of the target.

Ignore - completely ignores any related report filtering criteria.

None - directs the mstr engine to use a particular fact table to calculate a metric.


Grouping options:

MSTR

Standard - Groups by the attribute level specified as the target.

None - Excludes the targer (and its children) for the report grouping. Calculates one total for target attribute.

Non-Aggregatable Metrics

Are those which should not be summed across a particular attribute or hierarchy.

Transformation Metrics

Are schema objects used to compare like values at different times. eg. This year versus last year or date versus month to date. Transformations are useful for discovering and analyzing time based trends in our data.

MSTR


Type of transformations

Expression based transformation – use mathematical formula in definition.

Table based transformation – reference a physical table in the data warehouse that defines the transformation from one time period to another.

Base formulas

Reusing formulas


Conditional metrics

It contains its own filter. It is completely separate and independent of any report filter.

MSTR

Nested Metrics

Avg(sum(revenue){~, employee}){~, region}

Advanced Functions

  • Count metrics
  • Rank
  • RunningSum runningAvg, MovingSum and MovingAvg
  • Round
  • NTile
  • Date and Time Functions
  • AddDays, AddMonths, DayOfYear, DaysBetween, MonthEndDate, MonthsBetween, MonthStartDate, YearEndDate, YearStartDate

Advanced Subtotals

Custom Subtotals