In a previous article I wrote about strategies for reporting within ServiceNow however sometimes it’s necessary to extract data, either for reporting or to integrate with other systems. In this article I’ll explore some of the different approaches to this.
Before getting into the detail it’s important to consider some of the downsides of taking data out of ServiceNow:
- In-platform security and controls will be lost as soon as it leaves the Now Platform
- Data structures are designed for use within the Now Platform. Data therefore may not be in the format you need or expect for optimal use so will usually need to be reformatted or processed which could undermine ServiceNow being considered a single source of truth
- No matter how quickly you send data to an external system, it’s never as up to date as the source data in-platform.
Start by understanding your data
Far too frequently we’re concerned with building a solution without fully understanding the problem, and reporting is no different. Let’s look at some of the key questions that we need to understand:
What data is needed?
Replicating every piece of data in ServiceNow is often unnecessary, so only extract what is necessary.
How often does the data change?
How up to date does the external system need to be?
Knowing the rate of change and business need on age of data can start to inform which strategy is best. For example, if real-time data is necessary it limits the approach to querying ServiceNow directly.
What interfaces does the external system support?
Understanding the integration options available helps guide the best approach in ServiceNow. If the external system only supports ODBC for example, or if overall design is to include a data warehouse what format can the ETL or integration services consume data in?
Getting at the data
There are several ways to get data into an external system and I’ll walk through these in turn.
Export sets
Export sets are a no-code solution to extracting data from the Now Platform and saving it to a pre-defined location, it can then be loaded into a data warehouse for example. Generally speaking it isn’t good for reporting on directly. A MID Server is required and it needs access to the location the files need to be saved to.
To setup a scheduled export:
- Create an Export definition which specifies the table, or view, the fields to include and any filter.
- Create an Export set which specifies the export definition, file format, naming convention and location to save the data to.
- Finally, the export set can be scheduled to run at a pre-defined frequency by creating a scheduled extract which creates the necessary entries to schedule the creation of exports automatically.
Push on change
This strategy works for low volumes of data change and can be as simple as a flow sending the changed record to the external system using an IntegrationHub spoke and is a common approach to integrations in general.
It does assume the external system can receive data via a webservice and is a good option if the spoke already exists otherwise it can quickly become technically complex. For high volumes of data change it can start to adversely impact performance as each connection and transmission of a record takes a while to process.
Querying ServiceNow directly
All tables within the Now Platform are accessible via the Table API which allow webservice calls to apply a query and return data. This can be a powerful approach as many modern reporting platforms and developer toolsets can rapidly consume webservices directly. Some of these platforms also have caching options to prevent unnecessary calls back to ServiceNow to refresh data.
ServiceNow also provide the ODBC driver, which is just a wrapper around the Table API. It’s a read-only ODBC driver that allows a pretty complete subset of SQL to be applied to ServiceNow tables. ServiceNow itself just returns tables; the processing of SQL is handled within the ODBC driver locally.
Reporting directly does seem like the best option however having many requests for data could start to cause performance issues within an instance. The strategy can also be used as an extract step before transforming and loading into a data warehouse or other database as an alternative to export sets.
Handling volumes of data
The Now Platform places limits on the amount of data a webservices will return and limits the number of rows in export sets. These limits are put in place to prevent performance issues and can pose challenges when large volumes of data need to be exported. Depending on the rate of change of records it is often preferable to export just the changed records or delta instead of every record which means both the Now Platform and external system have less data to process.
A colleague has provided a great article detailing this in far more detail, see extracting large volumes of changing data via the Table API.
Export sets can also handle delta’s using the sys_created_on or sys_updated_on field and can be enabled when the scheduled export is defined.
Third party solutions
I’ve summarised some strategies above, each of these take time to implement but there are also several third-party tools already available in the ServiceNow Store. Some of these tools may carry additional licence costs but are supported by other vendors and should be explored.
Buy versus build is a decision for many companies and not a straightforward decision. While custom built solutions can offer virtually unlimited flexibility, they can quickly spiral in complexity and incur technical debt if not correctly governed. A purchased product or store app may be perceived as having less flexibility than a custom solution but has the advantage of being supported and evolved by the vendor and extensively tested.
I often find there is not a one-size fits all solution reporting on data in external systems. In many cases, what actually works is a combination of some of these different approaches according to the precise requirements.
