Data Integration
About a month ago when I was talking abut slide simplification, I got a handful of queries about what it was we were up to.
Simply, we’re building a data infrastructure to integrate a number of different business systems. Currently, our ticketing, gift shop, accounting, development, and scheduling systems are tenuously integrated. Over the years, a number of bad habits, internal processes, and highly manual solutions have evolved to work around the inherent limitations of each individual systems. Yeah, we can get stuff to flow one to other, but it’s frequently not pretty and is a huge time and resource suck across a good chunk of the museum.
We have a few things on the upside. First, all of the business systems are MS SQL database driven, so there’s a similar and redundant back-end architecture. Second, while we’ve been doing some institutional reorganization our workload on exhibit development has decreased over the short-term. And, third, and this one is the critical one, we’re the lucky recipients of a generous donation of services from Accenture — a group that’s done stuff like this a few times before on a much larger scale.
A lot of the initial work has been decidedly non-trivial. Even though we’re a medium size company some of the databases (especially Raiser’s Edge for Development (fond-raising, not software)) are surprisingly complex and closed. We’ve also learned that even simple things like database replication can be tricky since tables are temporarily locked and some systems really like to temporarily drop tables which the replication process can prevent.
Ultimately, we’re creating a rich query environment which does an end-run around the limitations of any given system. Data gets pulled from the parent databases into a new environment, entities and relationships across the datasets are normalized, and outputs are available to pretty much anyone. There’s an interesting interface and user experience problem ahead, in which we figure out how to simplify complex SQL queries and reports for the average user, but we’re early in that phase.
At the moment, I’m thinking about developing a query system that has similar functionality as Automator on OS X, except that it’s web-based so it can be part of our SharePoint implementation. I’m surprised at how often we ask our consultants for a solution that’s outside of their usual development scope. The response is that they’ve usually never had clients ask for it, but I think the real problem is that more often than not people conform to a possible solution rather than finding the right solution for the problem at hand — which is really why we’re headed down this whole path in the first place.