Data Warehouse Cheat Sheet for Nonprofits

This article is part of a series “Museum data and what to do with it”, which looks at data systems and management practices in museums. See the first piece here.

As nonprofits begin to put more effort in collecting data from their visitors, an important question arises: how can these organizations efficiently integrate and explain this data? Data warehouses can be powerful tools to help organizations, and this article provides a quick overview of data warehouses for nonprofits with minimum experience in data science.

What is a data warehouse and what can it do?

Similar to external hard drives which provide space to save files and applications, data warehouses provide space to store multiple types of data. The type of data that can be stored in a data warehouse can include both operational and transactional data sets. Operational data includes information collected at the an admissions desk, such as zip codes, names, emails and addresses. Transactional data refers to data collected from ticketing, event registrations and gift shop sales.

If the concept of a data warehouse is confusing to you, try to think of it as a 3-D Excel spreadsheet, where you store separate information on each 2-D sheet, and then you are able to aggregate data across different sheets in effective ways to produce meaningful insights for the management team.

The healthcare industry actively conducts research on the impact of data warehouses for nonprofits, but issues addressed in such reports also apply to other types of nonprofits. Research from HealthCatalyst shows that the wasted time spent hunting and gathering data leaves many organizations with no time for the most value-added task, which is interpreting the data and improving performance based on these interpretations.

A data warehouse makes it possible to gain new perspectives into an organization’s data by integrating data from multiple pools. The purpose of collecting data is not just to possess it, but rather to use it to help organizations make data-informed decisions. A data warehouse boosts an organization’s ability to explain their data by providing a structure and user-friendly tools to understand data from multiple sources.

The architecture of specific data warehouses varies between organizations based on their individual needs. Some organizations have a simple structure because of the limited numbers of data sources while others may have a complex system with dozens of data sources.  While it is hard to explain the specifics of a single data warehouse’s architecture, it is more reasonable to examine different layers in a data warehouse. If you are interested in learning more about the architecture of a data warehouse, this article from 1keydata would be a good start. Idealware also has an introductory article that covers the basis of a data warehouse that is easy to understand.

What is the difference between a database and a data warehouse?

One major difference between a database and a data warehouse is the functionality to either “read” or “write” data. A database is designed to “insert” or “write” data. Databases are usually the destination of modified data from a single data source, usually a front-end application such as a point-of-sale system for visitors to purchase admission tickets at the front desk. The data is recorded from the application and input into the database. These databases are not designed to retrieve the data efficiently out or to “read” the data. A data warehouse, however, is specifically designed to do just that. It is built to retrieve efficiently and integrate data from multiple databases.

Similarly, a database often contains point-in-time data. For example, a museum’s customer database can easily tell you the membership status of a visitor as of that specific moment, while it might have a harder time providing an overview of the history of the membership data. A data warehouse, on the other hand, is structured for historical reporting, and its main purpose is to analyze and interpret data.

The table below is a simplified summary:

There are also distinctions between data warehouses and terms like “data mart” and “data lake.” This video here provides some great explanations.

Is Implementing a data warehouse right for your non-profit?

Building a data warehouse is a complex, expensive, and time-consuming task. Data warehousing is not a great option for small non-profit organizations with little or no IT support. Building a data warehouse requires a considerable investment, including at least 80 to 120 hours for planning, set up, and building the data exchange processes for a warehouse. For smaller nonprofit organizations, implementing a single constituent relationship management system (CRM) is already a big leap forward. AMT Lab has several articles on exploring different CRM options through the lens of the nano-nonprofit, that help small non-profit organizations consider their particular needs, constraints, and advantages.

For larger non-profit organizations, however, a data warehouse can be a worthwhile investment. Many organizations are currently using multiple software packages. Even though the applications might work well by themselves, it is often labor intensive for organizations look across their different platforms. A data warehouse can help those organizations to understand how they are performing and explain the data in a deeper and more meaningful way.

There’s not an exact threshold for a non-profit organization to know for sure whether a data warehouse is necessary. However, according to a research article from TechnologyAdvice, data warehouses can save organizations lots of labor time by storing important information in the same location. Rather than keeping data in several different places, organizations can harness centralized data to enhance strategic decisions, without having to aggregate different sources. Fundraising is always an important aspect for nonprofits, and as organizations expand beyond direct mail and start to rely on online giving, social media campaigns, and e-newsletters, a data warehouse can help nonprofits to save time and money, eliminate errors and minimize the labor needed for manual documentation.

At any rate, building a data warehouse is a big decision for any organization. It’s a more advanced technique which will require technical skills and a considerable investment. Nonprofits should self-assess their organization before making the decision to build a data warehouse. The costs of data warehousing are relatively easy to estimate. Like most IT projects, they include the associated hardware, software, and personnel costs. has a useful article on accessing and justifying data warehousing ROIs for organizations.


Further Reading



Banner image from Wikimedia Commons, the free media repository