Hello SCN,

 

Firstly, I thank Blag for wonderful blogs on HANA & R like HANA meets R and R meets HANA . *Which introduced me to this amazing language called “R”.

 

In this blog I will discuss about how ODBC helps HANA to connect with different tools like Crystal reports 2011, R etc.We will also discuss about creating a procedure in HANA and calling the same to create a table in SAP HANA database. We will use ‘R’ to read the data from HANA and to plot a graph on that table. Then we will understand different problems faced while trying to plot a graph on top of tables in SAP HANA database and what is the future road map of HANA & R.

 

1) ODBC and HANA:

 

Data Services supports several ODBC data sources natively, including:

  • MySQL
  • Neoview
  • Netezza
  • Teradata

 

Configuring HANA ODBC:

 

The following are the necessary credentials for configuring ODBC driver for HANA:

 

SERVER = <server_name>:3<xx>15
USER = <user_name>
Password = <password>

 

In my case it is:

 

SERVER=hanasvr-04:30015
USER=S0008208595
Password=********

 

Steps for Configuring Data sources for ODBC driver of HANA:

 

Go to Control Panel -> Data sources (ODBC)

A1.png

The following screen will appear.

A2.png

Now Press “Add” to add a new DSN based on ODBC Driver “HDBODBC32” which is a ODBC driver for HANA.

A3.png

The following screen will appear where you will have to enter the DS name along with its description and Server name.

A4.png

If you are still facing issues with “Server: Port” number, you can find the number in the properties tab of your system node in HANA
Studio as in the below screen.

A5.png

With this we created our new data source for ODBC driver of HANA. We can test our connection here by pressing “Connect” in the above
screen. Which will navigate us to the below screen.

A6.png

On pressing “OK”, we will get the message “Connect successful” as in the below screen.

A7.png

Press “OK” to continue. With this we have successfully created a DSN for ODBC driver. We can now use this DSN to connect from R to SAP
HANA Database and read the tables.

 

We can also connect to Crystal reports 2011 with the help of this ODBC connection.

2)  Installing R and R STUDIO (GUI):

 

To use “R” (Similar to S) , we have to first install “R” language and then install the GUI (windows/Unix)
version.To install R, Use the link http://cran.r-project.org/  and for R STUDIO use the link http://rstudio.org/ .

 

Using RODBC package:

 

Now we have to install the Package “RODBC” for using ODBC Driver and connecting to SAP HANA Database. Download the package from RODBC and install it as shown below.

 

A8.png

Now we are all ready to use our ODBC Driver and read the tables in SAP HANA Database from RSTUDIO and display
them in different plots or graphs.

 

Connection statement for SAP HANA Database:

 

Library ("RODBC")
ch<-odbcConnect("SVR3",uid="S0008208595",pwd="*******")

 

Here Ch is used for storing the necessary DSN name along with User id and Password to connect to SAP HANA
Database.


3) Talking with SAP HANA Database using R:

 

In this case I would like to create a procedure on SBOOK table in SFLIGHT, which shows the “Revenue per Agency”.  We will use this procedure to fill the table FLIGHT” and connect to this table from R STUDIO and display the result in a plot.

 

Creating a procedure:

A9.png

CODE:

 

CREATE PROCEDURE STOC

      (IN MANDT NVARCHAR

            (3),IN AGENCYNUM NVARCHAR

            (8),IN FORCURAM INTEGER,IN FORCURKEY NVARCHAR

            (5),IN NAME NVARCHAR

            (25),IN COUNTRY NVARCHAR

            (3),IN CURRENCY NVARCHAR

            (5)

      ) LANGUAGE SQLSCRIPT AS

BEGIN SELECT

       "SBOOK"."MANDT", "SBOOK"."AGENCYNUM", SUM

      ("SBOOK"."FORCURAM") as "FORCURAM", "FORCURKEY", "NAME", "COUNTRY", "CURRENCY"

FROM "SFLIGHT"."SBOOK", "SFLIGHT"."STRAVELAG"

WHERE "SBOOK"."AGENCYNUM" = "STRAVELAG"."AGENCYNUM"

AND "SBOOK"."MANDT" = "STRAVELAG"."MANDT"

GROUP BY "SBOOK"."MANDT","SBOOK"."AGENCYNUM","SBOOK"."FORCURKEY","STRAVELAG"."NAME","STRAVELAG"."COUNTRY","STRAVELAG"."CURRENCY"

into FLIGHT;

      END;

Create a table “FLIGHT” as shown below.

