Sunday, January 13, 2013

De ERP a SAP HANA (Pequeña aplicacion ABAP)


Disclaimer: Este es un proyecto personal. No esta auspiciado ni soportado por SAP de ninguna forma. No intenta ni supone se un reemplazo para ninguna herramienta de migracion de SAP. Solo es algo que hice por diversion y aun esta en fase beta. Utilicenlo bajo su propio riesgo.

En mi trabajo diario, no necesito utilizar ABAP...pero despues de 11 anhos...es dificil no utilizarlo alguna veces.

Como todo es SAP HANA, decidi (hace algun tiempo) construir una pequeña aplicacion ABAP para mover tablas del ERP a SAP HANA. Todavia esta en Beta y por supuesto no es la mejor manera de hacer este tipo de trabajo, puesto que como veran mas adelante, para cada registro genero una clausula INSERT...y el editor de SAP HANA tiene un limite en cantidad de lineas. Porque no genere un archivo CSVy lo cargue con SAP HANA Studio? Porque...este es un proyecto personal...y lo comparto simplemente porque alguien puede encontrarlo util...

Debo agradecer a mi amigo Kumar Mayuresh quien se tomo un tiempo para probar el programa y enviarme todos los errores que encontraban para yo poder correjirlos.

Aqui esta el codigo fuente...

ZERP_TO_HANA 
*&---------------------------------------------------------------------*
*& Report  ZERP_TO_HANA                                                *
*&---------------------------------------------------------------------*
*& Author: Alvaro "Blag" Tejada Galindo.                               *
*& Developer Experience                                                *
*& Company: SAP Labs Montreal.                                         *
*& Date: June 04, 2012.                                                *
*&---------------------------------------------------------------------*
*& This program comes with no warranty. Use it at your own risk.       *
*& This is just a personal project no aimed for productive             *
*& environments and not sponsored or supported by SAP.                 *
*& I'm not responsible for any caused damage.                          *
*&---------------------------------------------------------------------*
*& Reviewed on: December 11, 2012.                                     *
*& Reviewer: Alvaro "Blag" Tejada Galindo.                             *
*& Reason: Definition of the Data download structure.                  *
*&---------------------------------------------------------------------*
*& Reviewed on: January 11, 2013.                                      *
*& Reviewer: Alvaro "Blag" Tejada Galindo.                             *
*& Reason: Definition of the Struct/Data download structure.           *
*&---------------------------------------------------------------------*
*& Reviewed on: January 13, 2013.                                      *
*& Reviewer: Alvaro "Blag" Tejada Galindo.                             *
*& Reason: Check the Outputlen of the Domain.                          *
*&---------------------------------------------------------------------*

REPORT ZERP_TO_HANA.

TYPES: BEGIN OF TY_DD03L,
       FIELDNAME TYPE DD03L-FIELDNAME,
       POSITION TYPE DD03L-POSITION,
       KEYFLAG TYPE DD03L-KEYFLAG,
       ROLLNAME TYPE DD03L-ROLLNAME,
       DATATYPE TYPE DD03L-DATATYPE,
       LENG TYPE DD03L-LENG,
       DECIMALS TYPE DD03L-DECIMALS,
       DOMNAME TYPE DD03L-DOMNAME,
       END OF TY_DD03L.

TYPES: BEGIN OF TY_DD04L,
       ROLLNAME TYPE DD04L-ROLLNAME,
       DOMNAME TYPE DD04L-DOMNAME,
       OUTPUTLEN TYPE DD04L-OUTPUTLEN,
       END OF TY_DD04L.

TYPES: BEGIN OF TY_LINES,
       LINE TYPE STRING,
       END OF TY_LINES.

TYPES: BEGIN OF TY_TYPES,
       ERP TYPE STRING,
       HANA TYPE STRING,
       END OF TY_TYPES.

DATA: T_DD03L TYPE TABLE OF TY_DD03L,
      T_DD04L TYPE TABLE OF TY_DD04L,
      T_LINES TYPE TABLE OF TY_LINES,
      T_TYPES TYPE TABLE OF TY_TYPES.

DATA: V_FILENAME TYPE STRING.

