R meets HANA

If you read my last blog called HANA meets R you will remember that we read data from HANA into R directly, without having to download an .csv file, but using ODBC. This time, we're going to read data from HANA as well, but after do some nice tricks on R, we're going to post back the information into HANA.

Keep in mind, that is not an standard SAP solution. This only relies on a custom R package that can work with ODBC enabled tables, and like any custom packages, there are many limitations...anyway...this should be fixed when SAP released the official R into HANA integration.

In my previous blog Prediction model with HANA and R we create a stored procedure in HANA to populate a table called TICKETS_BY_YEAR, then on R we calculate the prediction for the next year and generate a nice graphic showing both the real data and the prediction. So...of course I'm not going to repeat all that.

This is the R code that we need to use...

library("RODBC")
ch<-odbcConnect("HANA",uid="P075400",pwd="HrCOpPk4")
Flight_Tickets<-sqlFetch(ch,"P075400.TICKETS_BY_YEAR")
period=Flight_Tickets$PERIOD
tickets=Flight_Tickets$TICKETS
var_year=substr(period[1],1,4)
var_year=as.integer(var_year)
var_year=var_year+1
var_year=as.character(var_year)
new_period=gsub("^\\d{4}",var_year,period)
next_year=data.frame(year=new_period,stringsAsFactors=FALSE)
prt.lm=lm(tickets ~ period)
pred=predict(prt.lm,next_year,interval="none")
period=next_year
tickets=pred
PREDICTION_TICKETS<-data.frame(period,tickets)
sqlDrop(ch,"PREDICTION_TICKETS",errors=FALSE)
sqlSave(ch,PREDICTION_TICKETS,rownames="id")
odbcClose(ch)

After we execute this code, we can check on HANA that our new table called PREDICTION_TICKETS was created...


And the data was populated as expected...


You may wonder...which are the limitations? Everything seems to work like a charm? Easy...not a lot, but important limitations...

* We don't have a way to validate if the table exists or not.
* We must delete the table before doing the insert, otherwise is not going to work.
* Even when the date field was called PERIOD, R named it "year" and pass it into HANA.
* We can't specify the type of the fields, nor the lenght
* We are forced to have an additional column with a numeric index, that we can nicely call "Id"...

As I said early...this is just a custom package that allows us to play...this shouldn't be used as a final solution, but as a playground. Enjoy!

Greetings,

Blag.

HANA meets R

In my previous HANA and R blogs, I have been forced to create .csv files from HANA and read them on R...an easy but also boring procedure...specially if your R report is supposed to be run on a regular basis...having to create an .csv file every time you need to run your report it's not a nice thing...

After spending some time reading and researching R...I finally came to a library that can read data from any relational database and being HANA, ODBC capable, the work is just a piece of cake -;)

For this examples, we must install two libraries: RODBC and Plotrix and create the DSN connection as shown here...


Here we're going to "Add..." a new "User DSN"


HANA already provides us a driver, so we're cool


Assign a name for the "Data Source Name", "Description" is optional and "Server:Port" should be of course filled.

Now...we're ready to go to our HANA studio an create a table and a stored procedure...

CREATE PROCEDURE GetTicketsByYearMonth
(IN var_year NVARCHAR(4),IN var_month NVARCHAR(2))
LANGUAGE SQLSCRIPT AS
BEGIN
select count(bookid), carrid
from sflight.snvoice
where year(fldate) = VAR_YEAR
and month(fldate) = VAR_MONTH
group by carrid
into TICKETS_BY_YEAR_MONTH;
END;

CALL P075400.GetTicketsByYearMonth('2011','12');

After we run our Stored Procedure...we have all the information in the table...Ok...only two fields...today was a hard day...I'm tired -:P


Finally...we can code some R! First, we're going to create a Fan Plot (The Plotix library is needed for that one) and then a Bar Plot...I used the same code for both, so just replace the comment on one by the other one and run it again...I know...I'm being lazy again...but at least I'm not reinveting the wheel -;) Two codes with only 1 different line? No thanks...

library("plotrix")
library("RODBC")
ch<-odbcConnect("HANA",uid="P075400",pwd="***")
res<-sqlFetch(ch,"P075400.TICKETS_BY_YEAR_MONTH")
fan.plot(res$TICKETS,labels=res$CARRIER,
         main="Tickets for December 2011")
#barplot(res$TICKETS,names.arg=res$CARRIER)
odbcClose(ch)

The code is very simple...we call the libraries we need, we stablish a communication to our DSN, we fetch the data from the table, we create the graphics and finally we close the connection.

And here come the graphics...



I will keep investigating on this way to connect HANA and R...more blogs should be on the way -;)

Greetings,

Blag.

Blag's Word Clock

Do you know what a Word Clock is? Well, it's basically a clock which gives you the time using words instead of numbers.


