Interoperating with PowerPivot

Question

Is there any way to interoperate directly with PowerPivot ?

I have done a few prototypes already :
- I used OLEDB to connect to the Analysis Services SQL Server instance, and sending some MDX.
- I used ADOMD.NET to fill a DataSet using an AdomdDataAdapter, but the AdomdCommand has to be filled with a MDX request as well.
- I can call the server using a connection string with only the http://...xlsx.

All this works fine (after a few days of work to setup SharePoint, PowerPivot, understanding the model, fighting with security, etc. of course), but it still is quite limited with respect to the complexity of the call : the request has to be done with some quite complex syntax. I would like to be able to simply pass some values for the slicers, ask PowerPivot to take these values into account and send me the possible values for the dependent slicers. In brief, taking advantage of what PowerPivot does for me in terms of DMX / ADOMD queries without having to be inside an Excel process or inside a Web Excel page.

I understand there is no API for PowerPivot yet, but I read I could connect to PowerPivot Web Service. Sadly, I did not find a single piece of technical information on that. It seems that a WCF call is possible, but which URL to call ? Where is the WSDL ?

If no simple interop is possible on the server, is the client approach (interoperating with the Excel Process, using COM or the .NET libraries) possible ? Typically, is it possible to simulate the click on a slicer value ? Or maybe doing the same thing on the server ? If there is an Excel Service, maybe the .NET interop classes can be used to get and send some information to the instance of the .xlsx file...

Sorry if all this text seems a bit confused. The reason is certainly it is a reflect of my mind at the moment. Thanks in advance for any help / insight on this issue.

Answer 1

We will get back to you on this

Answer 2

The In Memory OLAP Database engine is only accessible from within Excel or Excel Web Services, and there is no API for directly accessing the data. Having said that, it is possible to connect to the hosting server and execute MDX queries against it.

HTH

John Desch

Answer 3

Interoperating with the server is the same as any SSAS API. Your prototyping above is *exactly* right for interacting with PowerPivot on the server. You are doing the right things. There are no plans to write a simplier "easy to use" interface. This simplicity is what the Excel add-in is providing you.

The Excel team owns the interaction with the pivot tables on the server side. They have another API called EWS (Excel Web Services) which is a web service API for applications to interact with Excel workbooks programmatically from off the farm. Pivot tables are part of that API I believe. I don't know if they have added slicers or not. EWS is the "ease-of-use" feature that you are looking for. I don't believe that it is there today, but feel free to interact with that team and provide feedback.

---

Dave Wickert (MSFT) blog: http://www.powerpivotgeek.com/ Enjoy!

Answer 4

First, thanks for your help (and this is for John and Vijay as well).

I have tried the Excel Web Service approach and that indeed sounds very easy to use, particularly since I am not skilled at MDX. There is a web service called setParameters that can be used in correspondance with slicers, if they are exposed as Excel parameters when publishing the file to SharePoint. Sadly, I got the way to retrieve the slicers ID, but cannot get this to work, so I created another post on this (http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpointforsharepoint/thread/b45a900c-8d76-4972-95b3-e9519b58633e). As you said, maybe I should post this question to the Excel SharePoint forum rather than the SharePoint PowertPivot one ?

Would the PowerPivot Web Service be a possible other approach to my problem ? http://msdn.microsoft.com/en-us/library/ee637275%28SQL.105%29.aspx explains that there is a WCF web service to call PowerPivot Web Service, but it does not tell the symbolic URL to call it. There is an associated installation procedure, but it would have been great that the documentation provides at least the list of web methods or the WSDL (and also the URL to the .svc, so we can check rapidly if it is there), so we can see if it suits our needs without running an installation procedure that is not evident for a person who does not know SharePoint very well.

I had the same problem for Excel Web Service : the Microsoft documentation explains it exists, but I had to look in non-Microsoft forums to find it simply was exposed as http://<server>/_vti_bin/ExcelService.asmx. I understand this critic should be addressed to MSDN rather than here, but except for the polls that randomly appear on the site, I have not found a way to express this lack...

Answer 5

PWS is a modified version of the SSAS data pump. It is an http SOAP call, but not WCF. We implement PWS as a WCF service but it listens at the raw byte level (no channel) as the http traffic between the client libraries and the PWS (just like the client libraries to the SSAS data pump) is an old style SOAP format. Thus you cannot use WCF and make a newer style RPC. To use PWS you need our client libraries (i.e. msolap OLE DB provider or ADOMD.NET). That is why I was recommending that you use EWS since it supports a richer WCF/ASMX API.

---

Dave Wickert (MSFT) blog: http://www.powerpivotgeek.com/ Enjoy!

Answer 6

I found the documentation quite misleading, since it talks about a web service that we are not supposed to use it...

Thanks for the clarification, anyway !

Answer 7

hi Jp.

can you explain what you scenario are you trying to create? what is the use case?

we are planning to develop something around this specific usage, and I want to learn what customers are trying to achieve when doing it..

also, for now, the SSAS API (Via  MDX) works perfect. we use it in our products. watch a simple video here:

http://www.panorama.com/products/powerpivot-demo/powerpivot-demo.html

 

 

Answer 8

In our case, we've got a Java/C++ application where we want to integrate a call to a WinForm that will host the PowerPivot workbook from SharePoint. The user clicks on a report link, a new window comes up where we pass their credentials as a parameter to limit data access and display the interactive PowerPivot display. Ideally...

Answer 9

I'd like to host an in-memory OLAP cube and fill it from an ADO.Net dataset or else from an external data source.  Basically I want to do what Excel can now do (and what John Desch said is not available to .Net programmers via an API).

I suppose there would be some question about how this would play out as a source of revenue.  The scenario wouldn't involve a SQL Server or an Excel client...

Anyway, I have a Microsoft connect suggestion for anyone who wants to vote...

https://connect.microsoft.com/SQLServer/feedback/details/561495

 

---

David Beavon

Answer 10

Dave, jp,

I have been using oledb to interact with the PowerPivot server. As far as reading metadata and issuing queries everything works wonderful. This however only works when the SharePoint site is configured to use Windows integrated authentication - in which I believe the current user token will just "magically" gets passed to SharePoint. I would really like to extend my application to support non-integrated authentication. As I have tried, oledb or other ssas api seems to be incapable of transferring credentials/tokens properly to SharePoint. Have you guys worked around this issue?

Thanks.

Answer 11

I am developing an addin for excel 2010 and i need to interact with powerpivot engine. Is that possible? Can i interact with powerpivot engine using AMO? How can i do that? I am trying with this

Server

 

svr = new Server();

svr.Connect(

 

"Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$");

but, i have "data source must be specified..." error.

Thanks

Answer 12

 

Hi TFCarlos,

 

Did you get your answer from the PowerPivot for Excel forum? There would be the proper place to ask this question. Anyway, at this moment we do not support interactions with the embedded database other then by Excel. There is no public API, no documentation and no costumer support. This first version of PowerPivot is focused on the costumer experience, and not on the developers (that is, the PowerPivot V1 is all about self-service BI).

 

I hope that helps.

 

Best,

Mariano

---

Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.