Recently, I’ve met a phrase somewhere in a web magazine calling data mashups “a technology for competitive advantage.”  So I’d like to have some words on data mashups and their role in data integration. It’s not long ago that everybody was talking about data mashups; they were a kind of a buzzword, actually.

Enterprise data mashups integrate data from different enterprise data sources, including both desktop and web-based sources. Being quite flexible and light they help easily get data from heterogeneous sources, as well as read or write data, and customize data integration workflows.

Data mashups, normally, don’t demand users being experienced at coding, while help turn different data sources into reusable data services. So this is a great opportunity for non-technical business users to ease and improve data integration, combine different business data, and get a better view of enterprise environment.

So, though, today there’s not so much buzz around the technology, it is widely used in the enterprise data integration initiatives.

SugarCRM, a solution used by many companies, quite often is needed to be integrated with different third-party applications used by an organization. Sometimes there is a need for database integration, and there are multiple data sources to be in use.

Let’s imagine, for example, the need to take news from an RSS feed, extract customer information from GoldMine CRM, standardize and cleanse data, verify e-mails, names, and addresses, and then mix it all up and throw it across your SugarCRM tables. So, you have multiple data targets from which to aggregate information. The actions described below, are also applicable if you have to apply complex data cleansing or enrichment rules to the data on its way between the source and the target. Here is an example of how to perform such operations.

In this example we will use MySQL to host staging data. To connect to MySQL database, you may use embedded MySQL connector. Just drag-and-drop the connector to work panel, enter database authentication details, and provide the paths to MySQL.

To learn more about data integration with SugaCRM take a look at our whitepaper on “Five Steps To Integrate SugarCRM With Legacy Applications“

In one of my previous posts, I’ve written about application consolidation as the way to simplify data integration and enhance data quality. However, to run consolidation successfully, there are efforts to be applied to define which applications should be consolidated, and to create the standards of dealing with company’s data.

Making these decisions is not an easy task, so here is some more practical advice on consolidation of company’s applications I’ve found at searchcio.com recently. They are not so much about data integration needs, but mostly about business and financial side of the issue.

Surely, there should be time for a planning phase to prepare every step.  Then comes the phase essential from the financial point of view. This is analyzing the cost of system support compared to business value of each system. Such analysis will help to make the right decision on what application to keep, and what to stop using. Moreover, the company will find out systems the business value of which was low, but support was too costly, and thus cut some costs.

Before removing any application, it’s wise to make sure nobody needs it; otherwise, provide for the needed functionality to replace the application. Moreover, the needed data should be backed up, for as soon as the application removed, all the data will be lost.

So, not only the needs of better data integration drive application consolidation initiatives. Gartner experts recommend that each consolidation initiative could define the sources of savings. And again, any consolidation activity should be the responsibility of both business and technical sides.

What makes me turn to ETL topic again, is a very interesting piece by Rick Sherman, a business intelligence expert. Rick, actually, is pointing the fact, that today’s ETL market is limited by a small number of providers. But that’s not because there’s nobody apart from those companies, but because many data integration vendors are simply invisible to potential buyers.

I agree, I often read in the web complaints about large expensive data integration offerings, with wide functionality that a company looking for a data integration solution simply can’t afford. Moreover, very often such companies don’t need all that functionalities; their needs are not likely to go out of ETL boundaries. However, choose a data integration tool to answer their needs and budget is a challenge.

At the same time many vendors providing data integration and ETL tools which are something, as Rick says “between the mega-products and the bundled tools in terms of functionality and total cost of ownership,” are known to quite a small audience.

What’s the way out? Make some more effort and do not limit search by several best-known vendors. This is as for ETL buyers.  At the same time Rick called data integration vendors to come out of shadow and become more recognizable.

It’s a common thing for a large enterprise or a company to have multiple applications they work with. So once it comes the time to consider consolidate enterprise applications and to may be cut some of them to simplify data integration processes, to improve data quality, and business agility.

The question is which applications should stay and which should be cut. Such decisions don’t usually come easy and require a lot of preparatory work, and a right strategy. According to Gartner’s principles of application consolidation and expert advices I’ve found at SearchCIO.com, several steps should be fulfilled to make the right decision and ensure success of the project.

The strategic steps include:

Asking yourself questions about the value of applications used in the company, their functions, the ability to support them long term, etc.

Searching for sources of data replication. However, experts do not recommend throwing away duplicate applications, but to create strict standards for data definitions, technologies and applications to use whiting the enterprise.

Deciding which data is important. Though tricky, this decision will help to define the applications to keep or to cut. So the choice should be done from the data point of view, as, again, the goal of consolidation is o provide better data quality, having more consistent data views and dimensions from less applications to simplify data integration and business analytics.

Hosted CRM software has become a mainstream lately. Such solutions as, for example, SugarCRM represents a tremendous opportunity for companies to solve these challenges by leveraging proven, non-intrusive and scalable on-demand platform. Companies begin to realize that fully leverage the benefits of using SugarCRM it’s to integrate customer-facing business processes with the rest of the enterprise.

So, a company only needs to figure out how to integrate, replicate, or migrate their customer information between SugarCRM and 3rd-party systems and applications.

Prior to any data migration clarify the goals of the oncoming integration process. The more clearly you set the goals, the more accurate your SugarCRM integration will be. Sometimes you may need to join data; sometimes it’s all about eliminating duplications; and sometimes the data should be validated or filtered first.

To start reading source data, you need to establish connections to the source databases. In other words, you need to gain access to data tables, data structures, and data entries. This is where data integration actually starts. With visual tools like Apatar, for instance, you can do it without having to write a single line of a code. Just open the “drag-and-drop” job designer, choose the necessary data connectors, enter SugarCRM authorization details, and provide the paths to the database servers or storage files. The application is ready to operate with data.

To know more about the integration of SugarCRM with third-party applications, feel free to read our white paper on “Five Steps to Integrate SugarCRM with 3rd-Party Systems and Avoid Most Common Mistakes”

The popularity of ETL (extract, transform, and load) is not so hard to explain. Gathering the data from various sources in one destination, thus providing a unified view on, for example, customer data, or other important data that travels from detached departments, ETL, actually, rescues a company from data chaos. So data stops being a bunch of some contradictory records, and turns into a powerful tool able to improve company’s competitiveness.

Though it sounds so great, choosing and implementing ETL may be quite complex and tiring. Moreover, there should be the right methodology selected, as well as the right data integration model, etc. And this is more than true when ETL and data integration initiatives are started from scratch. Why?

Imagine a huge, ok, not huge, just a big organization.
First, there are lots of departments there.
Second, each department utilizes different applications.
Third, each department has its own rules on how data should be viewed and treated. This point is, actually, crucial one. For the most popular thing companies face starting data integration initiative is the data (sometimes the same data) entered and stored in different formats by different departments. So before integrating the data into one target, the data should be formalized somehow.

About several years ago this was impossible without amounts of hand-coding. Today, as the market for ETL solutions expands, vendors try to implement as much functionality to their tolls as users require. So modern ETL tools vary from simple exclusively extract-transform-load providing tools to multi-functional data integration platforms that allow performing various operations. The choice depends on what companies business needs are and whether this or that tool may provide the data quality just enough to fit those needs.

I’ve recently met an interesting thought about the source of issues in data integration at an article by Noreen Kendle, a data management analyst.

Very often we hear and read about data integration failures and challenges companies face when starting data integration initiatives. According to the article, the thing is that the typical tools/technology approach of merging data from various database systems doesn’t work due to several things, such as:

Methodology – Tools simply do their job on merging based on data field names or content. Assuming field names may be misleading, you can imagine the mess that may come out of it.

Dimensions of time – Time bring changes. This is true about data as well: data meaning changes over time, so two items that seem identical, but represented by different time mark may mean rather different things.

Capturing data relationships – The full meaning of data may be lost if the data is viewed individually, with no reference to its relations with other data. There should be relative meaning of data items preserved somehow in order to preserve data integrity.

So, the better is the data name quality, content, and structure/relationships the better tools/technology approach to data integration works. But all this is insufficient without understanding of the business meaning of data, i.e. the data should be mapped to a business data model, and only then it is ready for data integration.
: Configuration Guide

The times when data integration from multiple sources was mostly the case for big companies and enterprises have passed. Today more and more companies in small business have lots of various applications and utilize multiple databases, facing sometimes integration challenges.

The thing is, small companies often can’t afford having IT resources for data integration projects. Neither can they afford hiring somebody outside team, for this is quite expensive, plus, there are security questions that always rise when it comes to having somebody from outside work with company’s data.

However, modern ETL and data integration offerings help address small comanies’ data integration challenges providing solutions that cover the lack of IT expertise. Multiple vendors have introduced an alternative for expensive enterprise software, including open source technologies.

Open source data integration tools are the solutions for SMBs to consider. They may not be the answer for full master data management, but cope with classic extract, transform and load (ETL) functions successfully. Providing the possibility to run different operations through graphic UIs, they help avoid costly and complex hand-coding. Thus helping to integrate and migrate data from multiple sources that a company uses.