The first time I saw one...I knew I wanted to have one, but then I think to myself..."Hey! I'm a programmer...why buy one when I can build one?" Also...I realized that most of the Word Clocks doesn't give you the exact time as you can see in the picture...so I decided to fix that -:)

Next step was to decided which programming language to use...I needed a nice graphical interface so ABAP and R we're out of the question...web version wasn't what I was looking for, so PHP was discarded as well...so my next guess was a language I really like and didn't want to forgot so easily -;) So I choose Python and wxPython to make it work -:) Of course...I'm far from being a Python expert, so I'm sure my code could be a little shorter -:P

#Blag's Word Clock
#Alvaro "Blag" Tejada Galindo
#17/01/2012
import wx
import time
import datetime

class MyApp(wx.App):
    def OnInit(self):
        self.frame = MyFrame(None, title="Blag's Word Clock")
        self.SetTopWindow(self.frame)
        self.frame.Show()
        return True

class MyFrame(wx.Frame):
    def __init__(self, parent, id=wx.ID_ANY, title="",
                 pos=wx.DefaultPosition, size=(330, 260),
                 style=wx.DEFAULT_FRAME_STYLE ^ wx.RESIZE_BORDER,
                 name="MyFrame"):
        super(MyFrame, self).__init__(parent, id, title, pos,size, style, name)
        self.timer = wx.Timer(self)
        self.Bind(wx.EVT_TIMER, self.update, self.timer)
        self.timer.Start(1000)
        self.SetBackgroundColour("Black")
        text_font = wx.Font(15, wx.SWISS, wx.NORMAL, wx.NORMAL, False, u'Arial')
        self.label_it=wx.StaticText(self,1,"IT",wx.Point(10,10))
        self.label_it.SetForegroundColour("yellow")
        self.label_it.SetFont(text_font)
        self.label_is=wx.StaticText(self,1,"IS",wx.Point(35,10))
        self.label_is.SetForegroundColour("yellow")
        self.label_is.SetFont(text_font)
        self.label_half=wx.StaticText(self,1,"HALF",wx.Point(60,10))
        self.label_half.SetForegroundColour("gray")
        self.label_half.SetFont(text_font)
        self.label_twenty=wx.StaticText(self,1,"TWENTY",wx.Point(120,10))
        self.label_twenty.SetForegroundColour("gray")
        self.label_twenty.SetFont(text_font)
        self.label_quarter=wx.StaticText(self,1,"QUARTER",wx.Point(215,10))
        self.label_quarter.SetForegroundColour("gray")
        self.label_quarter.SetFont(text_font)
                
        self.label_ten=wx.StaticText(self,1,"TEN",wx.Point(10,40))
        self.label_ten.SetForegroundColour("gray")
        self.label_ten.SetFont(text_font)
        self.label_to=wx.StaticText(self,1,"TO",wx.Point(75,40))
        self.label_to.SetForegroundColour("gray")
        self.label_to.SetFont(text_font)
        self.label_one=wx.StaticText(self,1,"ONE",wx.Point(120,40))
        self.label_one.SetForegroundColour("gray")
        self.label_one.SetFont(text_font)
        self.label_two=wx.StaticText(self,1,"TWO",wx.Point(180,40))
        self.label_two.SetForegroundColour("gray")
        self.label_two.SetFont(text_font)
        self.label_three=wx.StaticText(self,1,"THREE",wx.Point(245,40))
        self.label_three.SetForegroundColour("gray")
        self.label_three.SetFont(text_font)
                
        self.label_four=wx.StaticText(self,1,"FOUR",wx.Point(10,70))
        self.label_four.SetForegroundColour("gray")
        self.label_four.SetFont(text_font)
        self.label_five=wx.StaticText(self,1,"FIVE",wx.Point(75,70))
        self.label_five.SetForegroundColour("gray")
        self.label_five.SetFont(text_font)
        self.label_six=wx.StaticText(self,1,"SIX",wx.Point(125,70))
        self.label_six.SetForegroundColour("gray")
        self.label_six.SetFont(text_font)
        self.label_seven=wx.StaticText(self,1,"SEVEN",wx.Point(170,70))
        self.label_seven.SetForegroundColour("gray")
        self.label_seven.SetFont(text_font)
        self.label_eight=wx.StaticText(self,1,"EIGHT",wx.Point(250,70))
        self.label_eight.SetForegroundColour("gray")
        self.label_eight.SetFont(text_font)
                
        self.label_nine=wx.StaticText(self,1,"NINE",wx.Point(10,100))
        self.label_nine.SetForegroundColour("gray")
        self.label_nine.SetFont(text_font)
        self.label_eleven=wx.StaticText(self,1,"ELEVEN",wx.Point(110,100))
        self.label_eleven.SetForegroundColour("gray")
        self.label_eleven.SetFont(text_font)
        self.label_twelve=wx.StaticText(self,1,"TWELVE",wx.Point(230,100))
        self.label_twelve.SetForegroundColour("gray")
        self.label_twelve.SetFont(text_font)
                
        self.label_minutes=wx.StaticText(self,1,"MINUTES",wx.Point(10,130))
        self.label_minutes.SetForegroundColour("gray")
        self.label_minutes.SetFont(text_font)
        self.label_past=wx.StaticText(self,1,"PAST",wx.Point(115,130))
        self.label_past.SetForegroundColour("gray")
        self.label_past.SetFont(text_font)
        self.label_to_x=wx.StaticText(self,1,"TO",wx.Point(180,130))
        self.label_to_x.SetForegroundColour("gray")
        self.label_to_x.SetFont(text_font)
        self.label_one_x=wx.StaticText(self,1,"ONE",wx.Point(215,130))
        self.label_one_x.SetForegroundColour("gray")
        self.label_one_x.SetFont(text_font)
        self.label_two_x=wx.StaticText(self,1,"TWO",wx.Point(265,130))
        self.label_two_x.SetForegroundColour("gray")
        self.label_two_x.SetFont(text_font)
                
        self.label_three_x=wx.StaticText(self,1,"THREE",wx.Point(10,160))
        self.label_three_x.SetForegroundColour("gray")
        self.label_three_x.SetFont(text_font)
        self.label_four_x=wx.StaticText(self,1,"FOUR",wx.Point(85,160))
        self.label_four_x.SetForegroundColour("gray")
        self.label_four_x.SetFont(text_font)
        self.label_five_x=wx.StaticText(self,1,"FIVE",wx.Point(150,160))
        self.label_five_x.SetForegroundColour("gray")
        self.label_five_x.SetFont(text_font)
        self.label_six_x=wx.StaticText(self,1,"SIX",wx.Point(205,160))
        self.label_six_x.SetForegroundColour("gray")
        self.label_six_x.SetFont(text_font)
        self.label_seven_x=wx.StaticText(self,1,"SEVEN",wx.Point(245,160))
        self.label_seven_x.SetForegroundColour("gray")
        self.label_seven_x.SetFont(text_font)
                
        self.label_eight_x=wx.StaticText(self,1,"EIGHT",wx.Point(10,190))
        self.label_eight_x.SetForegroundColour("gray")
        self.label_eight_x.SetFont(text_font)
        self.label_nine_x=wx.StaticText(self,1,"NINE",wx.Point(95,190))
        self.label_nine_x.SetForegroundColour("gray")
        self.label_nine_x.SetFont(text_font)
        self.label_ten_x=wx.StaticText(self,1,"TEN",wx.Point(165,190))
        self.label_ten_x.SetForegroundColour("gray")
        self.label_ten_x.SetFont(text_font)
        self.label_eleven_x=wx.StaticText(self,1,"ELEVEN",wx.Point(235,190))
        self.label_eleven_x.SetForegroundColour("gray")
        self.label_eleven_x.SetFont(text_font)
                
        self.label_twelve_x=wx.StaticText(self,1,"TWELVE",wx.Point(10,220))
        self.label_twelve_x.SetForegroundColour("gray")
        self.label_twelve_x.SetFont(text_font)
        self.label_oclock=wx.StaticText(self,1,"O'CLOCK",wx.Point(125,220))
        self.label_oclock.SetForegroundColour("gray")
        self.label_oclock.SetFont(text_font)
        self.label_am=wx.StaticText(self,1,"AM",wx.Point(235,220))
        self.label_am.SetForegroundColour("gray")
        self.label_am.SetFont(text_font)
        self.label_pm=wx.StaticText(self,1,"PM",wx.Point(283,220))
        self.label_pm.SetForegroundColour("gray")
        self.label_pm.SetFont(text_font)
    
    def reset_colours(self):
        self.label_pm.SetForegroundColour("gray")
        self.label_am.SetForegroundColour("gray")
        self.label_oclock.SetForegroundColour("gray")
        self.label_one.SetForegroundColour("gray")
        self.label_two.SetForegroundColour("gray")
        self.label_three.SetForegroundColour("gray")
        self.label_four.SetForegroundColour("gray")
        self.label_five.SetForegroundColour("gray")
        self.label_six.SetForegroundColour("gray")
        self.label_seven.SetForegroundColour("gray")
        self.label_eight.SetForegroundColour("gray")
        self.label_nine.SetForegroundColour("gray")
        self.label_one_x.SetForegroundColour("gray")
        self.label_two_x.SetForegroundColour("gray")
        self.label_three_x.SetForegroundColour("gray")
        self.label_four_x.SetForegroundColour("gray")
        self.label_five_x.SetForegroundColour("gray")
        self.label_six_x.SetForegroundColour("gray")
        self.label_seven_x.SetForegroundColour("gray")
        self.label_eight_x.SetForegroundColour("gray")
        self.label_nine_x.SetForegroundColour("gray")
        self.label_half.SetForegroundColour("gray")
        self.label_twenty.SetForegroundColour("gray")
        self.label_quarter.SetForegroundColour("gray")
        self.label_ten.SetForegroundColour("gray")
        self.label_ten_x.SetForegroundColour("gray")
        self.label_eleven.SetForegroundColour("gray")
        self.label_eleven_x.SetForegroundColour("gray")
        self.label_twelve.SetForegroundColour("gray")
        self.label_twelve_x.SetForegroundColour("gray")

    def hours(self,hour):
        if hour == 12:
            self.label_twelve.SetForegroundColour("yellow")
        elif hour == 11:
            self.label_eleven.SetForegroundColour("yellow")
        elif hour == 10:
            self.label_ten.SetForegroundColour("yellow")
        elif hour == 9:
            self.label_nine.SetForegroundColour("yellow")
        elif hour == 8:
            self.label_eight.SetForegroundColour("yellow")
        elif hour == 7:
            self.label_seven.SetForegroundColour("yellow")
        elif hour == 6:
            self.label_six.SetForegroundColour("yellow")
        elif hour == 5:
            self.label_five.SetForegroundColour("yellow")
        elif hour == 4:
            self.label_four.SetForegroundColour("yellow")
        elif hour == 3:
            self.label_three.SetForegroundColour("yellow")
        elif hour == 2:
            self.label_two.SetForegroundColour("yellow")
        elif hour == 1:
            self.label_one.SetForegroundColour("yellow")            

    def minutes(self,reminder):
        if reminder == 9:
            self.label_nine.SetForegroundColour("yellow")
        if reminder == 8:
            self.label_eight.SetForegroundColour("yellow")
        if reminder == 7:
            self.label_seven.SetForegroundColour("yellow")
        if reminder == 6:
            self.label_six.SetForegroundColour("yellow")
        if reminder == 5:
            self.label_five.SetForegroundColour("yellow")
        if reminder == 4:
            self.label_four.SetForegroundColour("yellow")
        if reminder == 3:
            self.label_three.SetForegroundColour("yellow")
        if reminder == 2:
            self.label_two.SetForegroundColour("yellow")
        if reminder == 1:
            self.label_one.SetForegroundColour("yellow")

    def update(self, event):        
        self.reset_colours()
        now=datetime.datetime.now()
        hour = now.hour
        minute = now.minute
        if hour >= 12 and minute != 0:
            self.label_pm.SetForegroundColour("yellow")
            hour = hour - 12
        elif hour < 12 and minute != 0:
            self.label_am.SetForegroundColour("yellow")
        elif minute == 0:
            self.label_oclock.SetForegroundColour("yellow")
            if hour >= 12:
                hour = hour - 12
                self.hours(hour)
            else:
                self.hours(hour)

        if minute >= 1 and minute <= 29:
            self.label_minutes.SetForegroundColour("yellow")
            self.label_past.SetForegroundColour("yellow")
            if hour == 12:
                self.label_twelve_x.SetForegroundColour("yellow")
            elif hour == 11:
                self.label_eleven_x.SetForegroundColour("yellow")
            elif hour == 10:
                self.label_ten_x.SetForegroundColour("yellow")
            elif hour == 9:
                self.label_nine_x.SetForegroundColour("yellow")
            elif hour == 8:
                self.label_eight_x.SetForegroundColour("yellow")
            elif hour == 7:
                self.label_seven_x.SetForegroundColour("yellow")
            elif hour == 6:
                self.label_six_x.SetForegroundColour("yellow")
            elif hour == 5:
                self.label_five_x.SetForegroundColour("yellow")
            elif hour == 4:
                self.label_four_x.SetForegroundColour("yellow")
            elif hour == 3:
                self.label_three_x.SetForegroundColour("yellow")
            elif hour == 2:
                self.label_two_x.SetForegroundColour("yellow")
            elif hour == 1:
                self.label_one_x.SetForegroundColour("yellow")
            division = minute / 20
            reminder = minute % 20                
            if division == 1 and reminder == 0:
                self.label_twenty.SetForegroundColour("yellow")
            elif division == 1 and reminder != 0:
                self.label_twenty.SetForegroundColour("yellow")
                self.minutes(reminder)
            division = minute / 10
            reminder = minute % 10                
            if division == 1 and reminder == 0:
                self.label_ten.SetForegroundColour("yellow")
            elif division == 1 and reminder != 0:
                self.label_ten.SetForegroundColour("yellow")
                if reminder == 9:
                    self.label_nine.SetForegroundColour("yellow")
                if reminder == 8:
                    self.label_eight.SetForegroundColour("yellow")
                if reminder == 7:
                    self.label_seven.SetForegroundColour("yellow")
                if reminder == 6:
                    self.label_six.SetForegroundColour("yellow")
                if reminder == 5:
                    self.label_ten.SetForegroundColour("gray")
                    self.label_quarter.SetForegroundColour("yellow")
                if reminder == 4:
                    self.label_four.SetForegroundColour("yellow")
                if reminder == 3:
                    self.label_three.SetForegroundColour("yellow")
                if reminder == 2:
                    self.label_ten.SetForegroundColour("gray")
                    self.label_twelve.SetForegroundColour("yellow")
                if reminder == 1:
                    self.label_ten.SetForegroundColour("gray")
                    self.label_eleven.SetForegroundColour("yellow")
            elif division == 0 and reminder != 0:
                if reminder == 9:
                    self.label_nine.SetForegroundColour("yellow")
                if reminder == 8:
                    self.label_eight.SetForegroundColour("yellow")
                if reminder == 7:
                    self.label_seven.SetForegroundColour("yellow")
                if reminder == 6:
                    self.label_six.SetForegroundColour("yellow")
                if reminder == 5:
                    self.label_five.SetForegroundColour("yellow")
                if reminder == 4:
                    self.label_four.SetForegroundColour("yellow")
                if reminder == 3:
                    self.label_three.SetForegroundColour("yellow")
                if reminder == 2:
                    self.label_two.SetForegroundColour("yellow")
                if reminder == 1:
                    self.label_one.SetForegroundColour("yellow")
                        
        elif minute == 30:
            self.hours(hour)
            self.label_half.SetForegroundColour("yellow")
            self.label_past.SetForegroundColour("yellow")

        elif minute >= 29 and minute <= 59:
            self.label_minutes.SetForegroundColour("yellow")
            minute = 60 - minute
            self.label_to_x.SetForegroundColour("yellow")
            hour = hour + 1
            if hour == 12:
                self.label_twelve_x.SetForegroundColour("yellow")
            elif hour == 11:
                self.label_eleven_x.SetForegroundColour("yellow")
            elif hour == 10:
                self.label_ten_x.SetForegroundColour("yellow")
            elif hour == 9:
                self.label_nine_x.SetForegroundColour("yellow")
            elif hour == 8:
                self.label_eight_x.SetForegroundColour("yellow")
            elif hour == 7:
                self.label_seven_x.SetForegroundColour("yellow")
            elif hour == 6:
                self.label_six_x.SetForegroundColour("yellow")
            elif hour == 5:
                self.label_five_x.SetForegroundColour("yellow")
            elif hour == 4:
                self.label_four_x.SetForegroundColour("yellow")
            elif hour == 3:
                self.label_three_x.SetForegroundColour("yellow")
            elif hour == 2:
                self.label_two_x.SetForegroundColour("yellow")
            elif hour == 1:
                self.label_one_x.SetForegroundColour("yellow")
            division = minute / 20
            reminder = minute % 20                
            if division == 1 and reminder == 0:
                self.label_twenty.SetForegroundColour("yellow")
            elif division == 1 and reminder != 0:
                self.label_twenty.SetForegroundColour("yellow")
                if reminder == 9:
                    self.label_nine.SetForegroundColour("yellow")
                if reminder == 8:
                    self.label_eight.SetForegroundColour("yellow")
                if reminder == 7:
                    self.label_seven.SetForegroundColour("yellow")
                if reminder == 6:
                    self.label_six.SetForegroundColour("yellow")
                if reminder == 5:
                    self.label_five.SetForegroundColour("yellow")
                if reminder == 4:
                    self.label_four.SetForegroundColour("yellow")
                if reminder == 3:
                    self.label_three.SetForegroundColour("yellow")
                if reminder == 2:
                    self.label_two.SetForegroundColour("yellow")
                if reminder == 1:
                    self.label_one.SetForegroundColour("yellow")
            division = minute / 10
            reminder = minute % 10                
            if division == 1 and reminder == 0:
                self.label_ten.SetForegroundColour("yellow")
            elif division == 1 and reminder != 0:
                self.label_ten.SetForegroundColour("yellow")
                if reminder == 9:
                    self.label_nine.SetForegroundColour("yellow")
                if reminder == 8:
                    self.label_eight.SetForegroundColour("yellow")
                if reminder == 7:
                    self.label_seven.SetForegroundColour("yellow")
                if reminder == 6:
                    self.label_six.SetForegroundColour("yellow")
                if reminder == 5:
                    self.label_ten.SetForegroundColour("gray")
                    self.label_quarter.SetForegroundColour("yellow")
                if reminder == 4:
                    self.label_four.SetForegroundColour("yellow")
                if reminder == 3:
                    self.label_three.SetForegroundColour("yellow")
                if reminder == 2:
                    self.label_ten.SetForegroundColour("gray")
                    self.label_twelve.SetForegroundColour("yellow")
                if reminder == 1:
                    self.label_ten.SetForegroundColour("gray")
                    self.label_eleven.SetForegroundColour("yellow")
            elif division == 0 and reminder != 0:
                self.minutes(reminder)    