FIELD-SYMBOLS: <FS_DD03L> LIKE LINE OF T_DD03L,
               <FS_DD04L> LIKE LINE OF T_DD04L,
               <FS_LINES> LIKE LINE OF T_LINES,
               <FS_TYPES> LIKE LINE OF T_TYPES.

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-T01.
PARAMETERS:
           P_SCHEMA(12) TYPE C OBLIGATORY,
           P_TABLE TYPE DATABROWSE-TABLENAME OBLIGATORY,
           P_FOLDER TYPE STRING OBLIGATORY,
           P_STRUC RADIOBUTTON GROUP RDN DEFAULT 'X',
           P_DATA RADIOBUTTON GROUP RDN.
SELECTION-SCREEN END OF BLOCK B1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FOLDER.
  CALL METHOD CL_GUI_FRONTEND_SERVICES=>DIRECTORY_BROWSE
    EXPORTING
      WINDOW_TITLE    = 'Select a Folder'
      INITIAL_FOLDER  = 'C:\'
    CHANGING
      SELECTED_FOLDER = P_FOLDER.

START-OF-SELECTION.
  PERFORM GET_TYPES.
  IF P_STRUC EQ 'X'.
    PERFORM GET_STRUCTURE USING P_TABLE.
  ELSE.
    PERFORM GET_DATA USING P_TABLE.
  ENDIF.

*&---------------------------------------------------------------------*
*&      Form  GET_TYPES                                                *
*&---------------------------------------------------------------------*
FORM GET_TYPES.

  SELECT FIELDNAME POSITION KEYFLAG ROLLNAME
         DATATYPE LENG DECIMALS DOMNAME
  INTO TABLE T_DD03L
  FROM DD03L
  WHERE TABNAME EQ P_TABLE.

  SORT T_DD03L BY POSITION ASCENDING.

  SELECT ROLLNAME DOMNAME OUTPUTLEN
  INTO TABLE T_DD04L
  FROM DD04L
  FOR ALL ENTRIES IN T_DD03L
  WHERE ROLLNAME EQ T_DD03L-ROLLNAME
    AND DOMNAME EQ T_DD03L-DOMNAME.

  "NVARCHAR
  APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
  <FS_TYPES>-ERP = 'CLNT'.
  <FS_TYPES>-HANA = 'NVARCHAR'.
  APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
  <FS_TYPES>-ERP = 'CHAR'.
  <FS_TYPES>-HANA = 'NVARCHAR'.
  APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
  <FS_TYPES>-ERP = 'NUMC'.
  <FS_TYPES>-HANA = 'NVARCHAR'.
  APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
  <FS_TYPES>-ERP = 'UNIT'.
  <FS_TYPES>-HANA = 'NVARCHAR'.
  APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
  <FS_TYPES>-ERP = 'CUKY'.
  <FS_TYPES>-HANA = 'NVARCHAR'.
  "INTEGER
  APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
  <FS_TYPES>-ERP = 'INT4'.
  <FS_TYPES>-HANA = 'INTEGER'.
  "DECIMAL
  APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
  <FS_TYPES>-ERP = 'QUAN'.
  <FS_TYPES>-HANA = 'DECIMAL'.
  APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
  <FS_TYPES>-ERP = 'DEC'.
  <FS_TYPES>-HANA = 'DECIMAL'.
  "FLOAT
  APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
  <FS_TYPES>-ERP = 'FLTP'.
  <FS_TYPES>-HANA = 'FLOAT'.
  "TINYINT
  APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
  <FS_TYPES>-ERP = 'INT1'.
  <FS_TYPES>-HANA = 'TINYINT'.

ENDFORM.                    " GET_TYPES

