MicroStrategy ONE

Connect to Concur Using ODBC

Starting in MicroStrategy ONE Update 12, a Concur ODBC driver is used to connect to Concur 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 Concur Using JDBC.

Concur is a travel and expense management system where employees can submit reimbursement requests and their managers can check and approve.

Concur is a SaaS data source so it does not provide many query processing capabilities except for some filtering. The Concur 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 Concur ODBC driver to connect Concur as a data source with the following 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 when you provide the token in the connection URL. By default, access tokens expire. You can configure the driver to automatically refresh your access token so that it remains valid for all subsequent connections. To do this, add your refresh token and your Concur application information, in addition to your access token. Enter the following variables:

  • BaseURL: The region-based host required to retrieve the access token. This variable is only required when you use a bearer access token.
  • InstanceURL: The URL of the Concur instance that you are connecting to.
  • YourAccessToken: Your access token for authenticating to Concur.
  • YourAuthType: 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.
  • YourClientID: The client ID associated with your Concur application when you are using the bearer access token.
  • YourClientSecret: The client secret associated with your Concur application when you are using the bearer access token.
  • YourPassword: The password that corresponds to your Concur username.
  • YourUserName: The username to access your Concur account.
  • YourRefreshToken: The refresh token associated with your Concur application.

Using Access Token

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

    • Copy 
    Driver={MicroStrategy Concur ODBC Driver};Host=[InstanceURL];Auth_Base_URL=[BaseURL];Auth_Type=[YourAuthType];Auth_AccessToken=[YourAccessToken]

  • For example, if you are using the old access token (pre-2017 authorization), the string should look similar to:

    • Copy 
    Driver={MicroStrategy Concur ODBC Driver};Host=www.concursolutions.com;Auth_Type=OAuth_2.0;Auth_AccessToken=kN9PcyQ9prK4LvUMMMpFL4R+lVE=

  • For example, if you are using the new OAuth 2.0 bearer access token, the string should look similar to:

    Copy 
    DRIVER={MicroStrategy Concur ODBC Driver};Host=www.concursolutions.com;Auth_Base_ URL=us.api.concursolutions.com;Auth_Type=OAuth_2.0_Bearer;Auth_AccessToken=kP9PcyQ7prK2LwUMZMpFQ4R+5VE;

Using Refresh Token

Access tokens are valid for a limited amount of time only. If you provide an Auth_AccessToken value and a refresh token in addition to your Concur application information, the connector can refresh the access token when it expires.

In the above case, the Auth_AccessToken value can be any placeholder value.

  • For example, if you are using the old access token (pre-2017 authorization), you can connect to the server using a connection URL written similar to:

    • Copy 
    Driver={MicroStrategy Concur ODBC Driver};Host=www.concursolutions.com;Auth_Type=OAuth_2.0;Auth_AccessToken=kN9PcyQ9prK4LvUMMMpFL4R+lVE;Auth_RefreshToken=rP78cyQ7prE7LwUerMpFQ4Y+98w;Auth_Client_ID=f145kn9Pcyq9pr4lvumdapfl4rive;Auth_Client_Secret=4MIIEowIBAAKCAQEA18f3PhzSxNF8kSHGphg9WWR8iXj

  • For example, if you are using the new OAuth 2.0 bearer token, you can connect to the server using a connection URL written similar to:

    • Copy 
    Driver={MicroStrategy Concur ODBC Driver};Host=www.concursolutions.com;Auth_Base_URL=us.api.concursolutions.com;Auth_Type=OAuth_2.0_Bearer;Auth_AccessToken=kN9PcyQ9prK4LvUMMMpFL4R+lVE;Auth_RefreshToken=rP78cyQ7prE7LwUerMpFQ4Y+98w;Auth_Client_ID=f145kn9Pcyq9pr4lvumdapfl4rive;Auth_Client_Secret=4MIIEowIBAAKCAQEA18f3PhzSxNF8kSHGphg9WWR8iXj

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 an access token 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.

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

    • Copy 
    Driver={MicroStrategy Concur ODBC Driver};Host=[YourInstanceURL];UID= [YourUserName];PWD=[YourPassword];Auth_Type=OAuth_2.0;Auth_Client_ID=[YourClientId]

  • For example, you can connect to the server using a connection URL written similar to:

    • Copy 
    Driver={MicroStrategy Concur ODBC Driver};Host=www.concursolutions.com;Auth_Type=OAuth_2.0;Auth_Client_ID=f145kn9Pcyq9pr4lvumdapfl4rive;UID=jsmith;PWD=simba123

  • Using the new OAuth 2.0 bearer token, connect to the server using a connection URL written in the following format:

    • Copy 
    Driver={MicroStrategy Concur ODBC Driver};Auth_Base_URL=[BaseURL];Host=[InstanceURL];UID=[UserName];PWD=[Password];Auth_Type=OAuth_2.0_Bearer;Auth_Client_ID=[YourClientID];Auth_Client_Secret=[YourClientSecret]

  • For example, you can connect to the server using a connection URL written similar to:

    • Copy 
    Driver={MicroStrategy Concur ODBC Driver};Auth_Base_URL=https://us-impl.api.concursolutions.com;Host=https://implementation.concursolutions.com;UID=skroob;PWD=12345;Auth_Type=OAuth_2.0_Bearer;Auth_Client_ID=f145kn9Pcyq9pr4lvumdapfl4rive;Auth_Client_Secret=MIIEowIBAAKCAQEA18f3PhzSxNF8kSHGphg9WWR8iXj

