Metric value [Mv]

Aplies to: datamart.

We want to store the calculations and definitions that are used by the business in our management reports in a central location using a readable language. Not interwoven into the many query’s that are part of the many reports that we produce. This is important because these calculations and definitions may change and the number of reports may grow rapidly.

The solution

We have 3 tables and a stored procedure.

[table “” not found /]

Example

[table “” not found /]
[table “” not found /]
[table “” not found /]
In the above example two contracts are sold on March-02 and one contract is aborted on March 03. If we want to see the sales in march we can just sum all values in Fct_Metric_value_stats with cal_date in march. We can easily build a drill-down report to show the individual contracts that were sold in March.

usp_update_metric_value

This procedure empties the Fct_Metric_value table and fills it using fresh calculations.

Metrics are mainly defined by queries based on the main Fact table (e.g. Fct_Contract) and Dim_Calendar.

For performance reasons we aggregate some metrics only per end of week, month, quarter and year in Fct_Metric_value_stats.

Pros

  • Performance: querying Fct_Metric_value_stats is much faster than performing the calculation during report execution.
  • Efficiency: analists don’t have to know the definitions by head. The can just select the right metric.
  • Data quality: when defintions change, you only have to change usp_update_metric_value. All reports use the same metric definitions. This is very useful when using drilldown reports.

Cons

  • Fct_Metric_value might become quite large. We need to keep focus on performance.