Self-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:
|
1 |
npm init |
After we’ve created the initial settings for our package we should install the libraries we intend to use:
|
1 |
npm install virtdb-provider bobj-access nomnom source-map-support --save |
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.
Second one is the name of our provider. This name should be unique through the environment but it is arbitrary.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
VirtDBProvider = require 'virtdb-provider' close = (virtdb, code = 1) -> virtdb?.close() process.exit code virtdb = new VirtDBProvider args['address'], args['name'], (err, authCode) -> if err? console.log "Failed to initialize VirtDB!", err close virtdb config = null console.log "Connected to VirtDB", authCode |
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.
|
1 2 3 4 5 6 7 8 |
fs = require 'fs' settingsTemplate = JSON.parse (fs.readFileSync "#{__dirname}/../settings.json") settingsTemplate.AppName = args['name'] config = null virtdb.registerConfig settingsTemplate, (configMessage) -> config = configMessage |
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.
|
1 2 3 4 5 6 7 8 |
fs = require 'fs' credentialTemplate = JSON.parse (fs.readFileSync "#{__dirname}/../credential.json") virtdb.registerCredentialTemplate credentialTemplate, (err) -> if err? log.error "Could not register credential template. Exiting...", V_(err) close virtdb |
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.
|
1 2 3 4 5 6 7 8 |
bo = require 'bobj-access' virtdb.onMetaDataRequest (request, sendFunction, credentials) -> bo.getTables config.WSDL.url, credentials, (err, tables) -> table = virtdb.createTable tables[0].Name for field in tables[0].Fields table.addField field.name, field.type sendFunction null, Tables: [ table ] |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
bo = require 'bobj-access' virtdb.onQuery (request, credentials) -> reply = virtdb.getDataHandler request bo.getTableData config.WSDL.url, credentials, (err, data) -> if err? log.error "Username and password are required.", V_(request) unless err? count = 0 for row in data reply.pushObject row count++ if request.Limit? and count >= request.Limit break reply.send() |
How it works
Rule number one: no posts without anim gifs. This is how you can register and use JS provider from tableau.
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
Latest posts by Tamás Földi (see all)
- Tableau External Services API: Adding Haskell Expressions as Calculations - November 20, 2019
- Scaling out Tableau Extracts – Building a distributed, multi-node MPP Hyper Cluster - August 11, 2019
- Python Experiments in Tableau 1. – Add live currency conversion to Tableau Dashboards using TabPy - January 9, 2018
