Real Time Dashboards 2 – SQL layer

This is part 2 of Real Time Dashboards. Reading the previous introductory post (it is short) will put this post in context.

As previously mentioned, our monitor checks up on an ETL process which is itself updating many databases as it executes.

For this post series, we are going to randomly grab an existing workflow (thank you Mazda) to monitor. We will be monitoring a ‘100-point’ car inspection. Lets imagine a car inspector checking things off in his tricorder or some other digital device. This device is, of course, running a program that records everything to a simple database.

I know, one would actually wait in a lobby while technicians check a car out. Feel free to swap my workflow with your workflow; the point to take from this is that our monitor will work well regardless the process being monitored 🙂

Hoping to keep this part basic, I am constructing a table called Step with the following fields: id, label, parentId, workEffort. WorkEffort will be a numeric field, a hypothetical amount of work each respective step would take (could define time, hammers to the head, amount of cowbell, etc.). This basic structure will allow the definition of any number of main and sub processes in a tree hierarchy. This table can be used to describe any number of main steps, each with any number of sub steps. It is important to note that we would be assigning ‘workEffort’ values to the children of our main steps for as many levels as we may have. I.E. – only the children of these steps (the inner steps) will have workEffort… Aggregating these values would, in turn, yield the workEffort of its parent. Peachy.

We will have a second table, Log, where the status of each of these processes can be recorded. The log table will have the following fields: id, active, success, startTime, endTime. Both active and success will be booleans and will indicate which process is active and which process, if any, fails.

Simple but complete enough to represent many real life processes we could monitor. At work, our typical process consists of a series of main steps each comprised of many sub steps. The tables described here would even allow us to take this a bit further as long as we remain sensible in our expectations on performance and complexity.

I have included the scripts to create each of these tables step and log for you. Download and replace the doc extension for sql. WordPress, free and wonderful, wouldn’t allow me to upload .sql files. Bah!

For the next post I will be describing this ‘car inspection’ process and how we can both model and mimic realistically.

Advertisements

One thought on “Real Time Dashboards 2 – SQL layer

  1. Pingback: Real Time Dashboards 2 – update « Mario Talavera Writes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s