if __name__ == "__main__":
    app = MyApp(False)
    app.MainLoop() 

Greetings,

Blag.

Prediction model with HANA and R

These days, I have been reading and playing a lot with R, and I really come to love it...of course, I don't have a clue on those weird statistics formulas, but it doesn't mean I can't use R and try do some awesome stuff with it.

So, yesterday I was thinking about doing another integration between HANA and R, my new adopted kids, so I came with the idea of building a prediction model for a flight company. I followed this steps.

1.- First, I need to choose a table, so I picked SNVOICE:

This table offers us, the carrier id, the date and book id, meaning the amount of tickets sold in a particular day. And from here when can do some calculation and determine how many tickets were sold in each month of a particular year.

2.- I needed a table to store my new information, so I created the table TICKETS_BY_YEAR:

3.- I needed a Procedure script to analyse the table, determine the total amount per day of the month and then gave a grand total per month.

CREATE PROCEDURE GetTicketsByMonth 
(IN var_year NVARCHAR(4),IN var_carrid NVARCHAR(2))
LANGUAGE SQLSCRIPT AS
v_found NVARCHAR(2) := 1;
sum_bookid INT;
v_date NVARCHAR(8) := '';
BEGIN                
TT_MONTH = select fldate, count(bookid) as "BOOKID"
           from sflight.snvoice 
           where year(fldate) = VAR_YEAR and carrid = VAR_CARRID
           group by fldate
           order by fldate asc;
