Skip to Content

Please scroll to the bottom for the update

About three months ago I wrote a blog on “Tableau on HANA”. Much to my surprise I see an interesting comment showing up by Mr. Francois Ajenstat:
Hi Ronald – the SAP HANA connector for Tableau has been released as part of the 7.0.9 maintenance release. You’ll have a better connection experience, functionality and performance with HANA.


Now I do not know Francois, but after a quick Google search I find out that Francois is the Director of Product Management for Tableau. Now he might be a bit biased, but still, a new native connection is interesting enough to investigate. And so I find myself once more behind my trusty Mac

It always starts with a download (again)

Indeed, a quick download is required again.  You need to have the latest version of Tableau, so be sure to pick it up here.

Connection to a table

Now instead of connecting to my “sales” table, let’s try something different and connect to my “Twitter” table, which I used in my last blog: The not so fuzzy fuzzy search.
Connection is still a breeze. Simply choose “SAP HANA”, fill in your server, username and password and select your schema:
Nieuwe afbeelding.png
Tableau will ask you how to connect to your data. Be sure to select “live data”.  One interesting thing is that Tableau does not come with warning messages as with the ODBC connection. Seems we’re good to go!

Time to build a report


Now, it’s still not a very smart idea to try to analyze 50 million records in one go. Be smart with your selections and do not forget to pause “auto update” or you will still be finding yourself drinking several cups of coffee before the result set comes back.
I create a simple report. A horizontal bar chart with per month showing count of Tweets (based on the contents of the Tweets). I create a quick filter on the content and select “Wildcard Match” in order to be able to do a broad search on the tweets I loaded.
The result looks something like this:
Nieuwe afbeelding (1).png
Note that I selected “apple” in my filter. The result comes back without any delay. The great thing is that I can actually take a look at the underlying data by selecting “view data”.  Over 12000 records are found containing the word “Apple”. You see some of the top results here:
Nieuwe afbeelding (3).png

Time to build another report


Now what happens when I build the same report, but use the ODBC connection to HANA. Well actually not a lot in terms of speed, data still comes back in a second. What is more interesting is the results I get back from the query:
Nieuwe afbeelding (4).png
Completely different result set! The search comes back with all records where the letters in the word “apple” match.  Even though this comes close to a fuzzy search ;-), it’s safe to say that the new connection is much more accurate than the ODBC one for this example.

Time to build another other report

Alright then, one more. This time we build our report on an analysis view and consume that view in HANA. According to Tableau that is supported now.
Please note that views are not in your default schema, but in the “_SYS_BIC” schema. My connection would look something like this:
Naamloos.png
Note that the package is part of the table name.
I drag my fields in the rows and columns and…
Nieuwe afbeelding (5).png
No sigar…
Naamloos.png
Now I believe this a general limitation in the HANA view (it is even an error message coming back from the generated SQL).
Seems the SQL statement is missing a “GROUP BY”. Putting the SQL statement in the HANA studio also generates the error:
 

Update 14-10-2013

As Rajiv Bahl pointed out correctly in the comments. This blog has a loose end. Because of the technical limitations I ran into last year, I decided to test out Tableau on HANA once more.

I created a new table, a view on that table (as Tableau supports both views and tables as a source) and an oData service. It seems also oData is supported as a source.

My table has a timestamp column to see how Tableau will handle this. Previously when using the ODBC connector, this could not be transformed in a time hierarchy making it impossible to use.

/wp-content/uploads/2012/10/1_297498.png
My data shows retail records from a cash register, very basic table set up.
/wp-content/uploads/2012/10/1_297498.png
In order to limit the number of records, I put a “Variable” in my view to see if this will be recognized by Tableau. Again a very basic Analytic view:
/wp-content/uploads/2012/10/1_297498.png

In my previous blog I got some comments on the number of records I was using with the “live” connector. In order not to make it to excessive, I’ve limited the number of records to less than 100.000 (selecting only “DebitCard”). Not close to big data, but more than enough. 100.000 records is also the limit in “Lumira” when using a “live” connector.
/wp-content/uploads/2012/10/1_297498.png

