SQLScript in DWC

HANA Tech Night, 23.5.2022

Jörg Brandeis

Jörg Brandeis

  • Selbständiger SAP Berater und Entwickler
  • Diplom Wirtschaftsinformatik
  • Seit 2002 Erfahrung im Bereich SAP Software und SAP BW
  • Mein Fokus ist die Entwicklung
  • Von 2002 bis 2015 Festangestellt bei der zetVisions AG, ab 2006 als Entwicklungsleiter für die Produktentwicklung in ABAP
  • Erfahrung mit SAP BW seit Release 3.1



Kontakt: joerg@brandeis.de

Twitter: joerg_brandeis

Website: www.brandeis.de

SAP Trainings by Brandeis Consulting

Technologieschulungen zu alle relevanten SAP Technolgien: 

  • SQLScript / HANA SQL
  • Calculation Views & HANA Modellierung
  • AMDP
  • BW/4HANA
  • Modern ABAP
  • HANA Cloud
  • ABAP Development Tools (ADT)

Motivation for this talk

  • Dataflows in DWC can only use Python :-(
  • SQLScript is not possible
  • I'd like to find out, what is possible with SQLScript or HANA SQL in the DWC

No SQL in the Architecture Slides of SAP

Approaches for SQL in DWC

  • SQL Expressions for columns in graphical views

  • SQL-View - Language SQL (Standard Query)

  • SQL-View - Language SQLScript (Table functions)

  • Open SQL Schema - A special schema for a space

  • Existing HDI Containers - Connection request via Ticket…

SQL Expressions for columns in graphical views

Even subselects are allowed in the Expression Fields!

SQL-Views (SQL)

SQL-Views (SQL)

  • One single statement
  • Nested Sub-Queries are allowed
  • Identifiers are case sensitive, even without double quotes!!!

SQL-Views (SQLScript)

SQL-Views (SQLScript)

  • Language SQLScript with all possible language extents

    • Table variables

    • MAP_MERGE operators

    • Libraries (e.g. STRING)

    • Imperative Logic in SQLScript, e.g. FOR-Loop or IF/ELSE

    • The identifier in standard HANA SQL manner

  • Limitations

Example: Transpose Data

Example: Transpose Data

lt_fiscper3 = select lpad(element_number, 3, '0') as fiscper3 from series_generate_integer(1,1,13);
lt_tmp = select *,
                fiscyear || fiscper3 as fiscper,
                CASE fiscper3
                  when '001' then "AMOUNT_01"
                  when '002' then "AMOUNT_02"
                  when '003' then "AMOUNT_03"
                  when '004' then "AMOUNT_04"
                  when '005' then "AMOUNT_05"
                  when '006' then "AMOUNT_06"
                  when '007' then "AMOUNT_07"
                  when '008' then "AMOUNT_08"
                  when '009' then "AMOUNT_09"
                  when '010' then "AMOUNT_10"
                  when '011' then "AMOUNT_11"
                  when '012' then "AMOUNT_12"
                END as  amount
           from "PCA_PLAN"
           cross join :lt_fiscper3;

return select  "ACCOUNT",
       from :lt_tmp;

Open SQL Schema 

Open SQL Schema 

Open SQL Schema 

  • Read Access to Objects in the DWC Schema
  • Objects are reachable from the DWC Schema
  • Full SQL Access with 
    • Integration Scenarios
    • Procedures and Functions
    • Tables and Views
  • But no Repository

Existing HDI Containers 

  • Connections must be requested via Support Ticket
  • Read Access from and to the DWC Schema
  • All Features from the BTP usable


  • HANA SQL and SQLScript can be leverages in many places in DWC
  • When a DataFlow should use SQLScript, you can create a SQL-View as a source
  • SQLScript is the language of choice for Data Warehouse Cloud

(C) Brandeis Consulting GmbH