Lesson 5 of 6
Query regression testing and reconciliation
10 min
Listen to this lesson
Every BW path ends with one question from Dan: "Does my margin report still show the same numbers?" Query regression testing answers it with evidence. This lesson shows how to select queries, fix their inputs, compare results cell by cell, and handle the features that most often differ.
Selecting the regression set
- Critical: queries used in period close, statutory or management reporting (the monthly margin by customer, the management pack).
- Heavily used: the top queries by executions and users from BW statistics.
- Risky: queries on providers with routines, currency conversion, hierarchies, exception aggregation, conditions or non-cumulative key figures.
- Representative: at least one query per InfoProvider in scope, so every provider is touched.
Contoso's set for the lift has 120 queries out of the roughly 840 that remain after cleanup (illustrative): 35 critical, 50 heavy, 25 risky and 10 representative. Each has a test variant: fixed variable values (company code 1710, fiscal period 09/2026, ledger 0L, currency USD), a fixed navigation state (drilldown by customer and month), and the expected data date.
Running the comparison
- Freeze the data: run the baseline on a closed period after the last load, or on a copy, so source data cannot change between runs.
- Baseline: execute each query with its variant before the change (for example with RSRT or Analysis for Office) and export the full result, including totals.
- Change: perform the lift, conversion or rebuild.
- Rerun: execute the same variant on the target (BW PCE query, BW/4HANA query, or the new Datasphere analytic model or SAC story) and export.
- Compare cell by cell on the characteristics and key figures, not just grand totals. Zero tolerance for amounts and quantities in document or local currency.
- Classify differences: expected (documented design change), defect, or test artifact (wrong variant, different data date).
Features that most often differ
| Feature | Why it differs after a change | How to test |
|---|---|---|
| Currency conversion | Different rate type, date or translation step in the target | Compare in source currency first, then translated, for a period with rate changes |
| Exception aggregation | Target model aggregates in a different order | Hand-calculate three customers; compare at each drill level |
| Hierarchies | Different hierarchy version, date or unassigned nodes | Compare node totals and the unassigned node |
| Non-cumulative key figures (stock) | Snapshot logic rebuilt differently | Compare stock at several key dates, including period ends |
| Restricted and calculated key figures | Filter or formula translated slightly differently | One known case per key figure |
| Routines | Logic not carried over in a rebuild | Known cases that exercise each routine rule |
Where query results most often change after a move, and how to catch it.
- Check the runSame variant, same data date, same user role on both sides?
- Locate the cellWhich characteristic values and key figures differ?
- Check the featureCurrency, hierarchy, exception aggregation, non-cumulative, restricted key figure, routine?
- Find the layerData (load or product), model (logic) or presentation (query or story)?
- ClassifyExpected design change, defect, or test artifact
With 120 queries and several cycles, comparison must be automated. Export each baseline and target result in a structured form (characteristic keys plus key figures), store them with the run ID and variant, and compare them by key with a script or a comparison view in Datasphere. The comparison produces a list of differing cells, not a yes or no. Manual comparison of exported spreadsheets is where most regression projects lose time and accuracy, especially for queries with hundreds of customers and twelve months.
What would you do?
After the rebuild of the margin report in Datasphere, totals match for September, but the Chicago (plant 1720) customers show 156.00 standard cost in BW and 150.00 in Datasphere. What happened?
Regression for a rebuild has one extra difficulty: the target is not the same query. A Datasphere analytic model and an SAC story may present data at a different granularity, use different names, or deliberately change a definition. Before comparing, Priya writes a mapping per query: which BW characteristic maps to which dimension, which key figure to which measure, and which differences are intended (for example a new revenue definition that excludes freight). Only unmapped or unintended differences are defects; intended ones need Dan’s explicit approval as a design change.
- Select critical, heavily used, risky and representative queries; give each a stored test variant.
- Freeze the data, take a baseline, change, rerun, compare cell by cell.
- Watch currency, exception aggregation, hierarchies, non-cumulatives, restricted key figures and routines.
- Classify each difference as expected, defect or test artifact; zero unexplained amount differences.
Check your understanding · not graded
Lesson check
1. Why compare query results cell by cell instead of only grand totals?
2. What does a stored test variant fix?
3. How should stock (non-cumulative) key figures be regression-tested?
4. What causes many false differences in query regression?