Lightning Talk 6 – Data for Reporting Purposes

Video Transcript

Hi. I’m Austin Peters again. And here to talk about managing data for reporting. So what we’ve talked about is that we’ve been using Cassandra for our data storage.

And one of the things that we’ve also mentioned is that we are specifically storing our data in Cassandra optimized for users. And because it’s optimized for users and for speed, for the application itself, is not necessarily stored in a way that is super intuitive to somebody that did not write the application to retrieve it, and then try to make reports or data or to make sense of it unnecessarily.

So it kind of looks like matrix a little bit to other people. However, what we went out to do is then actually make it available for a format that is nice and easy to report on, and then put it into a flat table structure.

So I’ll show a little bit more like that in just a little bit. So why we did it is and what and what we did we we take the data from Cassandra and we put it into a SQL database. And that is primarily because we are then optimizing it for a schema and a structure that is ideal for reporting out of and makes them very easy.

For people that are familiar with rewriting reports, to quickly do it and not have to look up a bunch of information as to what how different tables connect, and to make queries that are very complicated.

And then it also helps enforce the good practice of having a separate database for your production environments that you report off of, instead of reporting directly off of your production database.

Because, you know, maybe you’ll make a bad query. And now you can take down production, just by running and report. It also makes it very easy for archiving any data. So maybe you only want to do reporting off of your user information for a year. But you need to actually keep around user records or user submissions for much longer.

So that’s another option is, er, that’s another thing that makes it very easy, is archiving the data, because it’s in a separate database altogether. And it just like I mentioned, makes it a lot easier to write a report because it is an A flat table structure. So how it works in a picture basically is start off with actually submitting a request on a form, then we have a web hook that will execute a tree inside of kinetic task, then we have a handler.

And that handler does a bunch of magic that I’ll talk about in just a little bit. And then it’s eventually is inserted into a SQL database. So the SQL Reporting handler, the magic that it does is it actually manages the tables, columns and other information to go into the SQL database.

So it will actually on the fly when a new submission is made inside of Connect request, it will create a table for that form if it doesn’t already exist. And if it does already exist, it will update it with any new columns for that map directly to fields that are on the form.

And then the other thing that it does is manages the columns on the tables and make sure that the column names that it generates on the fly are within the restraints of your database for the column length names, and it’ll either create columns or add add nuance, if need be. Oops, sorry.

And then we also have a metadata table that we use with this which contains a mapping between the feet Build and see information to what your data is put into the SQL database itself. So for example, if you had a truncated table name,

There is a another table that stores what the full path was in C for that table in SQL. And so some reports that we have used or have written, we have a pie chart showing the breakdown of various submissions and different statuses that we have another one showing the popularity by submission count.

And another one that’s just kind of a CSV extract, you know, tables kind of structure for just trying to get at some of the instance data of the submissions. And if you’re curious about any more kind of reports and making them, Jared loves talking about it, and he’ll be very happy to talk more about how to write the reports off of that