Connecting Spotfire to Mongo via JDBC and Info Links

[Christian Blumenroehr]

To avoid ODBC connections from Spotfire to Mongo and all related issues around it (every user would need to install and configure an ODBC driver; there are mainly commercial products and the ODBC driver from TIBCO (which is actually the one from Simba) has issues) we tried to use JDBC connections to centrally set up and configure the connections in Information Designer.

We finally succeeded in setting up data sources for Mongo DB. Needed steps are to copy a Mongo DB driver to the lib directory on the Spotfire server (we usemongodb_unityjdbc_full.jar) and to add a data source template. For the latter we use a very simple one:

 

 

MongoDB

mongodb.jdbc.MongoDriver

jdbc:mongo://<host>:<port27017>/<database>

false

true

false

The tricky part is that the Information Designer creates an xml file on the Spotfire server in the tomcat directory when you create a data source. This xml file is an undocumented feature of Spotfire and itspecifies the relational mapping of the Mongo collection. If you modify the structure of your Mongo collection this won't be reflected in Information Designer (even if you refresh the data source) unless you delete this xml file on the server.

So our recommended steps to deal with Mongo data sources and when you need to reflect changes in the collection are:

 

 

Deletethe xml mapping file on the Spotfire server

 

 

Create a table alias for the data source.Once the table alias is expanded a new xml mapping file is created on the server and the alias will reflect the updated structure of the Mongo collection.

 

 

You can delete the table alias again

 

 

Refreshing the original data source in Information Designer will now also reflect the new structure

 

 

You can now create new Column objects and update the Info Links according to the updated structure

 

 

Is there any way to avoid steps 1-3 Ideally the xml file on the server would be automatically updated whenyou refresh the data source in Information Designer. I created also an Idea for this.

I was told that TIBCO would not officially support connections to Mongo via JDBC yet and they only recommend ODBC connections.However since the xml files are created by Spotfire, I wonder why this is the case as the feature is there.

[John Justus 2]

Thanks for sharing your Mongo DB connection approach.

If you are goingfor an odbc route, why do you think that every user would need to install and configure an ODBC driver Only developer machines require ODBC.

And then the servers would need ODBC drivers. Information Links can be then created from that ODBC datasource

Webplayer Users can then access the reports that was created using Mongo with out having their machines installed wtih ODBC drivers.

Ofcourse, JDBC is the nice approach as long as the steps 1-3 is avoided.

If those steps can't be avoided and if that is an overhead, then ODBC is the next option.

[Christian Blumenroehr]

Thanks for your feedback. In our environment many business users (scientists) are actually using the installed client (Analyst). That's why installing ODBC connectors is so cumbersome.

 

Furthermore commercial Mongo ODBC drivers come with additional costs and the free Mongo ODBC connector from TIBCO seems to have issues.

[Chris Brawn]

That's really useful, thank you for the article. Did you not get any issues with the driver (class) not being present in the files If so how did you manage to combat that

Thanks