Well, do you? Every association has a treasure chest – of data. It contains information about your customers (members, nonmembers and prospects), their interests, and activities too. That’s right, your AMS or CRM system. It is so important to your organization you might refer to it as a different name, like “the database” or the “membership system” but your Association Management System (AMS) or Customer Relationship Management (CRM) system is a goldmine. Great, found it, now what? If your association is using one of the most common systems, then it likely has a Microsoft SQL Server foundation for storing data and is designed to be a transactional database, or OLTP. The acronym stands for Online Transaction Processing and it describes an architecture for database design that is structured to handle the many, simultaneous database “reads” and “writes” that happen on a daily basis. For example, signing up a new member or paying for a downloadable publication are examples of two “writes” which were likely preceded by multiple “reads”.
Why is it important to understand what an OLTP is? If you want to use your treasure chest for data analytics, then the OLTP structure is not ideal. As the name implies, the system is designed for processing transactions & lots of them. Traditionally the data is broken up into many different tables; which is efficient for saving data quickly and efficiently. However, the structure is not efficient for analytics and here are the reasons why:
- The OLTP Intention – The data is stored at the most granular level (for efficiency) – yet analytics is the study of aggregates and patterns. A system designed around these “roll ups” has a different structure for optimization than that of a database designed for daily operations.
- Maintain Speed – Aggregating data can require a substantial amount of processing power on the server, especially when the system is designed optimally for supporting the transactions of a thriving business. It’s never a good practice to perform complex analytical computations on a transnational database because you run the risk of bogging down the entire system and even locking out new transactions.
- Data Separation – There might be other (OLTP) systems with valuable analytics data that can be blended in with your primary system such as: web site analytics, accounting, or sales commissions. If a common field can be mapped between the systems then the data can be analyzed together for additional insight it belongs in a data mart, not a transactional system.
That is enough about the problems, what is the solution? Build a data mart!! Here is a simple definition of a data mart “a body of historical data in an electronic repository that does not participate in the daily operations of the organization.” Moving key data to another system (the data mart) resolves the issues mentioned and provides the foundation for your association analytics.