SAP HANA Interview Questions: Calculation Views, Pushdown, and Join Cardinality

A SAP HANA interview answer should explain why a calculation view returns the right result before explaining why it is fast. Declaring a join cardinality, pushing down a filter, or removing a node can change what the optimizer is allowed to do. An incorrect assumption can make a dashboard faster while making its totals wrong.

Official facts in this guide come from SAP's learning and documentation resources. Candidate reports: none are used to claim these are a company's exact questions. Original exercise: the revenue model, queries, and expected totals below are synthetic. They test modeling judgment, not a specific HANA deployment's measured performance. Rehearse this distinction with Reason About Composite Join Keys and Predicate Placement.

What is a calculation view in an interview answer?

SAP documents calculation views as HANA-specific information models, distinct from ordinary SQL views. A useful interview explanation describes the model's sources, transformations, output grain, measures, and consuming queries rather than listing node names. SAP's database-view overview.

Say what one output row means. “One row per order line after enrichment” differs from “one row per region and month after aggregation.” That distinction determines which keys should be unique and when a measure may safely be summed.

Then explain the business contract. Does revenue include canceled lines? Which currency is used? Does a customer dimension represent the current classification or the classification at sale time? Which filters are applied by the consumer? A visually tidy model does not answer those questions automatically.

This article focuses on HANA Cloud modeling concepts. Exact features, tooling, privileges, and configuration differ across HANA Cloud and platform versions. Verify the environment before promising a particular optimization or a specific diagnostic screen.

Exercise: why did revenue rise from 180 to 280?

Start with a fictional sales_lines source. Each (order_id, line_no) identifies one row. Its three amounts are 100, 50, and 30. Customers A, B, and C correspond to those lines respectively. All amounts use one currency and the same reporting scope.

The customer classification source contains two rows for A: one historical East record and one current West record. B has one West record. C has no classification record. The model joins on customer_id alone using a left outer join.

The fact total is 100 + 50 + 30 = 180. After joining, A's 100 appears twice, B's 50 once, and C's 30 once with missing dimension values. The joined sum is 100 + 100 + 50 + 30 = 280.

The cause is multiplication of the fact grain, not arithmetic instability or a missing GROUP BY. Grouping by region redistributes the multiplied amount; it does not restore the original contract.

Use these diagnostic queries on the synthetic model. They are simple SQL relational checks, not generated calculation-view definitions.

SELECT SUM(amount) AS fact_total
FROM sales_lines;

SELECT customer_id, COUNT(*) AS matching_rows
FROM customer_classification
GROUP BY customer_id
HAVING COUNT(*) > 1;

SELECT s.order_id, s.line_no, COUNT(*) AS joined_rows
FROM sales_lines s
LEFT JOIN customer_classification c
  ON c.customer_id = s.customer_id
GROUP BY s.order_id, s.line_no
HAVING COUNT(*) > 1;

The last query exposes A's duplicated line. It is an article-specific correctness asset: it checks the grain after the transformation rather than hoping the final total looks reasonable.

How should you repair that model?

First ask what classification the report needs. If it needs the current classification, select the valid current dimension row under an explicit contract. If it needs classification at the sale time, join on customer plus the applicable validity interval. Check that intervals do not overlap and that boundary conventions are unambiguous.

Do not use DISTINCT as a general repair. Two legitimate lines can have the same amount, and distinct projected values can erase valid facts. Conversely, historical dimension rows with different regions may remain distinct and still multiply the total.

An arbitrary MAX(region) is also a semantic decision, not a neutral deduplication. It chooses a value according to ordering rather than the business rule. If multiple current rows exist, determine whether to reject the data, resolve a documented priority, or quarantine it. Make that choice visible.

For the current-classification version of our exercise, assume the synthetic source has an is_current marker with exactly one current row for A and B. Keep that restriction inside the dimension relation or the join's condition, preserving C as unmatched. The result should be West revenue 150 and an unclassified amount of 30, totaling 180.

Now test an important boundary: if the predicate c.is_current = 1 is moved into the final WHERE, C's null-extended row fails it and disappears. The total becomes 150. The predicate's location changed the result contract.

What does join cardinality actually mean?

Official SAP guidance: join cardinality describes the relationship between sources and can help the optimizer omit joins under defined conditions. A wrong setting can produce unexpected results. The cardinality proposal reflects the data at proposal time and does not automatically track future changes. SAP's join optimization lesson.

For our repaired left outer join, n:1 expresses that a fact row can match at most one row on the dimension side. It does not mean every fact has a match: C can remain unmatched. A guarantee of existence is a separate assumption.