A10.png

Now call the procedure to load “FLIGHT" table:

 

CODE:

 

CALLS0008208595.STOC ('300','000299', '123321','US', 'FLY','US','USD');

 

Now you can see the data in FLIGHT table.

A11.png

Now connecting to SAP HANA Database from R STUDIO *

 

CODE:

 

library("plotrix")
library("RODBC")
ch<-odbcConnect("DS",uid="S0008208595",pwd="*******")
res<-sqlFetch(ch,"S0008208595.FLIGHT")
FORCURAM=res$FORCURAM
NAME=res$NAMe
barplot(res$FORCURAM,names.arg=res$COUNTRY,
main="FLIGHT REVENUE PER COUNTRY")
odbcClose(ch)

 

OUTPUT:

 

 

A13.png

You can see in the above screen in console how it is getting executed.

In the next experiment I tried to the same on “BIG” data but it thrown me the following the error “finite 'xlim' ”. Means this bar plot doesn’t support BIG data plots.

http://hanahangout.com/wp-content/uploads/2012/03/a1i14.jpg

 

My observations in this experiment:

 

  1. I cannot use the views or procedures I have created using the wizard in HANA Modeler.
  2. If data is more the graphical representation becomes clumsy.
  3. I cannot use bar plot on BIG data as there is a possible limit as shown below when I tried to get the output on 900000 records.
  4. I am able to communicate only with tables in SAP HANA Database
  5. R language helps me to represent data mining techniques efficiently with the help of rich library of packages on different statistical formulas.

 

There are many tutorials available on R in the net for free as R is an open source. With SAP planning to tighten the integration between HANA and R, I hope this blog encourages you all to understand R and play with it on top of HANA.

Hello SCN,

We have another tool from SAP for “Business Users” on HANA to explore "BIG" data in an easier way.

Firstly, I thank Tammy Powlas for a wonderful blog on SAP HANA Information Composer – for the Non-Technical User?  and the HELP GUIDE from SAP.

I will share my experiences and views on using "Information Composer" and also explain how to use “joins".

 

We have to Log on to our "IC" using URL: http://localhost:8080/IC/ and give the necessary credentials in the below screen.

 

Then it navigates to the welcome screen, where we have 2 options as shown below.

1) Compose

2) Upload

 

 

1) Compose:

We have 5 steps in “Compose”.

Step 1: Specify source of data.

 

You can see that we have an option of selecting “ALL”. It means all the Analytic views, Attribute Views, Information Views and Data sets created on our server by different users ( depends on the privileges you have) will be displayed. You have an option as shown above to select a specific “Source” you want.

In this example, I took the attribute view which I created on Customer table named “customer” based on resort business data.

 

Step 2: select Source B.

 

Now I need to select another source with which needs “customer” data for analysis to know the frequency of a particular customer visit to resort.

I selected an analytic view, which I created on invoice data named as “SERINV”.

Step 3: Combine.

 

Now I will have to combine this data using union or joins. So when I clicked I got the below message.

Wow! I thought SAP is helping me by to create my information view by creating the necessary union or joins. Then I got the below message.

I tried to understand where it went wrong and tried different scenarios, then I understood that this feature only helps me to identify if “UNION” is possible.

In the below case, I used an information view created by another user named “AMEXANALYSIS” and an analytic view “STOCKS1” which has a similar structure. And now this feature worked in identifying the “UNION” relationship as shown below.

Now let us get back to our scenario.

It means I have to create mappings now. This tool provides me an example illustration on working of Unions and Joins as shown below.

If you want to know what these 3 types of joins means, this tool provides some illustrations which will help us as shown below.

We have some more example illustrations on Union.

So now I went back to create an Right join so that I get all the customer details with respect to my invoice data.

You can view sample data related to the field by selecting a particular field. Then you can preview as shown below.

Now I can see my customer name and details relating to the invoice ids.

In the above screenshot, you can see I have customer ID displayed twice. So I want to hide one of those this is where the next step helps me “REFINE”

Step 4: REFINE.

 

You can see here now I can select which fields have to be displayed from this screen. You can also see “cust_id_1” was “disabled” as I used this field in my join.

So I have unchecked “cust_id”, now to hide it.

Then I got this below screen which I could not understand. It is not allowing me to hide this field. I was unable to figure out the reason.

So I checked all the fields and I got the data.

I have an option to add a “calculated field” if I want to add any as shown in the below screen.

We can decide if the "calculated field" added is a "attribute" or "measure" as highlighted in the above screen.