*&---------------------------------------------------------------------*
*&      Form  GET_STRUCTURE                                            *
*&---------------------------------------------------------------------*
FORM GET_STRUCTURE USING P_TABLE.

  DATA: PKEY TYPE STRING,
        L_TYPE TYPE STRING.

  CONCATENATE P_FOLDER '\' P_TABLE '_STRUCT.txt'
  INTO V_FILENAME.

  APPEND INITIAL LINE TO T_LINES ASSIGNING <FS_LINES>.
  CONCATENATE 'CREATE COLUMN TABLE' P_SCHEMA
  INTO <FS_LINES>-LINE SEPARATED BY SPACE.
  CONCATENATE <FS_LINES>-LINE '."' P_TABLE  '" (' INTO
  <FS_LINES>-LINE.
  LOOP AT T_DD03L ASSIGNING <FS_DD03L>.
    FIND REGEX '\.' IN <FS_DD03L>-FIELDNAME.
    IF SY-SUBRC EQ 0.
      CONTINUE.
    ENDIF.
    APPEND INITIAL LINE TO T_LINES ASSIGNING <FS_LINES>.
    READ TABLE T_TYPES ASSIGNING <FS_TYPES>
    WITH KEY ERP = <FS_DD03L>-DATATYPE.
    L_TYPE = <FS_TYPES>-HANA.
    PERFORM DELETE_ZEROS CHANGING <FS_DD03L>-LENG.

    READ TABLE T_DD04L ASSIGNING <FS_DD04L>
    WITH KEY ROLLNAME = <FS_DD03L>-ROLLNAME
             DOMNAME = <FS_DD03L>-DOMNAME.
    IF SY-SUBRC EQ 0 AND NOT <FS_DD04L> IS INITIAL.
      PERFORM DELETE_ZEROS CHANGING <FS_DD04L>-OUTPUTLEN.
      IF <FS_DD04L>-OUTPUTLEN GT <FS_DD03L>-LENG.
        <FS_DD03L>-LENG = <FS_DD04L>-OUTPUTLEN.
      ENDIF.
    ENDIF.

    PERFORM DELETE_ZEROS CHANGING <FS_DD03L>-DECIMALS.
    CASE L_TYPE.
      WHEN 'NVARCHAR' OR 'FLOAT' OR 'TINYINT'.
        CONCATENATE L_TYPE '(' <FS_DD03L>-LENG ')'
        INTO L_TYPE.
      WHEN 'DECIMAL'.
        CONCATENATE L_TYPE '(' <FS_DD03L>-LENG ',' <FS_DD03L>-DECIMALS ')'
        INTO L_TYPE.
    ENDCASE.
    FIND REGEX '\/' IN <FS_DD03L>-FIELDNAME.
    IF SY-SUBRC EQ 0.
      CONCATENATE '"' <FS_DD03L>-FIELDNAME '"'
      INTO <FS_DD03L>-FIELDNAME.
    ENDIF.
    CONCATENATE <FS_DD03L>-FIELDNAME L_TYPE
    INTO <FS_LINES>-LINE SEPARATED BY SPACE.
    CONCATENATE <FS_LINES>-LINE ',' INTO <FS_LINES>-LINE.
    IF <FS_DD03L>-KEYFLAG EQ 'X'.
      CONCATENATE PKEY '"' <FS_DD03L>-FIELDNAME '",'
      INTO PKEY.
    ENDIF.
  ENDLOOP.
  REPLACE REGEX ',\Z' IN PKEY WITH SPACE.

  APPEND INITIAL LINE TO T_LINES ASSIGNING <FS_LINES>.
  CONCATENATE 'PRIMARY KEY (' PKEY '));'
  INTO <FS_LINES>-LINE.

  PERFORM DOWNLOAD_FILE USING V_FILENAME
                              T_LINES.

ENDFORM.                    " GET_STRUCTURE

