SAP HANA: Material Classification Data with LongText into HANA

[former_member364613]

In this Blog, I want to show How to bring Material Classification Data along with LongText into HANA.

As per example below, I want to bring all the Classification Data for this Material along with LongText.

This Below screenshot is from MM03 and you can see how the Classification Data is defines with Longtext.

ECC Version: 618.





 

In order to build a calculation view in HANA, we need below SAP TABLES to bring Classification Data.

• CABN - Characteristic


• CABNT - Characteristic Descriptions


• CAWN - Characteristic values


• STXH -  SAPscript text file header


• STXL -  SAPscript text file lines

As you can see below, to bring the Longtext, we cannot get the readable format text from these Tables STXH & STXL. These tables are in compressed format.



Oneway to bring this longtext in readable format is to create a custom program to convert the Data into readable format and store them into Z Table. That way we can use the Z table in our Calculation view.

Program to read the STXH & STXL tables and store the data in Z table. In this case I have created Table ZMAT_CLASS_TXT and Program ZMAT_CLASSIF_TXT.

Custom Table:



ABAP Code for Program to update the Classification LongText lines into Table:

*&---------------------------------------------------------------------*
*& Report ZMAT_CLASSIF_TXT
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT ZMAT_CLASSIF_TXT.
TABLES: ZMAT_CLASS_TXT, STXH.
TYPES: BEGIN OF TY_STXL,
TDNAME TYPE STXL-TDNAME,
CLUSTR TYPE STXL-CLUSTR,
CLUSTD TYPE STXL-CLUSTD,
END OF TY_STXL.
DATA:  T_STXL TYPE STANDARD TABLE OF TY_STXL.
FIELD-SYMBOLS: <STXL> TYPE TY_STXL.
* compressed text data without text name
TYPES: BEGIN OF TY_STXL_RAW,
CLUSTR TYPE STXL-CLUSTR,
CLUSTD TYPE STXL-CLUSTD,
END OF TY_STXL_RAW.
DATA:  T_STXL_RAW TYPE STANDARD TABLE OF TY_STXL_RAW.
DATA:  W_STXL_RAW TYPE TY_STXL_RAW.
* decompressed text
DATA:  T_TLINE TYPE STANDARD TABLE OF TLINE.
FIELD-SYMBOLS: <TLINE> TYPE TLINE.
DATA: T_STXH TYPE STANDARD TABLE OF STXH,
W_STXH TYPE STXH.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-t01.
SELECT-OPTIONS:
S_OBJECT FOR STXH-TDOBJECT NO INTERVALS OBLIGATORY.
SELECTION-SCREEN END OF BLOCK b1.

SELECT TDNAME TDOBJECT TDID
FROM STXH
INTO CORRESPONDING FIELDS OF TABLE T_STXH
WHERE TDOBJECT = S_OBJECT-LOW AND
TDSPRAS = SY-LANGU.
*AND THEN
* select compressed text lines in blocks of 3000 (adjustable)
SELECT TDNAME CLUSTR CLUSTD
INTO TABLE T_STXL
FROM STXL
PACKAGE SIZE 3000
FOR ALL ENTRIES IN T_STXH "WITH APPLICATION DATA AND TDNAME
WHERE RELID    = 'TX'          "standard text
AND TDOBJECT = T_STXH-TDOBJECT
AND TDID     = T_STXH-TDID
AND TDSPRAS  = SY-LANGU.
LOOP AT T_STXL ASSIGNING <STXL>.
*   decompress text
CLEAR: T_STXL_RAW[], T_TLINE[].

W_STXL_RAW-CLUSTR = <STXL>-CLUSTR.
W_STXL_RAW-CLUSTD = <STXL>-CLUSTD.
APPEND W_STXL_RAW TO T_STXL_RAW.
IMPORT TLINE = T_TLINE FROM INTERNAL TABLE T_STXL_RAW.
*  access text lines for further processing
ZMAT_CLASS_TXT-ZLINES = 0.
LOOP AT T_TLINE ASSIGNING <TLINE>.
ZMAT_CLASS_TXT-ZLINES = ZMAT_CLASS_TXT-ZLINES + 1.
ZMAT_CLASS_TXT-TDNAME = <STXL>-TDNAME.
ZMAT_CLASS_TXT-TEXT = <TLINE>-TDLINE.
MODIFY ZMAT_CLASS_TXT.
ENDLOOP.
ENDLOOP.
FREE T_STXL.
WRITE  'Successfully updated the Table ZMAT_CLASS_TXT'.
ENDSELECT.
I can schedule this Program by giving the TextObject to run Daily to update the Table.



After completion of the program execution in Dialog mode, you can see below screen.



Check the Table if the Longtext is uploaded.



Now, I have created a Calculation View in HANA to bring all the Material Classification Data along with LongText based on these Tables.

• CABN - Characteristic


• CABNT - Characteristic Descriptions


• CAWN - Characteristic values


• ZMAT_CLASS_TXT - LongText

Join_1:



Join_2 :



 

Join_3:



Projection_4: I have concatenated all the 10 Lines text into 1 calculated Column CA_LONG_TEXT with length 1333.

Note: The Max length we can use is 1333.



 

To read all the 10 Lines LongText, I have to create multiple joins with ZMAT_CLASS_TXT table with filters on Lines. For example, to read 1^st line of text, I need to filter on ZLINES = ‘00000001’.



 

Join_6:



 

Similarly, we need to add more joins to bring all the 10 Text lines.

Join_7:



Join_8:



…..

......

Create Joins until you reach Join_15 with all the 10 Text Lines.

Join_15:



Full Hana View DataFlow Diagram:



 

Output of the Hana View: