Blog Archives

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


Microsoft Exam MB2-700 – CRM 2013 Applications



Yesterday I passed Microsoft’s CRM 2013 Applications exam (MB2-700), which is part of the new wave of updated CRM exams for the 2013 release.  This post covers how I studied for the exam, what sorts of questions came up and what material I used for anyone else thinking of adding this qualification to their hit list.

In my limited CRM experience, very few of the projects I have done have used the Sales and Case management modules to their full potential – usually because the way these processes are modeled in the “out of the box” CRM does not match the specific customer’s needs.  As a result I realised, when studying for the exam, that I hadn’t fully explored all the functionality present and so relying on my work experience alone would not be enough to get through the exam.

A solid grounding in sales and case management also seems wise at the moment, given the enhancements to these areas which are arriving with the Spring 2014 CRM release.

The Exam

The exam is the usual 48 questions of multiple-choice format with a passing level of 70%.  It concentrates on two areas of CRM’s “out of the box” functionality, namely: Sales Management and Case Management and there are two seperate Microsoft courses which cover these (80545 – Cases and 80546 – Sales).  The exam I took was evenly balanced with questions from both areas. The Microsoft web page describing the exam and the skills tested is listed below and this should always be your final check list that you are ready: –


For preparation I did three key things: –

  1. I worked through the two official Microsoft courses (about 14 chapters of notes split into individual PDFs) and can happily confirm that the exam is based on this material with all questions relating to information covered in the courses.
  2. I made my own notes as I went (there’s a copy of them at the end of this article for download).
  3. I worked through some lab examples and tested out some scenarios in a copy of CRM 2013.  More so than the other exams I think this is critical for MB2-700 – as several of the exam questions related to the different processes for handling leads, opportunities, contracts, contract templates etc. and there is no substitute for actually playing with these – it certainly seemed to stick in my head a lot better when I was “hands on” as opposed to just reading lots of abstract training material.

Points Covered

Here’s a review then of the sorts of topics/questions that came up: – For CASE MANAGEMENT

  • Be comfortable with the workflow of a case, how it gets raised in the system and then routed to different people.
  • Know what parts queues play in case management, how permissions work with queues and the difference between “Worked On” and “Owner”.
  • Understand the use of the Knowledge Base, the different template types and how you deactivate/delete a template and what that means for any articles already using that template.
  • Know the workflow of a Knowledge Base article and the conditions for emailing it out to a customer.
  • Know the way that contract templates and contracts are used specifically the different allotment types and how they work.
  • Understand the use of contract lines and what state a contract must be in for you to be able to add these.
  • Be familiar with the contract life-cycle, what stages can pass to what other stages and what stages allow you to link cases to the contract.
  • Understand how cases can be linked to contracts to use up their allotment and the difference between calculated Total Time and Billable Time.
  • Look at the subject tree and know which entities MUST have a reference to the tree to be saved.
  • Know what conditions are necessary to be able to close a case (specifically closing activities linked to that case).
  • Be familiar with that entities can be easily converted in to cases.
  • Know about the use of comments and notes against a Knowledge Base article.
  • Memorise which entities are enabled for queues “out of the box” and which entities have queues created for them automatically.
  • Understand the relationship between a Queue Item and the record it relates to.
  • Understand the use of goal metrics and the goals that use them – this was one area where I found it essential to set up some examples myself and play with them.
  • Know how you use in-progress and actual goals.
  • Know about the different time periods to which a goal applies.
  • Have a play with rollup queries to understand their use.
  • For service scheduling, look at the service calendar and understand the different types of resources that can be scheduled.
  • Understand the selection rules for combining resources to provide a service.
  • Look at the periods of unavailability for resources and how the schedule engine will take note of these when proposing the next available slot.  Be aware that CRM is clever enough to take time zone in to consideration and site.
  • Be familiar with the use of capacity for resources.
  • Look at customer’s preferences for service provision.


  • Know the difference between a lead and an opportunity.
  • Be comfortable with the workflow of a lead and the entities that can be created when a lead is won.
  • Understand how emails, activities etc. can be converted in to leads and opportunities.
  • Be familiar with all the key entities in the sales area and how they link together – which entities can use sales literature for example?
  • Be comfortable with the workflow for an opportunity and what related entities get closed when a workflow is won.
  • Understand the use of the resolution activity.
  • Play with the product catalog and understand the use of unit groups, discount lists and products.
  • Understand the use of kits.
  • Know how the standard price directly present on a product record can be used by the price lists and the different sort of pricing methods available.
  • Be comfortable with how CRM handles price lists of different currencies.
  • Know the quote – order – invoice workflow like the back of your hand 🙂
  • Know how the use of price lists and system calculated pricing flows through this workflow.
  • Be familiar with what triggers an update to automatically-calculated prices, particularly for mixed currencies.
  • Look at the links between products and quotes and products.
  • Understand what happens to a quote when an order is generated.
  • Know what changes you can make to quotes, orders and invoices AFTER they have been activated.
  • Look at the Sales History and Sales Pipeline reports and understand what they are built to show and what data they use.


Solid preparation is needed for this exam as the questions went in to quite a bit of detail about the workflows, about the set up of certain screens and about how CRM can be used to handle some particular scenarios.

I firmly believe that playing with the system and testing out everything you learn as pure theory is the best preparation you can do.  On my exam there were a few of those annoyingly ambiguous questions that Microsoft seem to throw at you from time to time where it’s not 100% clear what exactly is being asked or multiple interpretations are possible but on the whole the exam was good, very close to the training material and a great way to familiarise yourself with these modules.

