martes, 29 de enero de 2013

CodeJam - SAP HANA


I have successfully delivered two SAP Code Jams...one in Montreal and the other in Lima. For that, I created a Workbook so everyone could gain experience and follow up on the Hands-On section of the event.

After those two events...I decided that it's time for this document to get retired...what does that means? I will not use it again...so I will share it with community.

It took me a lot of time and effort and mostly passion to write it...so I hope everybody find it useful.


Greetings,

Blag.

lunes, 21 de enero de 2013

PHP rocks on SAP HANA too!


A couple of days ago, a friend told me that some people were trying to make PHP and SAP HANA work without having any success...of course...I took that as my new goal...I haven't done any PHP is a very long time...but that never stops me -;)

First thing...was to create an ODBC connection and try to make it work...it didn't...but only because I create it using a "User DSN" instead of a "System DSN"...when do we need to use one or the other...I have no clue...but while it works...I don't care...

Now that it was working...it was time to make a simple query...this failed too...with a nasty and weird message...



Scrollable Result is not yet implemented...what's that suppose to mean? At first...I thought that maybe NVARCHAR is not supported, so I created a temp table using only two VARCHAR fields...same result...so it was something else...in the end...after looking in Google...I found out that some Databases allows cursors to go back and forth...and somehow...with SAP HANA it was giving me an error...easiest solution was to specify the cursor

$conn = odbc_connect("HANA_KT_SYS","SYSTEM","manager", SQL_CUR_USE_ODBC);

The use of SQL_CUR_USE_ODBC was all I needed to keep going...

For this example, I decided to create an Attribute View joining the tables SPFLI and SCARR. For the first screen I will show all the available CARRIDs and on the second screen, I will show a table with some of the fields on a HTML table. Something simple and direct.


Now, let's take a look at the PHP code...

PHP_SAPHANA.php
<?php
$conn = odbc_connect("HANA_KT_SYS","SYSTEM","manager", SQL_CUR_USE_ODBC);
if (!($conn)) { 
 echo "<p>Connection to DB via ODBC failed: ";
 echo odbc_errormsg ($conn );
 echo "</p>\n";
}
else{
 if(isset($_POST["CARRID"]) == false)
 {
  $sql = "SELECT CARRID, CARRNAME FROM SFLIGHT.SCARR 
                        WHERE MANDT = 300";
  $rs = odbc_exec($conn,$sql);
  print("<DIV ALIGN='CENTER'>");
  print("<H1>SAP HANA from PHP</H1>");
  print("<FORM NAME='Get_Data' 
                       ACTION='$_SERVER[PHP_SELF]' METHOD='POST'>");
  print("<SELECT NAME='CARRID'>");
  while($row = odbc_fetch_array($rs)){
      $carrid = $row["CARRID"];
   $carrname = $row["CARRNAME"];
   print("<OPTION VALUE='$carrid'>$carrname");
  }
     print("</SELECT>");
      print("<INPUT TYPE='SUBMIT' VALUE='Get Data'>");
      print("</FORM>");
      print("</DIV>");
 }
 else{
  $carrid_param = $_POST["CARRID"];
  $sql = "SELECT * FROM \"_SYS_BIC\".\"blag/AV_FLIGHTS\" 
          WHERE CARRID = '$carrid_param'";
  $rs = odbc_exec($conn,$sql);
  print("<DIV ALIGN='CENTER'><TABLE BORDER=1>"); 
          print("<TR><TH>MANDT</TH>
                       <TH>CARRID</TH>
                       <TH>CONNID</TH>
                       <TH>COUNTRYFR</TH><TH
                       >CITYFROM</TH>                        
                       <TH>AIRPFROM</TH>
                       <TH>COUNTRYTO</TH>
                       <TH>CARRNAME</TH>
                       <TH>DISTANCE</TH></TR>");
  while($row = odbc_fetch_array($rs)){
   $mandt = $row["MANDT"];
   $carrid = $row["CARRID"];
   $connid = $row["CONNID"];
   $countryfr = $row["COUNTRYFR"];
   $cityfrom = $row["CITYFROM"];
   $airpfrom = $row["AIRPFROM"];
   $countryto = $row["COUNTRYTO"];
   $carrname = $row["CARRNAME"];
   $distance = $row["DISTANCE"];
   print("<TR><TD>$mandt</TD><TD>$carrid</TD>
           <TD>$connid</TD><TD>$countryfr</TD>
        <TD>$cityfrom</TD><TD>$airpfrom</TD>
        <TD>$countryto</TD><TD>$carrname</TD>
        <TD>$distance</TD></TR>");
  }
  print("</TABLE>");
  print("<A HREF='PHP_SAPHANA.php'>Go Back</A></DIV>");
 }
}
?>

Now, we can call it from any Web Browser...




As you can see...there's no limitation to what you can do with SAP HANA...sometimes...it's just take a little bit more of research -;)

Greetings,

Blag.

SAP CodeJam Lima - The Experience


This Saturday 19, 2013 we held at the Aula Magna of the Universidad Peruana de Ciencias Aplicadas the first SAP CodeJam Lima.

(I had some problems with my camera but Lennon Shimokawa and Raúl Vides Mosquera Pumaricra came to the rescue...so...thanks guys!)

We have around 20 people and I talked about SAP HANA.


As I'm from Peru...and haven't come back in 2 years and 8 months...I decided to start the day with a little session called "From Lima to Montreal...from Consultant to Development Expert" to tell people about my experiences, motivations, problems and successes.


Sadly...we had some unexpected WIFI connection problems, so we couldn't connect to out TK ucloud SAP HANA Servers...but that didn't stop us -;) As I provided them an 86 pages Workbook and I use CloudShare.com as backup....we could continue learning and having fun -:)