I didn’t add any additional fields and proceeded to next step.

Step 5: FINISH.

 

Now I can share this view with other users and immediately start to use this new “MYVIEW1” in another “information view”.

Now let us discuss about another feature of this tool “UPLOAD”.

 

2. Upload:

 

We use “upload” option to load our “data sets” into “IC”.


This tool helps me to upload .xls, .xlsx and .csv files to my “IC”.

There are 3 steps in this wizard.

Step 1: Specify source of data.

 

You can “Browse” to your file on your local machine and select the required file for analyzing.

You have an option to upload the file along with “column headers”. There is a limit of max size “5 million records” for upload as shown above.

Now we can clean our data using "CLEANSE DATA" option as shown below.

It cleans data by merging items with similar meaning.

Step 2: Classify.

 

Now we need to classify our data to specify which fields are used for calculations as shown below.

Step 3: Finish.

 

 

Thus, I was able to load my data set.

Hope you understood the benefits of using "SAP HANA Information Composer”.

 

Regarding "External Data upload" using flat files to "SAP HANA information composer read this document by Debjit Singha

Link: http://scn.sap.com/docs/DOC-26727

SAP HANA Information Composer is positioned as a tool for the Non-Technical user.  Using Information Composer, the user should not have to go to IT to get their SAP HANA modeling done.  With the help and encouragement of Juergen Schmerder, I thought I would give it a try.

 

In this scenario, I am a "business analyst" combining two SAP HANA Analytic views: one view has NYSE data and the other view has AMEX data (courtesy of Ronald Konijnenburg).  Then I can use SAP BusinessObjects Analysis Edition for Office to analyze the data (or BusinessObjects Explorer) further.

The first step is to log in using this URL on your SAP Hana system.

http://localhost:8080/IC/

image 

Figure 1, logging on

Logon with your SAP Hana User name and password as shown in Figure  Figure 2, Compose

image 

Figure 2 Compose

 

Compose is on the left side at the top as shown in Figure 2.  Click Start + icon to get started with the Information Composer.

image 

Figure 3, Specify Source of Data

 

Figure 3 shows you select your first source of data.  I am going to select the Analysis Analytic view, which contains NYSE data, courtesy of Ronald Konijnenburg

image 

Figure 4 – a view of the ANALYSIS Analytic View with NYSE data

After showing Figure 4, click Next to select the next source of data.

image 

Figure 5  AMEX Analytic View

 

Figure 5 shows AMEX Analytic view.  Click the Next button to combine.

image 

Figure 6 Combine

 

Figure 6 shows the "combine" or union was successful.  As an end user, I did not need to tell SAP Hana what the joins were, which is nice.

image 

Figure 7 Manage Fields

 

Figure 7 shows how I can select fields to be included in the Information View (like SAP Hana Calculation View) for reporting.

image 

Figure 8 Finish

 

Figure 8 shows the Finish line.  Now I can share this Information View with others and publish it.

image 

Figure 9 BusinessObjects Analysis Office

 

I start SAP BusinessObjects Analysis Office, log on to SAP Hana, and select the Information View I just created as shown in Figure 9.

 

image 

Figure 10 - Analysis Office, view of Combined AMEX/NYSE data

 

Figure 10 shows the combined AMEX/NYSE data, and now as a business analyst I can start analyzing the data.

 

Key Takeaways:

1) Was Information Composer easy to use?  Yes, this only took a few minutes

2) This was easier than creating a union in SAP Hana's using a Calculated View

3) The business analyst still needs to know the data. Notice how I did not even look at the joins of the combined table; that could be a risk if you do not know the data.

 

I could see this used in other scenarios where a business analyst may want to upload data from Excel to enrich the SAP Hana calculated view.  However, if the data is disparate, you will still need a SAP Hana expert modeler to design this a calculated view.

Welcome back to the first instalment of HANA backtrace for 2012.
\   Let's look at what I've seen and heard about SAP HANA lately:
\  
\   Accessing data in a SAP HANA box from ABAP requires the well known database \   library (DBSL) and there are some new notes on this:
\   SQL hints\   have a long tradition with nearly all databases and SAP HANA is no exception.
\   Which of these and how are supported by the DBSL is explained in SAP note:
\   #1622681 - DBSL hints for SAP HANA
\  
\   If you're about to figure out what version of the DBSL is installed on \   your system you should have look into
\   #578324 - Make and release information for MaxDB \   DBSL.
\  
\   An overview about the DBSL versions can be found in SAP note
\   #1600066 - Available DBSL patches for NewDB
\   ++++ ---- ++++ ---- ++++
\  
\   Since also the SAP HANA box should be monitorable via Solution Manager, the saphostctrl \   program needs to be installed on it.
\   SAP note
\   #1625203 - saphostagent/sapdbctrl for NewDB
\   has the specifics for SAP HANA and in SAP note
\    #1031096 - Installation of paket SAPHOSTAGENT
\   you'll find a nice PDF explaining how to install the agent in general.
\  
\   I just installed it on my test machine and it seems to work:

