Power BI “Mash Up” of CRM and SharePoint Data
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.
Posted on October 2, 2014, in CRM General, CRM Integration and tagged CRM, Integration, Mash Up, Office 365, ORest, Power BI, Power Map, Power Query, Power View, SharePoint. Bookmark the permalink. Leave a comment.