I’ve been collaborating again with Salesforce Marketing Cloud expert Kyle Griffin, this time thinking through what it would require to build SFMC data extensions from queries against an Oracle database.

The traditional way to fetch data from a remote Oracle database is to connect to it over ODBC and send plain text representing SQL queries over the ODBC connection.

The software with which you want to fetch the data (in this case, Marketing Cloud) is typically responsible for handling the details of ODBC as well as for helping you turn the data that comes back into some sort of useful table-shaped data structure within your software (e.g. a data extension).

Unfortunately, neither of our Google skills have turned up any hint that Marketing Cloud is capable of doing all this for Kyle.

Instead, if Kyle wanted to turn SQL queries into SFMC data extensions, he’d have to type those queries into some sort of ETL tool capable of:

1. connecting to the database over ODBC and downloading data as queried with SQL
2. generating CSV files from the downloading data
3. connecting to Marketing Cloud over SFTP and sending it the CSV files

However, this particular Oracle database also has an HTTP-based GraphQL application programming interface (API).

Theoretically, I think this might give Kyle another option.

Intro to HTTP (for data folks & managers, especially!)

Katie ・ Jun 3 '19 ・ 10 min read

#tutorial #beginners #explainlikeimfive #http

According to Ivan Razine, you can create a SFMC Data Extension with Server-Side JavaScript (SSJS) code.

If you can also populate a data extension’s contents with SSJS and/or AMPscript, and if SSJS/AMPscript can make callouts to HTTP-based APIs sitting outside of Marketing Cloud, then I don’t see why code couldn’t be written to query the data from Oracle over its GraphQL interface.

Kyle is curious about this option because leaving the text of all the queries against Oracle inside of Marketing Cloud gives him more control over their maintenance than he would have if an ETL tool were in the way.

I don’t know enough about Marketing Cloud to dive in deep at this point, but here’s the game plan I told Kyle I’d follow if I were looking into it:

1. Make a “hello world” project in Marketing Cloud that builds a Data Extension based on data retrieved from a GET HTTP operation against https://yesno.wtf/api.
   • Treat the data that comes back as a 3-column, 1-row result set.
   • Be sure to leverage Postman or other GUI-based HTTP-request-making desktop software to ensure you’ve got the requests to YesNo working before you try to implement them in MarketingCloud.
2. Repeat the success from step 2 with another API whose dummy data you like (based on exploring it with Postman) from this list of public APIs.
   • Presuming the returned data is different, note the different steps you have to go through to turn it into something “table-shaped” and put it into a Data Extension.
3. Try again, this time forcing Marketing Cloud to make an HTTP request to a Postman Echo that requires authentication.
   • Your goal is to verify that you and your security team are comfortable with the mechanisms Marketing Cloud offers for storing secrets _ secretly _ and for using them later in HTTP-request-making SSJS/AMPscript.
4. Play with fetching data from an API that asks for you to pass it “GraphQL” queries, such as a POST request to https://api.graphql.jobs.
   • Here’s some text representing a nice little 4-column, many-row query against that API:

  {\r\n jobs\r\n {\r\n title\r\n postedAt\r\n description\r\n applyUrl\r\n }\r\n}

If, after completing these projects (and learning a heck of a lot of coding in the process!), you feel like this is an exciting and maintainable data integration architecture, reach out to your Oracle database experts to discuss the details of the GraphQL API it offers.

Don’t worry if you hit dead ends: remember that your organization’s trusty ETL tool optimized for SQL & SFTP will be excited to see you again.