getting started with sap hana developer center

SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com

© 2011 SAP AG 1

Getting started with the SAP HANA Developer Center

Applies to:

SAP HANA

Summary

Have you ever used the SAP HANA Developer Center? This guide will get your through some basics of the Developer Center and SAP HANA Studio.

Author(s): Alvaro Tejada Galindo

Company: SAP Labs Montreal

Created on: May 08, 2012

Author Bio

Alvaro joined SAP Labs after almost eleven years of doing ABAP development in both Peru and Canada. Beside ABAP, his main area of expertise is Scripting Languages, which he uses to develop integrations with SAP. He’s an SAP Mentor, Top Contributor and SCN Moderator.



© 2012 SAP AG 2

Table of Contents

First steps ...........................................................................................................................................................3

CloudShare: ........................................................................................................................................................5

Setting up SAP HANA Studio: ............................................................................................................................7

Working with SAP HANA Studio:......................................................................................................................12

Loading external data to SAP HANA................................................................................................................17

Selecting Data ..................................................................................................................................................21

Related Content ................................................................................................................................................35

Copyright...........................................................................................................................................................36



© 2012 SAP AG 3

First steps

The first thing we need to do is go to http://sapdevcenter.com

Click on In-Memory applications (http://scn.sap.com/community/developer-center/hana)



© 2012 SAP AG 4

Click on Request access to HANA development environment (http://www.sdn.sap.com/irj/sdn/index?rid=/webcontent/uuid/a01f2bae-82d8-2e10-f3ac-b33d4c919918)

Agree to the End User License Agreement.



© 2012 SAP AG 5

Don’t worry if you don’t have an access code, just verify that your information is correct and submit.

CloudShare:

Let’s go to CloudShare (http://www.cloudshare.com) and select CloudShare Enterprise.

Log in using your username and password. You will see your active environment (If you have more than one).



© 2012 SAP AG 6

Go into your SAP In-Memory Developer Desktop. And start the environment.

Select “View the machine” and we’ll be ready to go.

Once the machine is open, we will be able to open the SAP HANA Studio.



© 2012 SAP AG 7

Setting up SAP HANA Studio:

First thing we need to do when we open the SAP HANA Studio for the first time is to add a new system.



© 2012 SAP AG 8

We need to pass the server we’re assigned to and also the system number.

Of course, we must provide our Username and Password as well.



© 2012 SAP AG 9

We will ask also to create a master password, that will help us to recover our data is something wrong happens.

When everything is ready, we will see our catalog.



© 2012 SAP AG 10

As we can see, we have many schemas and the most important to use is the one with our same username, which in my case is P075400. This is our personal space where we can create, delete or modify any object. The other schemas like DMONEY or SFLIGHT are tables created to be consumed but not modified and of course not deleted.



© 2012 SAP AG 12

Working with SAP HANA Studio:

We’re going to start by creating a couple of simple tables. Right click on Tables and select New Table.

Create a first table called “PERSON” using the following fields:

ID � VARCHAR � 3

NAME � VARCHAR � 20

LAST_NAME � VARCHAR � 30

DOB � VARCHAR � 8

We have the option to save the table as Row Store or Column Store. In our case, as the table is going to be small and will hold only a few records, we will save it as Row Store.



© 2012 SAP AG 13

The second table will be called “PROJECT” using the following fields:

ID � VARCHAR � 3 � Key Field

NAME � VARCHAR � 20 � Key Field

RESOURCES � INTEGER

BUDGET � DECIMAL � 8,2

We’ll keep this table as Column Store.

Now that we have our tables, we must insert some random data. Right click on the PERSON table, select Generate and then Insert Statement.

SAP HANA Studio is going to generate an Insert Template that we must fill with the information we want to use in out table.



© 2012 SAP AG 14

Let’s generate some random data.

insert into "P075400"."PERSON" values('001','John','Smith','19750112');

insert into "P075400"."PERSON" values('002','John','Doe','19800101');

insert into "P075400"."PERSON" values('003','Joe','Page','19760228');

insert into "P075400"."PERSON" values('004','Sally','Ryan','19771230');

insert into "P075400"."PERSON" values('005','John','Sanders','19451122');

insert into "P075400"."PERSON" values('006','Peter','McDonald','19670818');

insert into "P075400"."PERSON" values('007','Mike','Melvin','19750812');

insert into "P075400"."PERSON" values('008','Chris','Jones','19750813');

insert into "P075400"."PERSON" values('009','Daniel','Stone','19720726');

insert into "P075400"."PERSON" values('010','Judy','Jones','19701210');

insert into "P075400"."PERSON" values('011','Mike','Peters','19810404');

insert into "P075400"."PERSON" values('012','Joshua','Reynolds','19750217');

insert into "P075400"."PERSON" values('013','Anne','Word','19710310');

insert into "P075400"."PERSON" values('014','Jeff','Field','19681215');

insert into "P075400"."PERSON" values('015','Megan','Ford','19700909');

After execution we can see that the data was successfully loaded into the table.

Right click on the table and select Open Content.



© 2012 SAP AG 16

Now that we finished with the first table, we can move to the second one, called Projects.

insert into "P075400"."PROJECT" values('001','ABAP Models',2,10456.34);

insert into "P075400"."PROJECT" values('001','ABAP on Eclipse',5,20000.00);

insert into "P075400"."PROJECT" values('001','ABAP on HANA',2,45500.00);

insert into "P075400"."PROJECT" values('002','SUP Apps',3,3567.12);

insert into "P075400"."PROJECT" values('003','Code Refactoring',1,5120.50);

insert into "P075400"."PROJECT" values('004','SAPScript convertion',5,567.90);

insert into "P075400"."PROJECT" values('004','Adobe Forms Docs',2,345.19);

insert into "P075400"."PROJECT" values('004','Smart Forms Docs',6,462.10);

insert into "P075400"."PROJECT" values('004','Smart/Adobe Forms',2,234.90);

insert into "P075400"."PROJECT" values('005','HANA Apps',2,50000.00);

insert into "P075400"."PROJECT" values('005','BW on HANA',4,50000.00);

insert into "P075400"."PROJECT" values('006','Flex Apps',7,3500.50);

insert into "P075400"."PROJECT" values('006','Dashboards',3,2345.29);

insert into "P075400"."PROJECT" values('006','XCelsius',4,5678.10);

insert into "P075400"."PROJECT" values('007','ABAP Classes',2,1250.10);

insert into "P075400"."PROJECT" values('008','OSS Notes',2,2500.00);

insert into "P075400"."PROJECT" values('009','SUP Killer Apps',2,5600.50);

insert into "P075400"."PROJECT" values('009','SAP Gateway',7,5800.00);

insert into "P075400"."PROJECT" values('009','SUP and Gateway',5,5678.00);

insert into "P075400"."PROJECT" values('010','Blue Ruby',6,3500.00);

insert into "P075400"."PROJECT" values('010','Blue Python',4,3500.00);

insert into "P075400"."PROJECT" values('011','ABAP Editor',5,4000.00);

insert into "P075400"."PROJECT" values('011','ABAP Debugger',5,2000.00);

insert into "P075400"."PROJECT" values('012','EP',5,8500.50);

insert into "P075400"."PROJECT" values('012','KM',5,6500.50);

insert into "P075400"."PROJECT" values('012','MI',3,4500.50);

insert into "P075400"."PROJECT" values('013','SAP Education',6,5000.00);

insert into "P075400"."PROJECT" values('013','Certifications',3,3000.00);

insert into "P075400"."PROJECT" values('014','SAP FI',4,500.50);

insert into "P075400"."PROJECT" values('014','SAP SD',6,400.50);

insert into "P075400"."PROJECT" values('014','SAP CO',3,500.40);

insert into "P075400"."PROJECT" values('014','SAP MM',7,400.50);

insert into "P075400"."PROJECT" values('014','SAP WM',2,300.99);

insert into "P075400"."PROJECT" values('015','Research',1,5000.00);

insert into "P075400"."PROJECT" values('015','Development',4,8000.00);

After execution we can see that the data was successfully loaded into the table.



© 2012 SAP AG 17

Loading external data to SAP HANA

We had already created two tables and generate some data for them, but what if we have a new table and its information resides on a CSV file? We need to upload it to our SAP HANA instance following these steps.



© 2012 SAP AG 18

Our CSV file is composed of two fields called ID and Area. They just provide the area to where the employees belong from within the organization. Keep in mind that no header should be provided. Also, don’t forget to set the field as text and set all the needed zeros.

In SAP HANA Studio, we must create an empty table called Area with the following structure:

ID � VARCHAR � 3 � Key field

AREA � VARCHAR � 20



© 2012 SAP AG 19

We need to open a connection to the ftp.sapdevcenter.com server, using for example: Fillezilla (http://filezilla-project.org/ ).

To get the information from the user name and password we must perform a select:

SELECT * FROM SYSTEM.FTP_SERVER

When we are connected, we must create a table (In my case, I create the “Blag” folder) and upload your CSV file to that folder.

Create a file names Area.ctl with the following information and upload it as well:

import data

into table P075400."AREA"

from 'Area.csv'

record delimited by '\n'

fields delimited by ','

optionally enclosed by '"'

error log 'Area.err'



© 2012 SAP AG 20

Open a new script file and write the following query and execute it:

IMPORT FROM ‘/dropbox/Blag/Area.ctl’;

After the execution, we can open the contents of the table and see that the data was successfully uploaded from the CSV file.



© 2012 SAP AG 21

Selecting Data

Now that we have our tables filled with data, it’s time to select some data.

Right click on the table node and select SQL Editor.

We’re going to do a simple INNER JOIN to select the information from both table, using the field ID as the connection.

When we run the script, we’re going to have all the information that we requested.



© 2012 SAP AG 22

Of course, we can narrow our selection, by choosing only the fields that interest us.



© 2012 SAP AG 23

That looks better, but we also might need the age of the person to do some analysis later. Let’s build another query to fetch the age, and also let’s get the Area from our new Area table.



© 2012 SAP AG 24

We got a nice result, with a join of the 3 tables, but wait, we have the Date of Birth but not the actual age. Let’s do another script to get that information.



© 2012 SAP AG 25

Now we’re getting close…but I don’t think that John Smith is actually 13,631 years old. The function days_between() is obviously giving us the age in days, and as there’s no years_between() function, we might need to build it ourselves.

First, let’s build another table. Yes, another one, but this time is going to be populated by our Stored Procedure.

ID � VARCHAR � 3 � Key field

NAME � VARCHAR � 50

AGE � VARCHAR � 3

With the table ready, we can proceed. We need to open a new SQL Editor and use the following code:



© 2012 SAP AG 26

Let’s try to explain the code. We declare two variables called v_age and v_years. As we need to extract each record to get the age in years, we create a cursor to a select with will fetch the ID, the name and the last name will get concatenated into the field “NAME” using the || concatenation command. To get the age in days, we use the function days_between and store the result in the field “AGE”.

We’re going to do a FOR – END FOR on the cursor, to read each record one by one. If the age in days is greater than 100, we’re going to divide it by 360 and by using the floor() function we’re going to round it. Finally, we’re going to insert the data into our EMPLOYEE table.

After execution we can see that our Stored Procedure has been created.

To run it, we just need to create a new SQL Editor and call it like this.



© 2012 SAP AG 27

After we run it, we can verify our Employee table.

Excellent! The name and last name are both in one field and we have the right age for every employee. Now, we can do our selection of data. And even better, we can create a view to store the information and access it in a faster a more convenient way.

We need to do a right click on the View folder and select New View.



© 2012 SAP AG 28

We’re going to call out view Employee_View, and we’re going to use all table to construct it.

As we’re going to build a join, we must press the Plus sign and select Insert table to insert all the needed tables.



© 2012 SAP AG 31

And from here, we must select the fields we need to use by right clicking on them and selecting Select.

After executing it, our new view will be ready to rock. Let’s open its content to see that the information is there.



© 2012 SAP AG 32

If we want to do a little of analysis inside our HANA Studio, we must right click on our view and select Open Data Preview.



© 2012 SAP AG 33

We need to press the Analysis button.

Here, we can for example use the Area and the Budget to get a graphic.

By changing the kind of graphic and adding the name we can have a more explicative graphic.



© 2012 SAP AG 34

We could even have this information displayed as table and then download it as a file.

If this isn’t enough to get you interested in SAP HANA, then there’s something wrong with you…this technology just rocks!



© 2012 SAP AG 36

Copyright

© Copyright 2011 SAP AG. All rights reserved.

No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may be changed without prior notice.

Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors.

Microsoft, Windows, Excel, Outlook, and PowerPoint are registered trademarks of Microsoft Corporation.

IBM, DB2, DB2 Universal Database, System i, System i5, System p, System p5, System x, System z, System z10, System z9, z10, z9, iSeries, pSeries, xSeries, zSeries, eServer, z/VM, z/OS, i5/OS, S/390, OS/390, OS/400, AS/400, S/390 Parallel Enterprise Server, PowerVM, Power Architecture, POWER6+, POWER6, POWER5+, POWER5, POWER, OpenPower, PowerPC, BatchPipes, BladeCenter, System Storage, GPFS, HACMP, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, Parallel Sysplex, MVS/ESA, AIX, Intelligent Miner, WebSphere, Netfinity, Tivoli and Informix are trademarks or registered trademarks of IBM Corporation.

Linux is the registered trademark of Linus Torvalds in the U.S. and other countries.

Adobe, the Adobe logo, Acrobat, PostScript, and Reader are either trademarks or registered trademarks of Adobe Systems Incorporated in the United States and/or other countries.

Oracle is a registered trademark of Oracle Corporation.

UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group.

Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and MultiWin are trademarks or registered trademarks of Citrix Systems, Inc.

HTML, XML, XHTML and W3C are trademarks or registered trademarks of W3C®, World Wide Web Consortium, Massachusetts Institute of Technology.

Java is a registered trademark of Oracle Corporation.

JavaScript is a registered trademark of Oracle Corporation, used under license for technology invented and implemented by Netscape.

SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP Business ByDesign, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and other countries.

Business Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other Business Objects products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of Business Objects S.A. in the United States and in other countries. Business Objects is an SAP company.

All other product and service names mentioned are the trademarks of their respective companies. Data contained in this document serves informational purposes only. National product specifications may vary.

These materials are subject to change without notice. These materials are provided by SAP AG and its affiliated companies ("SAP Group") for informational purposes only, without representation or warranty of any kind, and SAP Group shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP Group products and services are those that are set forth in the express warranty statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty.

getting started with sap hana developer center

Documents