Setting up the new HANA service in SAP Cloud Platform Cloud Foundry
Early August 2018, SAP announced the availability of SAP Cloud Platform on AWS in Australia. One of the key services which I was looking to try out was the new HANA service.
So what is actually new with the HANA service?
HANA as a service has been available for a long time in the Neo environment of SAP Cloud Platform. There are plenty of customers who have subscribed to the HANA service to create applications that leverage the core capabilities of HANA or implement data mart scenarios in the cloud.
In the Neo environment, customers could only request for HANA 1.0. There were also limitations around the elastic capabilities – for example, it was not straight forward to change the size of a HANA instance once it has been provisioned to a customer. Early 2018, there were some new capabilities introduced to allow customers to install and manage their HANA database using Self-Service options. You could only select from few of the available standard memory size – 64GB, 128GB, 256GB, 512GB, 1TB.
The new HANA service on Cloud Foundry is based on HANA 2.0 and offers on-demand self-provisioning capabilities. It also offers a flexible pricing model and allows you to scale the HANA instance according to you requirements. You can subscribe to blocks of HANA memory and accordingly spin up several HANA instances in various combination – For example, you can create two HANA instances with memory size 192GB and 96GB.
The complete Feature description of the service can be found in SAP Help.
In this blog, I will share my experience in setting up and connecting to the the new HANA service. There are some differences in how you would connect to the the new HANA service in Cloud Foundry when compared to the neo environment.
Create a HANA service instance
The below screenshot show the list of subaccounts which are attached to a Global account which has been initially created in the Neo environment.
I have used the “New Subaccount” button to create additional subaccounts. In this case, I have created a subaccount called “CAL” which resides in AWS.
Under Entitlement, you should be able to view the resources which your organization has subscribed too. You can edit this page and add resources to each of the subaccount. For my scenario, I would just increase the counter of the HANA service against the CAL subaccount.
Once the HANA service has been assigned to the subaccount, you should be able to view it in the
Under the spaces, within your subaccount, you should be able view “SAP HANA Service” in the Service Marketplace.
Select the HANA service and navigate to the “Instances” menu to create an instance of your HANA service.
Click on the “New Instance” button to trigger the wizard which will take you through the steps to create your first instance. In the first screen, select the plan – Enterprise or Standard (depending on what subscriptions you have).
In the Parameter section, provide the SYSTEM user password. You also get to select the size of your HANA instance here. It is blocks on 16GB with minimum two blocks. You can spin up a HANA instance upto 2TB. In the below screen, you also get to select allow the HANA service to be accessible on the Internet or to specific IP Addresses.
In the next screen, you can assign/bind any existing application to the HANA service. You can skip it if you don’t have any applications. In the last screen, you can provide the name for your HANA instance. I have provided a name “myhana2” as shown below.
When you click on “Finish”, this will trigger the creation of your instance and you will see the status as “Created” in few minutes. Open the dashboard by click on the icon shown below
Login with you SAP Cloud Platform login credentials.
The Dashboard will give details of the database. The ID, Provider, Memory size etc. The Database endpoint highlighted below will be required when you want to connect to this database using other tools. You also get to Start and Stop your instance from the dashboard.
You can also jump to the SAP HANA Cockpit from the dashboard. Note that you would need to provide SYSTEM user and the password provided earlier to access the cockpit.
Once you are in the HANA Cockpit, you can create users/assign additional privileges and perform other administrative tasks.
From the “Overall Database Status” tile, you can get into the details and also obtain the Tenant database name. You will need the database name later on.
In the Cockpit, you can scroll to the bottom and also verify the HANA version.
Click on the “Open SQL Console” at the top right hand corner of the cockpit to launch the SQL console. From here, you can do all the development tasks – creating HANA artefacts and run your SQL commands.
Connect Eclipse to HANA service
There are changes in how you would use Eclipse to connect to HANA service. If you would connect to a HANA service in the Neo environment, you would select “Add Cloud System”. For connecting to Cloud Foundry environment, use “Add System” as shown below.
In the Hostname, provide the database endpoint which you can obtain from the HANA service dashboard. Copy it without the port. For instance number provide 00 and select “Multiple Containers” and provide the Name as <HANA Tenant DB name: Port>. You can obtain the port from the database endpoint from the HANA service dashboard.
The HANA database name can be obtained from the HANA Cockpit by clicking on the first tile “Overall Database status”
In the next screen, provide the HANA DB user credentials and select “Connect using SSL”.
This will connect you to the HANA service on SAP Cloud Platform and you will be able to view the catalog objects within Eclipse.
Connect on-premise Reporting tools
In the Neo environment, when connecting to a HANA database, we would use SAP Cloud Connector to create a service channel. This has been explained by Manjunath Baburao in one of his blog – “Service Channel: Consume HANA databases on SAP Cloud Platform from OnPremise tools”
Each HANA instance has a secure endpoint which can be reached via public internet. You longer need a Cloud Connector or VPN connection. You can use TCP/IP (encrypted), secure Websockets (HTTP based) and any standard HANA client to connect to the HANA service.
In this blog, I will show how to use setup ODBC connection to the HANA service and also look at consuming this within on-premise Predictive Analytic Suite. I had previously covered how this is done in the Neo environment in this blog “Using Predictive Analytics and Python on SAP Cloud Platform HANA database”
The first part is creating ODBC connection. Ensure you have installed the latest HANA Client drivers from the marketplace. Launch the ODBC Data Source Administrator.
In the Server:Port – Provide the database endpoint which is available in the HANA service dashboard. Copy the details from the dashboard along with the port.
Make sure you select the checkboxes :”Connect using SSL” and “Validate the SSL certificate”.
Finally, provide the database user credentials and select “Connect using SSL”.
Test the connection and look for a success message.
[UPDATE 1-Sep-2017]: SAP HANA Academy has posted a video on the same topic and the recommendation is to use wsproxy.
The next part is on consuming this ODBC connection in Predictive Analytics tool.
From the Toolkit menu, click “Open the Data Viewer”.
Select “Database” from the Data Type drop-down box and click the Browse button to select the ODBC DSN created earlier. This will provide you with access to the catalog contents of the HANA service.
Using the same approach, you should be able to connect various on-premise reporting tools to HANA service on SAP Cloud Platform Cloud Foundry environment.
Nice blog Murali Shanmugham - this will be really important as customers come to use the new HANA service in the Cloud Foundry environment. Nice you also added information on how to connect to it via Eclipse and also on-premise tools. As always - thanks for sharing!
Hana 2.0 service was much awaited. Finally it arrives. Thanks for detailed blog
Thanks Murali Shanmugham This is very useful. It would be great if in another blog you can explain HDI-Container service and how it relates to your HANA instance. How to check the containers created by CF apps in there.
Are you coming to TechEd LV?
Cheers mate.
Thanks Pankaj Kumar
I will try and cover them in my next blog. I have started to publish how to create HANA artefacts on the new HANA service - https://blogs.sap.com/2018/09/13/build-database-objectsservices-in-hana-service-using-sap-webide-full-stack-part-1/
Bingo! I was looking for this information. Keep up the good blogging ! In my upcoming works, I will be using HANA service artefacts for business rules.
Super useful Murali. Thanks a ton!
First of all, excellent job.
I also created Cloud foundry HANA DB, but I have different endpoints, Direct SQL Connectivity and Web Socket Connectivity without database in Dashboard.
Do you have any idea?
Thanks. The UI is changing frequently. Web socket endpoint has become visible to support few other connectivity options.
Hello Murali,
Thanks for sharing this information. Can we create the HANA Service Instance in trial account ?
Thanks,
Kishore.
Hi Kishore, This is not possible as of today. I believe there are plans to enable this in the near future.
Thanks Murali for this information. While adding to eclipse using Direct SQL Connectivity URL, I am getting error "Database could not be reached". Can you please provide some idea on this one?
Check whitelist on SAP HANA Service Dashboard
Hi Tomasz,
The whitelist is already set to all public addresses. 0.0.0.0
Hello Amit Choudhary. Were you able to make a successful connection from eclipse? If so, could you please share the steps? I'm facing same error and the process mentioned in this blog doesn't seem to be working anymore.
Thanks.
What error do you have, and who your configuration looks so far?
Hi Tomasz,
I had similar issue with Ashish. I set it to all IP Address. But ping that host from sap network shows time out.
Hi Murali!
Thanks for sharing this blog. It's really helpful.
I am currently evaluating the SAP HANA Service to extract data from SAP to expose into an ODBC connection for a third-party MS Azure-hosted MS application outside the organisation's network. Would you have more info around this topic?
Is this a feasible solution as compared to using SAP BW?
Thanks.
Regards,
Madina
Thanks Madina. You could easily extract the data into HANA service. Wouldn't you use the cloud MS application to directly access APIs exposed from HANA?
Hi Murali,
Thanks for the very quick reply. Appreciate it.
Yes they have capability to do REST API but they prefer SAP placing the data on their server via ODBC connection. Is this feasible?
Also, there are no licensing issues or security issues with them directly accessing SAP HANA? Don’t we have to setup a Web dispatcher or VPN tunnel for that?
Just to provide a bit of more context, this application is a Finance Planning and consolidation tool (Similar to SAP BPC) but is Microsoft-based so the transactional data would be huge/voluminous.
Thanks.
Regards,
Madina
Hi!
I'm trying to reproduce those steps but in trial env it's not possible to run dashboard, it's not enabled. Why? Because it's trial account?
In Neo env is different from CF, is that correct? I can't see the same options into Neo env.
BR,
Mauricio
Hi Murali,
Nice Blog. It helped me to setup the Hana Service.
I have a query here. I have created hana service instance in a subaccount and I am trying to access hana databse from a java application deployed in a different subaccount. I could do this successfully by whitelisting IP in hana service and deploying cf application which uses below code for establishing jdbc connection -
Class.forName("com.sap.db.jdbc.Driver");
String url = "jdbc:sap://<hostname>:<port>/?autocommit=false&encrypt=true";
String user ="XXXX";
String password = "XXXXXXXX";
Connection cn = java.sql.DriverManager.getConnection(url, user, password);
Statement stmt = cn.createStatement();
String sql = "CREATE TABLE REGISTRATION " +
"(id INTEGER not NULL, " +
" first VARCHAR(255), " +
" last VARCHAR(255), " +
" age INTEGER, " +
" PRIMARY KEY ( id ))";
stmt.executeUpdate(sql);
Statement stmt = ((Connection) cn).createStatement();
ResultSet resultSet = stmt.executeQuery("select 'hello world' from dummy");
resultSet.next();
The same thing I would like to achieve by creating a data source in the code. I tried deploying cf application with below jdbc code -
Class.forName("com.sap.db.jdbc.Driver");
com.sap.db.jdbcext.ConnectionPoolDataSourceSAP ds = new ConnectionPoolDataSourceSAP();
ds.setUrl("jdbc:sap://<hostname>:<port>/?autocommit=false&encrypt=true");
ds.setUser("XXXXXX");
ds.setPassword("XXXXXXX");
PooledConnection cn = ds.getPooledConnection();
Statement stmt = ((Connection) cn).createStatement();
ResultSet resultSet = stmt.executeQuery("select 'hello world' from dummy");
resultSet.next();
But this fails with below error -
SAP DBTech JDBC: [4321]: only secure connections are allowed. The server encountered an unexpected condition that prevented it from fulfilling the request.
Do you have any idea how can I resolve this error. Also am I using the right class for creating data source?
Thanks & Regards,
Arpita Jain
Hi,
Connecting Ecliplse to HANA Service on cloud foundry keeps failing with error "Cannot access System on host: xxxx....ondemand.com"
Please can you suggest if there is a workaround.
Thanks
Sudarshan
Hi Murali Shanmugham,
Your blog is looking more useful, also i'm following you, I'm a native ios developer, I have question regarding SAP cloud platform . Can you please help me?
Questions:
I want to create native ios application with SAP cloud platform foundry. so how can i go further? moreover which data base can i work with? also how to connect this database with my application? i mean how to consume its service? and how to make this services.
My email: hrpatel200@gmail.com
My whatsapp: 9624648633
Thank you,
Ill be waiting for your positive reply.
Hi Murali,
Is it possible to connect to HANA DB in NEO environment through JDBC from a cloud foundry app?
The cloud connector is setup for NEO environment?
Regards,
Abhijith
Hi Murali,
many thanks for this very useful blog.
Is it possible to create an xs project? I see that xs engine is embedded in indexserver for sap hana service. The repositories are disabled.
Thanks and BR,
Fabio