Scribe Software Blog

Introducing the 5 Capabilities of a Collaborative Integration Platform

In my previous blog post, I spoke of the changing role of the partner brought on by the advent of cloud computing. In this new world, partners are moving away from a reliance on 'systems integration' and moving towards creating value-added industry focused solutions. A critical component to these solutions is the need to help companies make sense of the integration requirements to other business applications and data sources. When developing an integration approach, it is important to recognize the different and evolving roles between the partner and customer throughout the phases of an application deployment. Even if there isn't a partner involved, in many cases IT organizations act in a similar capacity as partner to the business to deliver a successful CRM project. A collaborative approach to integration between "partner" and "customer" best serves the integration needs of companies deploying these business applications.

It is rare that one person has all of the skills and knowledge necessary to fully implement an integration solution. In a CRM integration project for example, the development of the integration requires a solid understanding of the design of the CRM system and the key customer processes that drive the integration need. It also requires detailed knowledge of the other applications, many of them complex back office applications. The ability to allow multiple people to participate in the development and design of the integration solution in a coordinated manner is essential. Each of the parties should be able to design their discrete piece and simply fold it into the broader solution. The other members of the project team should be able to easily understand the work others have done and should be able add their input and comments. Team members that have moved onto other projects should be able to easily move back into an active role.

So beyond these team development capabilities what does a collaborative approach to integration really mean? Over the new few weeks I will be exploring the 5 capabilities of a collaborative integration platform. This exploration should shed some light on the direction we are headed with our new integration platform, Scribe Online, as well as some of the continued investments we will be making in Scribe Insight. So watch for my next post as we drill down on the first capability; Reusability.

Have Scribe SAP Integration Questions? New location to find it all!

As most of you know, Scribe can connect to virtually any endpoint.  In fact, the vast majority of our  Microsoft Dynamics customers use Scribe to connect to a multitude of non-Dynamics endpoints  including  databases/data warehouses, web stores, and in many cases back-office ERP systems.  As Dynamics CRM becomes more widely accepted and selected globally by enterprise level companies, so to does Scribe, due to our high attach rate to these projects.  And which ERP system do we find typically deployed throughout these larger organizations?  SAP. 

SAP has become a prevalent end point to which Scribe can publish and consume data.  As a result, the list of customers that use Scribe for their SAP<->CRM data driven needs is growing, as is the number of  requests from partners and prospective customers asking how Scribe can effectively address this use case.  Therefore, we have decided to launch a page on our website to address this specific topic.  You will find it here:  http://www.scribesoft.com/sap/

Please do not hesitate to send your customers, prospects, and team members to this page when they have questions pertaining to Scribe’s fit with SAP as an endpoint.  I believe the site will go a long way in clarifying our capabilities, answering your questions, and providing 24x7 access to pertinent Scribe/SAP information.

As always, fell free to contact the Scribe sales and partner management team if you have any questions. 

Here’s to a great 2011!

Using Web Services for Event-Based Integrations

The Scribe Adapter for Web Services is the Swiss army knife of adapters. You can pull it out of your bag of tricks to build sophisticated integrations to a wide array of applications and data sources. It does have a significant limitation – The Web Services Adapter can call out to SOAP-based web service providers. It cannot, however, act as a web service provider itself.

What the heck does this mean? Scribe Insight, through the Web Services Adapter, can initiate a request to a web service to query data or update data, but Scribe Insight does not operate as a web service provider. Other consumers of web services cannot initiate a web service interaction with Scribe Insight. Fortunately, this limitation is easily overcome using a simple easily-deployed custom web service.

Consider the following scenario faced by a Scribe partner – that partner wanted to build an event-based integration to send information from SAP/R3 to Microsoft Dynamics CRM. The partner could have built an integration process that executed a DTS with a source connection to a Web Service to poll the SAP application every couple of minutes, but the customer wanted the integration to be event-based – that is, as soon as records changed within SAP/R3, the integration should send that new or updated record over to Dynamics CRM in real-time or near real-time.

If you have a need to accomplish such a task, you can deploy a custom web service such as the following:

namespace SampleQueueService

{

    public class SampleQueueService : IQueueService

    {

        private const string ScribeInQueue = @"<your env>\private$\scribein";

 

        /// <summary>

        /// Sends a label and XML formated string to a Queue

        /// </summary>

        /// <param name="label">Mesage Label</param>

        /// <param name="xmlData">Message Body</param>

        /// <returns></returns>

        public bool SendToQueue(string label, string xmlData)

        {

            bool success = false;

            MessageQueue scribeInQueue = null;

 

            try

            {

                scribeInQueue = new MessageQueue(ScribeInQueue);

                scribeInQueue.Send(xmlData, label);

                success = true;

            }

            catch (Exception)

            {

                success = false;

            }

            finally

            {

                if (scribeInQueue != null)

                {

                    scribeInQueue.Close();

                }

            }

 

            return success;

        }

    }

}

For a more easily-read format to see this sample code, view the code gist.

This custom web service simply receives an XML message and in turn publishes that message onto the Scribe InQueue MSMQ queue. This web service can be easily deployed to an IIS web server.

Through this technique, you could write an integration such as shown below:

This diagram depicts the following steps:

1. The SAP NetWeaver Process Integration (SAP PI), formerly called AP Exchange Infrastructure (SAP XI), detects changes as they occur in SAP, formats those change events as XML messages in a format expected by Scribe Insight, and posts those messages to the custom-built web service as shown above.
2. The custom web service receives the XML message and deploys it to an MSMQ queue.
3. A Scribe Insight Integration Process, configured to pull messages from a queue-based source, reads the incoming XML, performs any manipulation or transformation required, and updates Dynamics CRM via the Scribe Adapter for Dynamics CRM.

As you can see from the above code sample, the logic is fairly straight forward for a .NET programmer to customer the sample web service to tailor it to suit your needs.

Guest Post: CRM/SAP Integration using Scribe

This post is a continuation and expansion on the original blog post published on the personal blog (http://www.mohamedibrahim.net/blog/tag/scribe) of Mohamed Ibrahim Mostafa of CIBER UK.

-----

My name is Mohamed Ibrahim Mostafa and I work as a senior consultant for CIBER UK, a Microsoft Gold Partner, SAP Gold partner and Scribe Reseller based in the UK specialising in systems integration and implementations of a wide range of solutions based on Microsoft technologies among others. In one of our projects, we were required to implement an integration solution to connect a SAP system with Microsoft Dynamics CRM 4. There are many ways to achieve such integration but we finally made the decision to use Scribe for several reasons among which is the fact that Scribe Insight provides the interfaces and full control for the client to carry on supporting their own system once we complete the integration.

Scribe provides a native business application adapter for Microsoft Dynamics CRM as well as standard connectivity adapters for Open Database Connectivity (ODBC), SQL Server, OLE DB, Web Services and XML. Hence, whatever your SAP setup is, you should be able to access it smoothly from within Scribe. Scribe's CRM adapter also offers a great feature which is the possibility to access partner hosted CRM 4 implementation even in the case of a shared CRM implementation where each CRM organisation on the same server belongs to a different client of the partner host.

To connect to a hosted CRM you should use the Web Form (IFD) authentication option in the CRM adapter connection option. You can input details in the login similar to the example below:

Figure: Connecting Scribe to remote CRM hosted server

In our project, we had SAP as the data source and Dynamics CRM as the target of the integration. Configuring the source fields is very important for the mapping process between source and target fields later. You can always edit the source field names, data type and lengths by editing the QETEXT.INI file. So in case of text source files, if the field names are not meaningful and data types need to be changed (such as NUMERIC to VARCHAR), you have full control of adjusting these from the QETEXT.INI file. Just close the Scribe workbench window and open the file in notepad or any similar text editor.

To plan and perform this integration, Scribe identifies 5 major steps required for such integration in this white paper. These are: Data extraction, Translation, data update, automation and then monitoring and management of the ongoing integration (usually using Scribe Console). The figure below shows the various steps for this integration.

1. Data Extraction: This will require direct access to the source applications via a database or a proprietary application programming interface ("API",) from enterprise applications such as Dynamics GP, JD Edwards, SAP which is our case here, MAS 90/200/500, and Siebel. This will also require the ability to capture net changes either through source queries or via published messages from the source where available.

    

2. Data Mapping and Translation: It is most likely that the semantics and format of many fields from the source data will be different than those in Dynamics CRM. Important required tasks include parsing and concatenating text fields, performing date and numeric calculations, executing conditional logic, and performing lookups to resolve synonym values. There will be also the need to maintain a cross reference of primary key values in order to apply updates from one record in your source to the corresponding record in Dynamics CRM. We will discuss some of these data manipulation examples with sample formulas for your Scribe workbench project (DTS).

    

3. Data Update: This capability is the most crucial, yet complex area of the integration task. Capabilities we should look for include (i.) avoiding duplicates using fuzzy logic (like comparing elements of the company name and zip code to look for an Account match) for record lookup, you can also use CRM duplicate detection rules to prevent duplicates, (ii.) performing insert and update operations against multiple objects within Dynamics CRM when processing a single source record, and (iii.) performing all target processing against the Dynamics CRM integration API to ensure that all data imported has been validated by Dynamics CRM's application rules.

    

4. Automation: This is where utilising a customisable template model that incorporates a one-step process from source to target is very useful. Once we have designed the business process, we will need to implement an automated event detection mechanism to initiate an update to Dynamics CRM.

    

5. Monitoring and Management: Once we have developed and implemented our data aggregation solution, we need to consider the ongoing management of the solution as well. We need to be able to (i.) remotely support the solution (including start and stop processes, diagnose errors, etc.) via a web browser, (ii.) automatically alert an administrator via email when processes fail, error, or produce abnormal data conditions, and (iii.) scale across multiple processors to support high volume data scenarios.

This summarizes the steps that you will need to take and follow to be able to achieve the full integration between the two SAP and CRM systems.

The following diagram depicts Scribe's open architecture for data interchange between CRM and SAP.

This open architecture enables a broad range of SAP customers, regardless of what version of SAP they are running or how they have configured their implementation, to be able to use Scribe to integrate with Dynamics CRM system.

Sample Data Manipulation Formulas:

Data coming from SAP could be of various types and forms depending on the SAP architecture and the configuration carried out during implementation. To integrate and upload this data to CRM, you will need to carry out various data translation, mappings and manipulation to be able to correctly update CRM fields, create new records and performing lookups to capture CRM's update records unique identifiers (GUID's) based on information received from Scribe.

