Types of Metrics

  • Simple metrics
  • Nested metrics
  • Compound metrics

Metric formatting

  • Metric value and header formatting
  • Subtotals/ aggregation tab

Advanced metrics

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

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.

Metric
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])

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.

Level metrics settings:
Target
Grouping
Filtering

Target is the attribute level at which metric is calculated.

Filtering setting governs the relationship between the report filter and the calculation of the metric.

Filtering options:
Standard
Absolute
Ignore
None

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 determines how the metric aggregates. Effects the GROUP BY clause of SQL.
Standard– Groups by the attribute.
None– excludes the target (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.


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.


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

Leave a Reply

Your email address will not be published. Required fields are marked *