The team also proposed a comprehensive data and analytics framework for fast and accurate data visualizations. Glimmer accepted Starschema’s plan for a project comprising a cycle of ETL and data engineering development and dashboard building, followed by multiple rounds of optimization.
Starschema designed a highly scalable cloud-based solution that can handle both small and extreme amounts of data. Microsoft Azure provides the cloud platform, Snowflake hosts the reporting data mart and Matillion serves as the ETL tool. Azure was a logical choice because Glimmer already had an Office 365 account and it provides access to both Snowflake and Matillion services.
Matillion was selected as the ETL tool because it enables the custom REST API extractions required for CommCare and Drupal, and for its native Snowflake support. Thanks to these features, the operations team could run transformation codes in the database from the ETL tool without moving the data in and out again, promoting quick delivery.
Although the initial project involved a relatively small amount of data, the team already anticipates how the number of tables and source systems will grow over time. For this reason, we chose a database that provides great flexibility to accommodate everything from ingesting only a couple of tables to handling terabytes of data.
These elements – Snowflake, Azure, Matillion ETL – together give Glimmer great flexibility to get data, quickly and reliably, from as many source systems as needed. One of the biggest challenges was to convert dozens of columns of data into reportable, easily queryable tables. We solved this by using custom Python code to complement out-of-box Matillion features to enable the dynamic adoption of new questions and categories during future iterations of the data collection process. Matillion’s Snowflake support proved to be a major asset as it allows for very effective data transformation.
The development process involved importing data from source systems to the cloud database and then transforming it into tables to work with BI tools. The team also created a security layer to ensure appropriate data access control. The connectivity between Azure components and Snowflake helped streamline the architectural part of the project, and as a result, the implementation phase could focus on improving the quality of data management and reporting, with minimal tweaking required for the components.
To unlock deeper insights in reporting, Glimmer chose to replace Excel sheets with Tableau dashboards. The Starschema data visualization team developed eight dashboards for monitoring CommCare data across two program areas, two dashboards for project-based data from the Drupal database, and three additional dashboards for assessment data also captured in CommCare. To optimize costs, the team duplicated certain dashboards and leveraged the new access control system to isolate them. This way, the duplicates can serve different users, eliminating the need to build unique dashboards for each team.
The solution introduced a knowledge transfer process to train Glimmer’s staff to work autonomously and effectively. One of the main goals was to give Glimmer the ability to create their own dashboards. To this end, the team held regular trainings with live demonstrations of dashboard-building best practices and helped to educate Glimmer’s users on methods for working within Tableau Server and Desktop, in addition to Azure, Snowflake and Matillion.
