ETL: The Power behind Questionmark Analytics
Readers of this blog will be aware that Questionmark is transforming the way we provide reports using our new Questionmark Analytics tool. The screenshots shared in previous posts demonstrate the new report types and the modern user interface, but what they don’t show is the technology that now makes it possible to provide rapid reporting even for the largest data sets. In this post we’ll take a look “under the hood” at this technology.
E is for Extract
Running reports can be a time-consuming job. Thousands or even millions of database records need to be collected together, cross-matched and reformatted before being sent to the user. Unfortunately, data doesn’t stand still. There is nothing to stop you from taking an assessment and generating a new result in the database while someone else is trying to get a report on the very same assessment. Conflicts between these two demands can cause databases to slow down, especially when you start scaling up. The solution is to extract the data from the main database and generate the reports elsewhere.
T is for Transform
When you take an assessment with Questionmark Perception the results are written to the database in the simplest, fastest way. This enables us to scale up the delivery of assessments to cater for vast numbers of participants. But there is a cost to this speed and simplicity. The data is not organized in a very convenient way for reporting; as a result, complex reports require much more data processing to create. The solution is to transform the data so that reports can be created quickly when they are needed.
L is for Load
Having extracted and transformed our data the last step in the process is to load it into a special-purpose database for generating the reports. We call this database our “Results Warehouse” — because the science of ETL is part of the larger field of Data Warehousing.
This new architecture provides rapid, scalable reporting that doesn’t interfere with the participant experience. Learn more about Questionmark Analytics here.