In any BI project, the ultimate goal is to provide the right data to the right user at right time within the right context. Organizational effectiveness in any functional area is measured using KPIs. The right set of KPIs provides organizations with a reference point to track the overall health of goals and objectives. For example, if an organization is looking to increase sales, it should monitor both Lead Conversion Rate as well as Customer Churn Rate, and a host of other KPIs to have a consistent view of the business objective.
What sets a Key Performance Indicator (KPI) apart from being ‘just a number’ is the business logic embedded as part of the definition. From a Data Warehousing perspective, fact data lays the foundation for KPIs to be computed. The properties and behavior of KPIs are heavily influenced by underlying fact types.
Fact Data Types can be grouped into three based on their behavior at run-time.
Cumulative / Additive
Any fact data i.e. measure/metric that can be aggregated across all dimensions of the model will fall under this category. This includes all aggregation functions such as SUM, COUNT, MAX, MIN, AVERAGE, etc. This is the simplest form of data and the majority of measures are cumulative in nature, which also acts as a base for the other two types.
Partially Cumulative / Semi-Additive
Measures that can be aggregated across all dimensions except ‘Time’ fall under this category. These are special scenario-based or business process-dictated facts that act as a cumulative fact in all dimensions, but along the Time dimension, these measures must refer to entire historical data to compute the correct value. Consider an example of a GL Account, which has financial entries in form of credit and debit entries. If we have to produce a report with a GL Account balance at end of each month for 12 months, the respective fact data i.e., credits and debits have to be aggregated for each month and the balance should be computed in reverse from the current balance. Employee count is another example that falls under these criteria, but calculating the number of employees at a point in time has a different approach.
Non-Cumulative / Non-Additive
These measures cannot be aggregated at all and are always calculated as the last measure. Different ratios and variances fall under this category and are at times referred to as ‘Run time Only Metrics’. These measures are not stored in the database and are routinely derived from two or more base measures that are either Additive or Semi-Additive in nature.
One important, and often overlooked point is the role a measure can play based on the context it is used. Assume Price and Quantity are two facts available and aggregating Price and Quantity in order to calculate Sales Amount as Price * Quantity will lead to erroneous results at run time. This calculation must be delegated to the database and aggregated later.
While defining a KPI, it is imperative that each fact data type is defined clearly so that behavior and property are known in advance.
NOTE: SAP BW attaches semantics to measures / Key Figures, wherein the property for Additive and Semi-Additive properties can be set during design time.