🔥 DAY03 [SAP HANA Database & Performance] {Part-48}
Welcome to the day where you stop writing flat SQL and start engineering reusable, secure, high‑performance data models. Yesterday you learned to build SQLScript procedures and table functions. Today we elevate that knowledge into HANA’s native modeling environment: Calculation Views. This is the tool SAP itself uses inside S/4HANA embedded analytics. If you truly want to exploit the column store, you must master calculation views.
By the end of this deep‑dive, you’ll be able to design a single calculation view that replaces a dozen legacy reports, enforces row‑level security, handles hierarchical rollups, and accepts dynamic input parameters – all while performing sub‑second aggregations on billions of records. We’ll also clarify when to use native HANA modeling versus BW on HANA, so you never make the wrong architectural decision again.
📐 Calculation Views: The Heart of HANA Modeling
A Calculation View (CV) is a logical data model that defines how to combine, transform, and aggregate data from one or more source tables or views. It can be built using a graphical editor (nodes and arrows) or via SQLScript manually. The result is a column‑view that the HANA optimizer can push down to the column engines, giving you maximum performance.
🧩 Graphical vs SQL-Based Calculation Views – Choosing Your Weapon
The graphical editor offers a drag‑and‑drop interface with dedicated node types: Join, Union, Projection, Aggregation, Rank, etc. It’s visual, intuitive, and perfect for 80% of use cases. But for complex logic, dynamic filters, or reusing existing SQLScript, the SQL-based view (a “Scripted Calculation View”) gives you complete control.
🔷 The Node Types – Your Modeling Lego Blocks
1. Projection Node
The simplest node. It selects columns from a source table or view, can rename them, and apply basic filters. Think of it as a SQL SELECT with a WHERE clause. Use it to reduce columns, alias them, or restrict data early to minimize later processing.
2. Join Node
Combines two or more sources based on a condition. Supports inner, left outer, right outer, full outer, and text joins. You can choose join type and cardinality. HANA will try to push joins to the column engine. In graphical mode, you drag lines between sources.
3. Union Node
Combines rows from multiple sources with the same structure. Useful when you have identical data partitioned by region or year, or when you want to merge actuals and plan data from different tables.
4. Aggregation Node
Performs GROUP BY and calculates aggregates (SUM, COUNT, MIN, MAX, AVG). You can define multiple aggregation levels. This is the powerhouse for analytical reports. HANA can push aggregation into the column engine, making it blazing fast.
5. Rank Node
Adds a ranking window function, e.g., TOP 10 customers by revenue. It’s a specialized node to avoid manual window functions.
🏭 Real‑Life Scenario: Building a Sales Overview Calculation View
Imagine you need a model that shows total sales by customer and month for a given company code. You would:
- Use a Projection node on ACDOCA filtering BUKRS and KOART='D'.
- Join with KNA1 to get customer names.
- Add an Aggregation node grouping by KUNNR, NAME1, and month (derived from BUDAT). SUM(WSL).
- Optionally, a Rank node to show top 10 customers.
This entire model runs in HANA without any data leaving the database. No ABAP. No Excel.
🔄 From Legacy to Future: Migrating Attribute & Analytic Views
In HANA 1.0, the modeling world was split: Attribute Views (master data), Analytic Views (star schema with facts and dimensions), and Calculation Views (advanced). In HANA 2.0 and S/4HANA, only Calculation Views remain recommended. The old views still run, but SAP provides migration tools to convert them to calculation views.
⚠️ Why Migrate?
- Performance: Calculation views are fully optimized for the column store; legacy views may not use the latest engine features.
- Flexibility: You can combine formerly separate attribute and analytic logic into one view, reducing layers.
- Future‑proof: SAP’s tooling (HANA Studio, BAS) focuses on calculation views. No new features for old views.
🛠️ Migration Steps (Hands‑on)
- In HANA Studio, right‑click an Attribute View → “Migrate to Calculation View”.
- Repeat for the corresponding Analytic View. Choose to merge them into a single Calculation View if they were used together.
- Review the generated graphical model; often it creates a join between the ex‑analytic view (fact) and ex‑attribute views (dimensions).
- Refactor: remove unnecessary nodes, apply filters, add calculated columns.
- Test performance and adjust.
🌳 Hierarchies in Calculation Views – The Secret to Rollups
Financial reporting demands hierarchies: cost centers roll up to cost center groups, profit centers to segments, materials to product groups. HANA natively supports two types of hierarchies directly inside calculation views.
📊 Level Hierarchy
A rigid structure with a fixed number of levels. For example, Country → Region → City. Each level is a separate column in the master data table. You define the hierarchy by specifying the parent column for each level. This works well when the depth is known and consistent.
👨👦 Parent‑Child Hierarchy
Flexible, unlimited depth. Defined by two columns: child node and parent node. Each row states “node X belongs to parent Y”. This is how most SAP hierarchies work (SET hierarchies). Calculation views can resolve these at query time with the HIERARCHY functions.
⚙️ Using Hierarchies in a View
After adding a hierarchy to a calculation view (either level or parent‑child), you can drag it into the output and use hierarchy functions: HIERARCHY_ANCESTOR, HIERARCHY_LEVEL, etc. When you query the view, you can ask for “all cost centers under DEPT01” and the engine traverses the parent‑child links at runtime.
🧪 Lab: Add a Cost Center Hierarchy to Your ACDOCA Model
Take the sales view from earlier. Join it with a cost center master (CSKS) on KOSTL. Add a parent‑child hierarchy definition pointing to your hierarchy table. Now you can report profit by cost center group without any ABAP pre‑aggregation. This is pure HANA modeling power.
🔐 Analytic Privileges – Locking Down Data at Row & Column Level
Security in HANA modeling is not an afterthought. Analytic Privileges (AP) let you restrict which rows or columns a user can see, based on their database authorization. You define an AP as a separate object and assign it to a calculation view. This is critical for S/4HANA embedded analytics where a plant manager must not see other plants’ data.
🛡️ Column‑Level Security
You can hide sensitive columns, e.g., salary or margin. Create an AP that grants or restricts visibility on specific columns. Users without access see NULL values or the column is removed from the output.
👤 Row‑Level Security
More common: restrict rows based on attributes like plant, company code, or profit center. The AP is defined with a filter condition, e.g., BUKRS = '1000' or a dynamic procedure that returns allowed values based on the user’s name.
⚡ Real‑Life Scenario: Plant Manager Dashboard
You have a calculation view for inventory that shows stock quantities for all plants. You apply an analytic privilege that reads from a custom table ZUSER_PLANT. The AP’s SQL condition: PLANT IN (SELECT plant FROM ZUSER_PLANT WHERE uname = SESSION_USER). Now when the plant manager runs the dashboard, they only see their own plant’s data. Zero code changes in the frontend.
🧪 Hands‑on: Create an Analytic Privilege
- In HANA Studio, right‑click your package → New → Analytic Privilege.
- Select “Dynamic” for row‑level restriction.
- Enter the SQL condition referencing
SESSION_USERand your authorization table. - Associate the AP with the calculation view under the “Analytic Privileges” tab.
- Test with different database users.
🎛️ Parameterized Calculation Views – One View, Infinite Possibilities
Without parameters, a calculation view is static. But business reports need dynamic filters: period, company code, material group. Parameters allow the consumer to pass values at query time, and the HANA optimizer can push these into the model’s filters, reducing data volume early.
📝 Input Parameters vs Variables
- Input Parameter: Defined in the calculation view and mapped to a placeholder. The caller must supply a value (or a default is used). Example:
IP_COMP_CODE. - Variable: A session‑level placeholder that can be set once and used across multiple views. Variables are defined in the HANA SQL console and can be reused.
⚙️ Creating a Parameterized View (Graphical)
In the calculation view editor, go to the “Input Parameters” tab. Add a new parameter, e.g., P_COMPANY (NVARCHAR(4)). Then, in a Projection or Join node, add a filter: BUKRS = $$P_COMPANY$$. Now when you query the view, you write:
🔄 Using Parameters with Hierarchies
You can parameterize a hierarchy node, e.g., “Show all children of parameterized parent”. This makes drill‑down reports extremely flexible.
🧪 Lab: Build a Fully Parameterized Sales View
- Start with a Projection node on ACDOCA, add filters:
BUKRS = $$P_COMP$$andBUDAT BETWEEN $$P_FROM$$ AND $$P_TO$$. - Add a Join with KNA1 on customer.
- Add an Aggregation node grouping by customer and month.
- Add a parent‑child hierarchy on cost center (from CSKS).
- Set analytic privilege to restrict by
PLANTbased on user authorization.
Congratulations! You’ve just built a secure, reusable, drill‑down analytical model. This is what real S/4HANA embedded analytics look like.
⚖️ BW on HANA vs Native HANA Models – The Architectural Crossroads
Many SAP landscapes run both BW/4HANA and S/4HANA. The question arises: should I model in BW or natively in HANA? The answer depends on the use case.
🏗️ When to Use Native HANA Modeling
- Real‑time operational reporting: You need up‑to‑the‑second data without latency of ETL.
- Mixed workloads: Combining transactional and analytical queries on the same tables (ACDOCA).
- Lightweight models: Fewer than 10 dimensions, no complex write‑back, no planning.
- Development speed: Quick prototyping without full BW development lifecycle.
🏢 When to Choose BW on HANA (or BW/4HANA)
- Enterprise data warehousing: Complex transformations, historical versioning, non‑SAP data integration.
- Planning and forecasting: BW‑IP or BPC integrated planning.
- Governance and lineage: Strong metadata management and data lineage requirements.
- Multi‑source consolidation: Merging data from multiple S/4HANA systems and non‑SAP sources.
🔄 The Hybrid Reality
Many organizations use both: native HANA models for real‑time cockpit apps and BW/4HANA for corporate planning and complex data blending. It’s not an either‑or. Your knowledge of calculation views gives you the foundation to work in both environments, since BW/4HANA itself generates HANA calculation views under the hood.
📌 What You’ve Built Today – The Complete HANA Modeler’s Toolkit
You can now construct graphical and SQL‑based calculation views, migrate legacy models, embed hierarchies, enforce row‑level security, and parameterize everything. This is the exact skillset that a senior HANA developer uses daily. Tomorrow, we’ll explore the development lifecycle: HANA Studio, HDI containers, Git integration, and the BTP‑based SAP HANA Cloud. You’ll learn how to move these models from dev to production like a pro.
EXPLAIN PLAN to see how the optimizer pushes filters down.
0 Comments
thanks for your comments!