The Problem

The nonprofit’s BI and analytics processes were struggling.

Powerful insights were not being generated like they could be, and leaders at the organization were not receiving accurate and timely data to help them make the best business decisions.

This was so because our client was working with a plethora of disparate data sources and lacked a sound data model.

We then created an SSIS ETL process to move data from the flat files. Our team also invoked stored procedures to munge and conform the disparate data sets into a star schema.

Company Z sells a platform that’s essentially an online portal for state educators to be able to access the data and see results – by school, by district, by zip code, etc.

The data model we created was nimble enough to support this platform as well as the legacy reporting requirement previously mentioned.

The business intelligence solutions we delivered were a success! Company Z was able to use a new ETL process and data model that was churning out invaluable analytics and insights.

The Context

Our client is a large nonprofit whose mission is to help schools improve through accreditation and professional services.

The company asked us for assistance with designing and implementing a business intelligence solution.

 

The Problem

The nonprofit’s BI and analytics processes were struggling.

Powerful insights were not being generated like they could be, and leaders at the organization were not receiving accurate and timely data to help them make the best business decisions.

This was so because our client was working with a plethora of disparate data sources and lacked a sound data model.

The Solution

We determined that our client’s data needed to be properly organized first.

Then a proper system needed to be put in place for business intelligence to thrive – all while care for data sensitivity was maintained.

Our team worked primarily with two sets of standardized testing data for our client. The data arrived in flat files and was sent by each state.

One important requirement of this project was to produce “legacy” reporting of student test results (this was a data dump of all the results). Fulfilling this entailed working closely with the organization’s data science team to ensure we knew what measures were needed to create optimal reports.

Our team also worked closely with the nonprofit’s IT department to understand our client’s master data sources as well as the sensitive nature of the data.

 
We then created an SSIS ETL process to move data from the flat files. Our team also invoked stored procedures to munge and conform the disparate data sets into a star schema.

Company Z sells a platform that’s essentially an online portal for state educators to be able to access the data and see results – by school, by district, by zip code, etc.

The data model we created was nimble enough to support this platform as well as the legacy reporting requirement previously mentioned.

 

Lessons Learned

 

1. If you’re working with a plethora of disparate data sources, your top priority should be to create a sound data model that connects your data sources properly so that reporting can be completed. Business intelligence cannot thrive without a great data model.

 

2. When working with sensitive information, limit the amount of users who have access to the data and take advantage of the sensitivity features of whichever tools you’re working with.