Founded in 1885 in Tucson, Arizona, the University of Arizona is a large and well-known public research university. Their mission is clear: to improve the prospects and enrich the lives of the people of Arizona and the world through education, research and creative expression. True to their Land Grant roots, they continually rank among the world’s foremost research universities with over $600 million in research investment each year. Combined with an annual enrollment approaching 45,000 students, as well as an extensive alumni network, their scope of impact is incredible.

To help advance the university’s mission and maintain a culture of excellence, the University of Arizona Foundation plays a huge role. By building strong relationships, securing philanthropic support and shrewdly stewarding the university’s assets, their goal is to keep the university thriving for future generations. Being entrusted with such resources requires intelligent decisions, which is the crossroads where the Foundation meets data.

Data Trust Issues

For quite some time, the Foundation struggled in obtaining rapid and reliable insight on giving metrics. The root of the challenge began with slow or tenuous access to their database, Blackbaud. The lack of timely and complete data communication between Blackbaud and the Foundation’s fundraising management software, Raiser’s Edge, ultimately led to some data uncertainty among staff. If the data wasn’t reliable, how could they effectively share any insight on fundraising progress?

Then came the issue of reporting. Like many organizations, the Foundation used Excel as their reporting workhorse. The only problem is that as staff spun up new Excel reports, they found that disparate data brought in from Raiser’s Edge didn’t always match up. With data trust issues throughout their data ecosystem and no real way to drill down into detail, they knew a change was needed.

The Power of Data Discovery

To help them diagnose their data problems more deeply and chart a path to progress, the Foundation reached out to InterWorks. The first order of business was for InterWorks to meet with Foundation staff to get a complete grasp on all of their challenges and goals. After listening carefully to their needs, InterWorks helped the Foundation create a plan of action, starting with a data discovery engagement.

To complete this data discovery engagement, InterWorks data engineer Alex Suarez came on-site to work side by side with Foundation staff for a week. After seeing how their environment worked firsthand, Alex was able to map out a path forward. To help them consolidate their reporting data into one reliable repository, he suggested the creation of a data lake. This wouldn’t be a master location for all data sources, and it wouldn’t be connected to anything else, but having this defined location for heavily used data sources would greatly simplify the bulk of data connection complexity.

A Holistic Solution

Alex first explained the process of data lake creation and then ran a few sprints to show Foundation staff how everything worked. These sprints included defining reporting requirements and then working backwards to determine what types of data the Foundation would need to support more heavily. From there, he worked with them to create ETL processes that would engineer this data in the most optimal format for analysis.

Throughout this whole process, Alex was teaching Foundation developers how to do these tasks on their own once InterWorks was gone. Because of the evolving nature of data, many data projects are never truly finished, so it was important that Foundation staff be empowered with the insight necessary to keep progressing on their own. It also helped in the sense that the developers now had the knowledge they needed to satisfy the requests of anxious report builders.

With the data lake created and ETL polish added, Alex and Foundation staff could move into the finer details of data formatting. One of the most crucial elements of this endeavor, at least in terms of preparing data for analysis in the ultimate destination of Tableau Software, was the creation of interest tables. These are essentially big tables that have everything to do with one vector of data, such as development officer activity in the case of the Foundation. This method was selected largely because of the performance priority. Tableau is entirely capable of joining numerous tables together, but having it process one wide and tall table vs. several tables often results in vastly improved performance.

When we say several tables, it’s important to understand scale. Before InterWorks came on, the Foundation had roughly 200 tables created in Blackbaud. When you consider joining that many tables together, even in a platform as capable as Tableau, it’s easy to see how performance can degrade rapidly. Fortunately, Alex was able to manually join many of these tables together and consolidate them by the common denominators identified in the report requirement stage earlier in the project. At the same time, he was able to pare down the number of superfluous fields and records to further optimize performance.

Once Alex and Foundation staff created the first interest table on development officer activity, they were able to package that table up in a Tableau Data Extract – the fastest data source for Tableau to analyze. From there, it was incredibly easy to create a view in Tableau showing just about everything their data had to say about development officer activity. InterWorks analytics consultant Tony Kau came in to give their reports some design love, helping them to create the beautiful, informative dashboards for which Tableau is known.

A Night-and-Day Difference

Though the changes enacted at the Foundation might not seem monumental as far as data projects go, the work completed by InterWorks and Foundation staff has already had an immense impact. Centralizing all relevant reporting data to a data lake simplified the Foundation’s challenge of what to do with all the data they were collecting. The ETL work and creation of interest tables took performance lightyears further than it once was, giving the Foundation the timely and reliable access they had long sought.

With improved data now feeding into Tableau, creating clear and relevant reports is much easier. The Foundation is ecstatic that they can now drill down deeper into specific metrics and records in a snap. Perhaps most of all, they love that they can look at data in a given timespan, something their previous data trust issues wouldn’t allow them to do with confidence. Simply knowing that everyone is looking at the same data has done wonders in rebuilding data trust. This is largely due to the Foundation now having an unbroken line of data communication from storage to analysis.

What’s Next?

With the basic frustrations of working with their data resolved, the focus for the Foundation is now shifting into fine-tuning their reporting methods. An essential piece to this next step is ramping up their use of Tableau Server, which will allow for greater control over what’s created and who’s accessing it, as well as performance across the board. InterWorks has already aided the Foundation in their Tableau Server efforts by helping them create a validation workbook using their data to test Tableau’s accuracy. All that’s left now is continued rollout and adoption.

As the Foundation continues to sharpen their use of Tableau and bring more people into their circle of data-driven insights, they will be well on their way to making the most informed decisions they possibly can. For the University of Arizona as a whole, this means a stronger connection to students and alumni alike, as well as better stewardship of resources, ensuring the university will grow for years to come.