There are almost as many ways to integrate data, as there are types of data, and source systems that hold data. There’s basically no way to cover them all in the space of one blog post.
In terms of Domo, though, all data is tabular – Domo doesn’t deal with binaries, log streams, delimited text files or complex JSON documents. Just plain old CSVs, which makes sense if you’re doing BI for business – most of the things that matter to decision-makers can be represented in table form.
So long as you can get your data in CSV format, there are a good couple of options to get it into Domo. In this case, I want to pull anonymous user data from write500 – just enough to chart user growth.
I’m also working within the limitations of what the free tier of Domo can do. The Enterprise tier is capable of more advanced acquisition, including basic JSON and CSV API endpoints.
In this example, I do have the benefit of being both the developer of the product, and the business user consuming the dashboards – so I can write any solution I need. Based on what the free tier is capable of, I have the following options:
That’s a lot of options – and at some point, I’ve used every one.
From top to bottom:
1. Cloud Storage
I could write a script on the server to package the data in a format that can be uploaded to cloud storage – so a zipped file pushed to Box, OneDrive or Dropbox for Business. Domo then has cloud connectors that will let me pull that down as usable data.
2. JSON API
I could expose the data through a simple JSON API, and use Google Sheets as an intermediary. It’s possible to use the built-in scripting language and some scheduling to populate a Google Sheet, which is then trivial to import into Domo. It’s not very scalable though.
3. Export to CSV
I could write a script to dump out my required data as a CSV file, then pull that over SFTP. This is easier to set up, but still requires some scripting work. I can then pull the resulting data with the CSV/SFTP connector.
4. Direct Database Access
I could use the MySQL connector to hit the write500 database directly. I’d have to open firewall ports, add users, and do a bunch of other setup first. I’m not in favor of doing that much work right now, though.
So we’ll go with 3 – I’ll write a script to produce the exact CSV file I need, then set up the SFTP connector to fetch it.
Deciding what to fetch
There’s something of an art to deciding what metrics to actually pull – you first need to decide what’s actually useful to you, in terms of answering your business questions. In this example, I know I simply need a dataset that looks like this:
|User ID||Date Registered||Is Subscribed|
How you generate that CSV is up to you – in my case, I wrote a basic Bash script to do that:
Ok, so “basic” might be the wrong word to use – but all that’s really doing is generating a CSV file, with the right header line and correctly-formatted data.
That script is set up to run every 30 minutes, and it will keep a fresh copy of users.csv in a dedicated user’s home directory.
Now to import that! Domo has a set of connectors for working with files:
On the next screen, typical SFTP setup stuff – host, username and password. If you run a tightly-secured system, you might also need to whitelist an IP range so that Domo can reach your server.
So what we just did there – we set up the SFTP connector to fetch that CSV file once every hour. Every time it does that, it will overwrite the dataset we have with new data – that’s what the Replace method means, in the Scheduling tab.
Finally, named and described. It’s helpful to prefix all your datasets with a short project name – makes it easier to find later.
In no time at all, we’ve got our data!
From here, the next step is to visualize it. That’s a whole topic all on its own (so I won’t go into detail here), but here’s a dead-simple line chart built from that data, to show the trending over time:
So that’s a basic rundown of a CSV connector, end-to-end. This example does lean towards the “more complex” side of data acquisition when it comes to Domo. Luckily, most of the high-value stuff exists in systems that Domo already has great connectors for.
If you want to be notified when I write more of this stuff, check the Subscribe widget in my sidebar, top right. Or you can follow me on Twitter.