Skip to Content
Author's profile photo Former Member

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

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 Ahmed 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

Assigned tags

      41 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo John Appleby
      John Appleby

      As anyone who knows me knows, I'm not a huge fan of "+1" comments... rather, rate 5* and like.

       

      But I did want to say that this sort of quality content is the reason why the SAP HANA Distinguished Engineer Program exists. Very nice indeed.

      Author's profile photo Thomas Zloch
      Thomas Zloch

      I was brought here by the direct mention, many thanks for that. Great to see how an idea can be re-used and enhanced in a quite different (and more modern) context.

       

      Cheers

       

      Thomas

      Author's profile photo Former Member
      Former Member

      Thomas Zloch was also mentioned in my blog. Sir thank you so much! I wrote a blog after your code snippet Alternative to READ_TEXT Function Module (No more FM needed). Plus I would love to thank Justin Molenaur for mentioning my blog is his Geeky article!

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Now this is a new spin on things, an ABAP'er calling a BI guy's blog Geeky

       

      Thanks for stopping by.

       

      Regards,

      Justin

      Author's profile photo Md Ekram
      Md Ekram

      Hi Justin,

       

      It has been deployed in Medical HANA in slightly different way on 11th Oct 2018 and It is productive now.

       

      Regards,
      Md.Ekram

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thomas, I had used some of your code as a reference when putting together a BW based solution long ago, so only fair to loop you in on the latest usage

       

      Just to add, if required I think you can get all the lines of a particular object by adding/modifying the include for code similar to the following. Of course you need to anticipate the largest text size and increase the target column appropriately.

       

      *new FS and variable to hold concatentated text

       

      FIELD-SYMBOLS: <TLINE> TYPE TLINE.

       

      DATA: lv_tline type string.

       

       

      *build a concatenated version of all text lines, choose your seperator if required

          LOOP AT LT_TLINE ASSIGNING <TLINE>.

            lv_tline = CONCATENATE lv_tline lv_tline SEPERATED BY '&'.

          ENDLOOP.

       

      *set result to the concatenated text lines

       

      <WA_R_STXL>-TEXT = lv_tline.

       

      Regards,

      Justin

      Author's profile photo Krishna Tangudu
      Krishna Tangudu

      Hi Justin,

       

      We are using import statement to make the text data to be presented in a readable format right?

       

      Regards,

      Krishna Tangudu

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Correct.

      Author's profile photo Former Member
      Former Member

      Justin, this is a gem of an article that should help a major pain point for many organizations. Thanks!

      Author's profile photo Raj Kumar Salla
      Raj Kumar Salla

      Agree with Jody. Excellent content.

      Author's profile photo Patrick Bachmann
      Patrick Bachmann

      Excellent blog Justin.  It looks like this will help us with an upcoming project.  Great stuff as always!

       

      -Patrick

      Author's profile photo shishupalreddy ramreddy
      shishupalreddy ramreddy

      Excellent blog, helpful and informative.

      Author's profile photo Former Member
      Former Member

      Hi

       

      Thanks for the blog..

       

      My requirement is similar but the -TDOBJECT = 'VBBK' AND TDID = '0002' AND TDSPRAS
      -E. I did all the coding similar as above. but while replicating in the HANA, data is not available in the STXL table. Can you please help me with the reason for the table is not getiing populated.

       

      Thanks,

      Viji

      Author's profile photo Alexey Ukrainsky
      Alexey Ukrainsky

      I find this way dangerous and could bring to inconsistent replication.

      As of DMIS 2011 SP6 we delivered the technique to ensure the consistent INDX table replication.

      Actually we reuse the functionality we used for data conversion in other application and made it work for replication. I will post the details soon

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Alexey, for the benefit of myself and others, can you please elaborate. Why is this inherently dangerous even if a customer is running SP6 or later? I have yet to see any other feasible solution for what is going to be a common requirement for replication of texts.

       

      Regards,

      Justin

      Author's profile photo Alexey Ukrainsky
      Alexey Ukrainsky

      Hi Justin,

       

      I created a new post about the standard method we developed for replication of INDX tables.

      Kind regards,

      Alexey.

      Author's profile photo vijaykumar ijeri
      vijaykumar ijeri

      Very nice research and putting it altogether is a great stuff!

       

      Thanks & Regards,

      Vijay

      Author's profile photo Former Member
      Former Member

      We just tried using this blog..But got stuck in replication. Created an OSS note and SAP replies that this is an incorrect post .

      To replicate INDX tables you have to be on SP6 mandatoriy..else replication will not work. I am hugely dissappointed following this post esp when SAP denies it categorically in a OSS ticket.

       

      Gurus, What do you suggest ??

       

      My code snippet which i modified was:

       

      1. 1. *&---------------------------------------------------------------------* 
        1. 2. *&  Include           Z_TEST_IMPORT_TEXT 
      2. 3. *&---------------------------------------------------------------------* 
        1. 4.   
      3. 5. types: begin of ty_stxl_raw, 
        1. 6. clustr type stxl-clustr, 
      4. 7. clustd type stxl-clustd, 
        1. 8. end of ty_stxl_raw. 
      5. 9.   
        1. 10.     DATA: lt_stxl_raw type standard table of ty_stxl_raw, 
      6. 11.     wa_stxl_raw type ty_stxl_raw, 
        1. 12.     lt_tline type standard table of tline, 
      7. 13.     wa_tline type tline. 
        1. 14.       
      8. 15.     *Filter out text records that we don't need 
        1. 16.     IF <WA_S_STXL>-TDOBJECT = 'VBBK' AND <WA_S_STXL>-TDID = 'ZD03' AND <WA_S_STXL>-TDSPRAS = 'RU'
      9. 17.       
        1. 18.     *Put Source fields into internal table for IMPORT statement to work on 
      10. 19.     wa_stxl_raw-clustr = <WA_S_STXL>-clustr. 
        1. 20.     wa_stxl_raw-clustd = <WA_S_STXL>-clustd. 
      11. 21.     append wa_stxl_raw to lt_stxl_raw. 
        1. 22.     import tline = lt_tline from internal table lt_stxl_raw. 
      12. 23.       
        1. 24.     *Only read first line of text 
      13. 25.     READ TABLE lt_tline into wa_tline INDEX 1. 
        1. 26.       
      14. 27.     *Only populate target column if there is a text line available 
        1. 28.     if sy-subrc = 0
      15. 29.     <WA_R_STXL>-TEXT = wa_tline-tdline. 
        1. 30.     endif. 
      16. 31.       
        1. 32.     ELSE. 
      17. 33.     SKIP_RECORD. 
        1. 34.     ENDIF. 
      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Saad, of course some custom code will not be supported by SAP directly.

       

      In addition, could you be a little more specific as to the issue you are facing? It's not very clear to me.

       

      I had some additions to the code that need to be added here, I'll do that later. After those changes, it is now running productively at the customer I am working with.

       

      Regards,

      Justin

      Author's profile photo Former Member
      Former Member

      The issue arises when replicating the STXL table. It fetches 0 records if I pursuit the steps you mentioned in the blog.

      Could you please provide what additional steps to be considered. It will be highly appreciated.

      We had comitted to customer that this would work based on this blog. a lesson learnt for us 🙂

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Well since you are attempting to implement this for paid work, I assume that when I provide the solution you will be sending me a check?

       

      The updates are in the code in the main post

       

      1.

      AND <WA_S_STXL>-SRTF2 = '0'. Added to if condition, at least for this requirement we only want to process the very first cluster.


      And the biggest offendor, which is probably where you are hung up - is solved with this.

      2.

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

      if <WA_S_STXL>-CLUSTD is not initial. 

      ** end change 05/13/2014 

       

      Essentially, there was no logic to check for a deleted record, for which SLT also tries to replicate. In these cases, all columns except the key will be blank, then this code attempts to unpack the CLUSTD table when in fact it's null. So in these cases, we need to ignore the deleted record in terms of processing the new column (the deletion still flows to HANA).

       

      Regards,

      Justin

       

      PS. You really didn't do much troubleshooting to figure out where the error was happening, I only read "SLT doesn't work". Think about digging a little deeper to find out the root cause.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      I would add that looking into Alexey's post above on the new method to replicate indx tables should be evaluated as well. I had not reviewed in length yet since we already have a viable solution.

       

      Regards,

      Justin

      Author's profile photo Former Member
      Former Member

      Committing to anything found on a blog to customers... is a bad idea.

       

      Committing to innovative ideas based on a blog, after developing and testing thoroughly, vetting for functionality, performance, security, etc... is a better idea.

       

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      But hey, I saw it on the internet - now do I win the RFP?

      Author's profile photo Former Member
      Former Member

      Hi Justin,

       

      Thanks for the wonderful Blog!!!.

      I followed the approach for one of my requirement to get the 3 custom text from STXL .

       

      When we replicated the data for STXL inot HANA, the data is not getting filtered and even text is not updating into HANA.However the structure change can be seen in HANA and also the load is not failing.

       

      Could you please let me what could be the reason for not filtering and text not updating in the HANA?

       

      Appreciate you help.

       

      Regards,

      Prasad

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Check the DB trigger code as mentioned above - you need to have both the initial load filtered (via DB trigger), and the deltas (via BOR filter). Is your initial load or delta not being filtered?

       

      If it's not failing, that might tell me that you have not set up the code to reflect your requirement, ie TDOBJECT and TDID. The code is executing successfully AND getting replicated, but the column is not being populated, maybe it's not making it past the set of IF statements.

       

      Regards,

      Justin

      Author's profile photo Former Member
      Former Member

      Hi Justin,

       

      I have used the same code and written in an Include and  defined a DB trigger for the record changes. but still the issue persists.

       

      Include code

       

       

      : 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 IN ('Z001','Z002','Z003') AND <wa_s_stxl>-tdspras = 'E'.

        IF <wa_s_stxl>-clustd IS NOT INITIAL.

      *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>-longtext = wa_tline-tdline.

          ENDIF.

        ENDIF.

      ELSE.

        skip_record.

      ENDIF.

       

       

      DB trigger Code

       

      :___.TDOBJECT = 'MATERIAL' AND :___.TDID IN ( 'Z001', 'Z002', 'Z003' )


      AND :___.TDSPRAS = 'E'

       

      Regards,

      Prasad

      Author's profile photo Former Member
      Former Member

      Indeed. the issue still persist.

      Author's profile photo Lars Breddemann
      Lars Breddemann

      Saw this one only now Justin... really, well done!

      I wish more content would be so relevant and well prepared and soundly stemming from actual developers' practice.

       

      Thanks for this piece.

      - Lars

      Author's profile photo Alexey Ukrainsky
      Alexey Ukrainsky

      I keep on saying there's the standard way of replicating INDX tables posted here http://scn.sap.com/community/replication-server/blog/2014/10/01/how-to-load-indx-tables-into-hana-using-slt

      Author's profile photo Former Member
      Former Member

      Thanks alot for the insightful blog.Can i use the read text function in Data services to get notes data  in CRM? i need to extract data from notes(comments) in CRM to an external table and to do deltas everyday.Please assist

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      I'm not a DS expert here, so my guess is that you can invoke ABAP commands from within DS. Therefore, when you are retrieving Delta's for your transactions, you would just pass in the text to the FM or process in mass as explained in the post by Thomas Zloch in the original blog post.

       

      Regards,

      Justin

      Author's profile photo Former Member
      Former Member

      Hi,

       

      I used the same code listed here just removed filters since I need the whole content of STXL (so also DB triggers are not in place) but when replication starts, I am getting many dumps for IMPORT command (like CONNE_ILLEGAL_TRANSPORT_HEADER or IMPORT_CONTAINER_MISSING). I can see some texts in HANA but it looks that when SLT reads and tries to convert some inconsistent texts it crashed. Any idea how to solve it?

       

      Thanks in advance.

      Jan

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Jan - don't follow this blog anymore - please use the link provided by Alexey above. This is an SAP provided solution and I have implemented the same a few times successfully. The link takes you to the "how to" documents that should walk you through the solution.

       

      Happy HANA,

      Justin

      Author's profile photo Former Member
      Former Member

      Justin

       

      It is a wonderful blog, but do you have any steps for implementing the same for DMIS SP10 Customers. We need to replicate STXL and STXH tables from ECC to HANA. We need to filter the Field "CLUSTD" which has LAW Data Type ( SAPscript : LONG RAW field for table STXL ). Lot of things changed in newer versions

      Author's profile photo Stéphane MARIE
      Stéphane MARIE

      Hi Justin, and thank you very much for this blog.

       

      We implemented that amost one year ago , following each and every of your instructions step by step...and it has been running smooth till then.

       

      Now I am getting ambitious in replicating each entire text by aggregating lines into one single HANA CLOB or TEXT column in my HANA target system (mainly for Text Analytics purposes).
      Considering it only from STXL replication perspective is tricky for many reasons : keeping consistency on line update, creating the text only on last line insertion...

       

      So I guess STXH has to be the trigger for aggregating data from STXL and post it as one single record in the target table.

       

      I am considering two options :
      - try to do it directly from SLT. I must confess that I have no clue on how to do it at this stage of my thinking on it ...

      - or do it internally in HANA, using a stored procedure called by a trigger on STXH replicated table.

       

      If you, or anyone has already explored this use case, then your/his/her guidance is more than welcome. And if anyone is cosidering the same challenge, then this current post can be considered as a formal offer for my contribution !

       

      Thanks for feedback.

      Stephane

      Author's profile photo Former Member
      Former Member

      Great blog Justin. Is there a way to utilize the text information in a Suite on HANA environment? We don't have SLT available to us but we do want to have access to the textual information.

      Author's profile photo Former Member
      Former Member

      Hello SLT Gurus,

      I am unable to see "Target Table Definition" tab and "Table Structure" tab in the Table Setting in Advanced Replication Settings.

      We are on DMIS 2011 SP11.

      Any help is appreciated. Thank you

      Soumyadeep

      Author's profile photo Alfonzo Vega
      Alfonzo Vega

      As this is already an old blog post, please be aware that sending texts from STXL to HANA can be done with zero code, please see SAP Note 2384490 How to replicate data from an INDX-like table to a target system using SAP Landscape Transformation replication server

       

      I followed the steps in the note attachment and in very little time had the data in a ZSTXL table. Zero line of code.

      MM03 material 378

      Data Preview table ZSTXL in eclipse

       

      Regards,

       

      Alfonzo

      Author's profile photo intellego Bi
      intellego Bi

      We just wanted to say THANK YOU for this great contribution. We had been researching various ways of displaying these LRAW SapScript texts in SAP HANA views and your solution worked like a charm.

      Thanks again!

      Author's profile photo Leo Frederiksen
      Leo Frederiksen

      Although this blog is old, it is still working fine. On SLT DMIS 2018 (and DMIS 2011 on source), the approach and logic here is still valid and a great feature to derive the text values.

      I have made a loop on the internal table to handle multiple lines in the text table, and concat the values into output. Just remember to use an output field with enough length.

      Thanks a lot for this great blog post.