Do not declare n:1 because it improves the preview time. In the broken model, A has two matches; the declaration would contradict the data. Treat the setting as a correctness promise you must maintain through loads, history changes, and new source versions.

An interview follow-up might ask: “The right side is unused in this query. Can HANA remove the join?” Explain the relevant conditions rather than saying all unused joins vanish. Our left join with a genuinely at-most-one matching dimension cannot multiply facts; other join types, cardinalities, and requested measures can change that reasoning.

If the incorrectly declared join is pruned when only revenue is requested but retained when region is requested, the two query shapes can expose different totals. That is a potential consequence of an invalid model assumption, not proof that HANA randomly changes results.

Distinguish cardinality from referential integrity

A statement that each sale matches at most one customer classification is not a statement that every sale has a classification. The fictional C row demonstrates the difference.

SAP's referential join guidance describes a join that behaves like an inner join while supplying integrity assumptions for optimization. Such assumptions need evidence. Do not replace the left outer join with a referential join just to seek pruning; an unmatched fact can then have a different result under the intended semantics. SAP's referential join explanation.

A practical answer states the invariant and its enforcement point: “We validate uniqueness of the current classification during loading, monitor unmatched facts separately, and select the join behavior required by the report.” This is more credible than “the dimension should be clean.”

Consider late data and deletion. A dimension can be valid at deployment and become incomplete after a failed load. A static model setting does not detect that operational change. Prepare a small reconciliation check that runs when data is refreshed, not just when the view is designed.

When is filter pushdown safe?

Pushdown moves work closer to the source so fewer rows may reach later operators. The first question is whether the moved operation preserves meaning. Our relational reasoning below illustrates that requirement; it is not a claim that every calculation-view optimizer performs these exact rewrites.

A region filter after enrichment might be pushed to a dimension source when equivalent semantics are preserved. But a filter on the null-supplying side of an outer join can change which unmatched rows survive. Explain the required output before deciding where to place it.

Aggregation creates another boundary. Suppose the report includes customers whose total revenue exceeds 100. Filtering individual lines where amount > 100 before summing is not equivalent. A customer with two lines of 60 qualifies by total but has no individual line above 100.

Likewise, filtering a ranked output before computing rank may produce a different winner set. Calculated measures, conversions, and historical joins can introduce dependencies. “Push everything down” is therefore not a complete design strategy.

In the interview, demonstrate one legal transformation and one illegal transformation using a tiny dataset. Then inspect what the engine actually did. SAP's SQL Analyzer lesson distinguishes generated SQL inspection from runtime analysis: seeing a query representation does not tell you its measured performance. SAP's SQL Analyzer lesson.

What runtime evidence should you request?

Ask for the actual consuming query, parameters, selected columns, data volume, and relevant execution evidence. A preview of one node may not resemble the full consumer query or its aggregation and filter requirements.

Use runtime analysis to identify records processed, expensive operators, memory pressure, and execution sequence. Do not infer a bottleneck from a complex-looking diagram alone. Compilation time, database execution, network transfer, and client rendering are different parts of user-visible latency.

Symptom	First discriminating check
Revenue differs when a dimension is selected	Compare instantiated query shapes and validate join multiplicity.
Only some customers have inflated totals	Check dimension uniqueness and validity overlap for those keys.
A filtered report loses unclassified sales	Inspect outer-join predicates and the intended unmatched-row policy.
Preview is fast, final aggregate is slow	Capture the full query's runtime row counts and memory evidence.
A change is faster but totals differ	Stop the performance conclusion and reconcile correctness first.

Do not sum all operator durations as though they were independent wall-clock contributions. Parallel execution and parent-child relationships require tool-specific interpretation. Present the observed evidence and the uncertainty before proposing a node removal or rewrite.

What proves the repair is ready?

Build a correctness matrix around the business contract. Include duplicate current classifications, an unmatched fact, historical boundary dates, equal-value fact lines, and a consumer that requests no dimension attributes. Compare both overall and segmented totals.

For our current-classification exercise, acceptance means the three fact lines remain three contributions; West is 150; unclassified is 30; total is 180. The amount-filter counterexample should remain a separate test demonstrating why 60 plus 60 cannot be filtered line by line.

Measure the repaired workload across realistic parameter classes after the totals pass. Record whether the result was fully fetched and which columns were selected. An improvement for the dimension-free query does not establish an improvement for every report shape.

Sources and Further Reading

• SAP Learning: Querying Database Views.
• SAP Learning: Optimizing Joins.
• SAP Learning: Analyzing Executions with the SQL Analyzer.