Power BI “Mash Up” of CRM and SharePoint Data

Power BI SharePoint and CRM


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: –

  1. Dynamics CRM 2013 (online)
  2. SharePoint online
  3. Excel 2013 Power Query / Power Pivot
  4. Power BI online site

 Kev Scenario

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: –

  1. A custom CRM entity called Attendee Group.
  2. A link from Attendee Group to the “out of the box” standard CRM Contact entity as the “principal contact” for that group.
  3. 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.

Mash Up Objects

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: –

  • Within CRM go to Settings > Customisations > Developer Resources and the link at the very bottom of the screen is the ORest data service that MS Excel can talk to.  This service was originally designed for JavaScript calls coming from pages within the CRM but since the 2013 release it has been opened up so that an Office 365-authenticated client can talk to it and ask for data.

Mash Up 2

  • 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”.

Mash Up 3

  • 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.

Mash Up 4

  • 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).

Mash Up 5

  • 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.

Mash Up 6

 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.

Mash Up 7

  • 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.

Mash Up 8

 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.

Mash Up 10

  • This took my original “Path” column and created two that I named Path.TextPart and Path.GUIDPart.

Mash Up 9

  • 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], “-“)

Mash Up 11

  • At this point the data sources were in an acceptable shape to begin linking them.

Mash Up 12

  • Within MS Excel I went to the “Power Pivot” tab and the “Manage” icon.

Mash Up 13

  • 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.

Mash Up 14

  • 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.

Mash Up 15

  • 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.

Mash Up 16

 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.

MashUp17 MashUp18


Posted on October 2, 2014, in CRM General, CRM Integration and tagged , , , , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: