Data Warehouse

What is a Data Warehouse, and why does my school need it?

At SchoolBI, we are often asked questions surrounding data warehouse technology. It’s a term that gets thrown around a bunch without much thought into what it actually entails. In short, a data warehouse is a system designed to pull your data from a variety of sources into one, easier-to-manage source with the end goal of preparing your data for visualization and/or analysis. (Oracle) Data warehouses are essential foundations for commercial entities and play a massive role in their operational practices and long-term strategies. With the increased success of private school institutional research functions and the desire to become truly data-driven while in the infancy of their data maturity journey, private K-12 schools are realizing how important data is to an organization.

Data Warehouses are the foundational component in building your school’s data maturity. Think of it as a hub that helps you make better decisions and understand how the school is performing. It’s like having all your data in one big, organized bookshelf, ready for you to learn from and use to improve the school.

Digging into more detail, it is important to understand one of the most important parts of a data warehouse approach. Extract, transform, and load (ETL) is the process of combining data from multiple sources into a large, central repository called a data warehouse. ETL uses a set of business rules to clean and organize raw data and prepare it for storage, data analytics, and machine learning (ML). (link to Amazon)

Let’s apply ETL to a real school setting using SchoolBI:

Extract: SchoolBI has prebuilt connectors to common data sources (Blackbaud, Veracross, Google Sheets, etc.) that leverage APIs and other technologies to specifically connect and ingest data from those systems into SchoolBI. The information is stored in raw form in a federated, secure, and scalable environment built for schools.

e.g. SchoolBI has a prebuilt connector that pulls in Blackbaud Enrollment Management data directly from Blackbaud through their API. We then store this in raw form in SchoolBI.

Transform: The ingested raw data is processed and prepared to be stored in the data warehouse. This is where much of the magic happens. This is where the data is cleaned, validated, structured, joined, deduping, derivation, splitting is done. Think of this as the stage where rules and policies are applied to produce authoritative data to be used at your school.

e.g. Your school has 10 years of student data in your core SIS system. For 7 of the 10 years your community used term “Caucasian” as one of the options for race. However 3 years ago you changed the value to “White non hispanic”. Then last year your school adopted the NAIS DASL standard for that classification as “White” for that identification. So your have three values in that field that moving forward you want to apply “White” to the race value. It is not appropriate, nor feasible to go to the source and change those values. But you need to look at a longitudinal study across those 10 years. Well a Data Warehouse can help transform that historic data to match your current standard without impacting the raw source data.

Load: Think of this as the process of taking the transformed data and now storing it in the actual data warehouse. There are different ways to do this: Full load (all at once), Incremental load (in batches or streaming as it changes). Regardless of which option is used, this process is the action of moving the transformed data into storage ready for visualization.

e.g. SchoolBI moves the transformed data above and stores it in a structured database to enable a Business Intelligence (BI) tool to connect for reporting. This is done automatically by the SchoolBI platform.