Rules

Secret ingredients to quality software

Edit
Info

Do you know the 6 ways to integrate your CRM 2011 data into SharePoint 2010?

Last updated by Tiago Araújo [SSW] on 18 Mar 2021 07:28 pm (about 2 months ago) See History

You have data in CRM 2011, so how do you see it in SharePoint? The data that is stored in CRM entities should be available in SharePoint so users can find and use the data in areas such as:

  • SharePoint search
  • SharePoint reporting (if you are using SQL Reporting Services in integrated mode)

There are many ways to get to this data, let's go through them:

Option 1: SharePoint BCS Adapter (provided by the CRM Team) RECOMMENDED

This BCS Adapter for CRM 2011 is from the CRM team (It does all of the BCS work for you by interrogating the CRM metadata service).

Summary: SharePoint BCS > Pre-built Adapter (.NET Assembly) > CRM web services > CRM database

Pros Cons

|

clip_image002[8]
Read/Write

clip_image002[9]
Minimal coding

clip_image002[10]
Easiest to implement

clip_image002[11]
The likely way forward (Best Practice as Microsoft) |
clip_image004[13]
Needs to be deployed and published to the web server.

clip_image004[14]
Less performance than SQL filter views directly

clip_image004[15]
Only recently released. |

figure5

More information:

  • Download from Microsoft
  • Read "Connecting to CRM Online 2011 with SharePoint 2010 Business Connectivity Services"
  • Run tool to generate the XML mapping (.BDCM)
  • This solution uses a BCS Connector – a .NET Assembly responsible for mapping external data into a form usable by SharePoint. This component is loaded and hosted within SharePoint 2010 and communicates with CRM via the CRM Proxy Service.

Option 2: SQL Server Filtered Views

CRM recommends that you *don't* read from the Tables, so they provide SQL Views for this purpose.

Summary: SharePoint BCS > CRM database

Pros Cons

|

clip_image002[2]
Best performance

clip_image002[2]
Codeless |
clip_image004
Read-only

clip_image004[1]
Not available for hosted CRM

clip_image004[2]
Security issues as you are exposing the view. |

Filtered Views in Microsoft CRM provide access to the data available that supports providing picklist name and id values (lookup tables).

More information:

If you only want read-only for CRM on-premises data for SharePoint users, this solution is fine. You create the External Content Type directly against the Filtered Views in the CRM database as per Use SQL and Filtered Views to Retrieve Data for Reports .

figure1
Figure: The result of "SELECT * FROM FilteredCtx_Project". Use Office SharePoint Designer to hook this up

Option 3: Web Services

CRM provides web services.

Summary: SharePoint BCS > Code calling CRM web services > CRM database

Pros Cons
clip_image002[3]
Read/Write
clip_image004[3]
Needs lots of code and test work.

clip_image004[4]
Needs to be deployed and published to the web server.

clip_image004[5]
Less performance than SQL filter views directly #1 |

Note: Performance could be improved by making the reads from the views and the writes through the web service

More information:

  1. Use BCS in VS 2010
  2. Write code that calls the CRM web services (that access the CRM data)
  3. Test
  4. Deploy

Option 4: Expose OData from CRM as RSS

The CRM 2011 OData Query Designer can be used to build queries to expose the data from CRM as RSS

Pros Cons
clip_image002[4]
Easy configuration
clip_image004[6]
50 records limit. Need to page through the results.

clip_image004[7]
Possible issues with firewalls and proxies because it uses Integrated Security for authentication.

clip_image004[8]
Read-Only

clip_image004[9]
No easy way to consume |

Note: You can really only call the OData endpoint from an application that already has an authentication cookie with the CRM server. i.e. you can't impersonate and call it like you can the standard WCF endpoints So it is really only suited to calling from Silverlight and JavaScript web resources that are delivered inside CRM (because they have the cookie)

More information: The first step is to expose the data:

  1. Install http://crm2011odatatool.codeplex.com
  2. Make a query
    figure2
    Figure: Designing a query
  3. See the data
    figure3
    Figure: See the data - RSS source for xtc_countrySet

The second step (and the problem) is consuming the data

figure4
Figure: BCS has no option to consume RSS data. Please Microsoft SharePoint Team, we need a new 'Data Source Type' = OData

In summary, CRM 2011 can expose OData, but SharePoint 2010 BCS doesn't consume OData.

The 3 options to consume the OData/RSS data:

  • Consume the OData by SQL Server, via TSQL ??? Then use BCS to call SQL Server. Summary: SharePoint BCS > DataSourceType: SQL Server > OData > CRM database

You would need to be crazy to go down this route Calling a Web Service from T-SQL (Stored Procedure) using MSXML RSS

  • Consume the OData by a BCS adapter + code calling web services (same story as above). Summary: SharePoint BCS > code calling OData > CRM database
  • Consume the RSS by "SharePoint RSS view web part" directly. Summary: SharePoint RSS view web part > OData > CRM database (not recommended as it could not be used in "SharePoint Search")

So OData is all things horrible because it is hard to eat :-(

Option 5: BizTalk

Biztalk is built for mapping systems together, unfortunately, this solution is only considered for large enterprises.

Summary: SharePoint BCS > BizTalk Database > CRM database

Pros Cons

|

clip_image002[5]
Read/Write

clip_image002[6]
The BizTalk data centre can also provide data for any system.

clip_image002[7]
Requires little code if users already have BizTalk |
clip_image004[10]
BizTalk :-)

clip_image004[11]
Deployment - Needs external work to deploy BizTalk server.

clip_image004[12]
Licence Cost |

Option 6: OData 3rd Party solutions (doesn't exist)

Today SharePoint 2010 exposes lists and document libraries as OData, but does not natively consume OData.

What does this mean?

  • CRM 2011 exposes it data as OData, but cannot consume OData
  • SharePoint 2010 exposes it data as OData, but cannot consume OData ...and there are no 3rd party solutions to solve this...
Adam CoganAdam Cogan
Adam StephensenAdam Stephensen
William YinWilliam Yin

We open source. This page is on GitHub