Strategies for monitoring or alerting about failed updates

Thanks for the detailed answer!

I like your idea of a status card, simple and workable. We don't have so many DataSets now that it couldn't work, but I see your point about scale. 

You feature requests sound good, I'd vote for them if I found them!

I may end up writing something with the API as I've already got the code to do the pulls. Kind of a bit elaborate, but it should work.

* Use the API to pull DataSet summaries, which include a last update timestamp. They're keyed off an internal ID, so it's okay if the name changes.

* Stash each DataSet ID, name, last update, column count, row count, etc. in a table.

* Add a field to the table for "too late threshold."

* Periodically run a script to find too late jobs.

In my case it would be a server-side script, pushing to Postgres, and then pulling again into Domo. It would be nice if there were a place in the DataSet settings to specify "Send an alert if this DataSet hasn't updated since 8 hours ago", or whatever you need. I didn't see anything of that sort for DataSets overall.

Now that is a great idea! I wasn't thinking about the presentation, and would not have come up with something some simple and complete. Now I want to go and write the code to make it happen!

Thanks again!

I need to get my head back into a lot of Postgres+Domo work, so I figured I'd dive in here...it's a no-risk problem to solve. I'm excited about checking out the Governance connector, as that's likely what we'll want.

But I figured I'd give this a shot and I came up with a visualization that I don't love. I figured I'd just use a bar chart showing late DataSets, and how late they are. I ended up with a scatterplot. 

On the full chart, I've got quick filters to hide/show things, which makes all of the not late jobs on the left easy to hide. That's fine, but I'm unclear why my simple bar chart looked terrible. Anyway, here are some nerdy notes for anyone that has a good reason to roll their own. We, for example, might want that as we send "red alerts" to a Slack channel and, the last I checked, Slack integration wasn't available for Domo.

Okay, the first step is to start from your scripting tool of choice. I'm using a minority language, so there's no point to posting the code here. The Domo API is pretty straightforward, for anyone used to dealing with things like that. You get an auth token, pass it in, and grab results. The relevant endpoint is documented here:

https://developer.domo.com/docs/dataset-api-reference/dataset#List%20DataSets

I reworked some code I wrote months ago and didn't look too closly. If I remember correctly, you don't get a count up front and have to keep fetching until the well runs dry. In any case, it's not that hard to do. What you get back is an array of JSON objects with a bit of information about each DataSet, such as this example from the docs:

That's very close to everything you need. All that's required past this is a threshold for how long you should wait before declaring a DataSet late/overdue/stale. 

I pushed everything up into Postgres in a simple table using the columns from Domo, plus an extra number for the threshold value.

BEGIN;

DROP TABLE IF EXISTS "admin"."domo_dataset" CASCADE;

CREATE TABLE IF NOT EXISTS "admin"."domo_dataset" (
"id" uuid NOT NULL DEFAULT NULL,
"name_" citext NOT NULL DEFAULT NULL,
"rows_count" integer NOT NULL DEFAULT 0,
"columns_count" integer NOT NULL DEFAULT 0,
"created_at_dts" timestamptz NOT NULL DEFAULT '-infinity',
"updated_at_dts" timestamptz NOT NULL DEFAULT '-infinity',
"late_threshold_minutes" integer NOT NULL DEFAULT 0,

CONSTRAINT domo_dataset_id_pkey
PRIMARY KEY ("id")
);

ALTER TABLE "admin"."domo_dataset" OWNER TO "user_change_structure";

COMMIT;

The exact details would depend on your database....I used Postgres style names. (All lower with underscores.) 

From there, the next step is to add a view that does the calculations. This kind of thing is very idiom-specific, so you would need different functions in Oracle/MS SQL Server/MySQL, etc. Here's a Postgres 10 version...and I may have messed up a calculation...this is just a proof-of-concept exercise for now.

-- View: admin.domo_dataset_plus
DROP VIEW IF EXISTS admin.domo_dataset_plus;

