Skip to Content

Hello All,

In this blog, I have explained about how to insert records into SAP HANA from a reporting tool like Web Intelligence. This concept can be used in most reporting tools like Dashboards, Lumira & Design Studio to insert data into SAP HANA.

Please find below steps to acheive this.

1. Create table in sap HANA

 

Create column table with name T_B_SALES with below structure in SAP HANA.

/wp-content/uploads/2014/11/pic1_590789.png

2. Create SAP HANA Repository

Goto Window -> Show View from Top Menu Bar

Select SAP HANA Repositories.

/wp-content/uploads/2014/11/pic2_590790.png

Right click on the Repository Area & create new repository.

/wp-content/uploads/2014/11/pic3_590791.png

Give Repository Name and click finish.

/wp-content/uploads/2014/11/pic4_590801.png

You will see the Repository successfully created in SAP HANA.

/wp-content/uploads/2014/11/pic5_590802.png

  3. reate XS Application

Go to Window -> Show View from Top Menu Bar and select Project Explorer.

/wp-content/uploads/2014/11/pic6_590788.png

4. In Project Explorer Create New XS Project

Right click in Project Explorer Area New-> Project ->SAP HANA Development->XS Project.

/wp-content/uploads/2014/11/pic7_590804.png

Give Project Name & Click on Finish. Now you will see a XS Project folder created in Project Explorer.

/wp-content/uploads/2014/11/pic8_590805.png

5. Create .xsapp & .xsaccess files in XS Project

Right Click on XS Project folder and select ‘file’ option.

/wp-content/uploads/2014/11/pic9_590806.png

  Give file name as .xsapp. Keep this file blank. This file is for XS engine to understand there is an XS application in this package.

/wp-content/uploads/2014/11/pic10_590807.png

Similarly create .xsaccess file & write below code in it.

/wp-content/uploads/2014/11/pic11_590808.png

Right Click on our XS Project XS_BOOK_STORE and create XS JavaScript Source File.

/wp-content/uploads/2014/11/pic12_590813.png

Name it as “insert_purchase_record.xsjs” and write below code in it.

  var cid = $.request.parameters.get(‘cid’);

  var bname = $.request.parameters.get(‘bname’);

var no_of_copies = $.request.parameters.get(‘no_of_copies’);   
$.response.headers.set(
‘access-control-allow-origin’,‘*’);

  var conn = $.db.getConnection();

   var pstmt = conn.prepareStatement(“INSERT INTO \”UNAME\”.\”T_B_SALES\”
values (‘”
+cid+“‘,'”+bname+“‘,current_date,”+no_of_copies+“)”);

var rs = pstmt.execute();

   $.response.setBody( “Entry has been created Successfully.”);

  }

  conn.commit(); 

  conn.close();

Note: UNAME is username in SAP HANA

Share Project

Right Click On Project and goto Team-> Share Project

Once your Project is shared small Yellow cylinder will appear at Project Logo.

/wp-content/uploads/2014/11/pic12_1_590814.png

Now Select all the 3 files (.xsapp,.xsaccess and insert_purchase_record.xsjs) and Activate the files.

/wp-content/uploads/2014/11/pic15_590821.png

Then all files will show the small Yellow cylinder at file logo after successful activation of files.

/wp-content/uploads/2014/11/pic14_590820.png

6. Create a WEBI report which will write data back to SAP HANA.

Create WEBI report with No data Source. As we do not want to see any data, and need to insert data into HANA DB.

Create 3 Variables in BO as below

/wp-content/uploads/2014/11/pic15_590821.png

Define 3 input control Text Boxes for each of variables.

/wp-content/uploads/2014/11/pic16_590823.png

/wp-content/uploads/2014/11/pic17_590824.png

Create Report with above variables as below. Try to articulate it to appeal to users.

/wp-content/uploads/2014/11/pic18_590825.png

Do not forget to select cell property of insert cell to Hyperlink, as below.

/wp-content/uploads/2014/11/pic19_590827.png

Now, right click On the “Insert” Text, Goto Linking->Hyperlink and put below code in window & parse it.

 

http://<HANA_SERVER>:<PORT>/XS_BOOK_STORE/insert_purchase_record.xsjs?cid=B0000001&bname=INDIA&no_of_copies=1 

  PORT: Mostly = 8000 (for HANA XS ENGINE)

/wp-content/uploads/2014/11/pic20_590831.png

Click on parse. Below window will now appear,

/wp-content/uploads/2014/11/pic21_590832.png

 

Now assign the BOBJ variables to URL parameters, as below.

/wp-content/uploads/2014/11/pic22_590833.png

7. Execute the BO Report

Now let’s test our XS application..!

 

Click on insert, this will take you to the browser and will prompt you to enter HANA password.

/wp-content/uploads/2014/11/pic23_590834.png

  After entering the password and clicking OK it you show you following window.

/wp-content/uploads/2014/11/pic24_590835.png

Now Check HANA table T_B_SALES. You will see one entry created in table..!!

/wp-content/uploads/2014/11/pic25_590836.png

You can now also try to update and delete the records in HANA DB. You can use the same table, on which we are performing DML operations, as the BOBJ report source and can see the records gets change at WEBI Report level at runtime.

Hope you find this worth of reading..!! Thanks

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

    1. Harshawardhan Ghatge Post author

      Yes Sahil, For that we need to create a Web Service in .PHP (I had created one) , connecting to particular database with insert command. and call that web service URL from webi

      (0) 
      1. KAPIL KHURANA

        Hi Harsh

        Can we insert the records of query at the report refresh.

        Let’s say if i have 2 queries in my webi report and i want to use the data of 1st query in to 2nd query.

        Can we insert the data of 1st query in db and use that in 2nd query.

        If yes can you tell how can we do that.

        Thanks in advance.

        Regards

        Kapil

        (0) 
        1. Harshawardhan Ghatge Post author

          Yes Kapil,

          I had created the same report.

          Steps are below..

          1. create a column table XYZ in HANA

          2. Create universe on XYZ & make it available in WEBI.

          3. Create a WEBI report with  2 tabs. (1. Insert & 2. View table)

          4. In first tab “insert”, you implement above logic to insert data into XYZ table.

          5. In second tab, pull simple table which will display the data in XYZ table.

          Once you insert record from 1st tab of report, refresh your 2nd query report “View Table” to view the recently posted data.

          Hope this is relevant.

          (0) 
  1. deepa m

    hi Harshwardhan,

    Thanks for this informative blog.In the above comments you have told that we can write back  to RDBMS from webi.I have a similiar kind of requirement,where i need to write back to MS sql databse from SAP design studio.can you please share the procedure that you followed to write back to relational database.

    Regards,

    Deepa

    (0) 

Leave a Reply