AMDP vs. CDS Views: Mastering Code Pushdown Strategies on HANA

One of the most critical decisions in SAP HANA development is choosing the right tool for code pushdown— moving logic from the application server to the database layer.

You have two powerful options: ABAP Managed Database Procedures (AMDP) using SQLScript, and Core Data Services (CDS) Views. Both achieve code pushdown, but they serve different purposes.

This guide will help you understand when to use each, how they differ, and which patterns work best for your specific use case.

Understanding Code Pushdown

Before HANA, ABAP applications followed this pattern:

1. SELECT * from database (all rows)
2. Transfer to application server
3. Process in ABAP loops (filtering, aggregation, calculations)
4. Return result

Problem: Millions of rows transferred over network, processed slowly in ABAP

With HANA, the paradigm shifted:

1. Send logic to database
2. Process in-memory with parallel execution
3. Return only final result

Benefit: 10-100x performance improvement

What is CDS (Core Data Services)?

CDS is SAP's declarative, SQL-based data modeling language that defines:

• Views on database tables
• Associations between entities
• Calculated fields
• Access controls
• Semantic annotations

Example: Basic CDS View

@AbapCatalog.sqlViewName: 'ZSALESDATA'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view Z_SALES_ANALYSIS as select from zsales_table
{
  key sales_id as SalesID,
  customer_id as CustomerID,
  product_id as ProductID,
  @Semantics.quantity.unitOfMeasure: 'Unit'
  quantity as Quantity,
  unit as Unit,
  @Semantics.amount.currencyCode: 'Currency'
  amount as Amount,
  currency as Currency,
  
  // Calculated field
  amount * 1.19 as AmountWithTax,
  
  // Aggregation
  sum( amount ) as TotalAmount
}
group by 
  sales_id,
  customer_id,
  product_id,
  quantity,
  unit,
  amount,
  currency

CDS Strengths

• Declarative – Define what, not how
• Reusable – Views can be consumed by other views
• Optimized – HANA optimizer handles execution
• Annotations – Rich metadata for UI, authorization
• Associations – Easy navigation between entities

What is AMDP (ABAP Managed Database Procedure)?

AMDP allows writing imperative SQLScript directly in ABAP classes, managed by ABAP lifecycle.

Example: Basic AMDP

CLASS zcl_sales_amdp DEFINITION PUBLIC FINAL CREATE PUBLIC.
  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.
    
    CLASS-METHODS calculate_revenue
      IMPORTING VALUE(iv_year) TYPE gjahr
      EXPORTING VALUE(et_result) TYPE zsales_result_tt.
ENDCLASS.

CLASS zcl_sales_amdp IMPLEMENTATION.
  METHOD calculate_revenue BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING zsales_table zcustomer_table.
    
    et_result = 
      SELECT 
        s.customer_id,
        c.customer_name,
        SUM( s.amount ) as total_revenue,
        COUNT( * ) as order_count,
        AVG( s.amount ) as avg_order_value,
        CASE 
          WHEN SUM( s.amount ) > 100000 THEN 'PREMIUM'
          WHEN SUM( s.amount ) > 50000 THEN 'GOLD'
          ELSE 'STANDARD'
        END as customer_tier
      FROM zsales_table as s
      INNER JOIN zcustomer_table as c
        ON s.customer_id = c.customer_id
      WHERE s.fiscal_year = :iv_year
      GROUP BY s.customer_id, c.customer_name
      ORDER BY total_revenue DESC;
      
  ENDMETHOD.
ENDCLASS.

AMDP Strengths

• Procedural – Full control over execution logic
• Complex logic – Loops, variables, if-then-else
• Multiple steps – Intermediate results, temporary tables
• Advanced SQLScript – Window functions, recursive queries
• Performance tuning – Fine-grained optimization

CDS vs AMDP: Side-by-Side Comparison

When to Use CDS Views

✅ Use CDS When:

1. Building Data Models for Consumption

// Exposing sales data for Fiori app
@OData.publish: true
define view Z_SALES_FOR_UI as select from zsales
{
  key sales_id,
  @UI.lineItem: [{ position: 10 }]
  customer_name,
  @UI.lineItem: [{ position: 20 }]
  amount,
  @UI.lineItem: [{ position: 30 }]
  order_date
}

