MicroStrategy ONE

Connect to Concur Using JDBC

Connecting to Concur using JDBC is only supported on MicroStrategy ONE Update 11 or earlier. If you are using MicroStrategy ONE Update 12 or later, see Connect to Concur Using ODBC.

Concur is a travel and expense management system where employees can submit reimbursement requests and their managers can check and approve. MicroStrategy ships the Concur JDBC connector for customers to connect Concur as data source when doing data import on Web and Workstation.

The Concur JDBC 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: JDBC Call Only
  • Data Import supported: Select Tables, Free Form SQL, Query Builder (In-memory)

JDBC Connection URL

Starting in MicroStrategy 2020 Update 3, the Concur JDBC driver to connect Concur as a data source has the following updated connection methods which include the OAuth 2.0 pre-2017 authentication mode, and the new OAuth 2.0 bearer token mode. You can find them on the data source dialog when importing data.

Using Access and Refresh Tokens

If you already have a valid access token, you can authenticate your connection by providing the token in the connection URL. By default, access tokens expire after some time. You can configure the driver to automatically refresh your access token so that it remains valid for all subsequent connections. To do this, include a refresh token and your Concur application information along with the access token.

The variables are defined as follows:

[BaseURL]is the region-based host required to retrieve the access token. This is required only when you are using bearer access token.

[YourAccessToken]is your access token for authenticating to Concur.

[YourAuthType] is the authentication type that must be used.

  • If you are using the old access token (pre-2017 authorization), set this to OAuth 2.0.
  • If you are using the bearer access token, set this to OAuth 2.0 Bearer.

[YourConsumerKey]is the consumer key associated with your Concur application when you are using the old access token (pre-2017 authorization).

[YourConsumerSecret]is the consumer secret associated with your Concur application when you are using the old access token (pre-2017 authorization).

[YourClientID]is the client ID associated with your Concur application when you are using the bearer access token.

[YourClientSecret]is the client secret associated with your Concur application when you are using the bearer access token.

[YourInstanceURL]is the URL of the Concur instance that you are connecting to.

[YourRefreshToken] is the refresh token associated with your Concur application.

