Introduction: 

This guide is intended for Support professionals and System Administrators responsible for supporting a CRM system which uses the Sage SData Integration.


What is SData?

SData is the short form of Sage Data, the HTTP-based communication protocol used by many Sage product APIs and integrations.

SData is a web protocol that:

  • is standards-based, building upon the HTTP, ATOM and soon JSON specifications
  • conforms to the REST principles
  • is applicable to a variety of scenarios such as APIs, mobile apps or 3rd party integration
  • is free to use

For a full description of the SData specfication visit the SData website: http://sdata.sage.com/

Currently the SData integrations used by Sage CRM are based on the SData 1.1 protocol and they use the SData Linking and syncing protocol that is described in great detail here: http://interop.sage.com/daisy/sdataSync/LinkAndSync.html

The SData linking and syncing protocol is based on the vector clock synchronisation algorithm which is a well known technique used by many synchronised systems.

http://interop.sage.com/daisy/sdataSync/SyncAlg/526-DSY.html
http://en.wikipedia.org/wiki/Vector_clock

 

What is GCRM?

The Global CRM contract (GCRM contract) is a common approach to integration across all Sage CRM software. It was originally developed to standardise the way all Sage's CRM systems approach integrating with Sage ERP products. It defines a common set of functions designed to ease development of integrations with ERP products.

GCRM uses SData as its communication protocol and ERP products are required to implement an SData interface that conforms to the GCRM specification.
 
For more information about GCRM please visit the GCRM section of the SData website: http://sdata.sage.com/sdata11/sdatacust_GCRM.html


Terminology:

Endpoint

SData synchronises between endpoints. It works by obtaining a batch of resource changes from a source endpoint and then applying this batch of changes to one or more target endpoints

Engine

The “middle man” process that exchanges data between applications

Digest

XML structure that describes the synchronization state of a resource

Feed

XML structure used to transfer changes to resources between endpoints. Contains many entries

Entry

Subset of a Feed and contains changes to a specific resource, e.g “TradingAccount”

Tick

Incremental value for a resource type that is incremented every time a resource is modified. Represents the state of the resource.

How an SData Integration Works:
 
 
SDataSync

The SData integration is effectively a 10 step process:

  • The SyncEngine requests configuration information from the CRM side. 
  • The SyncEngine initiates an integration sync.
  • CRM requests the Schema from ERP.
  • ERP sends the schema to CRM.
  • If version number has changed (or this is the initial sync), then the schema (and customistaions.xml if present) are processed. The changes are made to the DB if necessary.
  • CRM ignores them if version number in Schema has not changed.
  • The synchronisation of data starts at this step. The SyncEngine requests the SyncDigest from ERP.
  • The SyncEngine passes this digest to the CRM (in the body of a HTTP POST request).
  • The engine then makes a request to CRM for the 'new' data.
  • The SyncEngine then POSTs this new data to ERP.
  • ERP sends back the results of the POST, including whether it passed or failed, to the SyncEngine.
  • The SyncEngine passes these results back to the CRM side and the cycle is complete.
     

Key CRM Integration Tables

GCRMIntegration
Holds the information about the integration itself

GCRMLocalEntityMapping
Holds the information about  entities that are sync’d, order of the sync, sync direction, enabled /  disabled

GCRMLocalFieldMapping
Holds mapping information about each field for an entity

GCRMLocalRelationship
Holds mapping information relationships between entities

SyncTick
Holds one entry for our global tick number

SyncDigestEntry
Generally holds 2 entries per resource, one for CRM, one for ERP

SyncResource
Holds an entry for every resource instance sync’d


Setting Up an SData Integration

Before beginning the initial set up of an SData integration you must take a backup of your CRM database. The changes made to the database are not reversible.

Setting up Currency Codes

To complete these steps, you need to start by making the change from symbol to code, for example, $ to USD, to the base currency. The base currency cannot be edited, therefore to make these changes to the base currency, you must first make it available as a normal currency. This is described in the first part of the steps below.

To set up ISO currency codes in CRM:

1. Select Administration | Data Management | Currency Configuration

The Currency page is displayed.

2. Select the Change button.

3. Make sure your desired base currency is not selected in the Base Currency field. Select any other currency as the temporary base currency.

4. To make use of multi-currency, set the Is Single Currency field to No.

5. Select Save.

6. Select Administration | Data Management | Currency.

7. Click on the hyperlink of the currency that will be your base currency. For example, US Dollar.

