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.