Scribd
Upload
4 views2 pages

Benefits of Table Functions in SAP HANA

Table Functions in SAP HANA are preferred over SELECT queries and stored procedures for complex read-only logic as they return tabular results that can be directly integrated into Calculation Views and reporting tools. They support parameterization, encapsulate complex SQL logic, and offer performance benefits through execution within the database engine. This makes them reusable and modular, addressing limitations of both SELECT queries and stored procedures.

Uploaded by

sekharreddy1089
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
4 views2 pages

Benefits of Table Functions in SAP HANA

Table Functions in SAP HANA are preferred over SELECT queries and stored procedures for complex read-only logic as they return tabular results that can be directly integrated into Calculation Views and reporting tools. They support parameterization, encapsulate complex SQL logic, and offer performance benefits through execution within the database engine. This makes them reusable and modular, addressing limitations of both SELECT queries and stored procedures.

Uploaded by

sekharreddy1089
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

Ah, I see what you’re aiming at 👍 — why do we **actually** use a **Table Function**

in SAP HANA instead of just writing a **procedure** or running plain **SELECT


queries**?

This is a very common interview question for **HANA modeling**. Let me break it
down:

---

## 🔹 Why not just SELECT queries?

* A plain `SELECT` query is fine for **simple reporting**.


* But when logic gets **complex** (e.g., multiple joins, filters, hierarchies,
parameterization), you need something **reusable** and **modular**.
* `SELECT` alone can’t be parameterized and reused easily across models.

---

## 🔹 Why not just Stored Procedures?

* Procedures are good for **business logic & data manipulation**


(insert/update/delete).
* But **procedures cannot be consumed directly in Calculation Views** (the main
modeling objects in SAP HANA).
* Reporting tools (SAC, BW, Tableau, etc.) cannot read data directly from a
procedure.

➡ If you try to connect a **procedure to a Calc View**, it won’t work — because


procedures don’t act like a “data source,” they act more like an “action.”

---

## 🔹 Why **Table Functions** are preferred in Modeling

1. **Acts like a Virtual Table**

* A Table Function always returns a table → which means you can plug it directly
into a Calculation View.
* Reporting tools can query it just like a normal table/view.

2. **Reusable and Parameterized**

* You can pass parameters into a Table Function (e.g., Customer ID, Fiscal
Year).
* This makes it more flexible than static SELECT queries.

3. **Encapsulates Complex Logic**

* Joins, filters, unions, ranking, window functions, even calling procedures —


all can be wrapped inside a Table Function.
* Keeps your Calc Views **cleaner** by pushing logic into the TF.

4. **Performance Benefits**

* Table Functions are executed **inside the database engine (SQLScript)**.


* They benefit from **code pushdown**, meaning less data transfer and faster
queries compared to external SELECT scripts.

5. **Mandatory in Some Cases**


* Certain advanced nodes in Calculation Views (like **Scripted Calculation
Views**) are deprecated.
* SAP recommends using **Table Functions** instead.

---

## 🔑 Summary

| Approach | When to use


|
| -------------------- |
----------------------------------------------------------------------------------
|
| **SELECT query** | Simple, one-time ad hoc queries
|
| **Stored Procedure** | Business logic, DML operations (insert/update/delete),
batch jobs |
| **Table Function** | Complex read-only logic, reusable in **Calculation Views**
and **reporting tools** |

---

👉 So the answer to “Why use Table Function instead of Procedure/SELECT?” is:

**Because Table Functions can return tabular results that integrate directly with
Calculation Views and reporting tools, support parameterization, and encapsulate
complex SQL logic for performance and reusability.**

---

Would you like me to also give you a **real project-style example** (say in
BW/4HANA ADSO reporting), showing where a **procedure** fails but a **table
function** works perfectly?

Rule of Thumb:

If CDS has with parameters → pass them inside ( … ).

If CDS has no parameters → use normal WHERE.

You might also like