MicroStrategy ONE

Connect to Marketo Using ODBC

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

Marketo is a marketing automation platform that enables marketers to manage personalized multi-channel programs and campaigns to demonstrate prospects and customer trends.

Marketo is a SaaS data source so it does not provide many query processing capabilities except for some filtering. The Marketo 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 Marketo ODBC driver to connect Marketo 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 Marketo ODBC Driver};Host=@host;Auth_Type=OAuth 2.0; Auth_Client_ID=@client_ID;Auth_Client_Secret=@client_secret

  • Using access token

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

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

Marketo has an API limit of 10,000 requests per day.

ODBC Schema Tables

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

Configure Marketo 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 Marketo.

  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 or Standard:
    • Access Token: Enter the required fields Name and Access Token.

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

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

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

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

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

    • Edit the Connection String: Click Advanced Settings and select Edit connection string. Enter the connection information of the ODBC connection string to connect.

      • 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 Marketo 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 Marketo 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 Marketo Connection String Parameters

The Marketo ODBC Driver supports OAuth 2.0 data source workflow which can be authenticated using Client Credentials, or Access Token.

Obtain Client Credentials

Marketo’s REST APIs are authenticated with 2-legged OAuth 2.0 and Client IDs and Client Secrets are provided by custom services that user defined.

  1. Log into your Marketo user account.
  2. Navigate to Admin > Integration > LaunchPoint.

  3. Click View Details to see the Client ID and Client Secret.

Get Access Token Using Client ID and Client Secret

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

  1. Navigate to Admin > Web Services > REST API in your Marketo account and copy the Identity URL.

  2. In Postman, in the GET request field, enter the URL below replacing:
    • <identityURL> with your Marketo Identity URL
    • <Client ID> with the Client ID for your service
    • <Client Secret> with the Client Secret for your service

    https://<identityURL>/oauth/token?grant_type=client_credentials&client_id=<Client ID>&client_secret=<Client Secret>

  3. Click Send.

    The Expires In value is returned in seconds. A new access token will need to be generated after the allotted time.

Related Topics

Marketo Introduction

Marketo Authentication

KB486245: Marketo Connection Error [MicroStrategy] [Marketo] (20) Error while attempting to use REST API: Access denied Unknown Error Code