FOR ANALYSTS FOR BI PROS FOR EVERYONE LEARN Tableau
Roll your own Tableau Live Data Connector: ODBC for BusinessObjects
October 9, 2015
1
, , , , , , , , , ,

virtdbSelf-service does not start at report development. With tools like Alteryx or Talend you can get and transform your data easily and produce consumable output formats for your reporting or visualization tool (yes, for Tableau). This sounds cool, but it’s still an iterative process: extract and load, then analyze. It will never be real time. 

Tableau’s answer is the ODBC API: you can write ODBC driver for your non-supported data source, web service or application if you’d like to leverage direct connection without extracts. Bad news: writing ODBC drivers is not easy. Bad documentation, adding support for missing SQL arithmetic functionality. It’s an overkill in most of the cases.

Then what is the solution? We at Starschema built our own framework to add new live connectors to Tableau. We call it VirtDB. VirtDB is a self-service data integration solution designed for cases just like this. From now on you can easily develop data connectors in Javasript with minimal skills. Similar to Tableau Web Data Connectors, yet offering more: unlike Tableau WDCs these connectors can process not only extracts but live data just as well.

It’s a small step for us but a giant leap for all those Tableau customers who wish to see reports on real-time data, from custom sources.

Getting live data – the walk through

I just love using working examples. As the first exercise lets rewrite my SAP BusinessObjects Tableau Web Data Connector from extract-reliant WDC to fancy live Virtdb connector.

Using VirtDB environment

The easiest way to start with VirtDB is using the docker image that contains a fully-functional environment. The architecture of VirtDB is basically a data pipe which has two endpoints. One of them is a data source and the other one is a database engine. We can connect to the database to get data from the data source through standard ODBC protocol. In VirtDB these data sources are called data providers. The above mentioned docker image contains everything we need, our only task is creating the data provider.

Accessing BO

To access SAP Business Objects the easiest way is using the bobj-access javascript library. This little module can make our life very simple. We wrote it so you can expect superb quality.

Resources to create a new provider

To create a new VirtDB data provider we need to use the virtdb-provider library written in CoffeeScript which abstracts the mechanics of VirtDB to a simple interface. This is also an npm package. Moreover a very detailed tutorial on this subject also exists here.

If you are unfamiliar with coffee just transform the coffescripts to javascripts using the coffee compiler.

Preparing the package

As a first step we should create an empty npm package with the following command:

After we’ve created the initial settings for our package we should install the libraries we intend to use:

The nomnom and source-map-support packages are for the easy command-line argument handling and for the easy CoffeeScript code handling respectively.

Creating the provider

After we have prepared our package the only thing left is writing the actual provider. Using thevirtdb-provider module it’s pretty simple. We only should care about implementing few methods and all the other protocol and connection magic will just happen in the background. From this point we will take a very detailed look on the source of virtdb-bo-provider. For the sake of simplicity we won’t care about the explanation of parsing configuration files we will assume that we have them in variables.

Connecting to the VirtDB environment

The first step we should take is creating an instance from the virtdb-provider. To do that we need two things. First is the url of our VirtDB environment. This information is available on our GUI.

config-service-url

Second one is the name of our provider. This name should be unique through the environment but it is arbitrary.

Register our configuration template

Every component of the VirtDB environment can have a configuration. This configuration is handled by the user through the GUI. To use this feature we should register a configuration template in the system. This template will be displayed by the GUI and filled by the user. When the user fills this the provider he gets it back with the actual values. In our case the config template is stored in a seperate json file.

Register our credential template

In VirtDB credentials are very similar to configs. Those are also some kind of templates. The only difference is scope. While configs are always the same for every user, credentials are dependent on the user who sends the request to our provider. That’s why we get the actual crendential settings always with the requests and not in a separate way as we saw it in the case of configs.

Receiving metadata requests

In VirtDB a data provider needs to handle two kind of requests. First we will discuss the metadata requests. At the end of the day we want to see data queried from a database engine. But to achive this we will need some tables, columns and some other boring stuff in that database. To handle this problem we need to know the type and structure of our data. So metadata will make sense to our data. We should register a callback to virtdb that receives three arguments. First one is the request object. It contains all information about the request. The structure of this is in a well-defined format that is available in the meta_data.proto file in the virtdb-proto project. The second parameter is a callback which will need to be called with the response. The third and last one is the credential object that we have already discussed above. When we receive a request the only thing we have to do is using the bobj-access module that can connect to SAP BO and get our table. For the sake of simplicity we won’t process the request, the credential will be the only parameter that we care about. The virtdb object has some helper methods to help creating reply in a format corresponding to the protocol. Here we need the createTable method which returns a VirtdbTable. After calling that the we just need to add the name and type of the fields to the table using theaddField function.

Receiving data requests

Now we can handle metadata requests. Dealing with data queries isn’t more difficult. We should register an other callback that will handle the data queries. This callback will have two arguments. Similar to the metadata request the first parameter contains the request object defined in the data.proto, the second one is the same credential object. The virtdb object also has a handler for data requests. This getDataHandler method creates a VirtDBData object. Getting data from SAP BO is also easy using the bobj-access module. It gives us a bunch of rows and we just need to send back the requested amount.

How it works

Rule number one: no posts without anim gifs. This is how you can register and use JS provider from tableau.

how-it-works

Do you want to build your own connector and looking for some help? Just drop us a line and we’ll cover you!

Tamás Földi

Tamás Földi

Director of IT Development at Starschema
Decades of experience with data processing and state of the art programming. From nuclear bomb explosion simulation to distributed file systems. ethical hacking, real time stream processing practically I always had a great fun with those geeky ones and zeros.
Tamás Földi
  • Daniel Cedeño

    Hi Tamás! I want to create a custom connector to integrate Tableau and Surveygizmo… Can you help me?

Related items

/ You may check this items as well

Tableau Consistency Checker

Tableau Filestore Consistency Checker – How Repository Maps to Filestore

Self-service does not start at report development....

Read more
TCOT

Pushing the Limits of Tableau Server – Practical Lessons from a Zen Master

Self-service does not start at report development....

Read more
Sales dashboard embedded into SAP GUI

Embed Tableau Dashboards in SAP ERP and S4/HANA

Self-service does not start at report development....

Read more