Define statistical metrics
Statistical metrics include derived metrics, atomic metrics, business filters, business processes, and statistic granularity (dimensions). You must define these metrics before designing a model to create a user-friendly data warehouse.
Considerations for metric definitions
An atomic metric defines the statistical scope and calculation logic. You can create an atomic metric after a fact-based model or dimension-based model is defined. Derived metrics are common statistical metrics that are calculated using the following formula: Derived metric = statistical period + business filter + atomic metric + statistic granularity.
Note the following when you create derived metrics:
First, create the atomic metric. Then, confirm that the source model of the atomic metric contains a dimension-based model. This lets you set the statistic granularity for the derived metric.
The atomic metric and the business filter must be from the same dimension table or fact table and inherit the subject area of the source table.
Statistic granularity and period are required. Whether you include a business filter depends on the semantics of the specific derived metric. For example, if the payment amount is an atomic metric, both "buyer's payment amount in the last 7 days" (statistic granularity: buyer, period: last 7 days) and "buyer's Alipay payment amount in the last 7 days" (statistic granularity: buyer, business filter: Alipay payment, period: last 7 days) can be used as derived metrics.
A derived metric can be associated with only one atomic metric and inherits the subject area of that atomic metric.
Determine the metrics
This tutorial uses the scenario of a marketing data analyst in the E-commerce marketing department of Company A. The analyst's data requirements include the total sales of kitchenware products in each province for the last day, the names of the top 10 selling products in this category, and the distribution of user purchasing power (per capita consumption) in each province.
Based on standard definitions, the business process is "confirmed receipt of goods (successful transaction)". This corresponds to the measure (sales amount of the product) in the fact-based model. Therefore, based on the business requirements, you can define the atomic and derived metrics as follows:
Atomic metric: The total amount of successful product transactions.
Derived metrics:
Total sales of each kitchenware product in each province for the last day.
Per capita consumption (total consumption divided by the number of people) for the kitchenware category in each province for the last day.
To obtain the names of the top 10 selling products in the category, you can sort the total sales of each kitchenware product in each province for the last day in descending order and select the top 10 names.