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.

Five Ways Leading Businesses Use Tableau Write-Back Extensions

Forward-thinking organizations see investing in a Tableau license as just the start. They realize that customizing your Tableau deployment to your unique use cases is the key to making the most of the platform, and one way to do this is to streamline the communication and write-back processes to optimize workflows.

On-demand webinar: Tableau Dashboard Collaboration Tips and Strategies

Communicating about Tableau dashboards is a roundabout process – but it doesn’t have to be. In addition to extensions that enable write-back and annotation, there are strategies that teams can adopt to optimize the collaboration process. Watch this on-demand webinar to learn how to addresses challenges inherent to collaborative analysis of dashboards.

Better BI Decisions through Automated Reporting

To meet the demands of a rapidly evolving tobacco market, our client wanted to improve their reporting process for more accurate and timely insights. Their existing reports required a lot of manual work to prepare, and their quality regularly fell short of management’s expectations. To ensure that their BI environment would better serve the decision-making process, they reached out to Starschema for consulting services.

Improving Time-to-Insight with Elements for Tableau

At a Fortune 5 technology company, whenever Tableau users noticed data inconsistencies and irregularities, they had to go through a cumbersome process of going all the way back to the source of the data to question and update it. To reduce time-to-insight, they wanted to be able to write back to the data source, annotate the data and discuss the data point directly from the Tableau dashboard itself.