Cloud based systems, such as Microsoft Dynamics CRM Online and Salesforce, offer a robust customer relationship management system that can take much of the weight off the typical over-burdened IT staff. Regular maintenance, staffing and the like are almost eliminated from the day to day operations of the system. These online systems also typically offer a wide variety of reporting options of KPIs that help management make timely business decisions based on trends occurring in real-time.

For more system taxing, heavy computation/aggregation based analytics, the on-line system can have some serious setbacks. A catalog based company, for instance, may rely very heavily of marketing analysis of their customer base to determine how and where to spend their marketing dollar for the best ROI on a limited budget. Building out an analytics data cube on the cloud system platform would have several drawbacks including: space considerations for several tables of aggregated data, the time it takes to process the aggregations (the cloud system is typically not tuned for this kind of performance) and the overall impact on performance of the online system on the end-users. Remember, the on-line system is designed primarily as a transactional system.

A good solution for the need of data cube types of analytics is to bring the data out of the cloud and back down to earth, on premise in your own SQL Server database. This gives you all the power of a SQL Server tuned specifically for heavy computations. Also, building the aggregation tables and the analytical cube for reporting will have no impact on the performance of your online transactional system. Plus, you can combine this with data from other systems to give you reporting not at all available in the cloud. There are a few different options for this and I will talk briefly about three.

The first would be to request a copy of the underlying cloud system database and install it on your SQL Server. This will give everything, including the kitchen sink. Normally the entire database is not required for specific types of analysis. Also, the database would be ‘point in time’. Asking for a copy of the underlying database every month might be problematic with the cloud platform folks. You would also be dealing with what might be unfamiliar schema names for your objects and entities, making data location a bit of a challenge.

Another option would be to write out your own Web Services calls to the cloud systems API to populate a database that you have built to house the data. If you’re looking for fresh data on a periodic bases (and you don’t want to do a complete truncate and repopulate) you will also have to develop a ‘net-change’ method to extract only data that has changed or been added/deleted since the initial population of the target tables. However, characteristically you would be seeing the objects and entities through the Web Services metadata, which would give you more familiar nomenclature.

Then there is the ongoing maintenance of the extract process to consider. A third option is to use a purpose built tool that does the heavy lifting for you. For example, Scribe Software offers a cloud based replication service (Scribe Online Replication Service) which has a wizard which allows you to pick the objects of the cloud system, connect via the system's API, and schedule replicating the data locally. The first time the replication occurs, it actually will build the local tables on your SQL Server to house the data, based on metadata nomenclature for object and entity names. Scribe’s service also allows scheduling ‘net-change’ data updates. And because their service is cloud-based, there is virtually no impact on the on premise environment.

It’s your data. Don’t let great on-line transactional systems limit your analytics needs. Bring the cloud down to earth, then slice and dice to your heart’s content.

For more information about C5 Insight or this blog entry, please Contact Us