8. Change the symbol in the Symbol field to the ISO currency code. For example, USD. For the base currency, the Rate field should be set to 1.

9. Select Save.

 The updated currency is displayed in the list of currencies.

10. Select Administration | Data Management | Currency Configuration.

11. Select the Change button.

12. Reset the Base Currency field to the correct base currency.

13. Select Save.

You can now continue to edit the remaining existing currencies, or add new ones. In both cases, the Symbol field must contain the ISO code, not the symbol.

14. Select Administration | Data Management | Currency.

15. To edit an existing currency, click on the hyperlink of the currency.

16. To add a new currency, select the New button.

17. Enter the ISO currency code in the Symbol field. For example, EUR.

18. Enter the number of decimal places in the Precision field, and the current rate to the Base Currency in the Rate field.

19. Select Save.

The currency is displayed in the list of currencies.

 

Reviewing Proxy Settings

Proxy Settings allows System Administrators to set up a single generic user for proxy security for features requiring Internet access.

If the Proxy details were entered during the install process, the details are displayed here for editing.

Field

Description

Proxy Requires Authentication

Select if the proxy requires authentication.

GCRM Sync Engine Requires Proxy

Select if the GCRM Sync Engine requires a proxy.

Proxy User Name

Proxy user name. This field is required if the first check box is selected.

Proxy Domain

Proxy domain. This field is required if the first check box is selected.

Proxy Address

Proxy address. This field is required if the first check box is selected.

Proxy Password

Proxy password.

Proxy Port

Proxy port.

 

Reviewing Product Configuration Settings

To review product configuration settings:

1. Select Administration | Data Management | Products.

The Products tab is displayed.

2. Select the Product Configuration tab.

The OE Admin page is displayed.

3. Select the Change button.

The Use Pricing Lists, Use Units of Measure, Automate Opportunity Creation, and Order Level Discount fields are all read-only and are set to Yes.

The Sales Currencies Supported field retains the value that was present prior to creating the Integration.

The Quote Format and Order Format fields normally dictate the automated Quote and Order Reference number generation format.

This also applies to the Default Quote Expiration After field, which becomes read-only and is set automatically to 0.

4. Select the Save button.

The OE Admin page is displayed.

 

Activating Integration

To activate the Integration features in Sage CRM:

1. Select Administration | Customization | Component Manager.

The Components tab is displayed.

2. Select the Browse button, and navigate to the location of the SData Integration Component.

3. Select the Upload Component button.

The Integration Component is added to Sage CRM.

A successful upload of the Integration Component results in a new Integration menu button in the Administration menu.

Please refer to the System Administrator Guide for more information on working with the Component Manager.

 

Adding a New Integration
Before adding an integration, you must ensure the Apache Tomcat CRM service is running.

To do this, select Start | Control Panel | Administrative Tools | Services and check the status of the Apache Tomcat CRM service.
If it is not started, right-click the service name and select Start.
It is also recommended that you set the Apache Tomcat CRM service startup type to Automatic.

To add a new integration to Sage CRM:

1. Select Administration | Integration | New Integration.

The New Integration page is displayed.

NewIntegration

 

2. Enter the Integration details. The fields are described in the table below.

Field

Description

ERP Integration Name

Name of integration.

ERP URL

The URL used to locate the ERP system. The URL takes the format:

http[s]://<hostname>:<port>/sdata/<erpappname>/<contract>/<dataset>/

Note: Once the URL has been saved, it is not possible to change it.

ERP User Name

User name used to log on to the ERP system.

ERP Password

Password used to log on to ERP system.

CRM User Name

The CRM user name used when adding or updating data to CRM. User names should not include a semi-colon.

 

Select the Save button. This initiates a connection to the ERP system.

During this time the ERP system is contacted, and configuration and customization information is loaded into CRM. For example, all customizations required for the Accounts area for this particular integration are loaded into CRM.

On completion of a successful connection to the ERP system, the Integration Summary page is displayed. The Integration is disabled by default.

Integration Summary

If the connection is not successful, an error message and a Continue button is displayed. Selecting the Continue button returns you to the New Integration page.

If a connection is not successful, it may be for one of the following reasons:

? The username contains a semi-colon (;). The ERP REST-based web service uses the semi-colon to strip out the username of the CRM user who initiated the SData request, and including one could mean the username is truncated, meaning authentication could fail.

? The ERP server is not available. If the CRM system discovers that the ERP server is not available, the message "Failed to establish connection with ERP Server" is displayed. The error information is written to the Integration Log.

