Dynamics CRM and Power Pivot Express – Go From Data Mining Novice to Expert in 60 Minutes or Less

Want to give your user’s something to get excited about and encourage them to upgrade to Microsoft Office 2010, then Power Pivot might be the catalyst to make that happen.  

I read a few articles about the new Power Pivot features in Excel 2010. I have several users who love the ability to dynamically create Pivot reports from Dynamics CRM so I try to stay informed about changes to Excel’s integration with Dynamics CRM. Last week, I spent about 30 minutes checking out some great tutorials on Microsoft Excel Power Pivot and Microsoft Dynamics CRM.  It is very impressive and I would have to say that it definitely has the "WOW" factor.  This is great stuff!

Power Pivot provides the ability to have a fully automated connection to the Dynamics CRM database. Connecting to the database doesn’t get any easier than this. You can train virtually any user to make the connection and find what they need in just a few minutes.

Creating ODBC connections, modifying hidden properties of the queries, and working through several steps to drill down to the data needed is a thing of the past. Now, in less than 5 steps, the user can not only connect to a filtered view but also automatically connect to the related tables with no extra effort. Yep, that’s right; data from all the related tables is also included if the user selects that option. Your users don’t have to know about joins, modifying queries or take any other actions to pull back the data. Power Pivot does all the heavy lifting. There is also an option to write queries for the Power Users so they don’t have to use the Wizard if they don’t want to.

Here is a couple of screenshots to pique your interest.

Power Pivot Button in Excel


Power Pivots, Easy Connect Feature


Import Wizard – CRM SQL Connection


Data Retrieval Options


Data Import Wizard


In just five easy clicks, I am connected and importing Dynamics CRM data for analysis. In my humble opinion, Microsoft knocked this one out of the park!!

The biggest challenge you might have in opening this world of data to your user’s is teaching them what all that data means and to remember to connect to the filtered views and not the base tables.

Install Microsoft Office 2010 and download Power Pivot today to experience the true power of your Dynamics CRM data today! Don’t forget to visit the Power Pivot Learning site to view demos, experience Hands-On Labs, and more. You will discover just how easy it is to get up and running quickly. I think this is a Definite, Must Have for any Sale’s Person’s demo environment.


  1. Donna, have you been able to do this using CRM Online and the oData endpoint? I have been able to do it using Sharepoint 2010 feeds (from a list), but when trying with CRM Online I’m getting this error:
    Error Message:

    Cannot connect to the specified feed. Verify the connection and try again. Reason: An XML comment cannot contain ‘–‘, and ‘-‘ cannot be the last character. Line 2, position 43..

    Call Stack:

    at Microsoft.AnalysisServices.Modeler.DataImportWizard.DataSourceBasic.UpdateDataFeedParameters(ConnectionStringBuilder connBuilder, IDataSource dataSource, Boolean checkFeedValid)
    at Microsoft.AnalysisServices.Modeler.DataImportWizard.DataSourceBasic.GetCurrentConnectionString(Boolean checkFeedValid)
    at Microsoft.AnalysisServices.Modeler.DataImportWizard.DataSourceBasic.ClickTestConnection(Object progressControl)


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 )

Facebook photo

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

Connecting to %s