Gradient

A Better View of Operations for A Glimmer of Hope

Practice Areas

  • Data Visualization
  • Data Engineering

Business Impacts

  • Increased organizational efficiency
  • Increased capacity for program evaluation and insights

Challenges

  • Slow, manual, Excel-based reporting
  • Lack of technology for operations optimization

Technologies

  • Tableau
  • Snowflake
  • Microsoft Azure
  • Matillion
  • Python

Background

A Glimmer of Hope is a US-based non-profit philanthropic organization working to reduce poverty and build resilience in rural Ethiopia. Since 2000, they have completed over 10,000 projects with community-led, integrated solutions that have impacted more than five million lives by investing in economic empowerment through agriculture trainings and loans, supported by essential health and education projects

The organization generates large volumes of data during its day-to-day operation, but the management was dissatisfied with their ability to take full advantage of this data to support decision-making. To effectively track the outcomes and impact of their programmatic work, improve operational efficiency, optimize costs and allow donors to see the full impact of their donations, Glimmer initiated a project with Starschema to make the necessary developments to their data management and reporting practices.


A Better View of Operations for A Glimmer of Hope

Challenge

One of the ways that Glimmer collects a large amount of data is through local field workers in charge of providing support for short- and mid-term loans to families in rural Ethiopian communities. The workers submit the data via a mobile data collection platform, CommCare. Glimmer’s legacy reporting system – slow, manual and Excel-based – made it inefficient to monitor the activities of field workers and gain insights about the loans and households they are monitoring.

Related programmatic data from implementation partners is captured in a separate internal system built on the Drupal platform, which does not natively support data aggregation, reporting or visualization.

To achieve the desired operational improvements, Glimmer required a better data infrastructure, more effective data visualization tools and user training to familiarize staff with best practices for self-service analytics. In addition, the new reporting platform needed to be able to dynamically accommodate new survey categories (for example, baseline/midline/endline assessments) and incorporate additional source systems in future phases of work with minimal additional development.

A Better View of Operations for A Glimmer of Hope

Solution

Starschema first reviewed Glimmer’s data architecture and business requirements, and then conducted a tool assessment for the organization’s data analytics technology stack. During this work, Starschema suggested the adoption of a range of technologies and developments that would allow the organization to gain access to the desired insights.


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.

A Better View of Operations for A Glimmer of Hope

Outcome

The team used a cyclical development process which enabled Glimmer to gain access to new and deeper insights after just a few days of work, while the feedback from the main stakeholders helped to further improve these results. By the end of the handover period, the solution allowed for the granular monitoring of the performance of multi-year loan programs in multiple geographical regions, with needed insight into the activities of field workers and the loans and households they support.


In addition, the solution allows for data aggregation, reporting and visualization of project-based data captured in Glimmer’s Drupal database for the first time.

This has greatly helped the organization to optimize their operations by increasing capacity for program evaluation and insights and allowing the organization to detect operational anomalies, which will help ensure better support for local communities. The improved monitoring capabilities will support a more effective, data-driven decision-making process, and the new data framework will help Glimmer plan future initiatives without the need to invest in significant technological updates.

As a result, Glimmer now has an integrated system that will support their strategic monitoring and evaluation initiatives and help track outcomes and impacts against their theory of change, as well as enable the organization to demonstrate impact to its donors.

Data Visualization Project Planning Worksheet

This downloadable worksheet contains requirements and conditions that a client-side stakeholder in a project involving data visualization services needs to define to ensure timely delivery, optimized cost and valuable outcomes.

Cloud Security Best Practices That Organizations Often Overlook When Migrating

This white paper goes beyond cloud providers' documentation to introduce best practices that will help your organization ensure a comprehensive security solution for a cloud environment at optimal long-term cost. 

Data Marketplace Architecture

A leading investment management firm improved
the performance of its data marketplace with a new
architecture leveraging Snowflake and dbt.

The Cornerstones of an Effective Location Data Strategy

Geolocation data provides invaluable insights into the habits and preferences of users, customers and audiences. This white paper helps understand the fundamental opportunities and challenges inherent in using location data for business-critical processes in any industry.