? The ERP server name contains underscores. If you have host names with underscores in them and do not wish to change them, a DNS mapping can be added to the server IP Address used for the SData integration. However, please note that adherence to industry standards for host names is the recommended solution.

? CRM cannot be authenticated by the ERP system. If CRM cannot be authenticated by the ERP system, the message "Failed to authenticate ERP Login credentials" is displayed. The error information is written to the Integration Log. Note: It is possible that the ERP username or password could get changed in the REST-based ERP web service without CRM being aware of these changes. If this occurs, the ERP system will be unable to authenticate CRM if the CRM system submitted an SData request to the ERP system.

? The $Schema could not be found. If the $Schema cannot be retrieved, an error message to that effect is displayed. The error information is written to the Integration Log.

? Either the $Schema or $Customizations file contains bad XML. If CRM fails to parse $Schema or $Customizations correctly, an error message is displayed. The error information is written to the Integration Log.

 

Enabling an Integration

To enable an integration:

1. Select Administration | Integration | Integration List 

The Integration List page is displayed.

2. Click on the hyperlink of the integration that you want to enable.

The Integration Summary page is displayed.

The Integration Summary page displays additional information about the Status and Configuration of the integration. The fields on the Status panel are read-only and are described in the table below.

 

 Field

Description

ERP URL

The URL used to locate the ERP system.

Sync Schema Version

Version of the sync schema.

ERP Metadata Version

The $Customizations version of the ERP web service at the ERP URL. Note: This could be the same as the global $Customizations version already stored in CRM if multiple integrations are present in CRM and the version of $Customizations for this web service is no different to the version previously parsed by CRM.

Conflicts in Last Sync

Number of conflicts detected at the last synchronization. The Conflict Log is stored in the Logs folder of the CRM install. For example: ..\Program Files\Sage\CRM\InstallName\Logs\DateIntegrationName.log

Last Sync Time

Time of last synchronization.

Next Sync Time

Time of next synchronization. Note: The number of CRM installs and the volume of data to be transferred could affect the time it takes for an integration to synchronize.

Sync Status

Status of last synchronization. This field can display Success, Failed, Success With Warning, In Progress, Waiting to Sync, or Interrupted.

 

3. Select the Change action button

Note : If the integration is already enabled, you must first disable the integration to change the details on the Configuration panel.

The Integration Summary page displays the Configuration panel in edit mode.

4. Enter the Configuration details.

The fields, which have not already been covered in the New Integration page section above, are described in the table below.

Field

Description

Integration Timeout (Seconds)

The length of time in seconds that CRM will wait for, while expecting a response back from the ERP web service method.

Sync Interval (Minutes)

The duration in time (minutes) between each synchronization. It is possible for this to be set to 0. This means that there is no delay between synchronizations. Defaults to 5. Note: The number of CRM installs and the volume of data to be transferred could affect the time it takes for an integration to synchronize.

Create New Company For Each Account

Select from Yes or No. Selecting Yes means that a new Company is created each time a new account is created by the synchronization process. Defaults to No. Note: If set to Yes, the account address is automatically shared with the company. For more information on address sharing, please refer to Chapter 3: Working with Accounts.

Default Territory

The Administrator can select the territory that will automatically be assigned to every new account created in CRM by the synchronization. Defaults to Worldwide. Note: Orders and quotes are created in the same territory as their parent account.

Default Account Manager

The Administrator can select the user who will automatically be assigned as the Account Manager to new account records created in CRM by the synchronization. The primary team of the selected Account Manager will be set as the default team of the new record.

Field

Description

Allow Users To Create Accounts In CRM

Determines how Accounts should be created and or linked to the ERP system. Options are:

Disable. Users are not able to create Accounts in CRM.

Yes And No Link. Users can create Accounts in CRM, but they do not get linked to the ERP system. The Link Account button does not appear.

Yes And Manually Link. A Link Account button is available on the Account Summary page in edit mode. When the user selects the Link Account button, the Account is linked to the ERP system.

Yes And Automatically Link. Accounts can be created in CRM. Once the user saves the Account record, it will be linked to the ERP system at the next synchronization.

Allow Deletion of Linked Accounts

The System Administrator can override the normal user security options and remove permission to delete any Accounts in CRM, which are linked to an ERP system.

Max Errors Allowed

The maximum number of errors allowed to be received by the synchronization service before the synchronization is stopped.

Price ERP Product In Multi Currency

