How To: Update SAP HANA table using XS – a simple demo
SAP Design Studio 1.1 does not have the ability to directly post/update BW or HANA tables other than calling a URL. Since Design Studio is a dashboard tool, I don’t expect it to have update capabilities (other than for Integrated Planning scenarios – which I expect to be offered soon).
In this blog, I showcase a simple demo to display HANA data and a mechanism to update the data back using HANA XSJS.
I have used EFASHION model and in particular the ARTICLE_LOOKUP table. Using the application, I display a list of Articles and their Sale Price. By selecting the record, the Sale Price can be updated.
Here is the table structure:
Since Design Studio can use only HANA analytic and calculation views, I built a simple analytic view on this ARTICLE_LOOKUP table.
In my DS application I added this analytic view as a datasource and bound a crosstab to it as shown below.
On selecting an Article in the crosstab list, update panel is made visible.
A new Sale Price is entered and when the Update button is clicked, following script is triggered.
The new Sale Price along with the Article ID is passed as GET parameters to a HANA XSJS URL.
Following is the Code in the XSJS hosted on HANA server.
To keep the demo simple, I have not added any data validation. I use the posted URL values as input to a Procedure Call. Alternatively, I could have embedded the Update SQL directly in the XSJS file instead of calling the procedure.
Following is the Procedure code that uses the passed input parameters to update the ARTICLE_LOOKUP table.
As an example, here are the prior and post update values of one such record
Since this update is done externally using a URL call, Design Studio application does not have a way to know the result of the URL call output.
So I have to manually refresh the application to show the updated data. If SAP provides a wait function, we could have reloaded the datasource after a certain interval.
Here is the update data shown on the crosstab
Being a reporting tool, Design Studio, at least at this stage does not appear to be suited for complex data manipulation. SAP HANA XS development will be the more appropriate solution. Again, these are my personal views.
Thanks.
Murali,
Awesome blog - with clear step-by-step instructions. Thanks for sharing!
Regards,
Rama Shankar
Great blog, thanks!
very nice blog, with the planning still far out this is a great way to bridge the time
How did you insert the pencil icon in the crosstab?
added following to my custom css
.sapzencrosstab-DataCellContentDiv
{
background-image: url("http://png.findicons.com/files/icons/2166/oxygen/16/pencil2.png");
background-repeat: no-repeat;
background-position: left;
}
Good one!!
Nice Article 🙂
I think this blog post could be just a quick example to show anoter possiblity of Design Studio, but we would need to be a bit more careful about the the actual implementation.
In general, it would not be recommended to use HTTP GET to update data.
One reason is that we cannot use SSL encryption for the URL parameters.
Anoter reason is that there could be the risk of XSRF.
http://en.wikipedia.org/wiki/Cross-site_request_forgery
HANA XS has the capability to prevent XSRF but it would not be helpful for the data update with HTTP GET.
We would need to implement another API to call HTTP POST as a part of standard product in future if this requirement is common one.
Sorry, SSL encryption itself will be done for the query string as well.
But as there are lots of discussion like below, it would be less secure than HTTP POST.
http://stackoverflow.com/questions/2629222/are-querystring-parameters-secure-in-https-http-ssl
Regards,
Makoto
Makoto,
This was a simple demo to show interaction with SAP HANA.
With DS 1.1, we only have the option to call a URL. We don't have a way to submit form for POST request. If you know of any way to POST a request, let me know. It will be helpful.
Thanks.
Hi Murali,
As you said, it is totally not possible to make a HTTP POST request with the Design Studio as of now.
If you really need to update the data on HANA, it is highly recommended to create another screen with HANA XS and make a request with HTTP POST using XHR or so.
And if the SDK of Design Studio become publicly available, it could be used for such scenario as well.
It would be good to think about multipe possbilities but we would need to be careful about some kind of security risk as well.
Regards,
Makoto
SAP definitely needs to introduce a method for jQuery Ajax requests.
hi Murali,
Thanks for the blog.The information provided in the blog helped me to resolve the issue that i was facing from many days.But I have a small doubt.
Here you have used only the integer values.I have a requirement where the user will key in the values(data type is varchar) in the design studio and those changes will be reflected in the hana studio.
This logic works if the string data is entered in the single quotes ,else it is thowing the error.
But I want the values to be entered without quotes.
please help me to resolve this
Thanks,
Deepa