2. Creating Reusable Data Definitions

// Base view
define view Z_CUSTOMER as select from zcustomer
{
  key customer_id,
  name,
  country,
  credit_limit
}

// Consuming view (builds on base)
define view Z_CUSTOMER_SALES as select from Z_CUSTOMER
  association [0..*] to Z_SALES as _Sales
    on $projection.customer_id = _Sales.customer_id
{
  key customer_id,
  name,
  country,
  _Sales
}

3. Simple Aggregations and Calculations

define view Z_MONTHLY_REVENUE as select from zsales
{
  key substring( order_date, 1, 6 ) as YearMonth,
  currency_code,
  @Semantics.amount.currencyCode: 'currency_code'
  sum( amount ) as TotalRevenue,
  count( * ) as OrderCount,
  avg( amount ) as AvgOrderValue
}
group by 
  substring( order_date, 1, 6 ),
  currency_code

4. Building Analytical Queries

@Analytics.query: true
define view Z_SALES_CUBE as select from Z_SALES_FACT
  association [1] to Z_TIME_DIM as _Time
    on $projection.TimeKey = _Time.TimeKey
  association [1] to Z_PRODUCT_DIM as _Product
    on $projection.ProductKey = _Product.ProductKey
{
  @AnalyticsDetails.query.axis: #ROWS
  _Time.Year,
  _Time.Quarter,
  
  @AnalyticsDetails.query.axis: #ROWS
  _Product.ProductCategory,
  
  @AnalyticsDetails.query.axis: #COLUMNS
  @Aggregation.default: #SUM
  Amount,
  
  @Aggregation.default: #AVG
  Discount
}

5. Standard SQL Operations

Joins, unions, basic transformations, and filtering

When to Use AMDP

✅ Use AMDP When:

1. Complex Multi-Step Calculations

METHOD calculate_commission BY DATABASE PROCEDURE
  FOR HDB LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING zsales ztargets zbonus_rules.
  
  -- Step 1: Calculate base sales
  lt_base_sales = 
    SELECT employee_id, SUM( amount ) as total_sales
    FROM zsales
    WHERE year = :iv_year
    GROUP BY employee_id;
  
  -- Step 2: Get targets
  lt_with_targets = 
    SELECT 
      b.employee_id,
      b.total_sales,
      t.target_amount,
      CASE 
        WHEN b.total_sales >= t.target_amount THEN 1
        ELSE 0
      END as target_met
    FROM :lt_base_sales as b
    LEFT JOIN ztargets as t
      ON b.employee_id = t.employee_id
      AND t.year = :iv_year;
  
  -- Step 3: Apply bonus rules
  et_result = 
    SELECT 
      w.employee_id,
      w.total_sales,
      CASE 
        WHEN w.target_met = 1 AND w.total_sales > 500000 
          THEN w.total_sales * 0.15
        WHEN w.target_met = 1 
          THEN w.total_sales * 0.10
        ELSE w.total_sales * 0.05
      END as commission
    FROM :lt_with_targets as w;
    
ENDMETHOD.

2. Recursive Queries

METHOD get_org_hierarchy BY DATABASE PROCEDURE
  FOR HDB LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING zemployee.
  
  -- Recursive CTE for organization hierarchy
  et_result = 
    WITH RECURSIVE hierarchy AS (
      -- Anchor: top-level managers
      SELECT 
        employee_id,
        manager_id,
        name,
        1 as level,
        CAST( employee_id AS VARCHAR(1000) ) as path
      FROM zemployee
      WHERE manager_id IS NULL
      
      UNION ALL
      
      -- Recursive: subordinates
      SELECT 
        e.employee_id,
        e.manager_id,
        e.name,
        h.level + 1,
        h.path || '/' || e.employee_id
      FROM zemployee e
      INNER JOIN hierarchy h
        ON e.manager_id = h.employee_id
    )
    SELECT * FROM hierarchy
    ORDER BY path;
    
ENDMETHOD.

3. Window Functions and Advanced Analytics

