Creating and using HANA native scripted calculation view in SAP HANA Cloud
This article describes how to create a simple Web application running at SAP HANA Cloud and consuming HANA scripted calculation view. With a few steps we could create calculation view via HANA Studio SQL Editor and consume it in a standard servlet. The described scenario provides the opportunity to create on demand web application which consumes HANA database artifacts in secured and isolated manner.
Prerequisites
- You have an account on SAP HANA Cloud trial landscape
- You have downloaded and installed the SAP HANA Cloud Platform tools for Eclipse IDE in accordance with Installing SAP Development Tools for Eclipse.
- You have downloaded the latest version of SAP HANA Cloud SDK (Java Web) in accordance with the Installing Cloud SDK Guide.
Procedure
Step 1: Create a Trial Instance
Open the SAP HANA Cloud Cockpit and enter the HANA Instances section. Choose New HANA Instance. Enter the required name and save changes.
Step 2: Create and Consume a Scripted Calculation View in a Web Application
In a Web application, we’ll create a database table called Managers, and a relevant scripted calculation view. The scripted calculated view contains three major artifacts – user-defined type, stored procedure and column view. Script procedure could be defined via SQL or CE as described in SAP HANA SQLScript Reference.
Using cloud persistence service by plain JDBC, we’ll consume the calculation view in a servlet.
All this is implemented in the source code of the HANA Scripted View Demo application in GitHub. We’ll use it as an example.
To run the HANA Scripted View Demo application:
1. From GitHub, clone the https://github.com/SAP/cloud-hana-scriptview-demo repository and import the relevant project.
2. Using the context menu of the project in the Project Explorer view, choose Run As -> Run on server.
3. Choose the Trial landscape and deploy. For more details, see Deploying on the Cloud from Eclipse IDE.
4. In the Cockpit, go to Databases & Schemas. Check the DB schema ID associated with your scriptview application. It should be something like this: <your trial user>trial.scriptview.web.
5. In your Eclipse IDE, open the SAP HANA Development perspective.
6. In Systems, choose Add Cloud System.
7. Connect to the Trial landscape using your Trial account information. Choose the DB schema associated with the scriptview application.
8. Get the ID of the DB schema
9. Run a search & replace operation over the scriptview project to replace all occurrences of NEO_<YOUR_SCHEMA_ID> with the actual DB schema ID that you got in the previous step.
10. Republish the scriptview application and observe the table data visualized.
NOTE: ScriptViewDemoServlet is using persistence service via JDBC to consume scripted calculation view created via database tunneled HANA studio.
NOTE: Application web.xml is containing the additional configurations needed for the plain JDBC.
The application contains the following SQL scripts:
- cloud-hana-scriptview-demo\src\main\resources\sql\create_procedure.sql– the procedure that will be called when using the scripted view
- cloud-hana-scriptview-demo\src\main\resources\sql\create_tables.sql – creates
the type used by the procedure - cloud-hana-scriptview-demo\src\main\resources\sql\create_view.sql – creates the scripted view
public class ScriptViewDemoServlet extends HttpServlet {
.....
@Override
public void init() throws ServletException {
try {
DBUtil dbUtil = new DBUtil((DataSource) new InitialContext().lookup("java:comp/env/jdbc/DefaultDB"));
dbManager = new DBManager(dbUtil);
dbManager.initDB();
} catch (NamingException e) {
throw new RuntimeException("Failed to lookup default datasource", e);
} catch (IOException ioe) {
throw new RuntimeException("Failed to load resource", ioe);
}
}
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
PrintWriter writer = response.getWriter();
writer.print(getFormattedString(dbManager.getManagers()));
writer.flush();
}
....
}
public class DBManager {
....
private static final String SELECT_ALL_MANAGERS_SCRIPTED_VIEW =
"SELECT * FROM \"NEO<YOUR_SCHEMA_ID>\".\"manager/SCRIPTVIEW\"";
public DBManager(DBUtil dbAccess) {
this.dbAccess = dbAccess;
}
public List<ManagerEntity> getManagers() throws IOException {
Connection connection = dbAccess.getConnection();
List<ManagerEntity> managerList = new ArrayList<ManagerEntity>();
ResultSet result = connection.prepareStatement(SELECT_ALL_MANAGERS_SCRIPTED_VIEW).executeQuery();
while (result.next()) {
ManagerEntity manager = new ManagerEntity();
manager.setId(result.getInt("ID"));
manager.setName(result.getString("NAME"));
manager.setRegion(result.getString("REGION"));
manager.setBudget(result.getDouble("BUDGET"));
manager.setBudgetPercentageShare(result.getDouble("BUDGET_PERCENTAGE_SHARE"));
managerList.add(manager);
}
} catch (SQLException e) {
throw new RuntimeException("Error while reading customer data", e);
} finally {
dbAccess.closeConnection(connection);
}
return managerList;
}
....
}
Conclusion
This article demonstrates how HANA Cloud users could combine on-demand with calculation engine features. It is not intended to cover all scripted view, SQL/CE features and don’t cover HANA Modeler. It is very basic example which provides overview of HANA Studio/HANA Cloud integration and usage of database artifacts from standard cloud web application.
Contributor:
Dobrinka Stefanova
Reference
8 Easy Steps to Develop an XS application on the SAP HANA Cloud Platform
Great stuff Emil - thanks for sharing! Love the fact that we have the code ready to run in our github repo: https://github.com/SAP/cloud-hana-scriptview-demo.git
Keep'em coming!
Thanks for the support Matthias
Hi Nikolay,
i would like to try it on an AWS instance once i get it back up and running. just out curiosity, what is the business case for one table with 20 records in it? how can i convince a spreadsheet user that HANA is more suited than their desktop application to use it in their work?
thanks for sharing,
greg
Hi Greg,
I think there's some confusion here. The example provided does not relate to HANA One (our offering hosted on AWS), but demonstrates how-to leverage SAP HANA native capabilities on the SAP HANA Cloud Platform. If you want to try it out, you can register for your account here: http://scn.sap.com/docs/DOC-28197
Common, that was just an example to get a dataset rather quickly. Feel free to create millions of test data (or real data) 😉
Cheers,
Matthias
Hi Matthias,
Looks good. Just a clarity question. My understanding was that NATIVE applications do not use JDBC. Instead they work directly with the HANA platform using OData or JS. So, is this a new nomenclature?
Will appreciate your inputs.
Regards
Dinesh
Hi Dinesh,
There is no "new nomenclature", because the blog talks about "HANA native scripted calculation view" and its usage from web application in SAP HANA Cloud. May be you are confused with HANA XS applications, which are not target of the blog.
Best regards,
Dimitar
I got NPE ;-( Log https://dl.dropboxusercontent.com/u/24280972/.log
Hi Maksim,
It seems that "SYS" is set for your current schema and this schema access is restricted. You could set back your current schema to your default via the SQL Editor -
SET SCHEMA "NEO_ + <your default schema id>"
Please let me know if this helped, you could always contact me if you have any troubles - nikolay.stoichkov@sap.com or call me directly.
Best regards,
Nikolay
I tried the above tutorial. But got stuck at one place i.e. while testing the remote servlet access. I have also authorized my user i.e. - p1678012516trial with the role that I specified in web.xml of application. And when I am trying to login with this user while accessing the servlet it is not authenticating. The password that I am using is same as that of I use to login in SAP HANA Cloud Service. Repeated trial ended up in following error. Please help in getting out of this problem. I used FORM based authentication also in place of BASIC and the result was same.
HTTP Status 403 - Access to the requested resource has been denied
type Status report
message Access to the requested resource has been denied
description Access to the specified resource has been forbidden.
SAP
Hi Manoj,
"p1678012516trial" is yout account id. Do assign the role "jdbc_remote_access_admin" to your user id "p1678012516".
Best regards,
Dimitar
Hi Nikolay,
Is it possible to connect to HANA XS Engine from HANA Cloud?
If yes, could you please share some information?
Best Regards,
Ashok.
Hi Ashok,
Stoyan Manchev just released blog describing native XS application development at SAP HANA Cloud. Please check :
http://scn.sap.com/community/developer-center/cloud-platform/blog/2013/10/17/8-easy-steps-to-develop-an-xs-application-on-the-sap-hana-cloud-platform
Best regards,
Nikolay
Hi Nikolay,
Thanks for the information!
I would like to consume this calculation view (more precisely the HANA DB in HANA Cloud) in an SAPUI5 application using oData. Is there any possibility for this? Please suggest!
Regards,
Suganthi.
Hi SUGANTHI,
The following blog describes native XS application development at SAP HANA Cloud. It presents calculation view consumed via XSJS , but there should be no problem to use OData service instead. Please check :
http://scn.sap.com/community/developer-center/cloud-platform/blog/2013/10/17/8-easy-steps-to-develop-an-xs-application-on-the-sap-hana-cloud-platform
Best regards,
Nikolay
Hi guys, like mentioned above, this is just a regular scripted view. I want to script a real calculation view located in a package, but I cant find any reference how to do this. any suggestions how to script a calculation view via sql editor? thanks in advance, Christoph.
Hi Christoph,
Please check -
http://scn.sap.com/community/developer-center/cloud-platform/blog/2013/10/17/8-easy-steps-to-develop-an-xs-application-on-the-sap-hana-cloud-platform
Step 4 describes Calculation View Modeling
Best regards,
Nikolay
Hi Nikolay.. the problem is that I have to create a Calc View via HANA Studio (Content...). I want to create it via procedure thou. Is that generally possible or do I hav to do it always manually?? Cheers, Christoph.
Hi Nikolay Stoichkov,
I had a similer usecase of creating an attribute view and consuming that in the JAVA application.
I tried the same steps and it fails in executing the select statement on the HANA view and throws error. But if i execute the same query in the SQL console it works.
Am i missing something here? What else can be possible issue?
Regards,
Prasanna.