Since SAP Release 7.40 SP05, BW transformations can be executed directly on the SAP HANA database if no ABAP routines are used. The use of SQLScript transformations can greatly improve performance in a business warehouse. SQLScript can be executed in so-called AMDP (ABAP Managed Database Procedures). The outtab represents the result of the transformation.
For recurring tasks, an ABAP program is suitable that can be used to automatically generate an SQLScript snippet. SAP Business Planning and Consolidation (BPC) has a similar automation already available in the standard with the program "RSPLS_SQL_SCRIPT_TOOL".
Recurring tasks are, for example, when long field lists of a DataStore object have to be declared as outtab of the AMDP or existing ABAP transformations have to be converted to SQLScript.
Long field lists of a DataStore object are a big coding effort for the developer and an automation of such a process allows a considerable time saving. Automating the generation of SQLScript also ensures that the coding is standardized and less error-prone. An example generated SQLScript by the ABAP program is shown in the following section.
Calling the ABAP program for automatic generation of SQLScript
********************************************************************
* DISCLAIMER *
*
* Gewählte Parameter: *
* InfoProvider: /NLY/PAAEMP *
*
* Date: 04.01.2022 *
* Time: 10:52:07 *
* Benutzer: NLY *
********************************************************************
/* Vorlage fuer Outtab
outtab = SELECT
"/B787/S_PERNR" as "/B787/S_PERNR", -- Mitarbeiter
"SOURSYSTEM" as "SOURSYSTEM", -- Quellsystem ID
"DATETO" as "DATETO", -- gültig bis
"DATEFROM" as "DATEFROM", -- gültig ab
"/B787/S_TRECORDM" as "/B787/S_TRECORDM", -- Recordmode (Kunde)
"CALMONTH" as "CALMONTH", -- Kalenderjahr/-monat
"/B787/S_TREQUEST" as "/B787/S_TREQUEST", -- Request
"/B787/S_TLOADDAT" as "/B787/S_TLOADDAT", -- Load date - technisches Änderung
"/B787/S_TDELFLAG" as "/B787/S_TDELFLAG", -- Lösch Flag
"/B787/S_SCOST" as "/B787/S_SCOST", -- Kostenstelle
"/B787/S_SCOAR" as "/B787/S_SCOAR", -- Kostenrechnungskreis
"/B787/S_SDEFPS" as "/B787/S_SDEFPS", -- Kennzeichen für generelle Arbeit
"/B787/S_SFACTOR" as "/B787/S_SFACTOR", -- Faktor
"/B787/S_SDYAVG" as "/B787/S_SDYAVG", -- Anzahl Stunden pro Tag
"/B787/S_SWKAVG" as "/B787/S_SWKAVG", -- Anzahl Stunden pro Woche
"/B787/S_SMOAVG" as "/B787/S_SMOAVG", -- Anzahl Stunden pro Monat
"/B787/S_SYRAVG" as "/B787/S_SYRAVG", -- Anzahl Stunden pro Jahr
"/B787/S_SPROZT" as "/B787/S_SPROZT", -- Gewichtungsprozentsatz
"/B787/S_SSHORT" as "/B787/S_SSHORT", -- Objektkürzel
"/B787/S_SSTEXT" as "/B787/S_SSTEXT", -- Objektbezeichnung
"/B787/S_ORGID" as "/B787/S_ORGID", -- Organisationseinheit
"/B787/S_ORGHLEVL" as "/B787/S_ORGHLEVL", -- Hierarchiestufe
"/B787/S_ORGLVL1" as "/B787/S_ORGLVL1", -- Level 1 - ID
"/B787/S_ORGLVL2" as "/B787/S_ORGLVL2", -- Level 2 - ID
"/B787/S_ORGLVL3" as "/B787/S_ORGLVL3", -- Level 3 - ID
"/B787/S_ORGLVL4" as "/B787/S_ORGLVL4", -- Level 4 - ID
"/B787/S_ORGLVL5" as "/B787/S_ORGLVL5", -- Level 5 - ID
"/B787/S_ORGLVL6" as "/B787/S_ORGLVL6", -- Level 6 - ID
"/B787/S_ORGLVL7" as "/B787/S_ORGLVL7", -- Level 7 - ID
"/B787/S_ORGLVL8" as "/B787/S_ORGLVL8", -- Level 8 - ID
"/B787/S_ORGLVL9" as "/B787/S_ORGLVL9", -- Level 9 - ID
"/B787/S_SSTELL" as "/B787/S_SSTELL", -- Stelle
"/B787/S_SBUKRS" as "/B787/S_SBUKRS", -- Buchungskreis
"/B787/S_SWERKS" as "/B787/S_SWERKS", -- Werk
"/B787/S_SPERSA" as "/B787/S_SPERSA", -- Personalbereich
"/B787/S_SBTRTL" as "/B787/S_SBTRTL", -- Personalteilbereich
"/B787/S_SSTATUS" as "/B787/S_SSTATUS", -- Status der Vakanz
"/B787/S_STAT2" as "/B787/S_STAT2", -- Status Beschäftigung
"/B787/S_STAT1" as "/B787/S_STAT1", -- Status Kundenindividuell
"/B787/S_JUPER" as "/B787/S_JUPER", -- Juristische Person
"/B787/S_PERSG" as "/B787/S_PERSG", -- Mitarbeitergruppe
"/B787/S_PERSK" as "/B787/S_PERSK", -- Mitarbeiterkreis
"/B787/S_BTRTL" as "/B787/S_BTRTL", -- Personalteilbereich
"/B787/S_ABKRS" as "/B787/S_ABKRS", -- Abrechnungskreis
"/B787/S_ANSVH" as "/B787/S_ANSVH", -- Anstellungsverhältnis
"/B787/S_STELL" as "/B787/S_STELL", -- Stelle
"/B787/S_MOLGA" as "/B787/S_MOLGA", -- Ländergruppierung
"/B787/S_PLANS" as "/B787/S_PLANS", -- Planstelle
"/B787/S_VDSK1" as "/B787/S_VDSK1", -- Organisationsschlüssel
"/B787/S_PERSA" as "/B787/S_PERSA", -- Personalbereich
"/B787/S_GSBER" as "/B787/S_GSBER", -- Geschäftsbereich
"/B787/S_BUKRS" as "/B787/S_BUKRS", -- Buchungskreis
"/B787/S_NATIO" as "/B787/S_NATIO", -- Nationalität
"/B787/S_NATI2" as "/B787/S_NATI2", -- Zweite Nationalität
"/B787/S_NATI3" as "/B787/S_NATI3", -- Dritte Nationalität
"/B787/S_E_AGE" as "/B787/S_E_AGE", -- Alter
"/B787/S_C_AGE" as "/B787/S_C_AGE", -- Alter (Merkmal)
"/B787/S_GBDAT" as "/B787/S_GBDAT", -- Geburtsdatum
"/B787/S_PERID" as "/B787/S_PERID", -- Personal-Identifikationsnummer
"/B787/S_GESCH" as "/B787/S_GESCH", -- Geschlecht
"/B787/S_ORT01" as "/B787/S_ORT01", -- Ort
"/B787/S_SBGRU" as "/B787/S_SBGRU", -- Behindertengruppe
"/B787/S_SBFAK" as "/B787/S_SBFAK", -- Anrechnungsfaktor des Arbeitsamt
"/B787/S_SBPRO" as "/B787/S_SBPRO", -- Schwerbehinderung : Prozentsatz
"/B787/S_PSTLZ" as "/B787/S_PSTLZ", -- Postleitzahl
"/B787/S_STRAS" as "/B787/S_STRAS", -- Straße und Hausnummer
"/B787/S_ARBST" as "/B787/S_ARBST", -- Tägliche Arbeitsstunden
"/B787/S_CARBST" as "/B787/S_CARBST", -- Tägliche Arbeitsstunden (Merkmal
"/B787/S_WKWDY" as "/B787/S_WKWDY", -- Wöchentliche Arbeitstage
"/B787/S_CWKWDY" as "/B787/S_CWKWDY", -- Wöchentliche Arbeitstage (Merkma
"/B787/S_TEILK" as "/B787/S_TEILK", -- Kennzeichen Teilzeitkraft
"/B787/S_E_SCHKZ" as "/B787/S_E_SCHKZ", -- Arbeitszeitplanregel
"/B787/S_EMPCT" as "/B787/S_EMPCT", -- Arbeitszeitanteil
"/B787/S_CEMPCT" as "/B787/S_CEMPCT", -- Arbeitszeitanteil (Merkmal)
"/B787/S_WOSTD" as "/B787/S_WOSTD", -- Tatsächliche Wochenarbeitszeit
"/B787/S_CWOSTD" as "/B787/S_CWOSTD", -- Tatsächliche Wochenarbeitszeit (
"/B787/S_TRFAR" as "/B787/S_TRFAR", -- Tarifart
"/B787/S_TRFGB" as "/B787/S_TRFGB", -- Tarifgebiet
"/B787/S_TRFGR" as "/B787/S_TRFGR", -- Tarifgruppe
"/B787/S_TRFST" as "/B787/S_TRFST", -- Tarifstufe
"/B787/S_TRFKZ" as "/B787/S_TRFKZ", -- Gruppierung MaKr f Tarifregelung
"/B787/S_BSGRD" as "/B787/S_BSGRD", -- Beschäftigungsgrad in %
"/B787/S_CBSGRD" as "/B787/S_CBSGRD", -- Beschäftigungsgrad in % (Merkmal
"/B787/S_CTEDT" as "/B787/S_CTEDT", -- Befristet bis
"/B787/S_CTTYP" as "/B787/S_CTTYP", -- Vertragsart
"/B787/S_ARBER" as "/B787/S_ARBER", -- Ende Arbeitserlaubnis
"/B787/S_EINDT" as "/B787/S_EINDT", -- Ersteintrittsdatum
"/B787/S_KONDT" as "/B787/S_KONDT", -- Eintrittsdatum in Konzern
"/B787/S_ATZPH" as "/B787/S_ATZPH", -- Altersteilzeit Phase
"/B787/S_ATZMO" as "/B787/S_ATZMO", -- Altersteilzeit-Modell
"/B787/S_USRID" as "/B787/S_USRID", -- SAP Benutzer ID
"/B787/S_LFZED" as "/B787/S_LFZED", -- Lohnfortzahlungsende
"/B787/S_DOCNR" as "/B787/S_DOCNR", -- Belegnummer für Personenzeitdate
"/B787/S_KRGED" as "/B787/S_KRGED", -- Krankengeldende
"/B787/S_ANTAGE" as "/B787/S_ANTAGE", -- An-/Abwesenheitstage
"/B787/S_CANTAGE" as "/B787/S_CANTAGE", -- An-/Abwesenheitstage (Merkmal)
"/B787/S_ANSTUND" as "/B787/S_ANSTUND", -- An-/Abwesenheitsstunden
"/B787/S_CANSTUND" as "/B787/S_CANSTUND", -- An-/Abwesenheitsstunden (Merkmal
"/B787/S_ABRTAGE" as "/B787/S_ABRTAGE", -- Abrechnungsstage
"/B787/S_CABRTAGE" as "/B787/S_CABRTAGE", -- Abrechnungsstage (Merkmal)
"/B787/S_ABRSTUN" as "/B787/S_ABRSTUN", -- Abrechnungsstunden
"/B787/S_CABRSTUN" as "/B787/S_CABRSTUN", -- Abrechnungsstunden (Merkmal)
"/B787/S_AWART" as "/B787/S_AWART", -- Abwesenheitsart
"/B787/S_FLSTDAZ" as "/B787/S_FLSTDAZ", -- Flag: Beginn Abwicklung
"/B787/S_BGABW" as "/B787/S_BGABW", -- Beginn Abwesenheit
"/B787/S_KZFOKR" as "/B787/S_KZFOKR", -- Kennzeichen Folgeerkrankung
"/B787/S_KZMFKR" as "/B787/S_KZMFKR", -- Kennzeichen Mehrfacherkrankung
"/B787/S_LGTSICK" as "/B787/S_LGTSICK", -- Langzeit krank
"/B787/S_LGTABS" as "/B787/S_LGTABS", -- Langzeit abwesend
"/B787/S_KOSTL" as "/B787/S_KOSTL", -- Kostenstelle
"/B787/S_KOKRS" as "/B787/S_KOKRS", -- Kostenrechnungskreis
"/B787/S_FKBER" as "/B787/S_FKBER", -- Funktionsbereich
"/B787/S_MSTBR" as "/B787/S_MSTBR", -- Meisterbereich
"/B787/S_ORGEH" as "/B787/S_ORGEH", -- Organisationseinheit
"/B787/S_SPLANS" as "/B787/S_SPLANS", -- Planstelle
"/B787/S_TOHNEFS" as "/B787/S_TOHNEFS", -- Flag: ohne Fortschreibung
"/B787/S_TAKTST" as "/B787/S_TAKTST", -- Aktueller Stand
"/B787/S_TKZML" as "/B787/S_TKZML", -- Monatsletzter Kennzeichen
"/B787/S_HC_ME" as "/B787/S_HC_ME", -- Headcount (Monatsende)
"/B787/S_FTE_ME" as "/B787/S_FTE_ME", -- FTE (Monatsende)
"/B787/S_CNTCALD" as "/B787/S_CNTCALD", -- Kalendertage
"/B787/S_HC_AVG" as "/B787/S_HC_AVG", -- HC Durchschnitt
"/B787/S_FTE_AVG" as "/B787/S_FTE_AVG", -- FTE Durchschnitt
"/B787/S_COUNT" as "/B787/S_COUNT", -- Anzahl
"/B787/S_CNTWRKD" as "/B787/S_CNTWRKD", -- Arbeitstage
"/B787/S_CNTWRKDT" as "/B787/S_CNTWRKDT", -- Arbeitstage gewichtet
"/B787/S_UNIT1" as "/B787/S_UNIT1", -- Einheit
"/B787/S_UNIT2" as "/B787/S_UNIT2" -- Einheit
FROM :INTAB; */
The description generated in the SQLScript as a comment makes it easy for the developer to see what kind of field it is, if the technical name does not make this directly recognizable.
Download our whitepaper and learn how you can boost the performance of your BW with SQLScript
Another scenario is target matching to identify deleted and updated records in the target, as well as new records from the source. By identifying these records, a date field can be provided with the last modification date, for example, in order to subsequently be able to determine in the target when a record was modified.
The application possibilities of the automatic generation of SQLScript are manifold. All logic that recurs in AMDP-based transformations can be easily generated and easily adapted by the developer through automation.
Changes to the ABAP program are easy
The advantages of automatic generation of SQLScript are great. Costs are minimized and coding errors are avoided. Automated generation can increase quality and ensure consistency across different AMDPs.
Any DataStore objects can be passed to dynamically generate SQLScript for further use in AMDP. The developer's workload is thus reduced many times over and the coding is less error-prone. Even if the structure of a DataStore object changes, no major adjustments are necessary. The re-execution of the program for the automatic generation of SQLScript is sufficient to get an updated outtab.
Are you planning to migrate to SQLScript and need help in planning the right strategy? Or do you need experienced developers to implement your requirements? Please do not hesitate to contact us - we will be happy to advise you.