METHOD calculate_running_totals BY DATABASE PROCEDURE
  FOR HDB LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING zsales.
  
  et_result = 
    SELECT 
      order_date,
      customer_id,
      amount,
      -- Running total by customer
      SUM( amount ) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) as running_total,
      -- Rank within customer
      RANK() OVER (
        PARTITION BY customer_id 
        ORDER BY amount DESC
      ) as amount_rank,
      -- Moving average (last 3 orders)
      AVG( amount ) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
      ) as moving_avg_3
    FROM zsales
    WHERE order_date >= :iv_start_date;
    
ENDMETHOD.

4. Performance-Critical Operations

METHOD optimize_stock_reorder BY DATABASE PROCEDURE
  FOR HDB LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING zinventory zsales_forecast zwarehouse.
  
  -- Complex logic requiring fine-tuned performance
  
  -- Step 1: Current inventory levels
  DECLARE CURSOR c_inventory FOR
    SELECT warehouse_id, product_id, quantity
    FROM zinventory
    WHERE quantity < reorder_point;
  
  -- Step 2: Calculate forecast demand
  lt_forecast = 
    SELECT 
      product_id,
      SUM( forecast_quantity ) as projected_demand
    FROM zsales_forecast
    WHERE forecast_date BETWEEN :lv_start_date AND :lv_end_date
    GROUP BY product_id;
  
  -- Step 3: Optimal reorder quantities
  et_result = 
    SELECT 
      i.warehouse_id,
      i.product_id,
      i.quantity as current_qty,
      f.projected_demand,
      GREATEST( 
        i.reorder_quantity,
        f.projected_demand - i.quantity
      ) as reorder_qty,
      w.preferred_supplier
    FROM zinventory i
    INNER JOIN :lt_forecast f ON i.product_id = f.product_id
    INNER JOIN zwarehouse w ON i.warehouse_id = w.warehouse_id
    WHERE i.quantity < i.reorder_point;
    
ENDMETHOD.

5. Data Migration and Transformation

METHOD migrate_legacy_data BY DATABASE PROCEDURE
  FOR HDB LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING zlegacy_sales zcustomer_mapping zproduct_mapping.
  
  -- Clean and transform legacy data
  
  -- Step 1: Validate and clean
  lt_clean = 
    SELECT 
      sales_id,
      UPPER( TRIM( customer_code ) ) as customer_code,
      TRIM( product_code ) as product_code,
      TO_DECIMAL( amount, 15, 2 ) as amount,
      TO_DATE( order_date, 'YYYYMMDD' ) as order_date
    FROM zlegacy_sales
    WHERE amount > 0 
      AND order_date IS NOT NULL;
  
  -- Step 2: Map to new keys
  lt_mapped = 
    SELECT 
      c.sales_id,
      cm.new_customer_id,
      pm.new_product_id,
      c.amount,
      c.order_date
    FROM :lt_clean c
    LEFT JOIN zcustomer_mapping cm 
      ON c.customer_code = cm.legacy_code
    LEFT JOIN zproduct_mapping pm 
      ON c.product_code = pm.legacy_code
    WHERE cm.new_customer_id IS NOT NULL
      AND pm.new_product_id IS NOT NULL;
  
  -- Step 3: Final output
  et_result = SELECT * FROM :lt_mapped;
  
ENDMETHOD.

Decision Matrix: CDS vs AMDP

Combining CDS and AMDP

Often, the best approach is using both together:

Pattern: CDS for Model, AMDP for Logic

// CDS View - Data Model
define view Z_SALES_BASE as select from zsales
{
  key sales_id,
  customer_id,
  product_id,
  amount,
  order_date
}

// AMDP - Complex Calculation
CLASS zcl_sales_calculator DEFINITION.
  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.
    
    CLASS-METHODS calculate_metrics
      IMPORTING VALUE(iv_year) TYPE gjahr
      EXPORTING VALUE(et_result) TYPE zmetrics_tt.
ENDCLASS.

CLASS zcl_sales_calculator IMPLEMENTATION.
  METHOD calculate_metrics BY DATABASE PROCEDURE
    FOR HDB LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING z_sales_base.
    
    -- Use CDS view as input
    et_result = 
      SELECT 
        customer_id,
        SUM( amount ) as total,
        -- Complex calculation here
        ...
      FROM z_sales_base
      WHERE substring( order_date, 1, 4 ) = :iv_year
      GROUP BY customer_id;
      
  ENDMETHOD.
ENDCLASS.