v_date := (:var_year * 10000) + 101;
while :v_found <= 12 do 
select sum(bookid) into sum_bookid
from :TT_MONTH
where month(fldate) = :v_found;
insert into TICKETS_BY_YEAR
values(v_date,sum_bookid);
v_date := :v_date + 100;
v_found := :v_found + 1;
end while;           
END;

4.- Of course...I needed to call my Procedure...

CALL P075400.GetTicketsByMonth('2011','''AA''');

5.- Once finished, I checked my table to see if everything worked as expected...

6.- After realizing that my data was nice and clean, I exported to an .CSV file (Sorry...no pics this time...I already post it in a previous blog)

7.- I went to my R Studio and start coding...

Flight_Tickets=read.csv(file="Flight_Tickets.csv",header=TRUE)
period=Flight_Tickets$PERIOD
tickets=Flight_Tickets$TICKETS
var_year=substr(period[1],1,4)
var_year=as.integer(var_year)
var_year=var_year+1
var_year=as.character(var_year)
new_period=gsub("^\\d{4}",var_year,period)
next_year=data.frame(year=new_period,StringsAsFactors=FALSE)
prt.lm=lm(tickets ~ period)
pred=predict(prt.lm,next_year,interval="none")

plot(tickets,type="b",
     col="red",
     main="Annual Tickets Sale",
     xlab="Months",ylab="Tickets")