This SAP CodeJam was a replicate of the one I did in Montreal as I simply translate the whole Workbook into Spanish...we talk about Tables, Views, Attribute, Analytical and Calculation Views, connection with Microsoft Excel and SQLScript.


As always, I tried to show people how versatile and easy to use SAP HANA is with some examples and real life applications.



Of course, people ask me about SAP HANA implementations and how ERP fits into SAP HANA...



The whole day lasted from 9:00am to 2:00pm...time when the Pizza and Soda arrived...there are no pictures because everybody was really busy eating and talking...

In a nutshell, it was a fun and nice experience and everybody is looking forward for the next event -:)


Greetings,

Blag.

domingo, 13 de enero de 2013

From ERP to SAP HANA (Small ABAP App)


Disclaimer: This is a personal project. It's not endorsed or supported by SAP in any means. It's not aimed or supposed to replace any SAP migration tool. It's just something I did for fun and it's still on beta phase. Use it at your own risk.

In my daytime job, I don't need to use ABAP...but after 11 years...it's hard not to use it sometimes

As everything is SAP HANA, I decided (some time ago) to build a small ABAP program to move tables from the ERP to SAP HANA. It's still on Beta and of course it's not the best way of doing this kind of job, because as you will see later, for each record I generate an INSERT clause...and the SAP HANA editor has a limit of lines. Why didn't I generate an CSV file and upload with SAP HANA Studio? Because...this is a personal project...and I'm sharing it only because someone might find it useful...

I need to thank my friend Kumar Mayuresh who took some quality time to beta test and send me all the error he found so I could fix them.

Here's the source code...

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

The usage is very simple...we execute it and we need to provide the Schema, Table and the Folder were we're going to store the files. We can download the Structure or the Data.




With the two files ready, we simply copy and paste in an SQL Editor of SAP HANA and let it run.


Of course...the downside is that we need to copy the records in batch mode...meaning...100 lines or something like that...run them...and then continue with the other 100 or so...


As you can see...this is only for fun and for small testing...and might not work with all the tables...hope you like it anyway -:)

Greetings,

Blag.