Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
justin_molenaur2
Contributor
37,226

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

41 Comments
Labels in this area