lines(pred,type="b",col="blue")
legend("bottomleft",inset=.05,title="Real vs. Predicted",
       c("Real","Predicted"),
       lty=c(1,1),col=c("red","blue"))

8.- I watch my generated graphic showing the real tickets sale vs. the predicted tickets sale. The real is for every month of 2011 and the predicted for every month of 2012.

9.- Nothing to do here...it's done -:)

10.- See you next time with more HANA, R or another nice technology.

Greetings,

Blag.

Blag's best blogs picks from 2011

So 2011 is already gone...it was a good year, so now it's the time for to continue with a tradition I started 4 years ago with the blog Blag's best blogs picks from 2007.

I can see with hapiness, that each year, better blogs are being done, and it's hard for me not to include them on the list, so it became bigger each year.

Again, I'm not an expert in each field, far from that, the only thing I did, just like previous years, is browse the blogs from each month, and try to select the ones that for me are the best, or simply are hidden gems....blogs with no comments, but that really delivers something important. Hope you like this list -:) And if you're not in it...sorry, but make a huge list like this is a very overwhelming job, so I'm surely missed really nice blogs -:(

ABAP

Don't try to be smart. Be smart. by Tobias Trapp

Caffeine in Action by Daniel Vocke

ABAP Trapdoors: The Myth of the Instance Constructor by Volker Wegert

