Simple aggregations: These are things like Sum(Revenue), Average(Price), Count_Distict(Users). They are really easy to define and an isolated metric definition layer will do a good job of defining these. (View Highlight)
Note: Metric class 1
Aggregation with scalar functions: Similar to simple aggregations above, but with additional mathematical operators. For example, Profit may be defined as sum(Revenue) - Sum(Cost) - Sum (commissions). Alternatively, you may need some transformations at the row-level such as Sum(Revenue_in_local_currency * USD_Conversion_rate). (View Highlight)
Note: Metric class 2
Metrics that require joins: The simplest example can be a version of Revenue calculation where conversion rates change daily and you have another table that stores conversion rate in a dimension table. Now Revenue definition becomes Sum(Revenue_in_local_currency * conversion_rate(local_currency, transaction_date)). Here conversion_rate(local_currency, transaction_date) is a column from another table that needs to be brought in by joining on local_currency and transaction_date from the fact table. You could bypass the join requirement by defining a denormalized view, but views bring in other issues such as requiring all joins to be executed. (View Highlight)
Note: Metric class 3
Metrics with window functions: Things like Moving Averages, Cumulative Sum, or any kind of aggregation in data that represents a time-series or sequence of events that you need to aggregate on a window around current time falls in this group. (View Highlight)
Note: Metric class 4
Metrics with multiple aggregation levels: This is a special class of metrics that often represent ratios. For example, if you want to define the Market Share of a product in its category, you first have to sum up Revenue grouped at the Product level, then you want to sum up Revenue at the Product Category level, combine the results and then compute the ratio. In addition, if you want to observe Market Share across years then you need to include Year(transaction_date) in the grouping column both for numerator and denominator. So the grouping becomes dynamic. These metrics are handled differently in different BI products. For example, this would look like a Group Aggregate Formula in ThoughtSpot, in Tableau, they are called Level of Detail (LoD) Functions. (View Highlight)
Note: Metric class 5
Multi-fact metrics: Sometimes, your metrics span multiple fact tables that may or may not have a direct relationship with each other. For example:
Sales Fact and Bulk Purchase Fact for a retailer to calculate Profit Margin.
Services Provided, and Insurance Coverage for a hospital to calculate the profitability of different services. (View Highlight)
Note: Metric class 6
Getting these metrics requires being able to aggregate different fact tables to a granularity that joining the results makes sense and then post join, re-aggregate them. Legacy BI tools have two major issues here. They either couldn’t get to the level of granularity of data needed or required customer SQL, which is hard to maintain and limits the level of interactivity that can be built (View Highlight)
Note: Multi-fact metric calculations
Typically, the kinds of things that you specify in a logical data model are:
Business names for column (rev_txn_usd -> Revenue)
Whether a column is a Metric (Measure) or Dimension (Attribute). For example, Revenue is a Metric, but Age or Customer Name is a Dimension.
Joins: Exactly how to join tables (which pairs, join based on what join conditions, and whether it’s an inner join, outer join, or some other kind of join) and whether it represents a many-to-one, one-to-one, or one-to-many relationship. (View Highlight)
Note: Jobs to be done at the logical modeling layer
