Guest Post – Direct SQL Connectors to the Marketo Database

This is a guest post from Sumit Sarkar of Progress, Inc.  

SQL connectors to Marketo database

Marketo has well documented APIs for accessing data.  However, sometimes organizations need direct SQL access.  We are seeing the lines blur at Marketo shops between Marketing and IT which is increasing the demand for standards based SQL connectivity.

Direct SQL connectors to Marketo are available through Progress DataDirect Cloud.  DataDirect Cloud is a data connectivity service that exposes Marketo data through open industry standards for SQL access across ODBC (“Open Database Connectivity”) or JDBC (“Java Database Connectivity”); and REST with OData (“Open Data Protocol”).  Below are some popular use cases for connecting Marketo data out-of-box using DataDirect Cloud:

• Data Discovery and Visualization (Qlik, Tableau, SAP Lumira)
• Enterprise Reporting (SAP Business Objects, Microstrategy, Cognos)
• Data Integration (SQL Server Integration Services – SSIS, Oracle Data Integrator – ODI, Informatica)
• Data Federation (SQL Server Linked Server, SAP Hana SDA, Oracle Database Gateway)
• Ad Hoc Query (Microsoft Office, DB Visualizer, Aqua Data Studio)
• Data Preparation (Alteryx, Trifecta, Paxata)

How does SQL access to Marketo work?

• DataDirect Cloud creates a logical schema for data exposed by Marketo’s integration APIs.
• DataDirect Cloud processes SQL requests from lightweight ODBC or JDBC clients using an elastic real-time query engine on data fetched from the Marketo APIs.
• DataDirect Cloud connectivity is direct and real-time without any duplication of data.
• DataDirect Cloud Service abstracts the Marketo API such that end users do not have to worry about API version changes, or using SOAP versus REST.

DataDirect has been building this style of connectivity to SaaS data sources since 2006 starting with the first Salesforce ODBC driver built on top of its web service APIs.

Getting started connecting to Marketo:

• Register for a DataDirect Cloud Login
• Click “Data Sources” and then “+New Data Source” button

• Select “Marketo” and enter the connection information. You can check with your Marketo administrator or login to find connection information for SOAP integration.

• Click “Test Connection” button. Note there is an OData tab to produce OData from Marketo and we will discuss in a future blog post.
• Click on “SQL Testing” if you want to inspect the Marketo schema exposed or issue basic SQL queries from within the UI.

• Click “Downloads” on the left and select the DataDirect Cloud ODBC or JDBC driver for your application and platform to install.
• Once the DataDirect Cloud ODBC or JDBC driver is installed, you can connect any standards based applications to Marketo.

Here’s a video example of connecting using the DataDirect Cloud ODBC client.

Here are other DataDirect Cloud tutorials that apply to Marketo:

• SAP Data Analtyics
• Microstrategy Enterprise Reporting
• Oracle Data Integration

R&D challenges building SQL connectivity across cloud sources such as Marketo

• Not all SaaS APIs expose a standard query language. In those cases, the engineering team looks at each object individually.  Each object may be exposed with a different API with unique rules for invoking, searching filtering, etc.  It required a significant effort to provide a standard experience querying across the entire data model.
• Handling full join capabilities. In cases where the SaaS APIs do not support a query language with JOIN capability, the engineering team has to perform that operation.  This requires a translation from SQL to efficiently call Marketo APIs to return the minimal amount of data prior to performing the join.  When joining two very large objects, the data access layer may use up considerable resources on the application server or desktop.  Therefore, deployment of the data access layer to an elastic cloud service such as DataDirect Cloud makes a lot of sense for two reasons:
  1. Faster performance and use fewer memory/CPU resources on the client application server or desktop
  2. Leverage the superior bandwidth between DataDirect Cloud and Marketo where pre-joined datasets get exchanged.
• How to handle data models? Is it static or dynamic?  How are changes detected and communicated to the client?  Each SaaS data source is different and in the case of Marketo, certain objects are better queried through views and others through tables.  Handling this matrix of data models and objects across all SaaS sources was certainly a challenge.

 Marketo and DataDirect Cloud Reference for Developers:

• Marketo Connection Properties (link to docs)
• Supported SQL and extensions with Marketo (link to docs)
• Exposed Marketo Tables and Views (link to docs)
• Common error messages returned from Marketo (link to docs)

Biography: 

Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world’s leading consultant on open data standards connectivity with cloud data, Sumit’s interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for PaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others.

Twitter: @SAsInSumit

LinkedIn: www.linkedin.com/in/meetsumit