Kev Notes

Here are the notes I made during my own preparation, I hope they are of use to you.


Microsoft Exam MB2-703 – CRM 2013 Customisation



A few months ago Microsoft updated their set of four exams for Dynamics CRM to bring them in line with the CRM 2013 release.  Having done the original four exams in their CRM 2011 “flavour”, I took and passed MB2-703: Customising and Configuring CRM 2013.

This post then covers how I prepared, what sorts of questions came up and what material I used.

The Exam

The exam is the usual 48 questions of multiple-choice format with a passing level of 70%.  It concentrates on everything that can be customized and configured with CRM 2013 using the “out of the box” tools.  The full curriculum list is at the following link, and this should be your golden standard for what to study and your check list of knowledge just before going in to sit the exam.


The main resources I would recommend are: –

1)      If you can get hold of the official course material for this exam from the Microsoft Online Courses (MOC) site then this is the best source of training.  It includes eleven student PDFs for each of the main topics and also PowerPoint and lab exercises.  Working through this is the best way of preparing as questions on the exam should limit themselves to what is covered on this course.

2)      There is a new Customization guide within the CRM 2013 Implementation Guide and this is also a great resource for learning about customization options and restrictions.

3)      Any notes and material related to the older CRM 2011 Customisation exam – MB2-866 – are useful as background material as the new exam will continue to test feature that have not changed between CRM2011 and CRM2013 such as Business Unit organization, Security, customizing Views etc.

4)      If you’re still hungry for more after that then email me and I’m happy to pass on the notes I made (although they are rather rough) to show you exactly what I considered important.

5)      Finally there is no substitute for playing with a trial CRM 2013 online version and the tablet/smartphone application (if you have a device) to test out some of the new features and work through the labs from the MOC material mentioned at point 1.  Have a hunt around for useful blogs covering the new features if you want to go a bit deeper.

Points Covered

As a general pointer, the following sorts of questions came up a lot for me during the exam and are mentioned on the curriculum link: –

1)      Make sure you know how business units are organized and what happens when you deactivate a business unit, what happens to users belonging to that Business Unit?

2)      Know what inherited security roles are and how they behave when Business Units are reparented.

3)      Know the different types of Views within CRM 2013 and particularly the new view type Associated View.  Know what fields you can include within a view and the restrictions on sorting and specifying field as searchable.

4)      Know how views can be used in sub-grids to provide data and how the sub-grids themselves can be set up for lists or charts.

5)      Know how responsive design works with respect to tabs, columns and sections and how the same screen might get rendered on tablets and smartphones.

6)      Know some of the restrictions for the tablet application – how many dashboards can you have, can the user change forms, how do long complex forms get rendered?

7)      Know about the different types of relationships between entities within CRM 2013 and the differences between native and manual many-to-many links.

8)      Know the restrictions on relationships, the difference between parental and referential and how these get configured within CRM.

9)      Know what aspects of entities you can enable and disable and which ones can’t be disabled once they are turned on (Notes, Activities, Business Process Flows etc.)

10)   I didn’t get any question on Access Teams but since they are a new feature it would be wise to study these.

11)    Understand how Business Process Flows work, how they interact with security and how many flows you can have for an entity and how CRM reacts if you swap between flows.

12)    Understand how Dashboards are used, what you can place on a dashboard and how they render on tablets.

13)    Be comfortable with the use of managed and unmanaged solutions, what the difference is, how you create a managed solution and what you can use the version numbering for.

14)    Understand how to create a private chart from a public one.

15)    Understand how auditing works within CRM, how and where it is enabled and how the system behaves if you turn it off temporarily.

16)    Understand the difference between global and local options sets and how these might behave when you have set up mappings between two entities that both use an option set.

17)    Understand field level security and how it behaves across all platforms that CRM can be delivered on.

18)    Know your way around Quick Create and Quick View forms, what they are used for, where they get accessed from within CRM and constraints about using them.

19)    Know about the new Lync, Email, Ticker and URL formats for the Single Text field.

20)    Understand the use of and restrictions surrounding Business Rules, where and when you would use them, what you can do with them.



In summary, for anyone that has already taken the CRM 2011 Customisation exam then I think the Royal road to success with MB2-703 is to have a relatively quick refresher on the customisations features you’re already probably very familiar with from CRM 2011 before concentrating on the new features that CRM 2013 introduces (Quick Create/View Forms, Business Process Flows, Business Rules, Tablet Application, Associated View etc.)

For anyone approaching this exam without the CRM 2011 background then the Microsoft course along with some dedicated time playing with the CRM is essential.  There were lots of questions concerning how you achieve certain customisations within CRM and that sort of thing is much easier to remember if you have actually walked through doing it yourself, rather than simply reading about it in a more abstract manner.

Finally good luck and take heart, my experience was that the questions were clear and concise with little ambiguity so not too difficult if you’re prepared thoroughly.

Kev Notes

Here are the notes I made during my own preparation, I hope they are of use to you.


URLs for Connecting to CRM 2013

What URL?

I had a bit of difficulty confirming this recently as a web search did not turn up much, a bit surprising given how fundamental this information is.   I must have missed something obvious in the CRM 2013 SDK or other online sources but for connecting to a CRM 2013 online instance I believe the following URLs give the location of the discovery service.

Location Instance type URL
North America Office 365
EMEA Office 365
APAC Office 365

Here is the original MSDN article for CRM 2011: –