Data Integration using “Best of Breed” Approach to Enterprise Applications
WI NTERTHUR
M U S E U M & C O U N T R Y E S T AT E
Winterthur Museum Data Analysis
• Survey legacy systems: what do we currently have? • Analyze desired data relationships: which data need to be combined? • Evaluate mechanisms for combining and extracting data (ease of access, ease of maintenance, initial cost, etc.) • Factor these systems into evaluation of all future enterprise system purchases
WI NTERTHUR M U S E U M & C O U N T R Y E S T AT E
Winterthur’s Data
• Great Plains (Microsoft): finance • Vista (TMVista/Ticketmaster): ticketing (back office and front desk) • The Raiser’s Edge (Blackbaud): development • SquareOne (Computac): retail • Virtua (VTLS): library collections • KE Emu (KE Software): period room collections • ArcGIS (ESRI): mapping ------------------------------------------------------------• Sharepoint and Business Portal (Microsoft)
WI NTERTHUR M U S E U M & C O U N T R Y E S T AT E
Primary Image Data Repositories
• Virtua: library collections • KE Emu: decorative art object collections • ArcGIS: garden, grounds, physical plant -----------------------------------------------------• Digital Asset Management: ContentDM??
WI NTERTHUR
M U S E U M & C O U N T R Y E S T AT E
Primary Visitor Data Sources
• Vista: visitor & program • The Raiser’s Edge: donor & membership, selected program • SquareOne: visitor & retail sales • Great Plains: aggregate financials
WI NTERTHUR
M U S E U M & C O U N T R Y E S T AT E
Great Plains
Fi
n na
l ci a
s
Fi n
an
ci a
ls
Square One (retail)
Financials
Vista (ticketing)
M
em
Ev en
t in
fo
be
rs hi
p
in
fo
Raiser’s Edge (membership info)
em M
be
h rs
ip
in
fo
Driving forces for migrating or correlating visitor data:
• Analysis of attendance/revenue (“what”) and identification of patterns/trends (“why”) • Marketing • Mailing list management • Customer service • Efficiency of internal operations • Development & Membership
WI NTERTHUR M U S E U M & C O U N T R Y E S T AT E
Multiple approaches
• Migration (e.g., synchronizing duplicate data in two databases) • Warehousing (e.g., OLAP “cube”) • Complex tables (e.g., JCA Repository) • Simple or relational queries against original or secondary data
WI NTERTHUR
M U S E U M & C O U N T R Y E S T AT E
Migration & Complex Tables
Raiser’s Edge
Membership info: ID Status Exp Date
Vista
Vista
New/renewing memberships Member activity
Raiser’s Edge
Vista
JCA Repository
Raiser’s Edge
Sales and event info linked for easy analysis
Mailing list filtered on member type & activity
Mailing list de-duping
Current practices at Winterthur:
• Migration: done routinely on very selected basis • Warehousing: considering OLAP (On-Line Analytical Processing) cube for 3-dimensionality • Complex tables: using JCA Repository and customized datasets that I developed • Relational queries and pivot table analyses (a poor man’s OLAP cube) against Repository and other datasets from original sources—used every day!
WI NTERTHUR M U S E U M & C O U N T R Y E S T AT E
Typical Analysis
• Identify question or problem • Analyze, drill down, analyze, drill down • Use best datasets and tools for the problem at hand, but they need not be overly sophisticated nor expensive to implement • Example of a “down and dirty” approach using simple queries to Excel and pivot table consolidation and analysis
WI NTERTHUR M U S E U M & C O U N T R Y E S T AT E
Attendance Report
WI NTERTHUR
M U S E U M & C O U N T R Y E S T AT E
Why the attendance/revenue discrepancy?
WI NTERTHUR
M U S E U M & C O U N T R Y E S T AT E
JCA Repository Table
WI NTERTHUR
M U S E U M & C O U N T R Y E S T AT E
Compare relative changes in attendance & revenue
restrict to Feb-Mar periods of interest
sort
field
WI NTERTHUR
M U S E U M & C O U N T R Y E S T AT E
Looking for where attendance is up more than revenue
More significant
30%
Less significant
20%
10%
0% Adults Senior (62+) Group Member-Guests Student Children Other
-10%
red = attendance shift blue = revenue shift
-20%
-30%
WI NTERTHUR
M U S E U M & C O U N T R Y E S T AT E
Digging deeper: adults and members
WI NTERTHUR
M U S E U M & C O U N T R Y E S T AT E
And deeper: adults and Study Visas
WI NTERTHUR
M U S E U M & C O U N T R Y E S T AT E
Oh well: Not so mysterious!
WI NTERTHUR
M U S E U M & C O U N T R Y E S T AT E
The only data worth saving and integrating is that which you’ll use!
Focus on the questions you need to answer and develop datasets and tools to answer those questions.
WI NTERTHUR
M U S E U M & C O U N T R Y E S T AT E