Last but not least, a very basic service:
/wp-content/uploads/2012/10/1_297498.png
I created three simple reports out of this (all look the same, one for the view, one for the table, one for the oData service):
/wp-content/uploads/2012/10/1_297498.png
Results looks like this:
 
/wp-content/uploads/2012/10/1_297498.png

Data is put in a line graph, aggregated values showing time (the timestamp) and a summation of the amounts. Time is correctly recognized and can be shown as seconds, minutes, hours, etc. with a simple click.Tableau does not recognize the variable I created in the view. The only way I can limit the data in my live connection is therefore to have the filter take place as a “datasource” filter after the initial report was created:
/wp-content/uploads/2012/10/1_297498.png

When refreshing the report for a second time, it seems that the filter is applied. However I’m not completely sure if this is executed in the most efficient way.
I timed the E2E report execution from logging on to having the results on the screen. It takes 50 seconds for the table and the view based reports.   
The oData as a source is using the following URL to filter on “DebitCard” only:
 
 
IP is hidden 😉
/wp-content/uploads/2012/10/1_297498.png

Number of records is correctly derived.
Running the report takes about 35 seconds. As the connection details are stored in the connection itself, a user does not have to log on which probably saves 10 seconds. It therefore seems slightly faster than using the table and view connection with the filtered data source parameter.
/wp-content/uploads/2012/10/1_297498.png

So what about Lumira?

Lumira cannot connect directly to a table but only to views. No doubt that this is primarily driven by having the most optimal way of connecting the report to HANA and leveraging the speed (proper filtering and using Analytic views makes use of the aggregation power of HANA). Lumira recognizes the variable of course therefore limiting the data set upfront.
/wp-content/uploads/2012/10/1_297498.png

Running the report takes about 20 seconds.The output is similar and also Lumira recognizes the timestamp and can create a time hierarchy out of it:
/wp-content/uploads/2012/10/1_297498.png
It does need a manual step for doing that.
/wp-content/uploads/2012/10/1_297498.png
So what about oData?

Lumira is also able to handle oData by using Freehand SQL. Hwever not as a live connection and idoes not recognize any filtering upfront:


/wp-content/uploads/2012/10/1_297498.png

It seems it wants to read the metadata. Slight modification (delete everything after .xsodata) and we’re good to go:
/wp-content/uploads/2012/10/1_297498.png
  
You can optimize this SQL to limit the number of records by using a simple “where” clausule. It will take some time before you get a response, but it will work:
 
https://dl.dropboxusercontent.com/u/11677676/10%20-%20Copy.png

My conclusion:
  • Running a report on an Analytic View in Tableau is slower than in HANA. Probably due to the way the data is filtered
  • Running a report on a table takes as much time as on a view. No noticeable performance difference. Lumira has no way of reporting on top of a table directly
  • oData works as asource in both Tableau and HANA, however, Lumira will need to acquire the data upfront which takes time:

https://dl.dropboxusercontent.com/u/11677676/11.png

Please leave your comments below!

Take care,

Ronald

To report this post you need to login first.

4 Comments

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

  1. Rajiv Bahl

    Hi Ron,

    Great blogs and I read the first one with ODBC connector and was hoping you would write one with the native HANA connector.

    So what’s your conclusion is it working now and how’s the performance and limitations on using the HANA views.

    Also you should put a link to this new blog in your earlier blog Tableau on HANA”.

    Thanks a lot.

    Cheers,

    Rajiv

    (0) 
    1. Ronald Konijnenburg Post author

      Hi Raj,

      Seems indeed this blog has a loose end. The error is a known restriction (same when eg using Webi).

      I’ll see if I can find some time to test Tableau once more.

      Tx!

      (0) 
  2. David Owen

    Hi Ron, Rajiv,

        Good blog and great content. Can you tell me if Tableau is capable of accessing SAP HANA LIVE content? What I mean is calculation views, not just views or tables.

    So far I have been unable to make it work even with the native connector.

    (0) 
    1. Rizwan Ali

      Hi David,

      You can use custom SQL to connect to a calc view.

      select * from “_SYS_BIC”.”{project_name}/{view_name}”

      Hope this helps.

      Rizwan Ali

      (0) 

Leave a Reply