ODBC Schema Tables

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

Configure Concur using ODBC on Web and Workstation

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

  3. Search "Concur" and choose Other Relational Sources.

  4. In Database Version, choose Concur.
  5. Expand Default Database Connector and choose Add New Database Connection.

  6. In Connection String, enter one of the following strings based on your connection:
    • Using the old access token (pre-2017 authorization):

      • Copy 
      Driver={MicroStrategy Concur ODBC Driver};Host=www.concursolutions.com;Auth_Type=OAuth_2.0;Auth_AccessToken=kN9PcyQ9prK4LvUMMMpFL4R+lVE=

    • Using the new OAuth 2.0 bearer access token:

      • Copy 
      DRIVER={MicroStrategy Concur ODBC Driver};Host=www.concursolutions.com;Auth_Base_ URL=us.api.concursolutions.com;Auth_Type=OAuth_2.0_Bearer;Auth_AccessToken=kP9PcyQ7prK2LwUMZMpFQ4R+5VE;

    • Using refresh token:

      • If you provide an Auth_AccessToken value and a refresh token in addition to your Concur application information, the connector can refresh the access token when it expires. In this case, the Auth_AccessToken can be any placeholder value.

      Copy 
      Driver={MicroStrategy Concur ODBC Driver};Host=www.concursolutions.com;Auth_Type=OAuth_2.0;Auth_AccessToken=kN9PcyQ9prK4LvUMMMpFL4R+lVE;Auth_RefreshToken=rP78cyQ7prE7LwUerMpFQ4Y+98w;Auth_Client_ID=f145kn9Pcyq9pr4lvumdapfl4rive;Auth_Client_Secret=4MIIEowIBAAKCAQEA18f3PhzSxNF8kSHGphg9WWR8iXj

    • Using OAuth 2.0 authentication with standard Concur credentials:

      • Copy 
      Driver={MicroStrategy Concur ODBC Driver};Host=www.concursolutions.com;Auth_Type=OAuth_2.0;Auth_Client_ID=f145kn9Pcyq9pr4lvumdapfl4rive;

  7. In Authentication Mode, choose one of the following based on your connection:
    1. For standard authentication:

       
      1. Choose Standard.
      2. In Default Database Login, choose Add New Database Login.
      3. Enter your Username and Password.
      4. For access token or refresh token authentication, choose Preconfigured.
  8. Click Save.
  9. In the Add Data Source dialog, choose a Project to assign and access the data source.
  10. Click Save.

In-Memory Data Import

After creating and connecting to a Concur 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 Concur.

  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 Concur.

  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 Concur for both Database and Version. Use one of the following methods to configure the DSN-less connection:

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

    • Standard Access: Enter the required fields Host Name, Client ID, Concur User ID, and Password.
    • Refresh Token: Enter the required fields 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 ODBC connection string to connect and enter the required fields Host Name and Access Token.

      • Type any character in the User 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 Concur 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 Concur 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 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.