Next week I’ll have the honour of bluffing my way through a talk for the Geneva SharePoint User Group (SUG) about integrating Dynamics CRM and SharePoint and trying to pull off that “look like you know what you’re talking about” vibe. To steer them away from my frightening lack of knowledge about all things SharePoint, I decided to go beyond the “out of the box” CRM integration options (of the SharePoint List Component and configuring Dynamics to use SharePoint as a document repository) and show them something a bit different – Power BI being used to link SharePoint and CRM data and report on it.
This then is a walk-through of doing just that, based around a dummy demo scenario, to give you an idea of how the data modelling of Power Pivot can be used to integrate the two data sources and provide a different take on CRM – SharePoint integration. It assumes a background level of knowledge on how you work with Power Queries in Excel as we’ll be using: –
- Dynamics CRM 2013 (online)
- SharePoint online
- Excel 2013 Power Query / Power Pivot
- Power BI online site
The Kev-tastic scenario for my demo was that we have groups of attendees booking to attend my talk and for each group there has a principal contact who provides some documents that need to be stored on SharePoint (maybe their booking information or travel arrangements). The data modelling is therefore: –
- A custom CRM entity called Attendee Group.
- A link from Attendee Group to the “out of the box” standard CRM Contact entity as the “principal contact” for that group.
- Contacts are enabled to store documents on SharePoint e.g. we have a CRM and SharePoint that are already linked using the default functionality described on hundreds of other blogs.
We want some reporting on how many documents have been submitted per Attendee Group, not an easy question to answer from CRM where we have no entity tracking the actual individual documents on the SharePoint side.
I set up a trial Office 365 Dynamics CRM instance for the demo and added SharePoint to it and Power BI. I input some demo data so that Attendee Group and Contact were populated within the CRM and there were some Contact-linked documents in the SharePoint instance.
The walk-through then will deal with: –
- How do we get the data?
- How do we link it once we have it?
- How do we use it once it is linked?
1 – Getting the CRM Data
The critical thing for all of this to work is that CRM and SharePoint bounce provide web service endpoints that you can connect to from MS Excel to grab their data. Here’s how: –
- From within MS Excel you go to the “Power Query” tab, the “Get External Data” section of the ribbon bar and then use the “From Other Sources” drop-down icon to select the option “From OData Feed”.
- This prompts you for the URL you found at Step 1 and you need to ensure that you specify “Organization” authentication and have entered your Office 365 credentials.
- If all goes well, Excel will query the service and provide you with a list of its tables from which you can multi-select the ones you’re interested in copying in. In my case it was the ContactSet data and my custom AttendeeSet entity (prefixes in the screenshot below by the CRM publisher I used to create this custom entity – avws).
- I then performed a bit of clean up within MS Excel to ensure that I was only retrieving the columns I was interested in and that things like the Contact ID were retrieved rather than the default “Record”. This work is performed from within the Query Editor in Excel, you use the ribbon bar to select the columns you want, click on column headings to resolve data that shows as “Record” into actual IDs and so on.
2 – Getting the SharePoint Data
So great, at this point we have data from CRM loaded in to Excel. The next step is to do the same thing for SharePoint.
- Again, within Excel within the “Power Query” tab, the “Get External Data” section of the ribbon bar and the “From Other Sources” drop-down icon there is a “From SharePoint List” option which should, in theory return the lists that you need.
- In my case this didn’t work however. I saw the default “out of the box” SharePoint lists but not the ones that CRM had automatically created within SharePoint.
- The workaround for this then (if you have the same problem), is not to use “From SharePoint List” but to use “From ORest Feed” again (as we did with CRM) and use the URL https://%5BYOURCHAREPOINTSITE%5D/_vti_bin/listdata.svc to then happily return all the lists which CRM has created within SharePoint for you.
- So at this point I could now bring back the data of the SharePoint site and I can then see the lists that CRM created there including, in my case, the “Contact” list that I am interested in as it will be the location of all documents related to CRM’s Contact entity.
3 – Linking the Data
As a quick demonstration I wanted to link the CRM data to the SharePoint data to show a query that would span information from both sources. The key challenge was to find something to link them therefore.
I believe it was the Spring release of CRM that modified the behaviour in SharePoint so that the GUID of the CRM record now appears in the SharePoint List folder name. If I have a contact called Fred Bloggs then his documents will be stored under Fred-Bloggs_123456789012345612, for example. All I needed to do was to strip out this GUID from the SharePoint data and I could then use that for the link.
- So first I had to go to my Power Queries within Excel and use the “Split” function to split the SharePoint “Path” column in to two columns based on the location of the underscore. This isolated the GUID part.
- This took my original “Path” column and created two that I named Path.TextPart and Path.GUIDPart.
- I then noticed that the GUID I had split from the SharePoint source was a string of numbers only but that in my CRM data source the GUID was separated by dash characters e.g 123456789012345612 in SharePoint and 1234-5678-9012-3456-12 in CRM. I therefore needed to add a Custom Column to the CRM source that took the GUID and stripped out the dashes with a Power Query expression. I did this by editing the Power Query for the CRM Contact data adding a custom column and using the Power Query expression Text.Remove([ContactId], “-“)
- At this point the data sources were in an acceptable shape to begin linking them.
- Within MS Excel I went to the “Power Pivot” tab and the “Manage” icon.
- I switched this in to diagram view and could see my three data sets – the Attendee Group and Contacts from CRM and the Documents from SharePoint.
- I could then wire up these three sources to use the Contact GUID common between them.
- I now had a basic data model spanning CRM and SharePoint data.
4 – Using the Data
All of this would have been pretty pointless unless I had some appealing “eye candy” to show the attendees at next week’s talk. With my data model in place I could then: –
- Create Power View visualisations that, for example, showed me the count of documents in SharePoint per CRM Contact or, going further, a count of documents per country that the contacts were from.
- Power View and Power Map were both used to try to “mash-up” the source information into something attractive that rammed home the point that the reports were pulling together SharePoint and CRM information in a way that would be a bit complicated to do from either CRM or SharePoint alone.
5 – Conclusion
For about two hours of effort (including a fair amount of Googling and restarting Excel when it crashed), it was straightforward to consume SharePoint and CRM data via their ORest service endpoints and then use the GUID encoded within the SharePoint folder name to link document data back to its parent CRM record. As a final twist, I published the Excel report up to a Power BI website and then created a custom CRM dashboard containing an IFrame that pointed at the report URL so that the data on document usage was delivered directly (and seamlessly) within CRM.
In October 2014 I will be giving a talk to the Geneva SharePoint User Group about the integration options between Dynamics CRM and everyone’s favourite document management repository. It’s a rich subject with a lot of blog posts, Microsoft white papers and general articles and so here is a condensed version if you’re looking for a quick overview of the fundamentals of integrating the two – both the “out of the box” features and the possibilities for extending the functionality with custom code.
I have included a list of references at the end to draw them in to one place and my material for the presentation (including a PowerPoint overview) is available on OneDrive here: –
First a word about the versions of SharePoint that are compatible with CRM: –
|Dynamics CRM 2011||Dynamics CRM 2013|
Option 1: Do Nothing
Simply, if we do not perform any integration then SharePoint cannot access/benefit from any of the data in CRM and CRM cannot use SharePoint as a repository for its documents and the only option for working with files will be to attach them to CRM Activity or Notes. Files attached in this way will, of course, not be searchable.
Option 2: Out of the Box
CRM 2011 and 2013 add slick “out of the box” integration options to get CRM and SharePoint working together and these are configured via the CRM Settings > Document Management page. For CRM 2013 online a pop-up alert appears at the top of the page nagging you to set this up.
Essentially you point your CRM at a SharePoint instance and select whether you want to create folders for each entity type or create sets of folders underneath a Contact / Account parent node. Whenever you then wish to associate documents with your CRM record, a SharePoint folder is created (if one does not exist) and the uploaded material is placed there. A recent enhancement in the Spring 2014 release of CRM is that the SharePoint folder now contains the CRM record GUID to make this easily available to any SharePoint code.
To present the documents stored on SharePoint within CRM there is the free CRM List Control. This gets installed on the SharePoint server (under Site Settings > Web Designer Galleries > Solutions) and presents the list of documents in a style consistent with CRM’s look and feel. If this List Control is not installed then CRM simply renders an IFRAME to show the SharePoint contents.
With CRM 2011 and (I believe) 2013 on-premises you need to download and install the List Control yourself but if you’re working with CRM and SharePoint online then the List Component is not required and direct server to server communication can be enabled instead.
Option 3: IFRAMEs
Another nice simple option, you can expose CRM pages within SharePoint and vice-versa by simply setting up IFRAMEs and referencing the appropriate URL. This is particularly useful when you want to deliver CRM Dashboards or SharePoint document lists that are not linked to CRM data.
Security considerations are important here t ensure that the authenticated user works across both sites.
Option 4: SharePoint Business Connectivity
SharePoint includes the Business Connectivity Services to allow SharePoint Team Sites to display and work with data from a range of other sources. You’ll need the free SharePoint Designer to configure this but the basic steps are to create an External Data Source and set up the Lists and Forms that SharePoint will use to display the data retrieved.
I set up a demonstration for CRM 2013 on-premises and SharePoint 2010 by setting up a SQL Server External Data Source referencing the CRM database. The only supported way to work with data within the CRM database is via the Filtered Views and so this method can be used to present Read-Only data but not to allow Updates and Deletions of CRM data from SharePoint.
Option 5: SharePoint Search
The previous example of using the Business Connectivity Services can be extended to set up a SharePoint Basic Search Centre relying on the External Data Source of a CRM database. This is particularly useful if you want to search multiple entities within the CRM with SharePoint.
Option 6: Power BI
Although it strays a little from the subject of SharePoint and CRM integration, it is worth noting that Excel 2013 comes with an ORest connector that can be used to pull CRM data in to Excel for Power Viewm Power Pivot and Power Map visualisations. Data Models can be built and data from several sources linked within Excel to provide a “mash up” of CRM and SharePoint data.
The final Excel containing the data model and its reports can either be uploaded to a SharePoint site document list and opened with Excel online or can be published to the dedicated Power BI site offered as a service via Office 365.
Power BI looks set to become then the quickest and easiest way to deliver reports using both SharePoint and CRM data.
Option 7: Custom Code
Finally, the web service endpoints exposed by SharePoint and CRM can be used by custom code for bespoke integration. CRM exposes both SOAP/WCF and ORest services and one thing to note is that if you’re planning to integrate CRM 2013 and SharePoint 2010 you’ll need to create your own proxy service in the middle as CRM 2013 is .Net 4.0 and SharePoint 2010 Web Parts only support .Net 3.5.
The CRM SDK contains sample code showing you how to programmatically work with the SharePoint entities within CRM.
The core intention is to use SharePoint as a document repository for CRM but the possibilities of working direct with Filtered Views in the CRM database (for on-premises), with Power BI and with the CRM SOAP and ORest web services means that SharePoint could just as easily be used to deliver CRM Reports or be a portal solution on top of CRM data.
For companies already using SharePoint as their key collaboration and communication tool, there are therefore plenty of options for bringing CRM “in to the fold” and making its functionality available.
Here’s a list of useful articles and references – everything you could possibly want to know about SharePoint and CRM!
Overviews & Introductions
Walkthrough of setting up CRM Online and SharePoint Online: –
Blog Series on Various Integration Options
Shortcomings of Security Model
Power BI in CRM
Using BDC service within SharePoint to pull in CRM data.
Calling SharePoint from CRM