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

To report this post you need to login first.

37 Comments

You must be Logged on to comment or reply to a post.

  1. 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

    (0) 
    1. Justin Molenaur 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

      (0) 
  2. Vijayashree R

    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

    (0) 
  3. 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

    (0) 
    1. Justin Molenaur 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

      (0) 
  4. Saad Mirza

    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. 
    (0) 
    1. Justin Molenaur 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

      (0) 
      1. Saad Mirza

        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 🙂

        (0) 
        1. Justin Molenaur 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.

          (0) 
          1. Justin Molenaur 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

            (0) 
        2. Jody Hesch

          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.

           

          (0) 
  5. Prasad Patil

    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

    (0) 
    1. Justin Molenaur 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

      (0) 
      1. Prasad Patil

        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 stxlclustr,

        clustd TYPE stxlclustd,

        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_rawclustr = <wa_s_stxl>clustr.

            wa_stxl_rawclustd = <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 sysubrc = 0.

              <wa_r_stxl>longtext = wa_tlinetdline.

            ENDIF.

          ENDIF.

        ELSE.

          skip_record.

        ENDIF.

         

         

        DB trigger Code

         

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


        AND :___.TDSPRAS = ‘E’

         

        Regards,

        Prasad

        (0) 
  6. 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

    (0) 
  7. Joel Rotich

    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

    (0) 
    1. Justin Molenaur 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

      (0) 
  8. Jan Tretina

    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

    (0) 
    1. Justin Molenaur 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

      (0) 
  9. Umamaheshwar Muramshetty

    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

    (0) 
  10. Stephane 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

    (0) 
  11. Abhishek Singh

    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.

    (0) 
  12. Soumyadeep Guha

    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

    (0) 

Leave a Reply