1. Unique Identifier Lookup sample formula:

   For Scribe to lookup the GUID of a record, a unique Identifier is needed to return this value. So for example, to get the GUID representing a CRM Account record, we can use the account number as the unique identifier:

   DBLOOKUP (S1, "T", "account", "SAP_AccountNumber", "accountid" )

    

   Where S1 is the first Source field, T= Target, SAP Account number has the unique account number for the customer from SAP and accounted is the CRM GUID for the account record.

1. Country Lookup sample formula:

   If SAP is sending the country value as a two digit code such as GB for Britain and US for USA, etc. You will need to create a new entity in CRM called countries that can act as your lookup table that corresponds to SAP's country lookup table with at least two fields in each record: country code and Country Name (country friendly name. Attribute name is usually the default "name" attribute with any new entity). The formula can look like this:

   DBLOOKUPCached(S2, "T", "SAP_country", "SAP_name", "SAP_countryid")

   This will return the lookup to the country that corresponds to the country code in the source field.

    

   You can do the same for language for example where your formula returns a pointer to the language entity record that has the language code corresponding to what SAP has passed on in the source fields.

   DBLOOKUPCached(S3, "T", "SAP_language", "SAP_languagecode", "SAP_languageid")

    

2. Picklist sample formula:

   To update a CRM picklist based on data coming from SAP, you need to use a text file which you can call it whatever you want (such as: sap_picklist.txt) but you must ensure the file is in the same folder as your scribe dts project file. So if we have a list of department codes coming from SAP, our picklist file can look like that:

   /*Contact Person Department */

   [Department]

   1=1

   2=18

   3=53

   4=4

    

   So if SAP sends a record with department = 1, this will correspond to the CRM picklist item with id = 1. If it is department 2 in SAP, then this will be CRM picklist item with id = 18, etc. This is all of course depending on your CRM picklists configuration.

    

   The formula in this case will simply look like this and Scribe will do the rest for you:

   FILELOOKUP(S4, "cp_picklist.txt", "Department" )

3. Radio button sample formula:

   Cases where SAP is sending a Boolean value (e.g. true/false, yes/no or 1/0). For example, if data coming from SAP represents the value of "send marketing material" field in CRM Contact entity, the following formula can map the value to CRM:

   IF((S5="Yes")=FALSE, 1, 0)

   i.e. if SAP sends Yes in the record, select the "yes" value for the CRM attribute.

    

   SAP might be also sending "1" or "true", so the formula can be:

   IF((S5="1")=FALSE, 1, 0) OR IF((S5="True")=FALSE, 1, 0)

    

   You can also add a small check if the value is sent blank, to set it to true by default then:

   IF(ISERROR(S5) , 0, IF((S5="Yes")=FALSE, 1, 0))

    

4. Checkbox sample formula:

   If a check box value is coming from SAP is sent as 1/0 then the formula to map to CRM should be: IF( S6="1", 1, 0 ) or in case of True/False: IF( S6="True", 1, 0 )

    

This post is a continuation and expansion on the original blog post published on the personal blog (http://www.mohamedibrahim.net/blog/tag/scribe) of Mohamed Ibrahim Mostafa of CIBER UK.

CIBER is an international IT provider supplying high quality solutions to customers in the private and public sectors all over the globe. Operating in 18 countries, with 8,000 employees and annual revenues of approximately $1 billion, CIBER serves its customers from 20 offices in Europe, 60 in the US and 4 in the Asia Pacific region. For enquiries about CIBER services please email: enquiry@ciber-uk.com or contact +44 (0) 870 000 0204.