Tableau on HANA, using the new native connector (UPDATED 14-10-2013)
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:
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:
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:
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:
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:
Note that the package is part of the table name.
I drag my fields in the rows and columns and…
No sigar…
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.
My data shows retail records from a cash register, very basic table set up.
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:
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.
Last but not least, a very basic service:
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):
Results looks like this:
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:
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 😉
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.
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.
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:
It does need a manual step for doing that.
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:
It seems it wants to read the metadata. Slight modification (delete everything after .xsodata) and we’re good to go:
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:
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:
Please leave your comments below!
Take care,
Ronald
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
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!
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.
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