MicroStrategy ONE

Connect to ServiceNow Using ODBC

Starting in MicroStrategy ONE Update 12, a ServiceNow ODBC driver is used to connect to ServiceNow to import data on Web and Workstation, rather than a JDBC driver.

If you are using MicroStrategy ONE Update 11 or earlier, see Connect to ServiceNow Using JDBC.

ServiceNow is a web-based tool in which users can log service requests or incidents to ask assistant. All the requests should be routed to the appropriate service desk staff and will be managed on time. The primary purpose is to provide an easy-access self-service tool and users can request and receive necessary assistant timely and correctly.

ServiceNow is a SaaS data source so it does not provide many query processing capabilities except for some filtering. The ServiceNow ODBC driver supports standard SQL-92 syntax and functions but is limited in write back and performance. MicroStrategy will support in-memory data import and choose to process data on our side.

  • Data Retrieve Mode: ODBC Call Only
  • Data Import supported: Select Tables, Free Form SQL, Query Builder (In-memory)

ODBC Connection URL

MicroStrategy ships the ServiceNow ODBC driver to connect ServiceNow as a data source with the following connection methods. You can find them on the data source dialog when importing data.

  • Using OAuth 2.0 authentication client ID and client secret

    DRIVER={MicroStrategy ServiceNow ODBC Driver}; Host=@hostname;Auth_Type=OAuth 2.0;Auth_Client_ID=@client; Auth_Client_Secret=Client_secret ;UID=@username;PWD=@password

  • Using access token

    DRIVER={MicroStrategy ServiceNow ODBC Driver}; Host=@hostname;Auth_Type=OAuth 2.0;Auth_AccessToken=@token

  • Using generic OAuth

    DRIVER={MicroStrategy ServiceNow ODBC Driver}; Host=@hostname;Auth_Type=OAuth 2.0; Auth_AccessToken=@token

    • The connection string for generic OAuth is the same as using access token but you have to create a standalone Authentication Service to store OAuth parameters. For more information on accessing the required parameter value in the connection strings, see How to Get ServiceNow Connection String Parameters

ODBC Schema Tables

ServiceNow stores data in structures which do not follow the rules of traditional tables and columns. The ODBC drivers map ServiceNow data into its designed relational schema with fixed schema tables. The mapping method is complicated, so lower performance is expected when querying tables with large amounts of data.

Configure ServiceNow using ODBC on Web and Workstation

  • Workstation
  • Web
  1. Add an environment connection and log in to Workstation.
  2. Click Data Source under Administration.

  3. Find and choose ServiceNow.

  4. Expand Default Database Connector and choose Add New Database Connection.

  5. Enter the required information.

  6. In the Authentication Mode drop-down, choose Access Token, oAuth, or Standard:
    • Access Token: Enter the required fields Name and Access Token.

    • oAuth: Select an existing service or click Add New Authentication service. If you add a new Authentication Service:

      1. The Identity Provider will be Generic, by default.
      2. In MicroStrategy Configuration, enter the OAuth parameters.
      3. Click Save.

    • Standard: Enter the required fields Name, Client ID, Client Secret, Username, and Password.

  7. Click Save.
  8. In the Add Data Source dialog, choose a Project to assign and access the data source.

  9. Click Save.

In-Memory Data Import

After creating and connecting to a ServiceNow data source, you can retrieve data through ODBC and then publish a cube for dashboards, reports, and documents.

The examples below are using the Select Tables import option.

  1. In the left Navigation pane, click next to Datasets.
  2. Choose a Project and select Data Import Cube.

  3. Click OK.
  4. Click on the data source to connect to ServiceNow.

  5. If needed, click Prepare Data to wrangle your data or rename columns.

  6. Click Finish.

  7. Select Import as an In-memory Dataset.

  1. Click Create > Add External Data.

  2. Find and choose ServiceNow.

  3. Choose Select Tables, Type a Query, or Build a Query to import data.

    It is recommended to use Select Tables to reduce joins on the driver side for best performance.

  4. In the Data Source interface, select DSN-less Data Source and choose ServiceNow for both Database and Version. Use one of the following methods to configure the DSN-less connection:

    • Standard Connection: Enter the required fields Host Name, Client ID, and Client Secret. In User and Password, enter your ServiceNow username and password.

    • Access Token: Enter the required fields Host Name and Access Token.

    • Edit the Connection String: Click Advanced Settings and select Edit connection string. Check the ODBC connection strings or edit the connection information. In Data Source Name, enter a name for the data source connection.

      • Type any character in the Username and Password fields, as they will not be used in the connection.

  5. Click Save.
  6. Click on the new Data Source to connect.

In-Memory Data Import

After creating and connecting to a ServiceNow data source, you can retrieve data through ODBC and then publish a cube for dashboards, reports, and documents.

The examples below are using the Select Tables import option.

  1. Click your ServiceNow data source and select the tables you want to import.

  2. If needed, click Prepare Data to wrangle your data or rename columns.
  3. Click Finish.
  4. Select Import as an In-memory Dataset Mode to publish a cube.

How to Get ServiceNow Connection String Parameters

The Simba ServiceNow ODBC Driver supports OAuth 2.0 Access Token Authentication. This OAuth flow redirects the end user from a user defined app to the ServiceNow Interface to authenticate and authorize the request. Then it redirects the user back to the app with an authorization code that can be used to obtain an access token.

Create and Configure OAuth Provider for Client Credentials

  1. Log into your ServiceNow account with your credentials.

    ServiceNow URL: https://<my_instance>.servicenow.com/dev.do

  2. Navigate to System OAuth > Application Registry and click New.

  3. Click Create an OAuth API endpoint for external clients.

  4. Enter a Name and Redirect URL. Leave the Client Secret blank. It will auto-generate.

  5. Submit the request to get the Client ID and Client Secret connection parameters.

Get Access Token through Postman

  1. Use Post to request to URL: https://<my instance>.service-now.com/oauth_token.do
  2. In Type, choose No Auth.

  3. Add the following keys to the body and ensure the content type is application/x-www-form-urlencoded:

    • grant_type

    • client_id

    • client_secret

    • username

    • password

  4. Click Send. The Postman will return the Access Token and Refresh Token in JSON format.

Related Topics

KB486202: Refresh token authentication mode has been retired for ServiceNow due to an ODBC driver limitation