Replacing Existing CRM Systems

Replacing an existing CRM can be a daunting task and with Party even more options are available. Below are some notes I made on the options available to site builders and developers.

Disclaimer: These notes were made when consulting for a particular client interested in replacing their existing (large and bloated) CRM solution. Not all of the arguments made work in a generic context. Hopefully they will be of use to you when you decide how best to build your drupal CRM.

What are the options?

• Migrate - this means setting up Drupal Native entities to store our data and migrating data from [External CRM] into Drupal.[External CRM] data could then be totally deleted as its all now stored in Drupal's SQL tables.
• Entifying - Entifying means to define a Drupal entity whose load and save methods interact with an external data source, namely the [External CRM] (MSSql) Database. This could either be through direct queries or using [External CRM] Webservice (not reccomended as its very very slow!). This option comes in a couple of flavours:
  • Properties - Entity Properties are (usually) values stored in the same table as the entity itself and don't use Drupals field API. This is the way most of the data is already stored (I think).
  • Fields - This involves using the field API to access data. Gives slightly more flexibility. See MongoDB

  As we'd be writing a lot of Code to do either method it's not obvious which method (if any) is better. Breaking out into fields gives us a little more freedom to change the way the Entity works without damaging the fields stuff. But it would require consistant storage for different kinds of data.

Reasons not to Migrate

• The whole system has to be finished before we can use it.
  • Puts alot of pressure on the initial build
  • There are alot of features to pull inhouse
• Doesn't prove anything about Party's data set stuff - which should be able to cope with stuff like this
  • The difference really is that we don't actually want [External CRM] whereas often companies want their records spread accross multiple systems.
• We don't really want a [External CRM] clone we want something better
  • Migrating forces us to copy [External CRM] data structure or make guesses at improvements
• Based on point 1, not migrating allows [Client] to change the goal posts as we go, ensuring they end up with the right system for them.

Entifying Notes

Entities always need a Unique Key, is this constraint met in [External CRM]?
Could override entity load and save - but we would need to define entity properties carefully, and it would have to be one entity type per table in [External CRM].

Data Module

• Defines custom tables of data - integrates with the feeds module for external sources.
• Data Entity module turns these tables into entities
• Don't know about external data tables - conversation with joachim suggests it's not done but possible.
• Drupal already allows you to specify specific tables to use different databases, so this doesn't necessarily need to be external in that sense

Loading info from external tables

• http://drupal.org/project/sqlsrv - a MSSql driver for Drupal - would make accessing the [External CRM] database properly possible.
• Produce and ExternalEntityController that has totally different load and save methods to reference the external database
• Views EFQ module can then be used to list these (even though they're stored in different databases) and do some filtering

NOTE: You can only include fields in one database for filtering/sorting/joining. Using rendered entities can use fields from multiple locations but you can’t do filtering by them. If we used a new Field Storage Engine, then 1 view could look at all the data in that fields storage engine - however - if we were then to add drupal fields to the entity (using the start Field SQL engine) we wouldn't be able to create a view that filtered by the drupal field AND the [External CRM] field.

Field Storage Engines

• Took a look at what MongoDB (http://drupal.org/project/mongodb) does - looks fairly simple to write a field storage engine - for each field type we wanted to use we would have to implement a field storage hook. If I remember correctly, when defining a field Field Storage API makes you define a couple of hooks to specify storage - we may be able to do the same.
• Gut instinct is that writing a field storage engine is a big deal and non trivial.
• More I read about field storage engines the less insanely difficult they seem to be. This is starting to look like a contender with Entity Properties. http://api.drupal.org/api/drupal/modules!field!field.attach.inc/group/fi...
• We would eventually need to migrate from these external fields into drupal fields (probably?) - this would be harder than using Migrate module as Migrate is set up to map table rows to entities.

Recommendations

• Entify (Properties) - Entify the tables (as long as they have a unique ID) with all the tables fields as properties, and any relationship tables as some kind of field.
  • Write an ExternalEntityController that has load methods that grab data from another table. Hopefully most of our Entities can build on top of this.
  • Expose these Entities as Data Set’s for Party.
  • Use magic to colllate the correct entities into a single party (Use our [External CRM] login stuff to match users to Party’s)
  • Where records map easily into things that exist in drupal (I’m thinking taxonomy) we migrate these.
  • With things in Entities/Fields most other drupal UI things should fall out
  • Problems
    • Views integration is not always going to be simple as one query can only deal with one database. Possible Solution: Use SOLR to index everything and build views based on that. These views are not the most responsive (there’s a small delay between changes on drupal taking effect on the SOLR Server)
  • What assumptions have i relied on to make that recommendation?
    • Data falls nicely into properties
• Possibly a middle path - Migrate Entities in so that entities are local and use a field storage engine to keep most of the Data in [External CRM]
  • One of the last thing to replicate is the internal relationships between records - so this probably isn't very helpful
  • Jamie: Do we have the migrate problem here too? Eventually we’re going to want a lot of these fields in Fields API not propeties so we’ll have to migrate the properties into fields in the future?