PowerPivot for Excel 2010 is not a new feature, but only recently I have became aware to its capabilities in conjunction with Microsoft Dynamics CRM 2011.

One of the data sources PowerPivot can extract data from, is OData Services. As Microsoft Dynamics CRM 2011 exposes an OData service (OrganizationData.svc), PowerPivot can be harnessed to analyze MSCRM2011 business entities data and solves some basic problems by the way:

  • Lack of Outer Join support in Advanced Find queries
  • Dynamic PivotTable/Table performance issues
  • One entity limit in Dynamic PivotTable export to Excel
  • 10K records export limit when Exporting data to Excel
  • 5K records count limitation in MSCRM2011 Views

PowerPivot can also be useful in data migration scenarios as it can extract data from various data sources and help manipulating it before importing to MSCRM2011.

Unfortunately, as stated in the Q2 2012 Service Update Release Preview Guide document, PowerPivot and PowerView would not support Microsoft Dynamics CRM Online organizations in the near future:

“It should be noted that for the Microsoft Dynamics CRM Q2 2012 service update the Power View reports will be available for On-Premises customers only. Future service updates will expand the reach of Power View to Microsoft Dynamics CRM Online customers.”

In the following posts, I will demonstrate data extraction from MSCRM2011 using PowerPivot and some related solutions.