Any API(Java or others) to connect and read PowerPivot data..

[sourav.majumder]

​

Below are the questions I have - 1. Is there any java API to connect and read the PowerPivot excel 2010 data? 2. Is there any web service available to connect and read the PowrPivot data? 3. Any .Net API to do the same? 4. Once imported how and in what structure PowerPivot stores the data with the excel? How to understand the relationship and read the data?

 

[JavierGuillen]

Hello

PowerPivot has no API. On the other hand, if interested in the Tabular engine, you *can* code one through .Net interfaces.

However, most of the time what you want is to extract data our of the engine. If this is the case, once you publish to a PowerPivot Gallery in SharePoint, you can query the PowerPivot model through DAX or MDX, and as so consume its data in .Net or a tool that understands OLAP/MDX (even non-Microsoft ones).

An example of this approach is posted here: PowerPivot and Beyond: Expanding your analytical repertoire « Javier Guillén



Javier Guillen

 

[sourav.majumder]

Thanks for the information.
Once the PowerPivot excel is published in SharePoint, is there any web service available to interact with the data? What about PowerPivot web service?
Can we publish PowerPivot excel as a service in SharePoint so that a wsdl will be published in sharepoint and Using my client application we can call those?

 

[Kyle123]

xposted here: API/Service to interact ith PowerPivot excel 2010

 

[JavierGuillen]

There is no wsdl interface. The interface to query data can be done through the SQL Server Analysis Services provider. If you are using .net, you can use the ADOMD.Net API to query data or metadata for the published PowerPivot model (in SharePoint). This interface uses XMLA when communicating with the server model, though you will be issuing an MDX statement.

Here is an example using a multidimensional SSAS cube:

Retrieving Data Using the CellSet

As PowerPivot (when published to SharePoint) is hosted as a model that has multidimensional interfaces, you can use the same method to programmatically retrieve data.

 

[sourav.majumder]

Can I export PowerPivot data as xml or in any other format?

 

[JavierGuillen]

from the powerpivot window directly, no. If I understand your questions correctly, you seem to be interested in consuming PowerPivot as a service. Is this right? When using PowerPivot in SharePoint, it *is* in fact hosted in a service (the SSAS dedicated service), and you can then query the model hosted on this service to retrieve data and save it in other formats (you can query it from Excel or other tools that understand MDX or DAX)

 

[sourav.majumder]

1. I want to read PowerPivot data that appears in tabular format in Excel with PowerPivot plugin.
2. I tried with Apache POI, it can't do this. Any other Java API or any other languages can fulfill req as mentioned in 1.
3. Next thought of any service format it can be exposed in Sharepoint so that I can write a Java client to read the data?

4. Per your reply is it possible to call the SSAS service and fire query from any Java client?

 

[JavierGuillen]

in that case, yes you do need SharePoint so you can then query the SSAS service which hosts the PowerPivot data.

Another option is moving the PowerPivot model to a full blown SSAS BISM Tabular environment, in which you won't need SharePoint (or Excel) anymore.

As long as you can use the SSAS provider you can query the PowerPivot hosted model or SSAS Tabular environment from code. The link I gave above details how to access a multidimensional SSAS environment from .Net code; I am not familiar with a Java implementation.

 

[sourav.majumder]

I am trying to find out how to create a SSAS service endpoint / Webservice endpoint in SharePoint which hosts and can interact with PowerPivot embedded data.

Can u please provide some guidance.

Thanks for providing me the most accurate answers, I do appreciate your help.