Operations Research & ABAP by Tobias Trapp

 

Open Source

More Barcodes with Barcode Writer in Pure Postscript by Robert Russell

Dealing with R and HANA by Alvaro Tejada Galindo

abap2gapps: is your ABAP ready for the Google cloud? by Ivan Femia

OAuth2: Next generation authentication API by Ivan Femia

 

On Demand and Software as a Services (SaaS)

Why Dick Hirsch is mostly wrong about ByDesign guerilla tactics by Dennis Howlett

Guerrilla Tactics for SAP’'s OnDemand 'Go to Market' Strategy by Richard Hirsch

 

Improving My Experience

Does the SAP SCN community need more achievements and rewards? by Tom Cenens

"Programmers are lazy" - InnoJam them! by Chris Paine

Download basket approvals gone with the wind by Tom Cenens

 

SAP Developer Network

16 SAP Mentor Magic Moments 2010 by Mark Finnern

SCN Blogs Go Mobile by Gali Kling Schneider

SDN Time Capsule : How it all started by Jeff Word by Martin Gillet

Things that drive me crazy on SDN by Martin Maruskin 

Top 25 SCN Blogs of all Time by Mark Finnern

 

Web Dynpro

Kiss of Life for ABAP Dynpro –- It’s going to stay, so let’s improve the integration by Thorsten Franz

 

