Microsoft® Dynamics GP Integration
Microsoft Dynamics GP, formerly known as Great Plains, is an accounting and Enterprise Resource Planning solution. CSM integrates with Dynamics GP so that Dynamics GP Customer data can be shared with and used within CSM (ex: Populates a Customer Record).
How the Integration Works
Shared external data is either imported or linked. Both imported and linked data is accessed using an External Connection that you create; however:
- When you import external data into CSM, you import it into the Cherwell database. From that point forward, the records can diverge, although data can be re-imported (entirely replacing existing data or appending/updating changed data) if desired. Re-imports can be run manually or they can be regularly scheduled using the CSM Scheduler. A Business Object keeps track of the external data and facilitates viewing/updating that data in CSM.
- When you link to external data, you view (and sometimes update) the external data in CSM but it continues to reside in the external database. A special External Business Object keeps track of the external data and facilitates viewing/updating that data in CSM keeps track of the external data and facilitates viewing/updating that data in CSM.
Typically, Dynamics GP Customer data is imported into the Customer-Internal Business Object. CSM provides several wizards to walk you through the steps to create the External Connection and import the external data.
Note: Dynamics GP utilizes a normalized Microsoft SQL Server (MSSQL) database (data is spread across many tables). Before CSM can share data in a normalized external database, you might need to create one or more database Views to collect, combine, and filter the information you are interested in sharing (importing). You can create the Views in the Dynamics GP database or in the CSM database. If you choose to create Views in the CSM database, you might need to create a Linked Server.
When sharing data with Dynamics GP, you will most likely want to:
- Create a View of the Dynamics GP Customer data.
- Create an External Connection (SQL Server) to the Dynamics GP data.
- Import the Dynamics GP Customer View into the CSM Customer-Internal Business Object (after mapping he Customer-Internal Business Object to the View).
Steps to Integrate
Perform the following high-level steps to import Dynamics GP data. For detailed step-by-step instructions, refer to the Create an External Connection to an External Database section in our online help.
Note: Most steps are performed in CSM Administrator, within a Blueprint.
- Create a Customer Information/Profile View of the Dynamics GP data to which you want to connect.
Note: You can create the Views in the Dynamics GP database or in the CSM database. (If you choose to create Views in a CSM database, you or your onsite DBA might need to create a Linked Server in SQL.)
- Create a Blueprint.
- Create an External Connection to the Dynamics GP data. Data is either in the external Dynamics GP database or in the database Views, which are accessed through a Linked Server. Accessing a Linked server depends on the configuration of your CSM.
Use the External Connection Wizard (CSM Administrator>Blueprint>Tools>External Connections) to create an External Connection. Typically:
- Data Source: Select SQL Server.
- Database Location, Name, and Login Options for the Dynamics GP database: Ask your DBA.
- Database Owner or Schema: Auto-populated by your database selection.
- Pooling Options: Keep defaults.
The new connection is shown in the External Connections window.
- Map each CSM Business Object to the appropriate Dynamics GP View:
Use the External Data Wizard (CSM Administrator>Blueprint>"Business Object">Map to external Data) to map the data. Typically:
- Import vs. Linked: Click Import Data.
- Data Source: Select the External Connection you just created.
- External Table to Map: Select the View in your external database.
- Fields to Map: Map each individual field from the View to a field in the Business Object.
- Unique Key: Varies (uses the field mapped to the Unique ID value).
- Timestamp Fields: None.
- Import the SQL View into the appropriate CSM Business Object.
Use the External Data Import Wizard (CSM Administrator>Database>Import External Data) to import the data. Typically:
- Select Business Object: Select the Customer-Internal Business Object.
- Existing Records: Select Update Existing Records.
- Choose Filter: Select All Records (typically, data in a View is already filtered).
- (Optional) If you have additional mapped data, you might want to edit the Business Object Forms and Grids to the display the additional information.
Use the Form Editor to contain controls that show (and allow editing) of the values in the fields, and the Grid Editor to define how the Business Object is displayed.
- Publish the Blueprint.