Exporting data to on-prem database for reporting

Siva Kaza · ‎06-01-2020

We want to export data from certain selective tables like Incident, Change to an on-prem database to enable our users to be able to perform reporting by cross-referencing this data against other data available on-prem.

I have searching through communities for recommendations and best practices on doing this.

This is what I found so far:

There is no direct way of performing direct ServiceNow table export to a database.

Most common way suggested are:

Using REST/SOAP Web Services to query the data and write it to a database. Usual web service limitations apply here.
Using export set to export data to a flat file on mid-server and then reading the file and writing it to the database. Again limitations based on file-type apply here

Are there any other options available?

Is one of the above options better than the other?

Has anyone done something similar?

Are there any specific recommendations or best practices to follow with either of the approaches or any other approach?

Michael Jones - · ‎06-02-2020

It would depend on how often you want to "push" data to your local source. With ODBC you can essentially perform queries on demand, so this approach is best when you expect or require something lime near-real-time synchronization of data. I've seen situations where you need to generate reports hourly, and this type of connection is well suited to that requirement.

I've never noticed any impact to using ODBC beyond what you might experience pulling up a list view and filtering records, other than perhaps the initial data load. If you try to take a year's worth of records from multiple tables all at once to populate your local source, I would expect some performance lag - and would suggest scheduling that outside of business hours. After the initial load you should only need delta's and those tend to run smoothly.

You also have the advantage of scaleability - if you want to add tables to your local source, you only need to add permissions to your ODBC user for those tables and update your queries. Easy.

Update sets are find if you only need to keep data up to date with a 24 hour lag, for example. The whole process to generate the exports, put them somewhere, and the process them is a bit cumbersome and requires more effort whenever you need to add something new. You might also run into security issues such as being required to use SFTP for file drops, which ServiceNow doesn't natively support without some plugins beyond the basic platform (or some rather advanced customizations).

There's also a bit more overhead when using exports sets so, you can see some performance when either dealing with excessive amounts of data, or where you are running a lot of exports repeatedly at the same time (such as trying to keep data up to date hour by hour).

In the end, it comes down to your personal capabilities, preferences and the details of your situation. ODBC takes longer to get setup and requires someone on the local source side to do some of the configuration, but has a lower cost in overhead and ongoing maintenance and monitoring. Export sets are easy to setup from the ServiceNow side, but take more effort to get the data where you need it and to process it.

Hope that is helpful!

If this was helpful or correct, please be kind and click appropriately!

Michael Jones - Proud member of the CloudPires Team!

I hope this helps!
Michael D. Jones
Proud member of the GlideFast Consulting Team!

View solution in original post

Michael Jones - · ‎06-01-2020

You can, under limited circumstances, use an ODBC connection for this type of access directly to the back-end data.

These links will get you started in the right direction!

https://docs.servicenow.com/bundle/orlando-application-development/page/integrate/odbc-driver/concep...

It's only supported on Windows machines, and you will need to be logged into HI in oder to download the driver, but I've used this method in the past to allow exports to a local data warehouse for processing.

Hope this helps!

If this was helpful or correct, please be kind and click appropriately!

Michael Jones - Proud member of the CloudPires Team!

I hope this helps!
Michael D. Jones
Proud member of the GlideFast Consulting Team!

Siva Kaza · ‎06-02-2020

Thanks for the response Michael.

Does using ODBC have any additional benefits over something like export set?

How is the stability and performance of the driver in your experience?

Michael Jones - · ‎06-02-2020

It would depend on how often you want to "push" data to your local source. With ODBC you can essentially perform queries on demand, so this approach is best when you expect or require something lime near-real-time synchronization of data. I've seen situations where you need to generate reports hourly, and this type of connection is well suited to that requirement.

I've never noticed any impact to using ODBC beyond what you might experience pulling up a list view and filtering records, other than perhaps the initial data load. If you try to take a year's worth of records from multiple tables all at once to populate your local source, I would expect some performance lag - and would suggest scheduling that outside of business hours. After the initial load you should only need delta's and those tend to run smoothly.

You also have the advantage of scaleability - if you want to add tables to your local source, you only need to add permissions to your ODBC user for those tables and update your queries. Easy.

Update sets are find if you only need to keep data up to date with a 24 hour lag, for example. The whole process to generate the exports, put them somewhere, and the process them is a bit cumbersome and requires more effort whenever you need to add something new. You might also run into security issues such as being required to use SFTP for file drops, which ServiceNow doesn't natively support without some plugins beyond the basic platform (or some rather advanced customizations).

There's also a bit more overhead when using exports sets so, you can see some performance when either dealing with excessive amounts of data, or where you are running a lot of exports repeatedly at the same time (such as trying to keep data up to date hour by hour).

In the end, it comes down to your personal capabilities, preferences and the details of your situation. ODBC takes longer to get setup and requires someone on the local source side to do some of the configuration, but has a lower cost in overhead and ongoing maintenance and monitoring. Export sets are easy to setup from the ServiceNow side, but take more effort to get the data where you need it and to process it.

Hope that is helpful!

If this was helpful or correct, please be kind and click appropriately!

Michael Jones - Proud member of the CloudPires Team!

I hope this helps!
Michael D. Jones
Proud member of the GlideFast Consulting Team!