OData data source for PowerPivot

Question

Hi!

I am a developer focused on data-oriented business apps. I would like to offer PowerPivot as a reporting/data analysis solution for our application. OData would be a perfect data exchange standard because we need to be able to access business logic from within the application in reporting and data analysis.

I was able to implement a prototype OData provider for PowerPivot within a few hours. I deployed it to Azure and everything was fine. However, a few critical question came up when we took a closer look:

1. The OData syntax allows to pass parameters (in case of service methods) and query parameters (e.g. where-clauses). I can add those parameters to the URI in PowerPivot (nearly) without any problem. Is it possible to integrate e.g. the value of a formula in the sheet in the URI to make the OData query more dynamic? If not I would strongly vote for Microsoft adding this feature!
2. In the "advanced" dialog of OData I can enter user & password. How to use this feature if multiple users access the same excel sheet? Is it possible to route through the username of the currently loged on user? How does this work in the case of PowerPivot for SharePoint? I have to consider user permissions depending on the loged on user in SharePoint, too.
3. Is PowerPivot + OData supposed to work in Office365, too? If so, how does user managent work there? Is there a way to pass the live ID of the Offce365 user to the OData service?

Any help appreciated! Thank you in advance!

Kind regards,
Rainer

---

Rainer

Answer 1

Rainer,

Did you ever get this one solved?

Thanks!

---

Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

Answer an interesting question? Create a wiki article about it!

Answer 2

Hi Ed!

No, unfortunately not. I would love to see more support in BI/Reporting client-side tools for consuming OData including a query editor.

We have decided to solve our BI requirements in our own product time cockpit using RDLC (see this blog for details). However, we still haven't deleted our custom OData provider implementation. If client-side support gets better in the future, we might want to return to that path.

Rainer.

---

Rainer

Answer 3

Hi Rainer,

Thank you for your question. I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

Thank you for your understanding and support.

Regards,

---

Elvis Long
TechNet Community Support

Answer 4

Hi Rainer,

Thank you for your question. I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

Thank you for your understanding and support.

Regards,

---

Elvis Long
TechNet Community Support

Elvis, any luck finding someone?

Thanks!

---

Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

Answer an interesting question? Create a wiki article about it!

Answer 5

Regarding #3.... you should deploy the Power BI Preview for Office 365.

Specifically, you should use Power Query and use OData as your data source.

http://www.microsoft.com/en-us/powerbi/default.aspx 

Thanks!

---

Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

Answer an interesting question? Create a wiki article about it!