Standards

Sorry Singleton I don't love you anymore. by Chris Paine

 

Community Projects

Can the community be improved? by Dennis Howlett

A simple way of giving back to the community by John Astill

Damned If You Do And Damned If You Do Not by Marilyn Pratt

 

Governance, Risk and Compliance

Making the case for SAP Mentor alumni program by Dennis Howlett

 

Beyond SAP

[Plagiarism] Why thieves from portals like saptechies.com are safe ? Bloggers please help... by Michal_Krawczyk_PIXI

OINK OINK! Welcome to the SAP Gamification Cup! by Mario Herger

Is ABAP for Non-ABAPers (Functionals)? by Fabio Luiz Esperati Pagoti

Not your Grandfather’s SAP (Recommended by Ivan Femia) by Thorsten Franz

About 'Embracing Inclusion to Drive Innovation' by Matthias Steiner

R.I.P Dennis Ritchie, and thank you ! by Vijay Vijayasankar

Rest In Peace, Game Changer by Vijay Vijayasankar

A word of thanks by Tom Cenens

Join with SAP and the U.N. as One of the 7 Billion! by Mark Yolton

 

Ranting

Bad good, and Great Consultants??? by Michelle Crapo

Etiquette Versus Netiquette by Bala Prabahar

Five signs that the new SCN is dysfunctional by Jim Spath

 

ERP

Embed an HTML landing page into your SAP GUI home screen by John Moy

Why Workday is a Major Threat to SAP by Jarret Pazahanick

 

Business Process Management

SAPMentors + ASUG +VNSG +SAP = #winning! by Susan Keohan

 

Business Process Expert

Embracing Inclusion –- Driving Innovation : An Introduction by Marilyn Pratt

 

SAP TechEd

Why I'm excited about the upcoming TechEd in Las Vegas by Matt Harding

Help an SAP Mentor with his travels? by Jim Spath

SCNotty Goes To Bollywood by Jim Spath

Design Thinking, Women in technology at Tech-ed BLR 2011 a participant’'s view by SINGHKUMUD

 

Python

Tasting the mix of Python and SAP by Alvaro Tejada Galindo

 

In-Memory Business Data Management

POV: HANA's impact on ABAP Programming by Ram Batulla

Quick Thoughts about HANA and InMemory Technology by Richard Hirsch

Finding SAP HANA Documentation by John Appleby

An InnoJam Experience - with HANA flavour by Sarat Atluri

Are we putting the cart before the horse? by Bala Prabahar

Experience HANA - the wish list by Vijay Vijayasankar

How much of the game will HANA change? by Vijay Vijayasankar

Why SAP HANA 1.0 SP03 - Project Orange - will be a runaway success by John Appleby

Using Excel on HANA by Thomas Zurek

ExaData, ExaLogic, and now ExaLytics? ExaSperating… by Aiaz Kazi

 

Mobile

An Android App for searching HELP.SAP.COM by John Moy

Mobile SAP Applications using DHTMLX Touch by Brad Pokroy

SAPMentors Outreach iOS App by Bjoern Weigand 

Proudly Presenting the SAP Mentors Outreach Mobile App for Android – Connect with SAP Mentors at SAPphireNOW/ASUG Orlando by Thorsten Franz

Thoughts on the current debate about the Sybase Unwired Platform and options to energize the mobile development community by Richard Hirsch

SAP Mentor Outreach is now available on JQuery Mobile by Ivan Femia

An experiment of Android with HANA In-Memory Database by Sudhir Verma

BSP mobile logon screen using jQueryMobile by Alessandro Spadoni

 

SAP Streamwork

SAP StreamWork: Picking up Steam? by Tammy Powlas

 

Social Media and Social Networks

Why Google+'s rapid adoption doesn't impress me by Jamie Oswald

8 Ways to Let You Know SCN is Listening (on Twitter) by Sylvia Santelli

Confessions of mixed emotions about the coming new SCN by Gretchen Y Lindquist

What I Learned About Social Media Marketing from a Webinar on Mobile Marketing by Natascha Thomson

How cool is SCN? by Graham Robinson

What it means to me to be an SAP Mentor by Natascha Thomson

12 SAP Troublemakers by Jarret Pazahanick

I'm outta here !! by Dennis Howlett

Unsolicited Advice for Blogging Marketers by Jamie Oswald

 

Emerging Technologies

My first Android Application by Girish Kaimal

 

