When building an integration between business systems, or even simply performing a migration from a business system to a flat file extract, a great way to find and extract the data you need is using an application publisher.

An application publisher is a purpose built tool that typically uses the API of the application to make available data from the systems objects or entities. Scribe Software offers a great application publisher for the Microsoft Dynamics CRM adapter. This publisher uses a couple of plugins that Scribe drops into the CRM application, which makes the entities and their fields available to build a record set to publish out of the application. This a very handy little feature. There are, however, limitation to what the publisher can put together as a record set. Because it is using the API of Dynamics CRM, what is being built in the background is a fetch XML that the API can then pass to the database. There are limits to how complex the fetch XML can be, how deep the joins can be, and the needed transformations that can be accomplished. Because of these limitations, the Scribe publisher might not be the way to go.

In this case, writing a stored procedure can be the best way to get around the application publisher’s limitations. For example, if the business is using CRM Connections to build out details of Account relationships, the connections structure can be somewhat complex to deal with. Also, you may need several Connection values ‘flattened out’ as a string to import into another system or even into a flat file recordset. The Scribe Application Publisher is just not designed to accomplish this scenario.

Using a stored procedure for your source record set does not limit you on how the record set is published, though. The stored procedure can be called as a source of the translation and give you exactly what you need as the source data for your translation. The stored procedure can be used as a source in a Scribe Query publisher, which I’ll talk about in a soon to be published blog.

It’s always best to keep it simple and use the most knowledge transferable methods as possible, when developing your solution. However, when need be, don’t be a afraid to branch out and dive into the stored procedure method. It can save the day.

 For more information about C5 Insight or this blog entry, please Contact Us