\  \   \     \       \         \       \     \  

\         
/usr/sap/hostctrl/exe/saphostctrl -function GetDatabaseStatus -dbname HAN -dbtype hdb
Database Status: Running
    Component name: hdbdaemon (HDB Daemon), Status: Running (Running)
    Component name: hdbnameserver (HDB Nameserver), Status: Running (Running)
    Component name: hdbpreprocessor (HDB Preprocessor), Status: Running (Running)
    Component name: hdbindexserver (HDB Indexserver), Status: Running (Running)
    Component name: hdbstatisticsserver (HDB Statisticsserver), Status: Running (Running)
    Component name: hdbconnectivity (HDB Connectivity), Status: Running (connect possible)
    Component name: hdbalertmanager (HDB Alertmanager), Status: Running (No alerts on database.)
\
\        

\  \  

In the same area SAP note
\    #1672908 - sapdbctrl getProperties on NewDB failed
\   fixes a bug.
\   ++++ ---- ++++ ---- ++++
\  
\   Of course since the last HANA backtrace several new revisions have been released and \   hundreds of bugs and enhancements have been made available (though not all listed in \   detail):
\  
\    #1673965 - SAP HANA appliance: Revision 24 of SAP HANA database
\    #1680966 - SAP HANA Modeler - revision 24 - upgrade news and \   fixes.
\    #1680966 - SAP HANA Modeler - revision 24 - upgrade news and \   fixes.
\    #1664657 - SAP HANA Modeler Rev. 23: upgrade news and fixes
\    #1661415 - SAP HANA Studio - Rev 23: Attr.reihenfolge in \   Berechnu.sicht
\    #1663228 - SAP-HANA-appliance: Revision 23 of SAP HANA database
\    #1653292 - SAP-HANA-Appliance: Revision 21 of SAP HANA database
\    #1654160 - SAP HANA Modeler - revision 21 - upgrade news and tips.
\  
\   Due to the large number of bugs that have been fixed, make sure to install the current \   revision as soon as possible.
\   ++++ ---- ++++ ---- ++++
\  
\   If you're running BW on HANA SAP notes
\    #1637145 - SAP BW on HANA: Sizing SAP In-Memory Database
\    #1660125 - SAP HANA database: Table consistency check
\    #1664983 - RSHDB: Consistency check (004)
\   will be interesting to you.
\   ++++ ---- ++++ ---- ++++
\  
\   That's so far about the SAP notes I stumbled over - the following links are some SDN \   blog posts I found pretty interesting.
\   Make sure to check them out:
\  
\   In his two posts Krishna explains the design of the new In-Memory \   versions of InfoCube and DSO:http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/28081

\  \  

\  \  

++++ ---- ++++ ---- ++++

\  \  

For the BWA user base thinking about the future of this BW add-on is quite common \   nowadays, and my colleague from SAP Labs provides some insight:
\   SAP NetWeaver BW Accelerator is NOT Dead
\   ++++ ---- ++++ ---- ++++
\  
\   Very interesting and as usual nicely written are the posts from Blag about the interaction of R (a free statistics \   package) and HANA:http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/28445

\  \  

\  \  

As R is about to be fully integrated into HANA in later versions, these information \   might become obsolete, but given the state of development today they help a great \   deal.
\   ++++ ---- ++++ ---- ++++
\  
\   Even more hype-oriented and tech-nerdy is the phantastic project to have Apples Siri\   interact with HANA:
\   Siri meets HANA
\   ++++ ---- ++++ ---- ++++
\  
\   Finally Jeffrey is off to a long journey and he takes us along:
\  
\   Starting the Journey is a the first part of what likely will become a \   little series of blog posts about writing a book about HANA.
\   ++++ ---- ++++ ---- ++++
\  
\   That's all folks - see you next week.
\  
\   Best regards,
\   Lars

Actions

Filter Blog

By author:
By date:
By tag: