Restricted access

September 29, 2010

Data Integration Application for Microsoft Dynamics CRM

A couple of days ago, our team faced an issue when developing a custom application for data integration to Microsoft Dynamics CRM. We thought that sharing the problem and the solution will be useful for the community, so here it is.

Apatar developers had chosen PHP technology for that custom data integration application; I will explain why they had chosen PHP and not Java or Delphi in my next post. Meanwhile, please, keep in mind that this case describes accessing Microsoft Dynamics CRM by means of PHP NuSOAP.

The Microsoft Dynamics CRM Software Development Kit includes documentation that covers a wide range of instructive and practical information. Unfortunately, it does not provide the appropriate information on accessing Microsoft Dynamics CRM MetadataService (fields, tables, and their descriptions) and CrmService (accounts, contacts, leads). To be more exact, it provides the same URL formula for accessing both metadata and data services:


where service name is either MetadataService or CrmService.

In the course of development, it became clear that this formula does not work for both services. So, the correct formulas are:

For Metadata


For CRM data


As you can see, to access MetadataService you have to add ?wsdl at the end of URL, and to access CrmService you don’t have to add anything.

Hope you’ll find this information useful!

September 24, 2010

Customer Data Integration Using RSS Feeds

Filed under: Data Integration, Database Integration — Tags: , , — Katherine Vasilega @ 7:45 am

One of the ways to improve your business management is to keep your CRM users better informed. You can send out emails to your employees when a specific event happens in CRM (new contact or new lead is added), but you can go further than that and provide the same up-to-date information without emails. Database integration with RSS feeds opens up great opportunities for customer service and workflow.

Database integration with RSS allows aggregating RSS feeds, filtering them by relevant keywords and providing the relevant content to a specific user. Database integration allows creating one generic feed or multiple feeds to give CRM users the option to customize the desired information. Let’s say you have five new customers a day – you can immediately inform sales managers of new opportunities and leads, alarm executives when opportunities close and new ones come in, send contacts’ details to your marketing department, etc.

You can use CRM – RSS feeds integration in another way. Database integration with news search RSS via Yahoo! News, Google news, CNET and other portals will help you gather information about your potential customers. You can immediately send out this information to sales people and give them something to talk about with possible clients.

Database integration with RSS feeds allows collecting relevant information and using it to update your CRM system, arm your employees with relevant information and improve customer service.

September 17, 2010

Data Mapping for Data Integration

Filed under: Data Integration, Data Migration, Data Synchronization, Database Integration, ETL — Katherine Vasilega @ 6:43 am

Your data sources grow together with your business. You have ERP, CRM systems, mail clients, Web forms, Excel documents and it’s getting harder to distinguish the accurate data. Data integration can solve this issue, but how do you transfer data from multiple sources in a nice and easy way? You’d probably need to deploy a system that allows automating the process of data transfer.

What is data mapping?

Data mapping is used in data integration when you need to gather information form multiple sources. Data mapping involves matching between a source and a target, e.g., two databases that contain the same data elements but call them by different names. A simple example of data mapping includes moving the value from a ‘customer name’ field in one DB to a ‘customer last name’ field in another DB. To do so, your ETL tool needs to know that you want to take the value from the source field ‘customer name’, cut out the first part (name) and leave the second part (last name), and move it to the target field ‘customer last name’. Besides, the steps of performing these operations need to be marked in the data integration process.

Data mapping tools

The modern ETL systems include the functionality of making data maps. Commonly, these are graphical mapping tools. They enable you to draw a line from one field to another, identifying the correct connection. It’s relatively easy to do if you want to, let’s say, move your contacts from the mail client to the CRM. But what if the task is more complicated, such as to move the information received through a Web contact form (first name, last name, address, phone, email, company name) to your CRM that has different fields for all these values? It will take much time to do it manually and it will take some time for you to draw a data map in your ETL tool, unless you are an IT specialist.

Open source ETL

Remember, if you are using an open source ETL tool, there is always a community behind it. People from all over the world create data maps for various purposes and make them available for free download. You can find great tools for complicated data integration tasks and use them for free. No need to draw a data map of your own, just use what has already proved to be effective. That way you can execute your data integration with no effort and money spending at all.

March 5, 2010

Database Integration: On the Importance of Data Quality Standards

It’s a sad fact, but many organizations realize the poor quality of the data in their databases, only when it comes to database integration. Data quality issues are among the common reasons for data integration failure.

This neglecting attitude to data quality lies in the fact that companies often don’t understand how much data quality impacts business processes. Thus, each data source or database a company uses may have its own rules and standards for data quality. The issues, however, evolve as soon as the database integration started in order to get a unified look at, for example, company’s customers’ data.

Those issues may come out of the difference of data fields, for example, or data formats, so the same contact may be represented differently in different databases. Thus when it comes to database integration, it can’t be performed correctly due to those differences, which may lead to data duplication, and many more data quality issues. In fact, in the result of integrating several databases of poor quality, a company gets one big database of poor quality. This means that database integration was in vain, as it failed to achieve its main goal of providing the company with a general view of business data, while the integration expenses were significant.

Unfortunately, data quality technology does not always allow organizations to fix poor data. So, it’s much wiser to implement company-wide standards for data quality to prevent the appearance of data quality issues associated with integration of data from heterogeneous sources, then to perform data cleansing and other data quality procedures afterward.

November 27, 2009

Database Integration: Integrating Data from Multiple Sources to SugarCRM

Filed under: Data Integration, Data Migration, Database Integration — Olga Belokurskaya @ 6:27 pm

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

November 13, 2009

Preparing for SugarCRM Data Integration

Filed under: Data Integration, Database Integration — Olga Belokurskaya @ 10:39 am

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

September 9, 2009

How Database Synchronization Differs from Other Operational Data Integration Types

Filed under: Database Integration, ETL — Tags: , — Olga Belokurskaya @ 7:47 am

Being a type of operational data integration, data synchronization stays a bit apart from other operational data integration types, such as data consolidations, collocations, migrations, and upgrades.

They differ in terms of the main mechanism of dealing with data in databases.  While migration, consolidation, etc. suppose moving databases (either several databases to a single one, or a database from one system to another), synchronization suggests moving, exchanging data between different databases. The pluses of synchronization include:

  • Leaving database investments and the business processes that depend on them intact
  • Being extremely helpful when managing related databases which are impossible to consolidate (let’s take, for example Salesforce and QuickBooks synchronization).

However, the necessity to have a permanent integration infrastructure for daily data feeds (as it is mentioned in TDWI research), which is costly both in term of initial investments and maintenance, is the main drawback of synchronization.

In terms of tools, ETL is the most popular choice for database synchronization.

June 25, 2009

Open Source Data Warehouses: the Benefits

Filed under: Data Warehousing, Database Integration, Open Source — Tags: , — Olga Belokurskaya @ 1:21 am

Open source data warehouses possess the same options as any other types of open source software: the same model of licensing, community development processes, and same degree of openness. They may be offered as free downloads, or for a nominal flat fee, as fully supported systems. Or there may be no limit to the number of licenses and implementations a company may make with the software.

Acording to BeyeNetwork article, the benefits of the open source data warehouses are following:

  • Up front and maintenance costs are less than those of proprietary software. Besides, there is a possibility to customize the products companies use to improve their operations, for the original source code is open and may be downloaded.
  • Skill sets that are widely available in the market are employed.  As a result, an organization with existing database or data warehouse expertise will not have to look further when a new open source data warehouse project is put into place.
  • Improved standardization. Transparent and community supported open source code considers important standards to be consistently supported across all versions and implementations. Something that proprietary formats cannot and will not offer.
  • Flexibility which enables enterprises to expand the solutions to an unlimited number of users, with no per-user or per-processor charges of proprietary software packages.
  • Community effect. Open source solutions leverage communities of developers and innovators to advance development. New code and features are contributed back to the community, constantly increasing the range of new options available to end users.  Moreover, companies may address the community in order to fix any bugs or security flaws, which takes, normally, only days, instead of waiting weeks and months for the next security patch or service pack from a vendor.
  • Incremental implementation.  There is no need to a mega project at once. Projects can start small and build upon the success of implementations. This dumps the tendency to “overpromise,” which is often a necessary evil for acquiring optimal levels of funding for data warehouse projects.

June 16, 2009

What Makes Customer Relationship Management Projects Fail

Filed under: Database Integration — Tags: , — Olga Belokurskaya @ 5:54 am

CRM has become extremely popular in the recent years. Nowadays, many companies have accepted the importance of CRM and have made the decision to implement CRM initiatives enterprise-wide. However, a great deal of CRM strategies fails. In fact, those failures may be caused by different factors. But here are the most typical reasons of CRM projects failures:


  • Believing that CRM starts and ends with software. However, a CRM project means the right people executing the right processes, using the best possible tools at their disposal.

  • Under committing a CRM initiative – which means failing to invest enough time and attention to to develop a comprehensive sales process reengineering vision, settling instead for a series of minor, tactical changes.

  • Considering CRM to be a part-time project. A part-time approach will not generate part-time results; it will generate no results at all. For CRM projects require full-time assignment of people for the duration of the project, active executive involvement, full-time commitment of personnel, accountability for results, and a process-driven budget.

  • Having big expectations and small budgets. Trying to implement a CRM initiative as cheaper as possible is a mistake a company will regret very soon. A low-cost approach is extremely prone to failure. Starting a CRM initiative one should never focus primarily on price, but only on benefits.

  • Picking wrong technology partners is a mistake that occurs when a company bases vendor choice only on product features. However a company must thing about their specific sales process functionality needs.

  • Assuming that automating your sales process will be similar to automating manufacturing or finance. The degree of complexity in implementing a CRM system is significantly higher than the other two examples. Budgets should be provided for necessary upgrades and for ongoing system support.

December 3, 2008

Making an Asset of Your Data

Filed under: Data Cleansing, Data Quality, Database Integration — Alena Semeshko @ 4:26 am

Just found a piece from Robert L. Weiner Consulting on the database management. To brush up on your database management strategy, check if you have any of the typical mistakes listed below.

  • Lack of specified data entry policies and procedures, hence no one knowing and applying them.
  • Using Excel as if it were a database (JUST a spreadsheet, okay?)
  • Having no one in charge of data entry/data quality training.
  • Forgetting all about backups, or running them way too seldom.
  • Allowing staff to copy sensitive information onto portable devices and take it home.
  • Insufficient password security measures.
  • Error management? Lack of error management strategies, careless data handling by staff.
  • Keeping your user access rights and security options a mess.

Not about you? Then congratulations, your database management efforts don’t need a fix.

« Older Posts