In SAP Business Warehouse (SAP BW), key figures represent the quantitative data essential for business analysis — such as sales revenue, quantities sold, or costs incurred. However, business requirements often demand more insightful metrics derived from these basic key figures. This is where Calculated Key Figures (CKFs) come into play.
This article explores the concept of calculated key figures, their significance, and how they enable users to create new, meaningful metrics in SAP BW reporting.
Calculated Key Figures are new key figures derived from existing ones using arithmetic operations, formulas, or complex expressions. They are defined at the reporting layer, allowing users to generate additional insights without altering the underlying data.
Unlike physical key figures stored in InfoProviders, CKFs are virtual — calculated on-the-fly during query execution.
- Flexibility: Create tailored metrics specific to reporting requirements without changing source data.
- Efficiency: Reduce the need for extra InfoCubes or DSOs to store derived data.
- Real-time Calculations: Get instant results based on current data during analysis.
- Simplify Reporting: Users can analyze performance ratios, percentages, averages, and more without external calculations.
- Ratios and Percentages: e.g., Profit Margin = (Profit / Revenue) * 100
- Differences: e.g., Sales Growth = Current Year Sales - Previous Year Sales
- Averages: e.g., Average Order Value = Total Revenue / Number of Orders
- Ratios Combining Key Figures and Characteristics: e.g., Sales per Customer or Cost per Unit.
Calculated Key Figures are primarily created within BEx Query Designer or SAP BW Query Designer:
- Open the Query Designer: Connect to your SAP BW system and open the relevant query.
- Navigate to Key Figures Section: Right-click in the key figures area and select “New Calculated Key Figure.”
- Define the Formula: Use existing key figures and operators (+, -, *, /) to create the desired calculation.
- Set Properties: Configure formatting options (decimal places, display units), exceptions, or filters.
- Save and Test: Validate the new CKF by executing the query and verifying the results.
Profit Margin (%) = (Net Profit / Net Sales) * 100
Sales Growth = Sales (Current Year) - Sales (Previous Year)
Note: Time-dependent calculations may require using variables or restricted key figures.
Average Sales per Customer = Total Sales / Number of Customers
- Restricted Key Figures: Combine CKFs with filters or conditions for more granular calculations.
- Exception Aggregation: Define how calculated key figures aggregate (sum, average, max) across dimensions.
- Variable Support: Use variables to dynamically adjust calculations based on user input.
- Hierarchy Awareness: Enable calculations to respect dimension hierarchies for accurate roll-ups.
- Keep It Simple: Use calculated key figures for straightforward metrics to avoid performance degradation.
- Avoid Excessive Nesting: Complex nested CKFs can slow down query performance.
- Test Extensively: Validate calculations with real data to ensure accuracy.
- Use Aggregation Rules Wisely: Understand how CKFs behave when aggregated at higher levels.
- Document Calculations: Maintain clear documentation for business users and developers.
Calculated Key Figures are a powerful feature in SAP BW that allows users to derive new business metrics dynamically, without modifying the underlying data models. By leveraging CKFs, organizations can gain deeper insights, improve decision-making, and build more flexible reports.
Mastering calculated key figures empowers SAP BW users to tailor analytics to evolving business needs efficiently and effectively.