Analyzing Opportunities in SugarCRM

Since we released our first Beta, we have been working on various examples to demonstrate the capabilities and benefits of a resource oriented approach to data integration.

One of the examples we have been working on is a data mart for SugarCRM opportunity analysis. We have now published that example on our content download site, , where you can download it, and try it out. (You will also need a SnapLogic server installation, to run the pipelines.)

The general idea behind data marts is simple – they are subject specific alternatives to a full blown data warehouse. The primary benefits of using a separate database to analyze an operational system are the ability to look at a snapshot of the constantly changing data, and the offloading of the queries to a separate database which is optimized for analysis using a star schema instead of a normalized schema.

In this example, we used SnapLogic to create an data mart in MySQL, based on a star schema which includes an opportunity fact table, and dimensions for accounts, campaigns, contacts, users, and time.

SnapLogic pipelines are provided which extract and transform data from SugarCRM, loading it into the star schema for analysis. The pipelines use standard SnapLogic components, and extract data from SugarCRM using a direct connection to the SugarCRM / MySQL database.

A set of reference reports based on OpenOffice are also provided, which demonstrate the type of analysis possible on top of this schema. Other reporting tools can (and should) also be used to analyze the data in the star schema. We chose OpenOffice spreadsheets and data pilot for the example, since it’s a good way to show the capabilities, without requiring a specific query tool to be installed as well.

This example demonstrates using SnapLogic to perform standard ETL functions, using the data services approach, and we encourage you to run the pipelines, and browse the code as an example.