How To...Load and Convert SAP Long Text into HANA using SLT

[justin_molenaur2]

Summary

I know this is one of the many favorite topics for an SAP BI/BW developer, and can be the source of some confusion on how to accomplish. I have been a part of many hotly debated sessions where IT will argue that this is too costly to extract with BW or that "long texts don't belong in the data warehouse". We can argue until we are blue in the face, but at the end of the day, there is definitely a need to extract texts for reporting. I'm of the opinion that it can now be done, and be done easily in the new HANA landscape with SLT.

Requirement

The typical requirement is to see the long texts associated with the various SAP objects for reporting purposes inside of HANA. These texts are stored in a compressed format in ECC inside of the STXH/STXL tables. The typical approach in ECC system or BW extraction is to use the READ_TEXT function module on a record by record basis.

Since SLT operates solely on a row basis with no [easily achieved] ability to use source [ECC] tables for lookups, the approach needs to be changed in order to facilitate the extraction and conversion of these texts. So what we’ll do here is extract all of the texts we need from STXL, convert them in SLT and then we’ll have table STXL for use inside of HANA for further modeling.

This approach assumes that only 1 line of text is required per object (TDNAME). If you need to concatenate multiple lines of text for a given object, you will need to modify the code to suit. This also assumes a very common text, MATERIAL, GRUN. I won't go into detail on the HANA modeling side as that should be pretty straightforward.

Keep in mind this is a valid approach up to DMIS 2011 SP05 as apparently in SP06 there are some fancier ways to filter your data without having to use DB triggers AND either an event based or trigger based filter How To filter on the initial load & parallelize replication DMIS 2011 SP06 or higher. The usual disclaimer applies, this is only an example - you'll need to modify to suit your requirement.

I want to thank tobias.koebler for a lot of the input in putting this together within SLT. For some hints on the ABAP include, thanks to the original insight from thomas.zloch in Mass reading standard texts (STXH, STXL) and more recently  mansoor.ahmed3 in Alternative to READ_TEXT Function Module (No more FM needed) for some of the examples to get this going.

Process Overview

SLT

1. Add configuration to alter table structure of STXL
   1. Remove field CLUSTD from target. This is not usable in HANA, so no need to waste column space.
   2. Add field TEXT to target, CHAR132. This new field will store a single line of long text.
2. Create an ABAP include for BOR event. This will perform the conversion of the CLUSTD table and put it in the new column created in step 1a. Additionally, this will filter the data during initial load to limit only what text objects are required.
3. Create DB trigger modifications. Since the STXL table can be quite large, we only want the SLT triggers to “listen” for changes in the source table that are applicable to the text objects required.
4. Start replication of table STXL to HANA.

HANA

1. Create an attribute view on Material (MARA) if not already existing.
2. Insert STXL with a filter (for required text) as a left outer join.
3. Add attribute view to required Views.

Detailed Steps

1. Add configuration to alter table structure of STXL
   1. Remove field CLUSTD from target
   2. Add field TEXT to target, CHAR132

2. Create an ABAP include for BOR event

3. Create DB trigger modifications

___.TDOBJECT = 'MATERIAL' AND ___.TDID = 'GRUN' AND ___.TDSPRAS = 'E'

The syntax here will be dependent on your source DB, see below or Selective Data Replication / Filtering in SLT

TABNAME	Enter the name of the table in the sender system here.
DBSYS	Enter the database type of the source system.
LINE_NO	You can specify multiple lines if the condition is too complex to be filled in only one line.
LINE	Enter the trigger condition here. Only when the data change fulfills the condition , it will be recorded into the logging table for the SLT replication.

Example: Only replicate if AS4LOCAL is equal N:

DB2	___."field1" = 'value0' AND ___."field2" IN ( 'value1', 'value2' )	___."AS4LOCAL" = 'N'
DB6	___.field1 = 'value0' AND ___.field2 IN ( 'value1', 'value2' )	___.AS4LOCAL = 'N'
MSSQL	field1 = 'value0' AND field2 IN ( 'value1', 'value2' )	AS4LOCAL = 'N'
ORACLE	:___.field1 = 'value0' AND :___.field2 IN ( 'value1', 'value2' )	:___. AS4LOCAL = 'N'

Source code for Include

*&---------------------------------------------------------------------*

 *&  Include           ZMBIA_HANA_STXL_BOR

 *&---------------------------------------------------------------------*



 types: begin of ty_stxl_raw,

 clustr type stxl-clustr,

 clustd type stxl-clustd,

 end of ty_stxl_raw.



 DATA: lt_stxl_raw type standard table of ty_stxl_raw,

 wa_stxl_raw type ty_stxl_raw,

 lt_tline type standard table of tline,

 wa_tline type tline.



 *Filter out text records that we don't need

 IF <WA_S_STXL>-TDOBJECT = 'MATERIAL' AND <WA_S_STXL>-TDID = 'GRUN' AND <WA_S_STXL>-TDSPRAS = 'E' AND <WA_S_STXL>-SRTF2 = '0'.



 **Justin Molenaur 05/13/2014 - add check for deleted record

 if <WA_S_STXL>-CLUSTD is not initial.

 ** end change 05/13/2014



 *Put Source fields into internal table for IMPORT statement to work on

 wa_stxl_raw-clustr = <WA_S_STXL>-clustr.

 wa_stxl_raw-clustd = <WA_S_STXL>-clustd.

 append wa_stxl_raw to lt_stxl_raw.

 import tline = lt_tline from internal table lt_stxl_raw.



 *Only read first line of text

 READ TABLE lt_tline into wa_tline INDEX 1.



 *Only populate target column if there is a text line available

 if sy-subrc = 0.

 <WA_R_STXL>-TEXT = wa_tline-tdline.

 endif.



 **Justin Molenaur 05/13/2014 - add check for deleted record

 endif.

 ** end change 05/13/2014



 ELSE.

 SKIP_RECORD.

 ENDIF.

\

Start Replication

Check DB Trigger in Expert Functions/View Trigger Source Code

Notice that the trigger code has specific conditions to only log changes where these are met. This will help filter the required data down during the replication phase.

View Trigger Source Code

Source Database System: DB6

" Generated at 02272014 195828 by ALESLT200 for ALESLT200

EXEC SQL.

  CREATE TRIGGER "/1LT/00000361INS" AFTER INSERT

    ON "STXL" REFERENCING NEW AS NEW

    FOR EACH ROW MODE DB2SQL

    BEGIN ATOMIC

IF (

1. NEW.MANDT = '200' AND
2. NEW.TDOBJECT = 'MATERIAL' AND NEW.TDID = 'GRUN' AND NEW.TDSPRAS = 'E'

) THEN

      INSERT INTO "/1CADMC/00000361"

        VALUES

         ( GENERATE_UNIQUE (), TIMESTAMP(GENERATE_UNIQUE ()), ' ',

       NEW."MANDT" ,

       NEW."RELID" ,

       NEW."TDOBJECT" ,

       NEW."TDNAME" ,

       NEW."TDID" ,

       NEW."TDSPRAS",

        'I');

END IF;

    END

1. ENDEXEC.

Voila! We have text associated with our Material in HANA

Now let's make a change and see if flow through to HANA

MM02/Basic Data 1, Basic Data Texts

Change text to append my name

Save

Somewhere between 1 and 4 seconds later...new data in HANA

Let’s check good old READ_TEXT to see the results – we have a match!

From here, the HANA modeling should take over and you should have your texts available for reporting.

Happy HANA,

Justin