Using Access Token

  • Connect to the server using a connection string written in the following format:

    JDBC;DRIVER={com.microstrategy.concur.jdbc42.CLJDBCDriver};URL={jdbc:concur://;Host=[YourInstanceURL];Auth_Base_URL= [BaseURL];Auth_Type=[YourAuthType];Auth_AccessToken= [YourAccessToken]}

  • For example, using the old access token (pre-2017 authorization):

    JDBC;DRIVER={com.microstrategy.concur.jdbc42.CLJDBCDriver};URL={jdbc:concur://;Host=www.concursolutions.com;Auth_ Type=OAuth 2.0;Auth_ AccessToken=kP9PcyQ7prK2LwUMZMpFQ4R+5VE}

  • For example, using the bearer access token:

    JDBC;DRIVER={com.microstrategy.concur.jdbc42.CLJDBCDriver};URL={jdbc:concur://;Host=www.concursolutions.com;Auth_Base_ URL=us.api.concursolutions.com;Auth_Type=OAuth 2.0 Bearer;Auth_AccessToken=kP9PcyQ7prK2LwUMZMpFQ4R+5VE}

  • For versions prior to MicroStrategy 2020 Update 3, use the following connection string:

    JDBC;DRIVER={com.microstrategy.concur.jdbc42.CLJDBCDriver};URL={jdbc:concur://;Host=@hostname;Auth_Type=OAuth 2.0;Auth_AccessToken=@token

Using Refresh Token

  • With an old access token (pre-2017 authentication), connect to the server using a connection URL written in the following format:

    JDBC;DRIVER={com.microstrategy.concur.jdbc42.CLJDBCDriver};URL={jdbc:concur://;Host=[YourInstanceURL];Auth_Type= [YourAuthType];Auth_AccessToken=[YourAccessToken];Auth_ RefreshToken=[YourRefreshToken];Auth_Client_ID= [YourConsumerKey];Auth_Client_Secret= [YourConsumerSecret]}

    For example:

    JDBC;DRIVER={com.microstrategy.concur.jdbc42.CLJDBCDriver};URL={jdbc:concur://;Host=www.concursolutions.com;Auth_ Type=OAuth 2.0;Auth_ AccessToken=kP9PcyQ7prK2LwUMZMpFQ4R+5VE;Auth_ RefreshToken=s9CCPRFoiYN4uOZhu7n1;Auth_Client_ ID=ehXWQ61dbHt6buG2ib66;Auth_Client_ Secret=f145kn9Pcyq9pr4lvumdapfl4rive}

  • With a bearer token, connect to the server using a connection string written in the following format:

    JDBC;DRIVER={com.microstrategy.concur.jdbc42.CLJDBCDriver};URL={jdbc:concur://;Host=[YourInstanceURL];Auth_Base_URL= [BaseURL];Auth_Type=[YourAuthType];Auth_AccessToken= [YourAccessToken];Auth_RefreshToken= [YourRefreshToken];Auth_Client_ID=[YourClientID];Auth_ Client_Secret=[YourClientSecret]}

    For example:

    JDBC;DRIVER={com.microstrategy.concur.jdbc42.CLJDBCDriver};URL={jdbc:concur://;Host=www.concursolutions.com;Auth_Base_ URL=us.api.concursolutions.com;Auth_Type=OAuth 2.0 Bearer;Auth_AccessToken=kP9PcyQ7prK2LwUMZMpFQ4R+5VE;Auth_ RefreshToken=s9CCPRFoiYN4uOZhu7n1;Auth_Client_ ID=ehXWQ61dbHt6buG2ib66;Auth_Client_ Secret=fkfglcsqjvN1Q7dvPXBD}

  • For versions prior to MicroStrategy 2020 Update 3, use the following connection string:

    JDBC;DRIVER={com.microstrategy.concur.jdbc42.CLJDBCDriver};URL={jdbc:concur://;Host=@host;Auth_Type=OAuth 2.0;Auth_RefreshToken=@refresh_token;Auth_Client_ID=@client_ID;Auth_Client_Secret=Client_secret)

Using OAuth 2.0 Authentication with Standard Concur Credentials

If you do not already have a valid access token, you can configure the driver to retrieve and use one based on your Concur application information. To do this, provide the client ID, client secret, and redirect URI associated with your Concur application in the connection URL. The variables are defined as follows:

[BaseURL] is the region-based host required to retrieve the access token. This is required only when you are using bearer access token.

[YourAuthType] is the authentication type that must be used. l If you are using the old access token (pre-2017 authorization), set this to OAuth 2.0. If you are using the bearer access token, set this to OAuth 2.0 Bearer.

[YourConsumerKey] is the consumer key associated with your Concur application when you are using the old access token (pre-2017 authorization).

[YourClientID] is the client ID associated with your Concur application when you are using the bearer access token.

[YourClientSecret] is the client secret associated with your Concur application when you are using the bearer access token.

[YourInstanceURL] is the URL of the Concur server you are connecting to.

[YourPassword] is the password corresponding to the username that you provided.

[YourUserName] is the username associated with your Concur application.

  • With an old access token (pre-2017 authentication), connect to the server using a connection URL written in the following format:

    JDBC;DRIVER={com.microstrategy.concur.jdbc42.CLJDBCDriver};URL={jdbc:concur:localhost;Host=[YourInstanceURL];UID= [YourUserName];PWD=[YourPassword];Auth_Type= [YourAuthType];Auth_Client_ID=[YourConsumerKey]

    For example:

    JDBC;DRIVER={com.microstrategy.concur.jdbc42.CLJDBCDriver};URL={jdbc:concur://www.concursolutions.com;Host=www.concursolu tions.com;UID=skroob;PWD=12345;Auth_Type=OAuth 2.0;Auth_ Client_ID=f145kn9Pcyq9pr4lvumdapfl4rive}

  • With a bearer token, connect to the server using a connection URL written in the following format:

    JDBC;DRIVER={com.microstrategy.concur.jdbc42.CLJDBCDriver};URL={jdbc:concur:localhost;Auth_Base_Url=[BaseURL];Host= [YourInstanceURL];UID=[YourUserName];PWD= [YourPassword];Auth_Type=[YourAuthType];Auth_Client_ID= [YourClientID];Auth_Client_Secret=[YourClientSecret]}

    For example:

    JDBC;DRIVER={com.microstrategy.concur.jdbc42.CLJDBCDriver};URL={jdbc:concur://www.concursolutions.com;Auth_Base_ Url=https://usimpl.api.concursolutions.com;Host=www.concursolutions.com ;UID=skroob;PWD=12345;Auth_Type=OAuth 2.0 Bearer;Auth_ Client_ID=f145kn9Pcyq9pr4lvumdapfl4rive;Auth_Client_ Secret=5kn9Pcyq9pr4lvu123pfl4r57}

  • For versions prior to MicroStrategy 2020 Update 3, use the following connection string:

    JDBC;DRIVER={com.microstrategy.concur.jdbc42.CLJDBCDriver};URL={jdbc:concur://;Host=@host;Auth_Type=OAuth 2.0;Auth_Client_ID=@client;UID=@username;PWD=@password}

See How to Get Concur Connection String Parameters for steps to acquire the correct parameters.

JDBC Schema Tables

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

Configure Concur Using JDBC on Web and Workstation

  1. Choose Add Data > New Data.
  2. Find the Concur connector from the data source list.

  3. Click the connector.
  4. Choose Select Tables, Type a Query, or Build a Query to import data. Click Next.

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

  5. In the Data Source interface, select DSN-less Data Source and choose Concur for both Database and Version. Use one of the following methods to configure the DSN-less connection:
    • Access Token: Enter the required field Host Name and Access Token.
    • Standard Access: Enter the required Host Name, Client ID, Concur User ID, and Password.
    • Refresh Token: Enter the required Host Name, Refresh Token, Client ID, and Client Secret.

    • Edit the Connection String: Click Show connection string and select Edit connection string. Enter the connection information of the JDBC connection string.

      Ignore Driver. Type any character in the User and Password fields, as they will not be used in the connection.

  6. Click OK.
  7. Click on the new Data Source to connect.

In-Memory Data Import

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

The examples below are using the Select Tables import option.

  1. Click your Concur data source and select the tables you want to import.
  2. Click Prepare Data.
  3. Click Finish.

  4. Select Import as an In-memory Dataset Mode to publish a cube.

How to Get Concur Connection String Parameters

You must join the Concur partner program to enable the Administrator account to view Client ID and Client Secret when modifying an Application.

  1. Log into your Concur account.
  2. Navigate to Administrator > Company > Web Services.
  3. Click Enable Partner Application.
  4. Click Modify and the Application connection details appear.

Generate the Concur Access Token and Refresh Token

The access token and refresh token are generated by sending a GET request via the Postman app.

  1. Download the Postman app.
  2. In the GET field enter the following URL: https://www.concursolutions.com/net2/oauth2/accesstoken.ashx.
  3. In the Authorization tab, choose Basic Auth. Enter your Concur account Username and Password.

  4. In the Headers tab, enter the following information:
    • Accept: Application/json
    • X-Consumer Key: Enter the Client ID
  5. Click Send. Postman returns the access token and refresh token in JSON format.