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.
Donna Edwards 
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)
============================
Hi Javier,
I’m not aware of support for the E-mail Router with oData endpoints or Sharepoint. Please consider asking your question in the Dynamics CRM Developer forum. You might be able to get some assistance there. Here is the link. http://social.microsoft.com/Forums/en-US/crmdevelopment/threads I hope this helps.