CREATE OR REPLACE VIEW admin.domo_dataset_plus AS
/* Create a CTE so that we can used the calculation easily in a case structure in the main select. */
with datasets as

(select domo_dataset.id,
domo_dataset.name_,
domo_dataset.columns_count,
domo_dataset.rows_count,
domo_dataset.created_at_dts,
domo_dataset.updated_at_dts,
domo_dataset.late_threshold_minutes,
domo_dataset.late_threshold_minutes > 0 as monitor_this,
round(extract(epoch from(age(now(),domo_dataset.updated_at_dts)))::numeric,0) as minutes_since_update

from domo_dataset)

select id,
name_,
columns_count,
rows_count,
created_at_dts,
updated_at_dts,
monitor_this,
late_threshold_minutes,
minutes_since_update,
minutes_since_update - late_threshold_minutes as minutes_late,

case
when late_threshold_minutes < 1 then 'unmonitored'
when late_threshold_minutes < minutes_since_update then 'late'
else 'okay'
end as dataset_status,

case
when late_threshold_minutes < 1 then false
when minutes_since_update - late_threshold_minutes > 0 then true
else false
end as is_late,

case
when late_threshold_minutes < 1 then 0
when minutes_since_update - late_threshold_minutes > 0 then 1
else 0
end as is_late_number

from datasets;

Honestly, that kind of dense, crazy-long SQL is pretty much impossible to read. You kind of have to build it up piece by piece. The final result is a view that's easy to search. So, to find the DataSets that are being monitored, here's all you need:

select * from domo_dataset_plus
where monitor_this = true

To find the late jobs, all that you need is this:

select * from domo_dataset_plus
where is_late = true

In Domo, I just pull in the whole table, by way of the view, as a replace:

select * from domo_dataset_plus

 That's it. Then the DataSet can be used in a card as you like. I added columns in the view instead of Beast Modes because, well, I don't have the Beast Mode sytnax memorized that well & putting it in the view makes the data useful from other locations. (Like if we needed to build out the Slack alert idea.) But you could absolutely use Beast Mode calculated columns against a raw table instead of using a view.

Thoughts on how to more effectively visualize everything would be much appreciated!

No, I can't see any good reason, past what you mentioned. Pulling data out of Domo, repackaging it, and then pulling it back into Domo is downright perverse. So even though I posted a sample of how to do just that, I'd not promote the idea. We may have other uses for the data but, right now, we don't. And if Domo adds (have they?) a way to push alerts to Slack, I don't think we would have any other reason to pull it out.

But every now and then, you just want to build a Rube Golberg machine to expand you mind a little. Sort of like those whimsical sculptures that used to be in airports a lot with all kinds of balls and conveyer belts:

https://en.wikipedia.org/wiki/George_Rhoads

Incredibly fun to look at, memerizing. But you wouldn't want it in your house!

In my case, I can't get into the Governance connector, I started a question on that elsewhere. (I seem to have everything I should.) And Ijust saw some of the cards that Domo builds on top of the DomoStats connector and, dang, those cards are sharp. Very nice. Excited to look at the Governance DataSets.

Thanks! You're kind.

I finally got the connector working. I'd been copying-and-pasting all along, but this time it worked. So now I've got an account configured for access to the governance DataSets, and I scheduled nearly all of them to run once a night.

What I _don't_ have is a clue as to what is in there. I've always got four pots on the boil*, so I've only found time to build one card off of one of the generated DataSets. Do you know if there are any detailed docs, videos, etc? 

I just stumbled across a series of beautiful cards that Domo builds from the DomoStats reports. The same sort of thing would be very nice for the Goveranance data. 

I'd be really curious to hear more about any ways you've found the various DataSets useful

Thanks

* Just watched your cautionary tale ?

Thanks Aaron, those are all excellent examples, I'm going to build them all and see what turns up.

Since I posted, I've buit a few more cards looking at things like PDP policy status, columns by data type, cards & pages, and a few others. Honestly, none of them are particularly useful yet, but pretty intresting. You examples sound immediately helpful.