CRM 2011 Online as a data source

Question

Looking for guidance on a practical approach to using CRM 2011 Online as a datasource for PowerPivot for Excel or PowerPivot for SharePoint.  I understand that the on-premise scenario is pretty straight-forward, but expect challenges with Online.  The business requirement is to update the PowerPivot reports from CRM Online data automatically once a day.

CRM 2011 has such weak BI capabilities, PowerPivot seems to be a reasonable solution if integration is possible, but if anyone has delivered BI from CRM Online, I'd love to learn what you did.

thx

Answer 1

 

Hi Wendelljs,

 

I believe that you may find issues on working in CRM 2011 Online and PowerPivot for SharePoint due to network security issues, since the data source will be in another network. You may find issues with firewalls and user authentication. I'll look into this a little further and will get back to you as soon as I have more information.

 

Best,

Mariano

---

Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.

Answer 2

Mariano,

Any further insights into using PowerPivot with CRM 2011 Online?  Is there a scenario that uses PowerPivot for Excel on the desktop that uses CRM 2011 Online as a data source (bypassing SharePoint issues)?

Answer 3

 

Hi Wendelljs,

 

 

Using PowerPivot for Excel and CRM 2011 Online is a supported scenario.

 

You can check out these blogs for deeper insights:

 

http://blogs.msdn.com/b/crm/archive/2010/06/03/microsoft-dynamics-crm-and-power-pivot-express-go-from-data-mining-novice-to-expert-in-60-minutes-or-less.aspx

And this video demo in youtube:

http://www.youtube.com/watch?v=sAS5hK3OmoU

 

---

Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.

Answer 4

Well demo as above links are just for CRM on premise not crm online.

But tool scribe online replication rs claims to bring crm online data to your local sql server.

Answer 5

 

Hello NYSYD,

CRM Online uses ODATA to transfer data, which is also used by PowerPivot to import data as data feeds. So you can use CRM 2011 Online as a data source in PowerPivot for Excel.

You can read more on ODATA and CRM 2011 Online in this blog:

http://blogs.msdn.com/b/crm/archive/2011/03/02/using-odata-retrieve-in-microsoft-dynamics-crm-2011.aspx

Thanks,

Mariano

---

Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.

Answer 6

Thanks for prompt response.

i can get this feed working in browser : https://crmorg.crm.dynamics.com//xrmservices/2011/OrganizationData.svc/AccountSet?

But still could not figure in Power Pivot how you feed the url in

Other feeds section... Do we have to create connection too?

i get xml error in powerpivot...

Any help would be appreciated

Answer 7

I'm still having the same challenge. Keep getting a 401, not authorized, message from PowerPivot and CRM 2011 when I Test the data feed connection. We have a demo coming up, and the PowerPivot demo will be the turning point for a CRM deal. Any guidance or examples of PowerPivot configuration is greatly appreciated.

Answer 8

Hello Upside Commerce,

 

PowerPivot data feed provider only supports ODATA with Basic authentication. What kind of authentication are you using? Is your CRM 2011 Online in the cloud or on-premises? Can you give us more details?

 

Thanks,

Mariano

---

Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.

Answer 9

I am investigating the same thing.   I am assuming since he mentions CRM Online, that he is trying to get  to the data in the cloud.   Which is what I am also trying to do.

---

My Ramblings @ http://alanwhitehouse.wordpress.com

Answer 10

I'm having similar issues with CRM 2011 OData endpoint in PowerPivot. I can connect to CRM2011 onpremise endpoint via IE browser and get all the elements back. In Power Pivot if I choose Basic authentication I get a 401 not authorized. If I choose SSPI I get a message that the feed is not valid.

Any help is appreciated.

---

Keith Mescha

Answer 11

Has anyone been able to get this to work? I am also looking for guidance to connect PowerPivot to CRM 2011 online.

Answer 12

I've not had any luck with this still. Microsoft, is there any other options?

 

Answer 13

I don't think the option to connect CRM Online with Power Pivot is available at this time due to Windows Live authentication.  You can use Scribe to replicate the CRM Online data to a local SQL instance and then use Power Pivot against that data repository. 

I hope this helps.

---

Regards, Donna