In any BI project, the ultimate goal is to provide the right data to right user at right time within the right context. Organization effectiveness in any functional area is measured using KPI’s. Right set of KPI’s provide organizations with a reference point to track 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 host of other KPI’s to have 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 definition. From Data Warehousing perspective, fact data lays the foundation for KPI’s to be computed. Properties and behavior of KPI’s are heavily influenced by underlying fact types.
Fact Data Types can be grouping 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 majority of measures are cumulative in nature, which also acts as base for 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 acts as a cumulative fact in all dimensions, but along Time dimension these measures must refer to entire historical data to compute the correct value. Consider an example of 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 current balance. Employee count is another example that falls under this 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, variances fall under this category and are at times referred 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 context it is used. Assume Price and Quantity are two facts available and aggregating Price and Quantity in order calculate Sales Amount as Price * Quantity will lead to erroneous results at run time. This calculation must be delegated to database and aggregated later.
While defining a KPI, it is imperative that the 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.