*&---------------------------------------------------------------------*
*&      Form  GET_DATA                                                 *
*&---------------------------------------------------------------------*
FORM GET_DATA USING P_TABLE.

  DATA: L_TABLE TYPE REF TO DATA,
        L_LINE TYPE STRING,
        L_LINEAUX TYPE STRING,
        L_WHERE TYPE STRING,
        L_TYPE TYPE STRING.

  FIELD-SYMBOLS: <FS_TABLE> TYPE ANY TABLE,
                 <FS_TABLE_HEADER> TYPE ANY,
                 <FS_LINE>.

  CONCATENATE P_FOLDER '\' P_TABLE '_DATA.txt'
  INTO V_FILENAME.

  CREATE DATA L_TABLE TYPE TABLE OF (P_TABLE).
  ASSIGN L_TABLE->* TO <FS_TABLE>.

  read table t_dd03l ASSIGNING <fs_dd03l>
  with key domname = 'SPRAS'.
  IF SY-SUBrC EQ 0.
    CONCATENATE <fs_dd03l>-FIELDNAME 'EQ ''E''' INTO L_WHERE
    SEPARATED BY SPACE.
    SELECT *
    FROM (P_TABLE)
    INTO TABLE <FS_TABLE>
    WHERE (L_WHERE).
  ELSE.
    SELECT *
    FROM (P_TABLE)
    INTO TABLE <FS_TABLE>.
  ENDIF.

  LOOP AT <FS_TABLE> ASSIGNING <FS_TABLE_HEADER>.
    APPEND INITIAL LINE TO T_LINES ASSIGNING <FS_LINES>.
    CONCATENATE 'insert into "' P_SCHEMA '"."' P_TABLE '" values(' into <FS_LINES>-LINE.
    LOOP AT T_DD03L ASSIGNING <FS_DD03L>.
      FIND REGEX '\.' IN <FS_DD03L>-FIELDNAME.
      IF SY-SUBRC EQ 0.
        CONTINUE.
        DELETE T_LINES FROM <FS_LINES>.
      ENDIF.
      CONCATENATE '<FS_TABLE_HEADER>-' <FS_DD03L>-FIELDNAME
      INTO L_LINE.
      ASSIGN (L_LINE) TO <FS_LINE>.
      MOVE <FS_LINE> TO L_LINEAUX.
      CONDENSE L_LINEAUX NO-GAPS.
      READ TABLE T_TYPES ASSIGNING <FS_TYPES>
      WITH KEY ERP = <FS_DD03L>-DATATYPE.
      L_TYPE = <FS_TYPES>-HANA.
      CASE L_TYPE.
        WHEN 'NVARCHAR'.
          CONCATENATE <FS_LINES>-LINE '''' L_LINEAUX ''',' into <FS_LINES>-LINE.
        WHEN 'DECIMAL' OR 'INTEGER' OR 'TINYINT' OR 'FLOAT'.
          CONDENSE L_LINEAUX NO-GAPS.
          CONCATENATE <FS_LINES>-LINE L_LINEAUX ',' into <FS_LINES>-LINE.
      ENDCASE.
    ENDLOOP.
    REPLACE REGEX ',\Z' IN <FS_LINES>-LINE WITH ');'.
  ENDLOOP.

  PERFORM DOWNLOAD_FILE USING V_FILENAME
                              T_LINES.

ENDFORM.                    " GET_DATA

*&---------------------------------------------------------------------*
*&      Form  download_file                                            *
*&---------------------------------------------------------------------*
FORM DOWNLOAD_FILE USING P_FILENAME
                         P_TABLE.

  DATA: SIZE TYPE I.

  CALL METHOD CL_GUI_FRONTEND_SERVICES=>GUI_DOWNLOAD
    EXPORTING
      BIN_FILESIZE = SIZE
      FILENAME     = P_FILENAME
      FILETYPE     = 'ASC'
    CHANGING
      DATA_TAB     = P_TABLE.

ENDFORM.                    "download_file

*&---------------------------------------------------------------------*
*&      Form  DELETE_ZEROS                                             *
*&---------------------------------------------------------------------*
FORM DELETE_ZEROS CHANGING P_VALUE.

  CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
    EXPORTING
      INPUT  = P_VALUE
    IMPORTING
      OUTPUT = P_VALUE.

ENDFORM.                    "DELETE_ZEROS

El uso es bastante simple...lo ejecutamos y debemos pasar el Schema, Tabla y la carpeta donde vamos a guardar los archivos. Podemos guardar la Estructura o la Data.




Con los dos archivos listos, simplemente copiamos y pegamos en un editor SQL de SAP HANA y lo ejecutamos.


Por supuesto...la parte negativa es que debemos copiar los registros en modo batch...lo cual significa...que debemos copiar 100 lineas o un poco mas...ejecutarlas...y luego continuar con las otras 100 o mas...


Como pueden ver...esto es solo por diversion y para pequeños tests...y quizas no trabaje con todas las tablas...espero que les guste -:)

Saludos,

Blag.
Posted by at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)