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])
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.
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
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:
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.
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