Using the ODBC-plugin with ServiceNow

General Add comments
by:

One of the remarks heard most often about a SaaS (Software as a Service) solution is the lack of control it gives you over your own data. In truth most of the existing reporting and BI functionality has to be replaced when moving to a new SaaS implementation.

These things changed when ServiceNow made an ODBC-plugin for the ServiceNow environment available. An ODBC plugin allows reporting or BI tools to connect directly to a database allowing for easier searching and selection of data.

During my first ODBC plugin implementation for a medium to large ServiceNow environment I ran into a few unexpected issues and didn’t manage to find a lot of clear information available on the topic of ODBC implementations. I hope that my lessons learned can help you with your implementations.

Getting the ODBC running

The installation guide provided on the ServiceNow wiki (http://wiki.servicenow.com/index.php?title=ODBC_Driver) helps with the installation. Though the lack of an IOS version might bother some (like me) installing & connecting the plugin is a simple matter. For testing & running your initial connection & SQL queries the included “Interactive SQL” program is a good start. Directly calling the ODBC from Excel also works.

Understanding the ODBC

The ODBC plugin for ServiceNow is a query-only interface; this means that no updates, deletes or inserts are possible when using the ODBC.
Using the ODBC requires a user account on the ServiceNow environment with permissions to the soap role and specific roles required for the tables in scope of the ODBC.
When querying through a table columns are identified by their database name. A choice or Reference column has an additional column with the “dv_” prefix added to its name. These dv_ columns contain the display values for these fields.

Performance & Errors

Initially when running queries through the ODBC the performance was not as good as we hoped. The results of even basic queries could sometimes take up-to 15 minutes. In the end we managed to reduce the duration of these queries to 15 seconds by making a few simple changes; replacement of multi table joins with dv_ fields and including a hard-coded date value instead of a variable DATE turned out to be the most important of these changes.
Besides performance issues we also ran into a few minor errors (permission & OS based) and one more complex error related to Field length:
This error occurred when attempting to extract the close_notes column. After investigating the issue it was shown that certain fields contained upwards of 80000 chars even tough the dictionary entry of the field was supposed to be limited on 4000 chars. Removing the specific column or replacing the selection of the close notes with a LEFT(4000,) constraint fixed the error.

Conclusion

All in all, the ODBC will give customers more control over their data as well as allowing the user of their preferred BI solutions. Some issues still remain but it’s a gigantic improvement over the now often seen scheduled export/import solution.

Now all I need is my IOS plugin…..

If you have any questions you can reach me via email on .img[at].img.

Leave a Reply