More blog posts in SAP HANA Developer CenterWhere is place located?
    1 2 Previous Next

    SAP HANA Developer Center

    28 Posts authored by: Alvaro Tejada Galindo

    Some time ago I post a blog called Getting flexible with SAP HANA that used SAP HANA, R and Twitter to demonstrate the Schema Flexibility capabilities...

     

    I came to realize that even when that example is really cool...it's not really aimed for beginners, because you need a lot of R and Regular Expressions experience to fully understand what's going on...so...I decided to write a more simple blog...using only SAP HANA to show this awesome option...

     

    So...what "Schema Flexibility" means? Well...it means that you can define a table with some columns and then dynamically add more columns at run time without the need of redefine the table structure...

     

    First...let's create a table using plain SQL...

     

    Create table

    CREATE COLUMN TABLE Products(

    PRODUCT_CODE VARCHAR(3),

    PRODUCT_NAME NVARCHAR(20),

    PRICE DECIMAL(5,2)

    ) WITH SCHEMA FLEXIBILITY;

     

    As you can see...it's just a table...but we're adding the WITH SCHEMA FLEXIBILITY option...

     

    Now...we can simply insert one product...

     

    Insert Product
    INSERT INTO Products values ('001','Blag Stuff', 100.99);

     

    Let's say that we need to add a new product...that comes in different colors...but our table doesn't have a COLOR column defined...but it doesn't matter...our table is flexible enough to hold it up...

     

    Insert new product
    INSERT INTO Products (PRODUCT_CODE,PRODUCT_NAME,PRICE,COLOR) values ('002','More Blag Stuff',100.99,'Black');

     

    Notice that we're defining all the columns and adding a new one called "COLOR"...and simply pass the new value...when we select all the records from the table, we will have this...

     

    Flexible_Schema_001.jpg

     

    As you can see...for the second record, we have the new column "COLOR" along with it's value...for the first record, we simple have an "?" because the "COLOR
    column didn't exist at the time of it's creation...

     

    Now...let's say we need to add another new product...that doesn't come in colors...

     

    Insert last new product
    INSERT INTO Products (PRODUCT_CODE, PRODUCT_NAME, PRICE) values ('003','Even More Blag Stuff',101.99);

     

    Notice that we need to specify the "regular" column names, but we don't need to care about the dynamic column...

     

    We will have this when getting all records...

     

    Flexible_Schema_002.jpg

    As the column "COLOR" already exist at the time of the creation of the last product...we will see a "?"  value again...

     

    I hope that with this small blog...this gets more clear Even where there are not so many use cases for this...I expect many people to get creative and use this cool feature...

     

    Small update

     

    You might have realized that when you create a new column..it's going to be generated as NVARCHAR(5000)...so that's not very helpful right?

     

    Sadly...we cannot change this because the feature is not "yet" supported...and that's because the field length can't be shortened...

     

    However, if we are passing a numeric value...then we're allow to change it...consider the following example...

     

    Altering COLOR column

    INSERT INTO Products values ('001','Blag Stuff', 100.99);

     

    INSERT INTO Products (PRODUCT_CODE,PRODUCT_NAME,PRICE,QUANTITY) values ('002','More Blag Stuff',100.99,10);

     

    ALTER TABLE Products ALTER (QUANTITY INT);

     

    Here...we're adding a new column called "QUANTITY" with a value of 10...at first is going to be created a NVARCHAR(5000) but with a simple ALTER TABLE we can change it to INT...

     

    Now, let's say we need to update the first field to include a quantity as well...using a simple UPDATE will do the trick...

     

    Updating a field

    UPDATE Products

    SET QUANTITY = 20

    WHERE PRODUCT_CODE = '001';

     

    Flexible_Schema_003.jpg

    Yesterday, I realized that it's been a while since a post a blog about SAP HANA...so I decided to think about something cool to write about...checking my feeds, I came to notice AngularJS The Super Heroic JavaScript MVW Framework...

     

    So...what is good about AngularJS? Well...according to them...

     

    Other frameworks deal with HTML’s shortcomings by either abstracting away HTML, CSS, and/or JavaScript or by providing an imperative way for manipulating the DOM. Neither of these address the root problem that HTML was not designed for dynamic views.

     

    So...in this blog, we're going to use PHP to get information from SAP HANA, return them as a JSON object and be presented by AngularJS.

     

    Let's get our hands dirty

     

    First...I create a System DSN for my SAP HANA connection...so I could call it from PHP...

     

     

    menu.php

    <?php

    $conn = odbc_connect("HANA_SYS","SYSTEM","********", SQL_CUR_USE_ODBC);

    $query = "SELECT table_name from SYS.CS_TABLES_ where schema_name = 'SFLIGHT'";

    $rs = odbc_exec($conn,$query);

    $result = array();

    while($row = odbc_fetch_array($rs)){

              $menu["table_name"] = $row["TABLE_NAME"];

              array_push($result,$menu);

    }

    echo json_encode($result);

    ?>

     

     

    This code will allow us to get all the tables names included in the SFLIGHT schema...so we can present a dropdown list to choose from...

     

     

    tables.php

    <?php

    $data = file_get_contents("php://input");

    $objData = json_decode($data);

    $data = $objData->data;

    $conn = odbc_connect("HANA_SYS","SYSTEM","*********", SQL_CUR_USE_ODBC);

    $query = "select column_name from SYS.CS_COLUMNS_ A inner join SYS.CS_TABLES_ B";

    $query .= " on A.table_oid = B.table_oid where schema_name = 'SFLIGHT'";

    $query .= " and table_name = '$data' and internal_column_id > 200 order by internal_column_id";

    $rs = odbc_exec($conn,$query);

    $result = array();

    $fields = array();

    $fields_array = array();

    while($row = odbc_fetch_array($rs)){

              array_push($result,$row["COLUMN_NAME"]);

              $fields["FIELDS"] = $row["COLUMN_NAME"];

              array_push($fields_array,$fields);

    }

    sort($fields_array);

     

     

    $content = array();

    $table = array();

    $query = "select * from SFLIGHT.$data";

    $rs = odbc_exec($conn,$query);

    array_push($content,$fields_array);

    while($row = odbc_fetch_array($rs)){

              for($i=0;$i<count($result);$i++){

                        $table["$result[$i]"] = $row["$result[$i]"];

              }

              array_push($content,$table);

    }

    echo json_encode($content);

    ?>

     

     

    This code will get a table name as parameter and will get the Fields name and the contents of the table...both PHP scripts will return a JSON object...

     

     

    get_menu.js

    function MenuCtrl($scope, $http) {

        $scope.url = 'menu.php';

            

            $http.post($scope.url).

            success(function(data, status) {

                $scope.status = status;

                $scope.data = data;

                $scope.tables = data;

            })

            .

            error(function(data, status) {

                $scope.data = data || "Request failed";

                $scope.status = status;        

            });

     

                $scope.gettable = function() {

                          $scope.url = 'tables.php';

            $http.post($scope.url, { "data" : $scope.table}).

            success(function(data, status) {

                $scope.status = status;

                $scope.data = data;

                                  $scope.contents = data;

            })

            .

            error(function(data, status) {

                $scope.data = data || "Request failed";

                $scope.status = status;        

            });

        };

    }

     

    This code will simply read back the JSON objects generated by the PHP scripts...the first part for dropdown list and the second for the tables fields and contents...

     

    Finally...we have our HTML which call the AngularJS script to perform the magic...

     

     

    main.html

    <!DOCTYPE html>

    <html ng-app>

    <head>

    <title>Angular.JS, PHP and SAP HANA</title>

        <link rel="stylesheet" href="css/bootstrap.min.css" type="text/css" />

        <script src="http://code.angularjs.org/angular-1.0.0.min.js"></script>

        <script src="get_menu.js"></script>

    </head>

    <body ng-controller='MenuCtrl'>

    <div align="center">

              <H1>AngularJS, PHP and SAP HANA</H1>

              <form>

                        <label>Choose table:</label>

                        <select ng-model="table">

                                  <option ng-repeat="table in tables" value='{{table.table_name}}'>{{table.table_name}}

                        </select>

                        <button type="submit" class="btn" ng-click="gettable()">Get Table</button>

              </form>

              <br/>

              <table border="1" ng-show="contents.length">

                        <tr ng-repeat="(key, value) in contents" ng-show="$first">

                                  <th ng-repeat="values in value")>{{values.FIELDS}}</th>

                        </tr>

                        <tr ng-repeat="(key, value) in contents" ng-show="!$first">

                                  <td ng-repeat="values in value")>{{values}}</td>

                        </tr>

              </table>

    </div>

    </body>

    </html>

     

    This code is simple...it will grab the JSON objects and simply use repeaters to read the information and place it on both the menu and the table.

     

    Here's are some screenshots of the application running...

     

    AngularJS_001.jpg

    AngularJS_002.jpg

    AngularJS_003.jpg

     

    Now...you might notice that the fields are actually sorted! And not in the defined order...well...this is a JavaScript problem more than an AngularJS problem...at least that's what I found out...but...who cares in the end...the data is presented and that's all that matters

     

    One thing to notice as well...is that AngularJS is not particularly fast...for some big tables...it will throw up a time limit exception...even when SAP HANA will send back the information really fast...and for sure PHP is going to create the JSON objects as fast as well...I guess the root problem is that AngularJS will sort the columns, read each line and then construct the table...or...maybe it's just because I'm an AngularJS newbie

     

    Either way...this my was my first experience ever with AngularJS...and I gotta say...I had a lot of fun! The learning curve is really fast and AngularJS provide many cool features that makes it and really good alternative for web development

     

    See you in my next blog

     


    If you have visited the SAP Developer Center today, you might have noticed a small change...

     

    CodeEx_000.jpgWe're introducing SAP Code Exchange 2.0...A new and better way to share your code. We have change our landing page and include some new rules that will please most of the developers our there...

     

    CodeEx_001.jpg

     

    If you read carefully, you might notice that if you're using any of our Developer Center provided tools (meaning that you have a valid license for it), you can actually share your code wherever you want...all we ask from you is the name of your project, the link and a small description. With that, we will able to feature your project on the landing page.

     

    CodeEx_002.jpg

    For ABAP based code, you will still need to use Code Exchange 1.0...but of course...we're working really hard to make things easier for everyone!

     

    Come on! Share your code! Now it's easier and better

    Most of you might not be aware of a feature introduced on SAP HANA SPS5. This new feature is called "Flexible Tables", which means that you can define a table that will grow depending on your needs. Let's see an example...

     

     

    You define a table with ID, NAME and LAST_NAME. The table works fine, but you realize that you need to also add the PhoneNumber and Address...in a normal situation, you will need to open the definition and add those fields...but using Flexibles Tables, you will need to only add those fields as part of the INSERT query and let SAP HANA do it's magic...

     

    Of course, that scenario is very unlikely to happen, because for a couple of fields, it doesn't make sense...so...where do we use this Flexible Tables? Enterprise Search, were we need to have all products on one table, and this table can have a really big amount of columns...as different products will have different characteristics...

     

    So...for this blog...I really break my head trying to find a simple scenario that could cover Flexible Tables...what I came up with? SAP HANA, Flexible Tables, R and Twitter...

     

    The code is very complex, so I'm not going to explain line by line how it works...but of course, I'm going to give a nice overview...

     

    The Twitter API (I'm using version 1 even when it's deprecated, simply because version 1.1 deals with Authentication and didn't want to spend too much time on that...) allows us to fetch information from Twitter...so in this case I was interested in the Hashtags...the ones that starts with an "#" and are used to identify and organize certain tweets related to an event, technology or famous person. (I'm using only Tweets, not taking Retweets into account)...

     

    Diagram_Flexible_HANA_01.png

     

    Using R, I read the User Timeline to get the most recent 200 tweets from a particular account. This information will be send back to SAP HANA to be stored.

     

    Diagram_Flexible_HANA_02.pngWith the 200 tweets, I extract all the Hashtags, summarize them and then save the information both in the final table and in a intermediate table (to be used for the next user). In this intermediate table, I will store a long string with all the Hashtags separated by a comma.

     

    When the next user arrives, all 200 tweets are read, Hashtags extracted, combine with the one saved in the intermediate table, summarized (This is very important because we want to keep track of the previous Hashtags, both the ones that are common to both users and the ones that only exist on the first or second user) and the information will be saved in the final table and the information from the next user will be save (replacing the previous one) as a long string in the intermediate table.

     

    Why I need to this? Simple...let's say that the first user has 3 Hashtags...#SAPHANA, #R and #Python with values 3, 2 and 1. The next user will have 5 Hashtags...#SAPHANA, #SAP, #Ruby, #IPhone and #Android with the values 2, 5, 1, 4 and 3.

     

    When we store the first user we will have:

     

    UserName SAPHANA RPython
    First_User321

     

    When we store the second user, we will have...

     

    UserNameSAPHANARPythonSAPRubyIPhoneAndroid
    First_User321????
    Next_User2005143

     

    Now...you may wonder...why R has "0" for the next user and SAP has "?" for the first user? Easy...as you can see...as we added more fields (at runtime) the table grow...the R field was already there for the first user so it got a "0" for the next user, however SAP wasn't there before, so we don't actually know what should the value for the first user...so a "?" will be in place...

     

    I'm sure you will have a better picture when you see the images of the table after I show the source code...

     

    First, we need to create a table called "TWITTER_USERS", that will hold the users that we want to work with...

     

    Flexible_HANA_001.pngThen, we need another table were we are going to store the Hashtags and its values as a long string. This table will be called "FIRST_HASH".

     

    Flexible_HANA_003.png

     

    Now, things get interesting, as we're going to create our Flexible Table using a very simple command...this table will be called "TWITTER_HASHTAGS".

     

    Twitter_Hashtags.sql

    CREATE COLUMN TABLE TWITTER_HASHTAGS(

    USERNAME NVARCHAR(10)

    ) WITH SCHEMA FLEXIBILITY;

     

    This table will look pretty regular when watching its definition...but it's a Flexible Table...as you can see...we only defined one field...so this table can grow and grow and grow

     

    Flexible_HANA_002.pngNext, we need to create a couple of type tables to allow to interact between SAP HANA and R...

     

    Table_Types.sql

     

    CREATE TYPE T_COL_NAMES AS TABLE(

    COL_NAMES NVARCHAR(1000)

    );

     

    CREATE TYPE T_COL_VALUES AS TABLE(

    COL_VALUES NVARCHAR(1000)

    );

     

    And now...we're ready to start with the code...one R procedure and two SQLScript procedures...

     

    GET_HASHTAGS.sql

    CREATE PROCEDURE GET_HASHTAGS(IN twittername TWITTER_USERS,IN first_hash FIRST_HASH,

                                  OUT out_col_names T_COL_NAMES, OUT out_col_values T_COL_VALUES)

    LANGUAGE RLANG AS

    BEGIN

    UserName = twittername$USERNAME

    hashline = first_hash$HASH_LINE

    hashvalues = first_hash$HASH_VALUES

     

    Get_Twitter<-function(p_source,p_pattern){

      datalines = grep(p_pattern,web_page,value=TRUE)

      getexpr = function(s,g)substring(s,g,g+attr(g,'match.length')-1)

      g_list = gregexpr(p_pattern,datalines)

      matches = mapply(getexpr,datalines,g_list)

      result = gsub(p_pattern,'\\1',matches)

      names(result) = NULL

      return(result)

    }

     

    Get_Hashtags<-function(p_source){

      check<-!length(grep('\\"([^,\\"]+)\\"', as.character(p_source)))

      if(!check){

        mypattern = '\\"([^,\\"]+)\\"'

        datalines = grep(mypattern,p_source,value=TRUE)

        getexpr = function(s,g)substring(s,g,g+attr(g,'match.length')-1)

        g_list = gregexpr(mypattern,datalines)

        matches = mapply(getexpr,datalines,g_list)

        result = gsub(mypattern,'\\1',matches)

        names(result) = NULL

        return(result)

      }else{

        result<-p_source

        return(result)

      }

    }

     

    url<-paste("http://api.twitter.com/1/statuses/user_timeline.xml?count=200&screen_name=",UserName,sep="")

    mypattern = '<text>([^<]*)</text>'

    web_page<-readLines(url)

    tweets<-Get_Twitter(web_page,mypattern)

    mypattern = '[^\\&]#(\\.?\\w+)'

    hash_list<-Get_Twitter(tweets,mypattern)

    hashtags<-sapply(hash_list,Get_Hashtags)

    hashtags<-as.vector(unlist(hashtags))

    hashtags<-toupper(hashtags)

     

    dt.hashtags<-data.frame(UserName,hashtags)

    tab.hashtags<-table(dt.hashtags)

    dt.hashtags<-as.data.frame.matrix(tab.hashtags)

    hashtags_names<-names(dt.hashtags)

    hashtags_names<-gsub("^\\.",'',hashtags_names)

     

    if(length(hashline>=1)){

              hash_line<-gsub("^(\\w)+\\,",'',hashline)

              hash_line<-unlist(strsplit(hash_line, split=","))

              hash_values<-gsub("^(\\'+\\w+\\')+\\,",'',hashvalues)

              hash_values<-as.numeric(unlist(strsplit(hash_values, split=",")))

              hash_frame<-data.frame(names=hash_line,values=hash_values)

              hash_frame["values"]<-0

     

              Col_Names<-""

              Col_Values<-""

     

              for(i in 1:length(hashtags_names)){

                          Col_Names<-paste(Col_Names,hashtags_names[i],sep=",")

                          Col_Values<-paste(Col_Values,dt.hashtags[,i],sep=",")

              }

     

               Col_Names<-gsub("^\\,|\\.",'',Col_Names)

              Col_Values<-gsub("^\\,|\\.",'',Col_Values)

              Col_Names<-unlist(strsplit(Col_Names, split=","))

              Col_Values<-as.numeric(unlist(strsplit(Col_Values, split=",")))

              new_hash_frame<-data.frame(names=Col_Names,values=Col_Values)

              new_hash_frame<-rbind(hash_frame,new_hash_frame)

              new_hash_frame<-aggregate(values ~ names, FUN = "sum", data = new_hash_frame)

              new_hash_names<-new_hash_frame$names

              new_hash_values<-new_hash_frame$values

     

              Col_Names<-"USERNAME"

              Col_Values<-paste("'",UserName,"'",sep="")

     

               for(i in 1:length(new_hash_names)){

                          Col_Names<-paste(Col_Names,new_hash_names[i],sep=",")

                          Col_Values<-paste(Col_Values,new_hash_values[i],sep=",")

              }

    }else{

              Col_Names<-"USERNAME"

              Col_Values<-paste("'",UserName,"'",sep="")

     

              for(i in 1:length(hashtags_names)){

                          Col_Names<-paste(Col_Names,hashtags_names[i],sep=",")

                          Col_Values<-paste(Col_Values,dt.hashtags[,i],sep=",")

              }

    }

     

    col_names<-gsub("^\\,\\.?",'',Col_Names)

    col_values<-gsub("^\\,",'',Col_Values)

     

    out_col_names<-data.frame(COL_NAMES=col_names)

    out_col_values<-data.frame(COL_VALUES=col_values)

    END;

     

    SAVE_HASHTAGS.sql

    CREATE PROCEDURE SAVE_HASHTAGS(IN in_col_names T_COL_NAMES, IN in_col_values T_COL_VALUES)

    LANGUAGE SQLSCRIPT AS

    v_select VARCHAR(2000);

    v_col_names_char NVARCHAR(1000);

    v_col_values_char NVARCHAR(1000);

    CURSOR c_cursor1 FOR

    SELECT COL_NAMES FROM :in_col_names;

    CURSOR c_cursor2 FOR

    SELECT COL_VALUES FROM :in_col_values;

    BEGIN

                        OPEN c_cursor1;

                        FETCH c_cursor1 into v_col_names_char;

                        CLOSE c_cursor1;

                        OPEN c_cursor2;

                        FETCH c_cursor2 into v_col_values_char;

                        CLOSE c_cursor2;

                        DELETE FROM FIRST_HASH;

                        INSERT INTO FIRST_HASH VALUES(:v_col_names_char,:v_col_values_char);

                        v_select := 'INSERT INTO TWITTER_HASHTAGS (' || v_col_names_char || ') VALUES (' || v_col_values_char || ')';

                        EXEC v_select;

    END;

     

    GET_TWITTER_USERS.sql

    CREATE PROCEDURE GET_TWITTER_USERS(UserName NVARCHAR(10))

    LANGUAGE SQLSCRIPT AS

    BEGIN

              Twitter_Users = SELECT USERNAME FROM TWITTER_USERS WHERE USERNAME = :UserName;

              First_Hash = SELECT HASH_LINE, HASH_VALUES FROM FIRST_HASH;

              CALL GET_HASHTAGS(:Twitter_Users,:First_Hash,T_COL_NAMES,T_COL_VALUES);

              CALL SAVE_HASHTAGS(:T_COL_NAMES,:T_COL_VALUES);

    END;

     

    In order for this to work, we need to insert some values on our "TWITTER_USERS" table...

     

    Twitter_Users.png

     

    And then, simply call the "GET_TWITTER_USERS" procedure...

     

    CALL_GET_TWITTER_USERS.sql

    CALL GET_TWITTER_USERS('Blag');

    CALL GET_TWITTER_USERS('Schmerdy');

    CALL GET_TWITTER_USERS('ggread');

     

    When we execute the first call...that's it with user @Blag we will have the following on the "FIRST_HASH" table...

     

    Flexible_HANA_004.png

     

    And this on our "TWITTER_HASHTAGS" Flexible Table...

     

    Flexible_HANA_005.png

     

    As you can see...our table started only with USERNAME...but as we pass in the Hashtags and its values...the table grew to able to hold them...

     

    When we call the next user...that's @Schmerdy we will have this on the Flexible Table...

     

    Flexible_HANA_006.png

    As you can see...in all the Hashtags that belong to @Blag but doesn't belong to @Schmerdy we have a "0" value...so what will happen to the ones that belong to @Schmerdy but not to @Blag?

     

    Flexible_HANA_007.png

     

    Those field will have a "?" value, as they didn't exits before we add them...and again...the table grew to hold all the new fields....

     

    Now...something interesting is that @Schmerdy had more Hashtags than @Blag...so what will happen when we call the last user which is @ggread that by the way...has less Hashtags than @Schmerdy and @Blag...

     

    Flexible_HANA_008.png

     

    @ggread will have a value "0" in all the Hashtags that doesn't belong to the user...but will have a value in the one that are similar to @Schmerdy...

     

    Flexible_HANA_009.png

     

    So...what will happen with the Hashtags that belongs to @ggread but doesn't exist on @Schmerdy or @Blag? Easy...they will be added and some "?" value will be placed where those Hashtags didn't exist before...

     

    Flexible_HANA_010.png

    I wish I could put the whole table...but it contains more than 50 columns...so better...I can export them to a .CSV file...and do some analysis using Visual Intelligence...

     

    Flexible_HANA_011.png

     

    Here, we can see how often these three user have used the Hashtags #SAPHANA, #SAP and #SAPTECHED in their last 200 tweets...

     

    So...that's it...a nice and simple way to demonstrate how the Flexible Tables work in SAP HANA by using my always beloved R

    The other day I was thinking about writing a blog using PowerBuilder, but couldn't decide which one other technology I should integrate it...of course...R came to my mind...

     

    My journey started around 4 days ago...when I start looking for ways to call R from an external language...last time I used Rook and Heroku to call R from SAP Mobile Platform as explained in my blog Consuming R from SAP Mobile Platform, but this time I knew that I needed to do something different.

     

    My first thought was to use Rserve which is an R Server used by the SAP HANA Studio to connect to R as explained in my blog When SAP HANA met R - First kiss, so I downloaded the REngine files which are two .jar Java files.

     

    In order to connect to Rserve we need to set it up...so basically with the Rserve package installed on my RStudio I simply need to create a small file...

     

    Call_Rserve.R

    library("Rserve")

    Rserve()

     

    When you run this...the R Server will start as a process that can be seen in the Task Manager.

     

    With the files ready, I went to PowerBuilder and simply add them to the Java classpath like I did for the SAP HANA jdbc connector as explained in my blog PowerBuilder - The new kid on Developer Center's block, it didn't work...the files we in the classpath but I couldn't call any method...so I decided to keep looking for alternatives and then I find Rcaller, a single .jar Java file that instead of using Rserve, call the R executable directly...it didn't work either...thing is...in PowerBuilder (with the exception of the SAP HANA jdbc connector) you need to use either an EAServer or EJB capable server like JBoss...I haven't use any of them before...and also you need to build the .jar Java file yourself as you need some internal information.

     

    I had the impression that I was going to a dead end...but then I think..."Hey...I'm using PowerBuilder.NET!" which means obviously that it relies on the .NET Framework...meaning that I could use .NET dll files...but I only had .jar Java files...

     

    Doing some more research I came to find IKVM which an application that allows you to convert any .jar Java file into a nice .NET dll file...the usage is very simple...

     

    Using IKVM

    Open CMD

    C:\>ikmvc -out:Rcaller.dll -target:module Rcaller.jar

     

    This will produce a .dll file called Rcaller.dll...I thought I was right on track so I include this file in my project references...it didn't work as sadly, IKVM is still on development and not everything from Java has been translated to .NET, like for example the Java.IO interface...I then convert both REngine .jar Java files into an .dll but had the same luck...some conversions were missing...

     

    Back to Google and keep looking...this time...straight for .NET implementations and I found R.NET which also uses the R executable to make the integration...it look very promising...however, R.NET is still in development and for some reason...I couldn't make it work as somehow the .dll file couldn't be found by the .NET engine...I thought...Ok...maybe it's PowerBuilder's fault...let's try straight on a real .NET environment...so I installed Visual Studio Express 2012 for Desktop...same luck...the .dll couldn't be found...

     

    I was tired and angry...nothing seem to work...but then I get back to the Rserve page and realized that it was a .NET project called RserveCLI that used Rserve to do the integration with R....when downloading the project...there's no available .dll but that wasn't a problem...as I simply compile it on VS Express and got a shiny .dll waiting to be tested...so I create a new C# Console project and test it...it worked perfectly...

     

    When I tried to use it on PowerBuilder.NET for the first time...it didn't work...so...as you may imagine...I was even more tired and even more angry...and for some unexplainable reason...I end deleting a single tiny file from the .NET framework that simply screw everything...I couldn't run PowerBuilder or VS Express any more...thought luck...another journey to fix my mess...I uninstalled all the .NET framework references...VC++ compilers...Runtimes...etc...it took me almost a full day to finally have everything back in place...however...up to this day...VS Express died completely...I can't even install it any more...as the installer show me the VS splash screen as then disappears with no visible error message...

     

    Anyway...at least PowerBuilder was working again...and I finally overcome my issues with the RserveCLI dll...

     

    The program that I'm going to show you, is for sure very simple...as due to the nature of the integration...and the fact that RserveCLI is still on development...there's really not much that we can do...but still...I believe it will help to illustrate two interesting points...

     

    • PowerBuilder.NET can interact with .NET dll files
    • R can be used by a long range of programming languages

     

    PB_R_References.png

     

    This program will ask you to fill two arrays, Array A and Array B...each with four elements (somehow...and for some reason that I'm still trying to understand...when calling R from PowerBuilder, you need to pass even bi-dimensional arrays...being the smallest of [2,2]...)

     

    Let's take a look at the layout...

     

    PB_R_00.png

     

    So...enough talk...let's go to the Source Code...

     

    First, we're going to define a Global Variable as we will use it in a lot of places...

     

    Global Variables
    RserveCli.RConnection conn

     

    Then, we going to call the main windows (w_window) open event.

     

    w_window.Open

    #if DEFINED PBDOTNET then

    @System.Net.IPAddress ipadd

    byte ip[4] = {127,0,0,1}

    ipadd = create @System.Net.IPAddress(ip)

    @System.Int32 port

    port = create System.Int32

    port = 6311

    conn = create RserveCli.RConnection(ipadd,port,"","")

    #end if

     

    Here, we are saying that we're going to use .NET styled code, so it should be ignored by the PowerBuilder compiler...as we're using Rserve on our local machine, we use the localhost IP address and assign the Rserve default port which is 6311. We define a connection to the server using the RConnection method. If you wonder why I'm using the @ it's because we want to make sure that we're calling the System from .NET and from PowerBuilder...I know that I said the code should ignored by the PowerBuilder compiler...but better safe than sorry....

     

    We have a button called cb_load which is going to load the arrays defined on the screen.

     

    cb_load.Clicked

    double varA[2,2], varB[2,2]

    varA[1,1] = double(Array_A_1.Text)

    varA[1,2] = double(Array_A_3.Text)

    varA[2,1] = double(Array_A_2.Text)

    varA[2,2] = double(Array_A_4.Text)

    varB[1,1] = double(Array_B_1.Text)

    varB[1,2] = double(Array_B_3.Text)

    varB[2,1] = double(Array_B_2.Text)

    varB[2,2] = double(Array_B_4.Text)

     

    #if DEFINED PBDOTNET then

    ::conn["A"] = RserveCli.Sexp.Make(varA)

    ::conn["B"] = RserveCli.Sexp.Make(varB)

    #end if

     

    Here, we simply create two arrays and fill them. We call ::conn with the extra "::" to let the compiler know that it's a global variable. When we use the Sexp.Make we're telling that we want to create an R vector using the provided array. So one vector called A and other called B.

     

    Now that we have the arrays loaded in memory, we can proceed with the other buttons.

     

    cb_sum.Clicked

    #if DEFINED PBDOTNET then

    txtResult.Text = ::conn["A+B"].ToString()

    #end if

     

    This is very simple, we're just saying...send this "A+B" command to R and give me the result as a String. In R, A+B will produce a factor sum...meaning that instead of creating a new factor containing the values of A and B, it's going to take the first element from A and sum it to the first element of B, it will do the same with the rest of the elements. You will see this more clear later.

     

    cb_multiply.Clicked

    #if DEFINED PBDOTNET then

    txtResult.Text = ::conn["A*B"].ToString()

    #end if

     

    This is basically the same, with the exception that it will multiply instead of sum.

     

    cb_min.Clicked

    #if DEFINED PBDOTNET then

    RserveCli.Sexp varC = ::conn["min(c(A,B))"]

    txtResult.Text = varC.ToString()

    #end if

     

    This is a little bit more interesting...we're going to create an Sexp variable and then perform a small operation in R...by doing "c(A,B)" we going to take all the element from B and them to A, so A it's going to contain both values...using "min()" we're to get the smallest value.

     

    cb_max.Clicked

    #if DEFINED PBDOTNET then

    RserveCli.Sexp varC = ::conn["max(c(A,B))"]

    txtResult.Text = varC.ToString()

    #end if

     

    Here it's the same story but we're going to get the biggest value instead of the smallest.

     

    cb_mean.Clicked

    #if DEFINED PBDOTNET then

    RserveCli.Sexp varC = ::conn["mean(c(A,B))"]

    txtResult.Text = varC.ToString()

    #end if

     

    Same story again...R is really easy to work with...we calculate the mean which will be simply the sum of all elements divided by the number of elements.

     

    cb_summary.Clicked

    #if DEFINED PBDOTNET then

    RserveCli.Sexp varC = ::conn["summary(c(A,B))"]

    string varS = "Min: " + varC[0].ToString() + " / 1st Qu: " + varC[2].ToString() + " / Median: " &

                                    + varC[2].ToString() + " / Mean: " + varC[3].ToString() + " / 3rd Qu: " &

                                    + varC[4].ToString() + " / Max: " + varC[5].ToString()

    txtResult.Text = varS

    #end if

     

    Here, we're going to obtain the summary of mixing A and B. The summary is a complex variable that will return us the Min and Max values, the 1st and 3rd Quadrants, the Median and the Mean values. By simply calling it as an array, we can extract all the values...

     

    Finally...and to allow our program to run...we need to call the last piece of code...

     

    wpfapp.Open
    open(w_window)

     

    wpfapp is the name of our application (by PowerBuilder default). Here we say, open our main windows called w_window (another PowerBuilder default)...

     

    Let's run the application and see how it works...

     

    PB_R_01.png

     

    We are simply filling the text boxes with some dummy data...Array A will contain 1,2,3,4 and Array B will contain 5,6,7,8.

     

    PB_R_02.png

     

    When we press the Sum button...1 and 5 will be summed, 2 and 6 will be summed, 3 and 7 will be summed and finally, 4 and 8 will be summed.

     

    PB_R_03.png

     

    When we press the Multiply button, the same thing will happen, but the numbers will be multiplied instead of being summed.

     

    PB_R_04.png

     

    You already know but will happen with Minimum, Maximum and Mean...so we move ahead straight to Summary. It will return the Min and Max, the 1st and 3rd Quadrants and the Median and Mean values.

     

    As you can see...this is a very simple example...but believe me...after full 4 days of work...I'm glad that I could finally make it work...and you thought that was job was easy? Think again 4 stressful days and nights...

    Since I wrote my blog When SAP HANA met R - First kiss I had received a lot of nice feedback...and one those feedbacks was..."What's new?"...

     

    Well...as you might now SAP HANA works with R by using Rserve, a package that allows communication to an R Server, so really...there can't be too many new features...but...the cool thing is that SAP HANA has been tested with R 2.15 and Rserve 0.6-8 so any new features added on R and Rserve and instantly available on SAP HANA

     

    But hey! I wouldn't write a blog is there wasn't at least one new cool feature, right? You can read more about it here SAP HANA R Integration Guide.

     

    Of course...for this you need SAP HANA rev. 48 (SPS5)

     

    So what's the cool feature? Well...you can store a train model like lm() or ksvm() directly on a table for later use. This is really cool, because if you have a big calculation to be made, you only need to store the model and use it later without having to reprocess everything again.

     

    Let's make an example...and hope all the R fans doesn't kill me for this...because when it comes to statistics...I'm really lost in the woods

     

    Let's say we have two tables from the SFLIGHT package...SPFLI and STICKET, so we want to predict how many times a customer is going to flight to different destinations (CITYFROM-CITYTO) depending on how many times all the customers has flights to those very same locations.

     

    We're going to create one SQLScript file to get the information, transform it, create the model and store it in the database...

     

    Build_Flight_Model.sql

    --Create a TYPE T_FLIGHTS to grab the information from the SPFLI and STICKET tables.

     

    DROP TYPE T_FLIGHTS;

    CREATE TYPE T_FLIGHTS AS TABLE (

    CARRID NVARCHAR(3),

    CUSTOMID NVARCHAR(8),

    CITYFROM NVARCHAR(20),

    CITYTO NVARCHAR(20)

    );

     

    --Create a TYPE FLIGHT_MODEL_T and a table FLIGHT_MODEL to get and store the model in the database.

     

    DROP TYPE FLIGHT_MODEL_T;

    CREATE TYPE FLIGHT_MODEL_T AS TABLE (

    ID INTEGER,

    DESCRIPTION VARCHAR(255),

    MODEL BLOB

    );

     

    DROP TABLE FLIGHT_MODEL;

    CREATE COLUMN TABLE FLIGHT_MODEL (

    ID INTEGER,

    DESCRIPTION VARCHAR(255),

    MODEL BLOB

    );

     

    --This R procedure will receive the T_FLIGHTS information, create table containing a field call FLIGHT_NAME that will contain the concatenation

    --of the CARRID, CITYFROM and CITYTO. ie: AA-NEW YORK-SAN FRANCISCO.

    --We're going to convert the table into a data.frame, so all the similar values in FLIGHT_NAME are going to be summarized.

    --Using the subset() function, we're going to get rid of all the FLIGHT_NAME's that has a frequency lower or equal than 0.

    --We're going to use the nrow() function to count all the FLIGHT_NAME occurrences and multiply that by 10 (Stored in f_rows)

    --We're going to use the sum() function to sum all the frequencies and the divide it by f_rows (Stored in f_sum)

    --We're going to use the mapply() function to divide each of the frequencies by f_sum

    --We're going to use the order() function to sort by FLIGHT_NAME

    --We're going to use the colnames() function to assign names to our data.frame

    --We're going to use the lm() function to generate a Linear Regression based on the FLIGHT_NAME and it's frequency

    --Finally, we're going to use the generateRobjColumn() custom created function to store the result of the model in the buffer.


    DROP PROCEDURE FLIGHT_TRAIN_PROC;

    CREATE PROCEDURE FLIGHT_TRAIN_PROC (IN traininput "T_FLIGHTS", OUT modelresult FLIGHT_MODEL_T)

    LANGUAGE RLANG AS

    BEGIN

    generateRobjColumn <- function(...){

              result <- as.data.frame(cbind(

                        lapply(

                                  list(...),

                                  function(x) if (is.null(x)) NULL else serialize(x, NULL)

                        )

              ))

              names(result) <- NULL

              names(result[[1]]) <- NULL

              result

    }

    tab<-table(FLIGHT_NAME=paste(traininput$CARRID,traininput$CITYFROM,traininput$CITYTO,sep="-"))

    df<-data.frame(tab)

    ss<-subset(df,(df$Freq>0))

    freq<-ss$Freq

    f_rows<-(nrow(ss)) * 10

    fsum<-sum(freq) / f_rows

    ss$Freq<-mapply("/",ss$Freq, fsum)

    flights<-ss[order(ss$FLIGHT_NAME),]

    colnames(flights)<-c("FLIGHT_NAME","FREQUENCY")

    lmModel<-lm(FREQUENCY ~ FLIGHT_NAME,data=flights)

    modelresult<-data.frame(

    ID=c(1),

    DESCRIPTION=c("Flight Model"),

    MODEL=generateRobjColumn(lmModel)

    )

    END;

     

    --This SQLSCRIPT procedure will grab all the needed information from the tables SPFLI and STICKET and will assign it to flights

    --We're going to call the R procedure FLIGHT_TRAIN_PROC

    --We're going to do an INSERT to finally store the model from the buffer into the database

     

    DROP PROCEDURE POPULATE_FLIGHTS;

    CREATE PROCEDURE POPULATE_FLIGHTS ()

    LANGUAGE SQLSCRIPT AS

    BEGIN

    flights = SELECT SPFLI.CARRID, CUSTOMID, CITYFROM, CITYTO

                 FROM SFLIGHT.SPFLI INNER JOIN SFLIGHT.STICKET

                 ON SPFLI.CARRID = STICKET.CARRID

                 AND SPFLI.CONNID = STICKET.CONNID;

    CALL FLIGHT_TRAIN_PROC(:flights, FLIGHT_MODEL_T);

    INSERT INTO "FLIGHT_MODEL" SELECT * FROM :FLIGHT_MODEL_T;

    END;

     

    CALL POPULATE_FLIGHTS();

     

    When we call POPULATE_FLIGHTS(), our FLIGHT_MODEL table should look like this...

     

    FLIGHT_MODEL.png

    If you are wondering why we have an "X"...it's because the content is serialized and stored in a BLOB field...if you inspect the content, you will receive a bunch of weird hexadecimal numbers...

     

    Anyway...it took 6.165 seconds to SAP HANA to process 1,842,160 records.

     

    Now the we have our model safely stored in the database, we can move to our next SQLScript file...

     

    GET_AND_USE_FLIGHT_MODEL.sql

    --We're going to create a TYPE T_PREDICTED_FLIGHTS and a table PREDICTED_FLIGHTS to store the information of the current number of flights and

    --the estimated (according to our prediction) number of flights

     

    DROP TYPE T_PREDICTED_FLIGHTS;

    CREATE TYPE T_PREDICTED_FLIGHTS AS TABLE (

    CUSTOMID NVARCHAR(8),

    FLIGHT_NAME NVARCHAR(60),

    FREQUENCY INTEGER,

    PREDICTED INTEGER

    );

     

    DROP TABLE PREDICTED_FLIGHTS;

    CREATE TABLE PREDICTED_FLIGHTS (

    CUSTOMID NVARCHAR(8),

    FLIGHT_NAME NVARCHAR(60),

    FREQUENCY INTEGER,

    PREDICTED INTEGER

    );

     

    --In this R procedure, we're going to receive the flight for a given customer, the model stored in the database and we're going to return the result so it can be

    --stored in our PREDICTED_FLIGHTS table.

    --We're going to use the unserialize() function to extract the model.

    --We're going to create a table containing a field call FLIGHT_NAME that will contain the concatenation of the CARRID, CITYFROM and CITYTO.

    --ie: AA-NEW YORK-SAN FRANCISCO. and also the CUSTOMID

    --We're going to convert the table into a data.frame, so all the similar values in FLIGHT_NAME are going to be summarized.

    --We're going to use the colnames() function to assign names to our data.frame

    --We're going to use the nrow() function to get the number of records in the data.frame (Stored in dfrows)

    --We're going to use the rep() function to repeat the CUSTOMID value of the first record dfrows times

    --We're going to use the predict() function to predict the amount of flights based on our model (retrieved from the database) and the new data that we recovered

    --Finally, we're going to create a data.frame containing all the information that should be stored in our table PREDICTED_FLIGHTS


    DROP PROCEDURE USE_FLIGHT;

    CREATE PROCEDURE USE_FLIGHT(IN flights T_FLIGHTS, IN modeltbl FLIGHT_MODEL_T, OUT out_flights T_PREDICTED_FLIGHTS)

    LANGUAGE RLANG AS

    BEGIN

    lmModel<-unserialize(modeltbl$MODEL[[1]])

    tab<-table(FLIGHT_NAME=paste(flights$CARRID,flights$CITYFROM,flights$CITYTO,sep="-"),CUSTOMID=flights$CUSTOMID)

    df<-data.frame(tab)

    colnames(df)<-c("FLIGHT_NAME","CUSTOMID","FREQUENCY")

    dfrows<-nrow(df)

    customid<-rep(df$CUSTOMID[1],dfrows)

    prediction=predict(lmModel,df,interval="none")

    out_flights<-data.frame(CUSTOMID=customid,FLIGHT_NAME=df$FLIGHT_NAME,FREQUENCY=df$FREQUENCY,PREDICTED=prediction)

    END;

     

    --This SQLSCRIPT procedure will select the information from the FLIGHT_MODEL table and store in the flight_model variable

    --We're going to select all the needed information from the table SPFLI and STICKET based on the customer ID number

    --We're going to call the R procedure USE_FLIGHT and it will return us the PREDICTED_FLIGHTS that we're going to store in the database

     

    DROP PROCEDURE GET_FLIGHTS;

    CREATE PROCEDURE GET_FLIGHTS(IN customId NVARCHAR(8))

    LANGUAGE SQLSCRIPT AS

    BEGIN

    flight_model = SELECT * FROM FLIGHT_MODEL;

    out_flights = SELECT SPFLI.CARRID, CUSTOMID, CITYFROM, CITYTO FROM SFLIGHT.SPFLI INNER JOIN SFLIGHT.STICKET

                       ON SPFLI.CARRID = STICKET.CARRID AND SPFLI.CONNID = STICKET.CONNID

                       WHERE CUSTOMID = :customId;

    CALL USE_FLIGHT(:out_flights, :flight_model, PREDICTED_FLIGHTS);

    INSERT INTO "PREDICTED_FLIGHTS" SELECT * FROM :PREDICTED_FLIGHTS;

    END;

     

    Now that we have all our Stored Procedures ready...we can create the last SQLScript file to actually fill our PREDICTED_FLIGHTS with some data...

     

    PREDICT_FLIGHTS_FOR_CUSTOMERS.sql

    CALL GET_FLIGHTS('00000156');

    CALL GET_FLIGHTS('00002078');

    CALL GET_FLIGHTS('00002463');

     

    As you can see...we only need to call the GET_FLIGHTS procedure, passing the Customer ID's...

     

    This process took only 970ms and generate 122 records for the 3 customers...

     

    Now I'm sure you realize how cool is this...if we haven't stored our model in the database...then we would have to calculate the model for each customer...and it would have took us around 7 seconds for each (get the lm(), plus the predict)...that would have been around 21 seconds for the 3 customers...while we can say that the whole process took us only 7 seconds...if you needed to calculate the prediction for all the more than 1 million customers...you will be in sort of trouble

     

    Let's see the content of our PREDICTED_FLIGHTS table...of course, I'm going to only show a part of it...

     

    PREDICTED_FLIGHTS.png

     

    We can dump this information to a CSV file from SAP HANA Studio and let's say...use it on SAP Visual Intelligence to generate a nice graphic...

     

    VI_PREDICTED.png

     

    Hope you like it

    Some time ago, I was contacted by a Harvard student called Hung Tran. Hung was working on a project involving SAP HANA and R, and he needed some guidance and help with some procedures. Of course, I helped him without actually knowing how big the project was.

     

    Time passed and Hung sent me a first draft so I could test it live...I was totally blown away...I could have never expected something like that...

     

    Before we continue...allow me to introduce "TEAM4Solutions", composed by students working on a really nice project.

     

    TEAM4Solutions.jpgFor left to right: Greg Zheng, Hung Tran, Julio Silveira, Michael Chepkwony and Ryan Talabis.

     

    So...what makes this project so interesting? The technologies they use to bring this project to life...

     

    1. SAP HANA Appliance (in-memory database and analytics engine)

    2. SAP HANA Studio (data modeling and management tool)

    3. R analytics engine and text mining packages

    4. Java-based middleware

    5. Flex based user interface (browser and mobile support)

    6. Crystal Reports Reporting

    7. Active Directory Integration

     

    Pretty impressive...so...in the means of provide a better understanding...I asked the team a couple of questions...

     

    Blag: Can you tell us why your team build this project?

     

    Team: Our team built this project as part of an academic requirement for Harvard Extension's Information Management Systems Capstone class. This project was meant to showcase the groups capability to produce a well-designed system which takes into consideration functional, technical and operational requirements in an enterprise environment.

     

    Blag: Can you tell us shortly about the application?

     

    Team: The application the group has created is an analytics application that consolidates and analyse various structured and unstructured product data. The goal of the project is to provide a platform that would provide users with real time access, correlation and analysis of product issue data. Specifically, the application focuses on the following use cases:

    • Providing on-site technicians with real-time access to issue and solution information to facilitate faster resolution of product issues.
    • Providing proactive analysis of user complaints in terms of product risk probabilities to reduce product recall instances and frequency.
    • Providing a means to analyze unstructured data from business units such as complaints resolution, maintenance reports, knowledge bases, product documentation and quality assurance reports.
    • Providing help desk with an easily searchable central repository of correlated information to assist with product and issue inquiries.
    • Providing visibility to various business units regarding customer complaints and issues to facilitate better product development and enhancements.

     

    Blag: Why did you choose SAP HANA?

     

    Team: SAP HANA was primarily due to:

    • Leverage the performance benefits of using an in-memory database. SAP HANA was obviously, one of the leading technologies in this area.
    • Leverage the built-in integration of R. As the project is primarily an analytics project, the flexibility and the wide variety of data mining packages of R was essential for the project.
    • The ability to embed R scripts in SQL. The group felt that the ease and flexibility of this approach will help make development more efficient and adding new analytics functionalities easier.
    • The column store structure provided by SAP HANA is conducive to the unstructured data that will be used in the project

     

    Blag: Which cloud provider did you used?

     

    Team: Amazon Web Service was used for the project.

     

    Blag: Can you tell us about your experience using the integrated R face of SAP HANA?

     

    Team: The built-in integration of R is one of the strengths of SAP HANA that was essential to the project. Since the project is essentially an analytics and data mining system, having R scripts integrate with SQL scripts made extracting and analyzing datasets very efficient. The combination of R and it is wealth of data mining packages, data sets that are processed in memory and a database environment tailored for unstructured text makes R + SAP HANA an ideal environment for the project. For this project, our team used one of the natural language processor libraries with in R to extract the common phrases from the unstructured data column. It was simply implemented with a few lines.

     

    Blag: How did you implemented the Fuzzy search on SAP HANA?

     

    Team: The fuzzy search was implemented using the text engine that is embedded into SAP Hana, which enables the ability to use it inside a SQL query.  The fuzzy search can be simply included in a “SELECT” statement using the “CONTAINS()” function with the “FUZZY()” option  in the “WHERE” clause.  For the purpose of this project, only the minimal options were used in the fuzzy search.

     

    Blag: Can you tell us about your whole experience using SAP HANA, R, Flex and Java?

     

    Team: As mentioned above, SAP HANA + R was essential for the project. The use of Flex is more in terms of providing not only a clean sleek look but also increase the usability of the application with the addition of drag/drop functionalities in both computer and mobile use. Java was primarily used as a middle ware to allow for expandability of the solution. One of our team members had experience with Flex + Java and connectivity to SAP Hana was easily implemented using JDBC. The only issue we came across was embedding R in Hana, but we found a tutorial that Alvaro Tejado Galindo wrote on how to embed R in Hana, which was very informative.

     

    Blag: Would you recommend SAP HANA?

     

    Team: Yes, we would definitely recommend SAP HANA particularly for analytics applications. The integrated R framework plus the speed of an in-memory environment provides tremendous possibilities particularly in real time data analysis not seen before in traditional implementations.

     

    Enough said...their project was graded the best of the semester and also raised the bar for future semesters.

     

    So after all this talking, I'm sure you want to see the application in action...gladly, the team provided a video demonstration, so please...enjoy...

     

     

     

    Before I finish...I would like to thank Greg, Hung, Julio, Michael and Ryan for allowing me to share their story...a story where SAP HANA is a big player.

    As you may already know...I love R...a fancy, open source statistics programming language. So today, I decided to learn something new using R.

     

    There aren't much Web Servers for R, but there's one that I really like called Rook, that I covered on my blog RSAP, Rook and ERP.

     

    Today, I tried a new one that's is making a lot of noise in the R community, called Shiny. I gotta say...I felt instantly in love with it...

     

    So you may ask...so what's so cool about Shiny? Well...besides the fact that it allows you to create web applications using R...it's completely dynamic...meaning that once you change a parameter, the graphic and not the whole web page is reloaded automatically...awesome, huh?

     

    So...when I wrote about Ruby and SAP in my blog Ruby joins the SAP HANA party, I tried to emulate an SE16 to browse tables from the SFLIGHT package...this time...I will do the same but with a little twist...the application will allow you to choose a table, but also to choose how many records you want to display...

     

    So, what we need? Simply...if you haven't already...install this two packages..."shiny" and "RODBC"...

     

    Shiny_SAPHANA_00.png

     

    After this, we need to create a folder called Shiny and after this create a new one called SAP_HANA_R (This is just to be organized).

     

    We now need to create two files, called ui.R and server.R

     

    ui.R

    library("shiny")

    library("RODBC")

     

    ch<-odbcConnect("HANA_TK",uid="SYSTEM",pwd="manager")

    odbcQuery(ch,"SELECT table_name from SYS.CS_TABLES_ where schema_name = 'SFLIGHT'")

    tables<-sqlGetResults(ch)

    odbcClose(ch)

     

    shinyUI(pageWithSidebar(

     

      headerPanel("SAP HANA and R using Shiny"),

     

      sidebarPanel(

        selectInput("Table", "Choose a table:",

                    choices = tables$TABLE_NAME),

        numericInput("Records", "Number of Records to view:", 10)

      ),

     

      mainPanel(

        tableOutput("view")

      )

    ))

     

    server.R

    library("shiny")

    library("RODBC")

     

    shinyServer(function(input, output) {

     

      output$view <- reactiveTable(function() {

        ch<-odbcConnect("HANA_TK",uid="SYSTEM",pwd="manager")

        schema_table<-paste("SFLIGHT.",input$Table,sep="")

        query<-paste("SELECT TOP",input$Records,"* FROM",schema_table)

        odbcQuery(ch,query)

        result<-sqlGetResults(ch)

        odbcClose(ch)

     

        head(result, n = input$Records)

      })

    })

     

    When we have finished with the two files...we can create a new one just to call our application.

     

    Shiny_HANA.R

    library(shiny)

    setwd("C:/Blag/R_Scripts")

    runApp("Shiny/SAP_HANA_R")

     

    Keep in mind that the setwd("C:/Blag/R_Scripts") is my main R Script folder, as setwd stands for "Set Working Directory"...

     

    When we run Shiny_HANA.R, the browser will popup showing the parameters and the table by default.

     

    Shiny_SAPHANA_01.png

     

    As you can see, we can choose a new table to display.

     

    Shiny_SAPHANA_02.png

     

    We can also choose how many lines or records we want to display...

     

    Shiny_SAPHANA_03.png

     

    Shiny_SAPHANA_04.pngI hope you like it...

    If you know me...you know I'm not a Linux boy...however...I still have my laptop LG T1 Express Dual that I bought on 2007...for my first SAP TechEd ever...as you may assume...running Windows and installing more software on this laptop was a nice way to slowly kill it...so the laptop stayed on my closed for a long time...until I decided to install Ubuntu on it and bring to live once again

     

    What they say it's true...even the crappiest and oldest laptop will behave great on Linux...and BTW, I'm writing this blog on my Linux box

     

    A couple of weeks ago I wrote a blog called PHP rocks on SAP HANA too! as I realized that I haven't blog about PHP and SAP HANA and also because I knew that a lot of people were facing issues trying to make it work together...but...I wrote for Windows...and Linux users are still struggling to get this working...so...time for a new blog...

     

    I gotta say...without this awesome blog HANA with odbc on Ubuntu 12.04 written by Ethan Zhang I will be probably still struggling with the connection details...but anyway...not being a Linux boy...I thought it would be a good idea to detail all the steps and problems that I overcome to finally make it work...

     

    Of course...I didn't have PHP installed on my Linux box...so the first step was install it...however, I didn't want to spend so much time installing everything separately, so I decided to use LAMP.

     

    LAMP Installation

    $ sudo apt-get install tasksel

    $ sudo tasksel install lamp-server

     

    After this two simple lines...PHP was up and running...so next step was install the SAP HANA Client. 32bits Linux Version.

     

    Now, I needed something to connect to my SAP HANA Server...so unixODBC was the best choice.

     

    unixODBC Installation

    $ sudo apt-get install unixODBC unixODBC-dev

     

    With this, it was time to configure the ODBC connection...

     

    odbc.ini configuration

    $ sudo vi odbc.ini

     

    [HDB]

    driver = /usr/sap/hdbclient32/libodbcHDB32.so

    ServerNode = hana_server:30115

     

    It was time to the first test...so I did the following...

     

    ODBC Testing
    $ isql -v HDB SYSTEM manager

     

    After this...I got an error saying that libodbcHDB32.so couldn't be found because the file or directory didn't exist...it was weird...I decided to take a look at the dependencies...

     

    Checking dependecies
    $ ldd /usr/sap/hdbclient32/libodbcHDB32.so

     

    This point me out to the fact that libaio.so wasn't found on my system...so using the Ubuntu Software System...I just installed it...

     

    libaio.png

     

    My next test of isql was successful, so...being kind of lazy...I just copied the code from my other blog and run it

     

    PHP_HANA.php

    <?php

    $conn = odbc_connect("HDB","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_HANA.php'>Go Back</A></DIV>");

              }

    }

    ?>

     

    PHP_HANA_Linux_001.png

    PHP_HANA_Linux_002.png

     

    As you can see...not too hard...even for a Windows boy

    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.

     

     

    CodeJam_SAP_HANA

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

    PHP_SAPHANA_001.pngScrollable 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.

     

    PHP_SAPHANA_002.png

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

     

    PHP_SAPHANA_003.png

     

    PHP_SAPHANA_004.png

     

    PHP_SAPHANA_005.png

     

    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

    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.

     

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

     

    ERP_TO_HANA_001.png

    ERP_TO_HANA_002.png

    ERP_TO_HANA_003.png

     

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

     

    ERP_TO_HANA_004.png

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

     

    ERP_TO_HANA_005.png

     

    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

    Three days ago, my friends at Packt Publishing send me a free ebook called PhoneGap Beginner's Guide. And while I knew about PhoneGap I had never really use it...so of course, my first thought was...How can I make this work with SAP HANA?

     

    Two days ago, I started to read the book and make the PhoneGap installation...which was a total pain and didn't even work...so I simply put it aside for next year...

     

    Yesterday...my developer spirit could more than me...and since 9 am to 11:30 pm I embarked myself on a crusade to have PhoneGap up and running and of course...to make it work with SAP HANA...here's my story...so you don't have to break your head

     

     

    With all that...I was almost ready to go...as I was missing the Cordova-2.2.0.jar file...for that...I did the following...

     

    • Download and copy into the Cordova\libs folder the commons-codec-1.7.jar.
    • I went to Cordova\android\framework\scr\org\apache\cordova and modified the file CordovaWebView.java by commenting out this two lines...
      • if(android.os.Build.VERSION.SDK_INT < android.os.Build.VERSION_CODES.HONEYCOMB)

                   settings.setNavDump(true);

    • Using CMD, I went to the Cordova\android\framework directory and execute ==> ant jar.

     

    Now...I was really ready to go...so I create a folder called Projects inside Corbova...and again on CMD I did the following...

     

    • Inside Corbova\Projects ==> create C:\Cordova\Projects com.BlagTest BlagTest

     

    That created a Blag_Test folder with all the related files from the Cordova project. Then I simply upload it to Eclipse, and made a couple of test to see if everything was working as expected...it did...so the new odyssey for SAP HANA was going to start...

     

    At first...my initial thought was...this should be easy...I have already do it on PowerBuilder, so basically I need to import the ngdbc.jar into my Eclipse Project and that's it...wrong! Didn't work...and after several tries and fails...I finally see the light...I delete the project from Eclipse...copy the ngdbc.jar inside my libs folder of BlagTest...re-imported on Eclipse...and magically...I got a connection to SAP HANA...

     

    Now...make that connection work was another nightmare...for this blog I needed to undust my almost forgotten knowledge of Java and JavaScript...and also...learn new things like PhoneGap and JQueryMobile...

     

    But...I'm going to try to keep the long story short, so you don't get bored...

     

    • I create a new class called MyClass.java (I was tired...so forget about the silly name)

     

    MyClass.java

    package com.BlagTest;

     

    import java.sql.*;

    import java.util.ArrayList;

     

    public class MyClass {

              public ArrayList<String> getData(String p_carrid){

                        Connection connection = null;

                  ArrayList<String> carrid = new ArrayList<String>();

                  String data = "";

                        try{

                                  Class.forName("com.sap.db.jdbc.Driver");

                                  try{

                                            connection = DriverManager.getConnection("jdbc:sap://XX.XX.XXX.XXX:30115","SYSTEM","manager");

                                  }

                                  catch (SQLException e){

                                  }

                        }

                        catch (ClassNotFoundException e){

                        }

                        if(connection != null){

                                  try{

                                            String sqlstring = "select CONNID, FLDATE, PRICE from SFLIGHT.SFLIGHT where carrid = '" + p_carrid + "'";

                                            Statement stmt = connection.createStatement();

                                            ResultSet rs = stmt.executeQuery(sqlstring);

                                            while(rs.next()){

                                                      data = rs.getString("CONNID") + "/" + rs.getString("FLDATE") + "/" + rs.getString("PRICE");

                                                      carrid.add(data);

                                            }

                                  }

                                  catch(SQLException e){

                                  }

                        }

                        return carrid;

                }

    }

     

    In this file, what I'm doing is establishing an JDBC connection to my SAP HANA Server hosted on TK ucloud biz. The I'm selecting the CONNID, FLDATE and PRICE from the SFLIGHT table where the CARRID is going to be a parameters send from the application. As I didn't want to pass a multidimensional array, or an array of arrays, or anything like that...I simply concatenate the values using a "/" to split them later.

     

    • I modified the already existing BlagTest.java file

     

    BlagTest.java

    package com.BlagTest;

     

    import android.app.Activity;

    import android.os.Bundle;

    import org.apache.cordova.*;

     

    public class BlagTest extends DroidGap

    {

     

              private MyClass mc;

     

        @Override

        public void onCreate(Bundle savedInstanceState)

        {

            super.onCreate(savedInstanceState);

            super.init();

            mc = new MyClass();

            super.appView.getSettings().setJavaScriptEnabled(true);

            super.appView.addJavascriptInterface(mc, "MyCls");

            super.loadUrl("file:///android_asset/www/index.html");       

        }

    }

     

    Here, basically we saying that we want to be able to send data from Java to JavaScript by using the setJavaScriptEnabled(true) and then adding the addJavaScriptInterface(mc, "MyCls") we're telling how our class is going to be called...when we call them from JavaScript.

     

    • Finally...I delete everything from the already generated index.html file and put this code...

     

    index.html

    <html>

    <head>

    <title>SAP HANA from PhoneGap</title>

    <meta name="viewport" content="width=device-width, initialscale=1.0"></meta>

    <link rel="stylesheet" href="http://code.jquery.com/mobile/1.1.0/jquery.mobile-1.1.0.min.css"/>

    <script src="http://code.jquery.com/jquery-1.7.1.min.js"></script>

    <script src="http://code.jquery.com/mobile/1.1.0/jquery.mobile-1.1.0.min.js"></script>

    <script>

                function getData(){

             var carridField = document.getElementById('carrid');

                               var getCarrid = carridField.value;

             var myArrayList = window.MyCls.getData(getCarrid);

             carridField.value = "";

             $("#content").append("<ul id='list' data-role='listview' data-inset='true'</ul>");

             $("#content").trigger("create");

             for(var i = 0; i < myArrayList.size(); i++){

                  var array = "" + myArrayList.get(i);

                  array = array.split('/');

                  var _connid = array[0], _fldate = array[1], _price = array[2];

                  var list = "<li><p>CONNID: " + _connid + "</p><p>FLDATE: " + _fldate + "</p><p>PRICE: " + _price + "</p></li>";

                  $("#list").append(list);

             }

             $("#list").listview("refresh");

              }

    </script>

    </head>

    <body>

     

    <div data-role="page">

              <div data-role="content" id="content">

                        <div align="center"><h1>SAP HANA from PhoneGap</h1></div>

                        Carrid: <input type="text" id="carrid" size="2"/>

                        <button id="submitCarrid" onClick="getData()">Submit</button>

              </div>

    </div>

    </body>

    </html>

     

    What I'm doing here...is as following...

     

    • I have an input text and a button. In the input text, we're going pass an CARRID value and when pressing the button, we're going to call a JavaScript function.
    • The JavaScript function will collect the value from the input text, will call our Java function using window.MyCls.getData() and pass the CARRID parameter. This should return an ArrayList...but instead...it return an Object...so we need to handle it later...
    • Using JQueryMobile we're going to create a ListView which is like an HTML Table on steroids...and the thing I love about JQueryMobile is that we only need to include one "link rel" and two "script src" lines to make it work...as it grabs it from an on-line location.
    • We're going to do a FOR beginning from 0 till the size of our Object, and then will extract it's content using .get() will turning it into an String using "".
    • We simply split the newly created String and assign it to variables.
    • We add the lines to our ListView and update it when we finish.

     

    After that, we can simply go to Project --> Clean to rebuild our project and then right click on our Project Folder and choose Run As --> Android Application.

     

    PhoneGap_SAP_HANA_01.png

    PhoneGap_SAP_HANA_02.png

    PhoneGap_SAP_HANA_03.png

    PhoneGap_SAP_HANA_04.png

     

    It took so long time...but the rewards can't be greater...hope you like this

    Yesterday, I wrote a blog called MySQL, PostreSQL and SAP HANA - A friendly comparison which I thought it was fun and cool...and maybe it was...but...thing can't be taken that lightly...no even by myself...

     

    My blog might be entertainment...but...there's a few points that must considered when doing something like this...

     

    • Benchmarking must be done using the same Hardware. There's no point on comparing a DB running on a single core to another one running on 4 or more cores.
    • Benchmarking must be done on the same environment. In this case I used local copies of MySQL and PostgreSQL while SAP HANA was running on AWS.
    • Benchmarking must be done with real knowledge of the technologies being used...I have almost no experience using MySQL and PostgreSQL...and that might lead to poor performance.
    • Benchmarking must be done on DB's that were designed to fulfil the same goal. SAP HANA was created for ERP/Enterprise applications. MySQL and PostgreSQL might be as well...but the difference is that they were not designed thinking on Big Data or the special kind of information managed by SAP.
    • Benchmarking results must be published making completely sure that the code is well done. I made a huge mistake on measuring the speed taking the Python processing time into account...Should have been focused on the DB's time only...but again...without the same hardware...it's pointless...

     

    This said...that was my first and last blog on the Benchmarking topic...I made a huge mistake trying to put myself on a territory that is clearly not my main area of expertise...sorry about that...sometimes, you let your passion for technologies to drive you in the wrong direction...lesson learned and lesson taken...

     

    Rest assure, that I will keep blogging and doing my crazy experiments...but the same mistake will be not repeated twice...

     

    Greetings,

     

    Blag.

    UPDATE! Thanks to a comment by Alan Suffolk I fixed my script a little bit, just moving the end = time.clock() and time_taken = end - start after the cur.execute(query) because that's exactly when the SAP HANA query ends...you can see the new processing time in the image below...

     

    My good friend Pakdi Decnud gave a great idea while we were having lunch the very same day of the SAP CodeJam Montreal event.

     

    Pakdi told me..."Why don't you make a comparison between SAP HANA and let's say...MongoDB"...I thought that it was of course a great idea...so yesterday I start exploring MongoDB...so you may ask yourselves..."Why then are you talking about MySQL and PostreSQL?"

     

    Easy answer...and here are my thoughts...

     

    • I really don't get MongoDB...the whole No-SQL is really alien to me...
    • MongoDB is "Document" based, meaning that you create collections of documents, no databases nor tables...
    • MongoDB doesn't support Inner Joins and aggregates need a framework that it's more weird than MongoDB itself...
    • MongoDB is not meant for enterprise applications

     

    That's why I decide to make a little bit shift and grab the two most used databases by start-ups and developers...MySQL and PostgreSQL.

     

    For this blog, I wanted to have a lot of information...so as always, I grab my beloved Python and create a little script to generate 1 million records for two tables. One script per table.

     

    The structure of the tables is as follows...

     

    DOC_HEADER

     

    Field NameDataTypeLength
    DOCUMENT_IDVARCHAR8
    YEARVARCHAR4
    AREAVARCHAR2
    NAMEVARCHAR20
    LAST_NAMEVARCHAR20

     

    DOC_DETAIL


    Field NameDataTypeLength
    DOCUMENT_IDVARCHAR8
    YEARVARCHAR4
    AREAVARCHAR2
    AMOUNTBIGINT

     

    And here are the script to generate the 1 million records in a nice .CSV file

     

    Doc_Header_Generator.py

    import random

    import csv

     

    names = ["Anne", "Gigi", "Juergen", "Ingo", "Inga", "Alvaro", "Mario",

    "Julien", "Mike", "Michael", "Karin", "Rui", "John", "Rocky", "Sebastian",

    "Kai-Yin", "Hester", "Katrin", "Uwe", "Vitaliy"]

    last_names = ["Hardy", "Read", "Schmerder", "Sauerzapf", "Bereza", "Tejada",

    "Herger", "Vayssiere", "Flynn", "Byczkowski", "Schattka",

    "Nogueira", "Mayerhofer", "Ongkowidjojo", "Wieczorek", "Gau", "Hilbrecht",

    "Staehr", "Kylau", "Rudnytskiy"]

    area = ["001", "002", "003", "004", "005", "006", "007", "008", "009", "010"]

    year = ["2000", "2001", "2002", "2003", "2004", "2005", "2006", "2007",

    "2008", "2009", "2010", "2011", "2012"]

     

    def Generate_File(pSchema, pNumber):

        iNumber = 0

        c = csv.writer(open("Doc_Header.csv", "wb"))

     

        while iNumber < pNumber:

            queries = []

            r_doc_id = random.randrange(1, 999999)

            r_names = random.randrange(0, 20)

            r_lastnames = random.randrange(0, 20)

            r_areas = random.randrange(0, 10)

            r_years = random.randrange(0, 13)

            iNumber += 1

            queries.append(r_doc_id)

            queries.append(year[r_years])

            queries.append(str(area[r_areas]))

            queries.append(names[r_names])

            queries.append(last_names[r_lastnames])

            c.writerow(queries)

     

    num_files = input("How many records?: \n")

    Generate_File(num_files)

     

    Doc_Detail_Generator.py

    import random

    import csv

     

    area = ["001", "002", "003", "004", "005", "006", "007", "008", "009", "010"]

    year = ["2000", "2001", "2002", "2003", "2004", "2005", "2006", "2007",

    "2008", "2009", "2010", "2011", "2012"]

     

    def Generate_File(pSchema, pNumber):

        iNumber = 0

        c = csv.writer(open("Doc_Detail.csv", "wb"))

     

        while iNumber < pNumber:

            queries = []

            r_doc_id = random.randrange(1, 999999)

            r_areas = random.randrange(0, 10)

            r_years = random.randrange(0, 13)

            r_amount = random.randrange(0, 10000, 1)

            iNumber += 1

            queries.append(r_doc_id)

            queries.append(year[r_years])

            queries.append(str(area[r_areas]))

            queries.append(r_amount)

            c.writerow(queries)

     

    num_files = input("How many records?: \n")

    Generate_File(num_files)

     

    With the two files ready, I upload them to MySQL, PostgreSQL and SAP HANA.

     

    To measure the speed, I create three Python scripts using...yes...again Bottle...

     

    The basic idea is to join the two tables, select the Document_Id, Year, Area and the sum of Amount.

     

    Let's start with the MySQL Script...

     

    MySQL_Bottle_Documents.py

    from bottle import get, run

    import mysql.connector

    import time

     

    @get('/show_query')

    def show_form():

        counter = 0

        start = time.clock()

        conn = mysql.connector.Connect(host='localhost', user='root',

                            password='root', database='P075400')

        cur = conn.cursor()

     

        query = '''SELECT A.DOCUMENT_ID, A.YEAR, A.AREA, SUM(AMOUNT)

                      FROM DOC_HEADER AS A INNER JOIN DOC_DETAIL AS B

                      WHERE A.DOCUMENT_ID = B.DOCUMENT_ID

                          AND A.YEAR = B.YEAR

                          AND A.AREA = B.AREA

                      GROUP BY DOCUMENT_ID, YEAR, AREA'''

        cur.execute(query)

     

         output = "<DIV ALIGN='CENTER'><TABLE BORDER='1'>"

        output += "<TR BGCOLOR='#B9C9FE'>"

        output += "<TH>Document</TH><TH>Year</TH>"

        output += "<TH>Area</TH><TH>Amount</TH>"

        output += "</TR>"

        for row in cur:

            counter += 1

            document_id = str(row[0])

            year = str(row[1])

            area = str(row[2])

            amount = str(row[3])

            output += "<TR BGCOLOR='#E8EDFF'>"

            output += '''<TD>%s</TD><TD>%s</TD>

                             <TD>%s</TD><TD>%s</TD>''' % (document_id, year,

                                                                                   area, amount)

            output += "</TR>"

        output += "</TABLE>"

        end = time.clock()

        time_taken = end - start

        output += "<H1>SAP HANA - %s records in %s seconds</H1></DIV>"\

        % (counter, time_taken)

        return output

     

    run(host='localhost', port=8080)

     

    I let the script run...and after more than one hour...I simply got bored and interrupt the process...

     

    So, I continue with PostgreSQL...

     

    PostgreSQL_Bottle_Documents.py

    from bottle import get, run

    import psycopg2

    import time

     

     

     

     

    @get('/show_query')

    def show_form():

        counter = 0

        start = time.clock()

        conn = psycopg2.connect("dbname=P075400 user=postgres password=root")

        cur = conn.cursor()

     

     

        query = '''SELECT "DOC_HEADER"."DOCUMENT_ID", "DOC_HEADER"."YEAR",

                    "DOC_HEADER"."AREA", SUM("DOC_DETAIL"."AMOUNT") FROM

                    public."DOC_HEADER", public."DOC_DETAIL" WHERE

                    "DOC_HEADER"."DOCUMENT_ID" = "DOC_DETAIL"."DOCUMENT_ID"

                    AND "DOC_HEADER"."YEAR" = "DOC_DETAIL"."YEAR"

                    AND "DOC_HEADER"."AREA" = "DOC_DETAIL"."AREA"

                    GROUP BY "DOC_HEADER"."DOCUMENT_ID", "DOC_HEADER"."YEAR",

                    "DOC_HEADER"."AREA"'''

        cur.execute(query)

     

     

        output = "<DIV ALIGN='CENTER'><TABLE BORDER='1'>"

        output += "<TR BGCOLOR='#B9C9FE'>"

        output += "<TH>Document</TH><TH>Year</TH>"

        output += "<TH>Area</TH><TH>Amount</TH>"

        output += "</TR>"

        for row in cur:

            counter += 1

            document_id = str(row[0])

            year = str(row[1])

            area = str(row[2])

            amount = str(row[3])

            output += "<TR BGCOLOR='#E8EDFF'>"

            output += '''<TD>%s</TD><TD>%s</TD>

                             <TD>%s</TD><TD>%s</TD>''' % (document_id, year,

                                                                                   area, amount)

            output += "</TR>"

        output += "</TABLE>"

        end = time.clock()

        time_taken = end - start

        output += "<H1>PostgreSQL - %s records in %s seconds</H1></DIV>"\

        % (counter, time_taken)

        return output

     

     

    run(host='localhost', port=8080)

     

    This time...I was lucky...

    PostgreSQL.pngOut of 2 millions records, PostgreSQL managed to aggregate the amount field and generate 7669 records in 36 seconds...not bad at all...

     

    For SAP HANA, I decided to take fully advantage of the Calculation Views, so I create the following...

     

    CV_Documents.pngI joined both tables, used a projection, applied the aggregation and specified the result...then I wrote this Python script...

     

    SAPHANA_Bottle_Documents.py

    from bottle import get, run

    import dbapi

    import time

     

    @get('/show_query')

    def show_form():

        start = time.clock()

        conn = dbapi.connect('******', 30015, '********', '*******')

        cur = conn.cursor()

     

        queries = ['''create type test_out as table (DEP_DESC NVARCHAR(20),

                   YEAR VARCHAR(4), Q1 BIGINT, Q2 BIGINT, Q3 BIGINT, Q4 BIGINT)''',

                   '''create procedure pyTest (OUT out_test TEST_OUT)\

                   language sqlscript reads sql data with result view ProcView as\

                   begin\

                   out_test = CE_CALC_VIEW("_SYS_BIC"."blag/CV_EXPENSES",

                   ["DEP_DESC","YEAR","Q1","Q2","Q3","Q4"]);\

                   end''']

     

        for query in queries:

            cur.execute(query)

            conn.commit()

     

        query = '''select DEP_DESC, YEAR, Q1, Q2, Q3, Q4 from ProcView'''

        cur.execute(query)

        ret = cur.fetchall()

     

        output = "<DIV ALIGN='CENTER'><TABLE BORDER='1'>"

        output += "<TR BGCOLOR='#B9C9FE'>"

        output += "<TH>Department</TH><TH>Year</TH>"

        output += "<TH>Q1</TH><TH>Q2</TH><TH>Q3</TH><TH>Q4</TH>"

        output += "</TR>"

        for row in ret:

            dep_desc = str(row["DEP_DESC"])

            year = str(row["YEAR"])

            Q1 = str(row["Q1"])

            Q2 = str(row["Q2"])

            Q3 = str(row["Q3"])

            Q4 = str(row["Q4"])

            output += "<TR BGCOLOR='#E8EDFF'>"

            output += '''<TD>%s</TD><TD>%s</TD>

                         <TD>%s</TD><TD>%s</TD>

                         <TD>%s</TD><TD>%s</TD>''' % (dep_desc, year,

                                                      Q1, Q2, Q3, Q4)

            output += "</TR>"

        output += "</TABLE>"

        end = time.clock()

        time_taken = end - start

        output += "<H1>SAP HANA = %s seconds</H1></DIV>" % (time_taken)

        return output

     

    run(host='localhost', port=8080)

     

    After the execution...I couldn't be happier...here's the result...

     

    SAP_HANA.png

     

    SAP HANA managed the same 2 million records...generate the same 7669 aggregated records in only 18 seconds...that's 50% faster than PostgreSQL and...well...let's only say...way faster than MySQL...

     

    Now...tell me that SAP HANA is not the coolest and fastest Database around...I dare you

     

    By doing that little fix on my Python Script for SAP HANA...the new processing time, without generating the Bottle table is...

     

    SAP_HANA_Fixed.png

    1 2 Previous Next

    Filter Blog

    By author:
    By date:
    By tag: