Dylan's BI Study Notes

My notes about Business Intelligence, Data Warehousing, OLAP, and Master Data Management

Use REST API to get the data

Posted by Dylan Wan on November 26, 2016

We now see more and more data are available in REST API.  Here are some of the data sources I worked earlier: ServiceNow Table API, REST API for Oracle Service Cloud,

Here is a check list to enable a data source for BI:

1. How does the service perform the authentication?

In the case of ServiceNow, they provided a good example:

curl "https://instance.service-now.com/api/now/table/problem?sysparm_limit=1" \
--request GET \
--header "Accept:application/json" \
--user 'username':'password'

The username and password can be passed as part of the request.

In some other system, the authentication is done via a separate login service call, and the login call will return a session id and sometime a cookie.

From the deployment planning perspective, we also need to think of how this user used for accessing the REST service will be created and what permissions should be given.

2. How to get the schema definition?

2.1 A list of tables?

Different applications may organize their data in different ways.  We need to know the schema definitions.  It may have SCHEMA -> TABLE -> COLUMN structure like how the data are organized in a RDBMS, such as Oracle.  In the case of ServiceNow, they just provide a list of table.  For Oracle Service Cloud, previously known as RightNow!, they have a good documentation about a list of objects they support: RightNow Core Objects.

Are custom tables allowed?  How do we get the name of the custom table?  Do you have a separate data dictionary services?

2.2 A list of columns?

Getting the data dictionary for the data is important as you can define the mapping and define the query definitions.  You do not need to bring all the data.  Knowing which data available is important.  We need to define what we need and how to select them or how to filter out the unwanted data.  For example, ServiceNow provides the WSDL supports :


It provides the column and data type of each columns:

Screen Shot 2016-11-26 at 8.06.05 AM.png

2.3 Data type mapping

Some source systems have their own special data type.  A data type mapping may be required for handling the data from the new data source.

2.4 Nested Structure

JSON and XML format allows nested structure.  A transformation service may be required for breaking the data from one “document” to multiple “tables”.  If this type of requirements exist, the data will be staged first before loading.

2.5 Lookup

For example, in Oracle RightNow, they have a special data type call “Menu Type”.  It basically provides the Name to ID conversion. If the service API allows to retrieve both, it may be easier. You can just retrieve both.  If the service API allow to retrieve ID only, you will need to either perform a JOIN when retrieving the data, or pull the lookup data separately.

Sometime the Lookup service is available as a different service call.

2.6 Key of the tables

  • Is there a surrogate key?
  • Can a composite key exist?
  • Do we need to concatenate the key of parent object for uniquely identify a record of the child object?

2.7 Joins

3. Getting the data

Here are some of the considerations:

  • Can we specify the list of attributes?
  • Can we define the filter criteria?
  • Do you have data size limitation?
  • Doe we need to handle pagination?
  • Can we perform incremental query?






Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s