Ruby

Ruby, Camping and...Gateway? by Alvaro Tejada Galindo

Building a Cross-Platform Mobile App with rhomobile by Mark Teichmann

 

Run SAP

SAP HANA InnoJam Online, SAP's new developer competition by Anne Hardy

SCN runs SAP! by Maya Bahar

 

SAP NetWeaver Gateway

Thoughts on NetWeaver Gateway by Graham Robinson

SAP NetWeaver Gateway: 90-day trial version, train race, webinar, and other news by Helena Losada 

SAP NetWeaver Gateway: A Poor Man's EDMX Generation Tool by James Wood

 

Community Day

SAP Inside Track Milan 2011 - The reporting by Ivan Femia

 

PHP

Consuming SAP NetWeaver Gateway OData web services using PHP by Christopher Reichley

 

Visual Composer

Old School UI Modeling - Meet HANA by Yariv Zur

Portal Development - Why Web Dynpro Java is replaceable by Tobias Hofmann

Happy New Year 2012!

I guess it's the time to talk about the good things that happened to me in 2011, and the things that I would like to happen in 2012...so here it goes -:)

2011
* Joined SAP
* Learned HANA, Gateway and R
* Quit being an ABAP Consultant after almost 11 years

2012
* Write my Python book
* Keep learning R
* Lost weight
* Give my daughter the best 4 years old party ever

And BTW...HAPPY NEW YEAR 2012!!!

Greetings,

Blag.

Your Shape: Fitness Evolved 2012

This is not the first time I blog about a fitness game...last time was Again...trying to loose some weight -:P and first time was This post sucks...I know that... but this time is different for two simple reasons...this time I'm talking about an XBox 360 Kinect Game and this time...it really works -;) Or at least it seems so -:P


I got the game for Christmas so I haven't play it to much, but let me tell you...it really makes you sweat...10 ten times more than Wii Fit Plus...also, using no controls more than your body is a must -;)




Of course I haven't count them (I'm too busy exercising myself), but I assume there's about 300 exercises...covering all needed exercises...meaning that this game...is hell of an exercise -:P

But...nothing is perfect you will say -:) What I really miss (And assume not everything is possible):

* Should warn after 30 minutes of training (Like the Wii Fit Plus)
* Should calculate your body mass or something (Like the Wii Fit Plus)
* Should show some chart of how many calories your burned on each day...you're supposed to log into Your Shape Center but it fails when you try to reach the Dashboard

Anyway...the game is really awesome and you should buy it if your into going to the gym...without actually going to the gym -:P

Greetings,

Blag.

Christmas Sale!

Again...up to 30% of discount in my books (printed version) in...

Blag's books in Lulu.com

Available until the first week of January...hurry up and take the deal -;)

Greetings,

Blag.

Decimal to Binary in "R"

Lately...I've been learning "R"...that weird programming language aimed for Statistics and Statistical programming...and I really like it...so as usual, I needed to create my own Decimal to Binary application -;)

binary<-function(p_number) {
  bsum<-0
  bexp<-1
  while (p_number > 0) {
     digit<-p_number %% 2
     p_number<-floor(p_number / 2)
     bsum<-bsum + digit * bexp
     bexp<-bexp * 10
  }
  return(bsum)
}

p_number<-readline("Decimal number?: ")
p_number<-as.numeric(p_number)
bsum<-binary(p_number)
cat("Binary: ", bsum)

For this example, I used RStudio.


Greetings,

Blag.

SAP HANA InnoJam Online Contest - Wake up call

Did you know that there's a SAP HANA InnoJam Online Contest? I hope not, because most probably...you haven't submit your idea...

Anne Hardy and Michelle Crapo has wrote blogs about it...SAP HANA InnoJam online contest, new start...Switching Gears - Have you heard about the Hana Online InnoJam? but it seems that sometimes two blogs are not enough to send a message...

Karin Schattka kindy create a WIKI with all the information and FAQ for this contest SAP HANA InnoJam Online 2012 so there's not much for me to say, except maybe to give you the Ideas Place link so you can submit your killer idea and win the contest -;)

sap_hana_innojam_online

But you know me...I just can't limit myself to that...didn't you know that if your idea is accepted...the 100 first will be, you're going to get access to the SAP HANA Sandbox? And that will be your space to install and/or additional tools that might help you, like "R" for example, or Microsoft Excel and Business Object Explorer.

Should I talk about the prizes? No...I don't think so...you can read it somewhere else, also I'm sure that for all geeks out there, having a chance to play with SAP HANA is prize enough, right?

As today, we have only 47 submissions on Ideas Place...meaning that 53 places can still be filled...so hurry up! Think about your killer idea! Submit it to Ideas Place! Get approve and develop your solution using SAP HANA! Make it to the finals! Win the contest!

I really hope that after reading this blog, you submit your idea...I will be watching -;)

Greetings,

Blag.