Pattern: AMDP as CDS View Implementation

// CDS View with AMDP implementation
@ClientHandling.type: #CLIENT_DEPENDENT
define view Z_COMPLEX_CALC
  with parameters
    P_Year : gjahr
  as select from zsales
{
  key sales_id,
  customer_id,
  @ObjectModel.readOnly: true
  calculated_metric
}
implemented by method zcl_complex_calc=>calculate

// AMDP Implementation
METHOD calculate BY DATABASE PROCEDURE
  FOR HDB LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY USING zsales.
  
  RETURN 
    SELECT 
      sales_id,
      customer_id,
      -- Complex SQLScript logic
      ...
    FROM zsales
    WHERE year = :P_Year;
    
ENDMETHOD.

Performance Considerations

CDS Performance Tips

• Use appropriate indexes on base tables
• Avoid SELECT * - specify only needed fields
• Use associations instead of joins when possible
• Consider partitioned tables for large datasets
• Monitor with HANA Studio SQL Plan Cache

AMDP Performance Tips

• Use table variables for intermediate results
• Minimize data movement between engine layers
• Leverage parallel execution with proper partitioning
• Use HINTS when optimizer makes poor choices
• Profile with HANA SQLScript Debugger

Testing Strategies

Testing CDS Views

" Create test class
CLASS zcl_test_cds_sales DEFINITION FOR TESTING
  DURATION SHORT
  RISK LEVEL HARMLESS.
  
  PRIVATE SECTION.
    METHODS test_sales_aggregation FOR TESTING.
ENDCLASS.

CLASS zcl_test_cds_sales IMPLEMENTATION.
  METHOD test_sales_aggregation.
    " Query CDS view
    SELECT * FROM z_sales_analysis
      INTO TABLE @DATA(lt_result)
      WHERE customer_id = '12345'.
    
    " Assertions
    cl_abap_unit_assert=>assert_not_initial( lt_result ).
    cl_abap_unit_assert=>assert_equals(
      act = lines( lt_result )
      exp = 5
    ).
  ENDMETHOD.
ENDCLASS.

Testing AMDP

CLASS zcl_test_amdp_calc DEFINITION FOR TESTING
  DURATION SHORT
  RISK LEVEL HARMLESS.
  
  PRIVATE SECTION.
    DATA mo_amdp TYPE REF TO zcl_sales_amdp.
    
    METHODS setup.
    METHODS test_revenue_calculation FOR TESTING.
ENDCLASS.

CLASS zcl_test_amdp_calc IMPLEMENTATION.
  METHOD setup.
    mo_amdp = NEW #( ).
  ENDMETHOD.
  
  METHOD test_revenue_calculation.
    DATA lt_result TYPE zsales_result_tt.
    
    " Call AMDP method
    mo_amdp->calculate_revenue(
      EXPORTING iv_year = '2026'
      IMPORTING et_result = lt_result
    ).
    
    " Assertions
    cl_abap_unit_assert=>assert_not_initial( lt_result ).
    
    READ TABLE lt_result INTO DATA(ls_result)
      WITH KEY customer_id = '12345'.
    
    cl_abap_unit_assert=>assert_equals(
      act = ls_result-total_revenue
      exp = 150000
      msg = 'Revenue calculation incorrect'
    ).
  ENDMETHOD.
ENDCLASS.

Best Practices Summary

✅ DO

• Start with CDS for standard scenarios
• Move to AMDP only when CDS limitations are hit
• Document AMDP logic thoroughly
• Use AMDP for testable complex logic
• Combine both when appropriate
• Monitor performance with HANA tools
• Write unit tests for AMDP methods

❌ AVOID

• Using AMDP for simple queries
• Putting business logic in CDS views
• Ignoring HANA optimizer in CDS
• Writing procedural code that could be declarative
• Not testing AMDP procedures
• Overcomplicating with unnecessary SQLScript

Conclusion

Both CDS and AMDP are essential tools in the HANA developer's toolkit. The key is knowingwhen to use which:

• CDS → Data modeling, UI consumption, simple aggregations
• AMDP → Complex calculations, multi-step logic, performance tuning
• Both → Leverage CDS reusability with AMDP power

Master both approaches, and you'll be able to solve any code pushdown challenge efficiently.