Select from Yes or No. Defaults to No. Set to Yes, this means that products can be selected even if they are not priced in the currency of the Account, Quote, or Order.

Select the Save button.

The following action buttons can be selected from the Integration Summary page:

Continue. Returns you to the Integration List.

Enable. Enables the integration. Toggles to Disable after the integration is enabled.

View Log File. Displays a list of log files for the integration. This information can also be accessed from Administration | System | Logging.

6. Select the Enable button.

7. Click OK and, after having checked the service is running, proceed with enabling the integration.

It is necessary to enable an integration before it is possible to start syncing information between CRM and ERP. It is not possible to trigger a sync for an integration that is disabled or has never been enabled.

An Immediate Sync action button becomes available after an integration is enabled. This can be used to perform a manual sync rather than waiting for a scheduled sync to occur.

8. Select the Continue button to return to the Integration list.

 

SData Integration Logging

CRM 7.1

Log4j.properties file location:

tomcat\webapps\<installName>SyncEngine\WEB-INF\log4j.properties

# defaultLog - default catch-all

log4j.rootLogger=DEBUG, defaultLog

This enables full logging on all operations. This will log all SQL operations resulting in large log files in a short space of time.

Make sure to reset this value when debugging is complete.

#syncengine

log4j.logger.syncengine=DEBUG, syncengine

This enables full logging for the syncengine code

#synchronisation

log4j.logger.synchronisation=DEBUG, synchronisation

This enables full logging for the synchronisation communication

#stacktrace

log4j.logger.stacktrace=DEBUG, stacktrace

This enables the stacktrace logging (useful for developers)

 

CRM 7.2 & 7.3

The two most importand log settings for the SData intefration in 7.2 & 7.3 are listed below. One is located in the SyncEngine webapp the other is located in the "crmj" webapp. Change these settings to debug prior to reproducing an issue with the SDate integration.

Location: \tomcat\webapps\<installname>SyncEngine\WEB-INF\log4j.xml

    <!-- CRM LOGS -->

    <logger name="com.sage.scrm.syncengine.gcrm" additivity="false">

        <level value="ERROR"/>

        <appender-ref ref="synchronisation"/>

    </logger>

 

Location: \tomcat\webapps\crmj\WEB-INF\log4j.xml

    <logger name="com.sage.crm.gcrm" additivity="false">

        <level value="ERROR"/>

        <appender-ref ref="gcrm"/>

    </logger>

 

Enabling Debug Logs in the  eWare.dll

To enable debug SData integration logging for the eWare.dll update the Custom_SysParam table to set  the Integration Logging system parameter to 5

SQL: 

UPDATE Custom_SysParams SET Parm_Value = 5

WHERE Parm_Name = 'IntegrationLogging'

 

Useful Tools

It's possible to intercept the SData communication by using a web debugging proxy like Fiddler or a HTTP sniffer like HTTP Debugger.

Fiddler

Fiddler is available to download from here: http://www.telerik.com/fiddler

In order to use fiddler to intercept the SData communication in CRM 7.1 you need to set it up as the default proxy for the SData webapp.

To do this go to:

 CRM\Services\IISUtils\CRMRewriter\Web.config
 
Uncomment the default proxy section:
 
Description
Restart IIS and tomcat.
 
Example Fiddler Trace:
Description
HTTP Debugger
 
HTTP Debugger can be downloaded from here:

http://www.httpdebugger.com/download.html

Once you install and run the program it is ready to go, no extra configuration is required.

A 14 day free trial is available without purchasing a licence.

Example HTTP Debugger trace:

Description

Common Issues and Support Cases

The following knowledge base articles highlight some of the issues we've experienced with the SData integration and the resolutions of those issues:

 

536-17652 SDATA Sync error because of customizations: https://community.sagecrm.com/knowledgebase/w/onpremisekba/1259.536-17652-sdata-sync-error-because-of-customizations.aspx

536-17540 Issues SData Integration: Failed to create the configuration https://community.sagecrm.com/knowledgebase/w/onpremisekba/1198.536-17540-issues-sdata-integration-failed-to-create-the-configuration.aspx

536-17140 Integration fails after ERP update https://community.sagecrm.com/knowledgebase/w/onpremisekba/882.536-17140-integration-fails-after-erp-update.aspx

536-17653 GCRM Synchronisation issue: Error while getting configurations from CRM https://community.sagecrm.com/knowledgebase/w/onpremisekba/1260.536-17653-gcrm-synchronisation-issue-error-while-getting-configurations-from-crm.aspx