Data Query Tool
2/7/2019
When we started the company, we chose to be a software-as-a-service (SaaS) provider rather than a traditional software company. It offered some advantages:
Quickly deploy new features and bug fixes
Reduce troubleshooting complexity since everyone runs the same version
Stable annual subscription revenue with low dependence on new customer sales
Align our interests by focusing on customer satisfaction and subscription renewals
Drive down total cost of ownership (TCO) by providing hardware and DevOps
While SaaS has been great, it has disadvantages too. Since customers can’t directly access the database, it’s hard to query unique combinations of data. Due to performance and security concerns, we don’t allow users to submit ad hoc SQL queries. When they need complex data, we’ve handled requests through our Technical Services Organization — which can be suboptimal at times.
To address these issues, we built a new data query tool that can access our internal tables. It is complex and intended for technically sophisticated users who understand databases. It can join data from multiple tables, choose columns, filter rows, and sort results in a grid of data rows. It’s a UI that generates basic SQL that's safe in our multi-tenant environment. Some examples:
Export all eighth graders for the current year (this is a simple one)
Find students with GPA below a threshold, get their attendance
Find student offenders on discipline incidents, get their demographic information
Find all students in AP-Chemistry, extract their grades for all classes
How could this data be used? It could feed external analytics and business intelligence tools, like Tableau or PowerBI. It could be used to integrate with external systems that don’t accept industry-standard data formats (like OneRoster and Clever). We expect it will become our primary data export mechanism moving forward.
To see how it works, we made a video.
https://youtu.be/SENk_LzOttc
We also wrote a KnowledgeBase article (KB) with step-by-step instructions.
https://commongoalsystems.zendesk.com/hc/en-us/articles/360022477492-How-do-I-create-a-data-query
This is our first version of custom export, and it supports ~400 frequently used tables. While this should be very useful, it doesn’t yet support 100% of our database -- you may find that some tables are not exportable. We plan to keep working on it, and coverage should increase over time.
The data query tool is not "teacher-easy," which is our typical goal for products. Instead it's flexible and powerful. Because of its complexity, we expect users will interact with the technical services organization. Our database is big, and it’s easy to get lost. Feel free to call us for help.
At this time, we’re not charging any additional support fees for data query. We’re hoping it won’t be a big impact on us -- not more than other new features. If we experience a big increase in support, or many customers want us to turnkey build their exports, we may consider adding non-free services. We’ll see how it goes, and keep everyone updated.
