Skip to Content

Hello Fellow SCNers,

This was the thought which came into my mind a week back…..”why can’t we develop something which reads HANA data and showcase in VB.Net application”…that’s where I ended up creating a simple application,just out of my interest. I named it as “SAP HANA Viewer”…yes I didn’t find a better name for this piece of code.

We say that HANA uses different drivers to expose its data to any client application, say jdbc, odbc, odbo, etc. I thought why can’t we use any of these drivers to consume HANA data. Thought about jdbc…Oops… I am not a good Java developer…Ok…let’s use something which I know. Found that odbc can also be used to read the data with some tweaks.

 

Now which language I can use to develop this application… The first thing which came to my mind is Visual Studio .Net in which I had little hands-on experience

So driver is identified and also the framework. Let’s start coding….This is how I started…and ended up with the following App.Though it may sound silly for core delveopers,just a contribution to our SCN…;-)

SAP HANA Viewer

A standalone application which helps the HANA user to

  • Connect to HANA DB (via preconfigured DSN) and know the DB Name, Version and the Host Name
  • Views Info: Total no.of views created in the HANA System and count on each type of Views(Attribute, Analytic and Calculation Views)
  • Users Info : Total no.of users created and their names
  • Roles Info : Total no.of roles created and their names
  • Server Status: Active Status of Index, Name, Statistics, Preprocessor, XSEngine and Daemon Servers
  • List of Active and Inactive views within a selected package
  • View the View’s Data: Open any view under the package and can come to know the type of selected View and also the data belongs to the view
    can be viewed in Excel file by a single click

Architecture

Arch.jpg

Initial Look:

/wp-content/uploads/2012/12/1_163408.jpg

Login Screen:

/wp-content/uploads/2012/12/2_163433.jpg

Login Screen with credentials entered:

/wp-content/uploads/2012/12/3_163434.jpg

Info about the Views and Server Status:

/wp-content/uploads/2012/12/4_163435.jpg

Info about the Users and Roles:

/wp-content/uploads/2012/12/5_163436.jpg

Selection of Packages from the list:

/wp-content/uploads/2012/12/6_163437.jpg

List of Active/Inactive Objects under different category:

/wp-content/uploads/2012/12/7_163438.jpg

Selection of Views (AT, AN or CV) from the list:

/wp-content/uploads/2012/12/8_163439.jpg

Type of View can be seen and Browse for the Excel location to open the View:

/wp-content/uploads/2012/12/9_163440.jpg

Opened View in Excel:

/wp-content/uploads/2012/12/10_163441.jpg

Few Code Snippets:

Connecting to HANA Server:

/wp-content/uploads/2012/12/11_163442.jpg

Reading the data from Recordset:

/wp-content/uploads/2012/12/12_163443.jpg

Opening Data in Excel:

/wp-content/uploads/2012/12/13_163444.jpg

Thanks for reading.

Comments are always welcome

Rgds,Murali

To report this post you need to login first.

9 Comments

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

  1. Bill Ramos

    Hi Murali,

       Thanks for sharing the application. Is there any chance you could repost the code blocks as text? I might add, that it would be better if you used the data query mechanism in Excel to loaf the data. That way you can use the data ribbon to manage connections. I’ve been trying to get Excel to talk to HANA with the AWS instance, but it looks like the ODBC driver with Excel (v38) is broken 🙁 . A bunch of others have reported the problem too – anything you can do on the SAP side to get an update driver available to use developers using HANA on AWS would be greatly appreciated.

    Regards,

    Bill

    (0) 
    1. Henrique Pinto

      I’ve managed to connect Excel to HANA on lower releases than 38.

      Make sure to use the client version compatible to your office version: several times, even if in a 64-bit machine, MS Office can be based on 32-bit binaries. In such cases, you need to use the 32-bit hdbclient, not 64-bit.

      Also, you need to set the proper DSN connection for 32-bit. It’s in a different location than just going to Adminsitrative Tools -> Data Sources (ODBC) – this one is for the 64-bit HDBODBC driver. The 32-bit HDBODBC, at least for me, is in c:\Windows\System64\odbcad32.exe. And yes, that is correct. At least for me, it is mixed up – HDBODBC32 driver is under 64 bit (and that’s the one my 32-bit Office tries to connect to). I never really understood why…

      (0) 
      1. Bill Ramos

        Hi Henrique,

            Thanks for the tips, but I’ve literally tried all combinations of using the ODBC drivers to connect to the Microsoft stack of BI and Office tools with no success – Test Connection works – actual attempts to enumerate tables fails. In some cases, Excel 2013 with PowerPivot actually crashes. The most current thread on the topic is at http://scn.sap.com/thread/3275724 and Patrick has reported there the latest Excel ODBO driver  HDBCLIENTXLS45_45-10011035.EXE on SAP Marketplace is working – but no luck with the ODBC driver. The other problem is, AWS customers like me have no access to the latest software on Marketplace 🙁 BTW, I know that this is supposed to work because back in July, when I tried it out on CloudShare, it worked great. I told my boss this and we decided to proceed with our project. I’m now in an ackward position informing him that the ODBC dirver isn’t working with HANA on AWS.

        Any help would be greatly appriciated…

        Regards,

        Bill

        (0) 
        1. Henrique Pinto

          I have definitely used MDX method successfully before, with standard HDBClient ODBC driver.

          I’ll run a test as soon as I get home.

          It might some blocking on the AWS/VPN.

          (0) 
          1. Henrique Pinto

            I was able to make it work though SAP VPN.

            Since it’s an MDX connection, I can only browse OLAP cubes (analytic & calc views):

            /wp-content/uploads/2012/12/excel_163867.png

            Again, this is using the standard ODBO driver in HDBClient package.

            For ODBC, it gives me the same error message that Patrick reported.

            (0) 
              1. Henrique Pinto

                BTW, you might want to look into using SAP BO Analysis for MS Office, in this case.

                It will contain most of the functionality you’d get with ODBC in Excel.

                (0) 

Leave a Reply