I recently got asked the question:
Does PowerPivot have any data access APIs? Is there any way to to programmatically access the data store?
I figured that a lot of people will eventually ask this question (heck, that’s what I had asked when I had first heard about PowerPivot). The answer as of 2010 is:
Right now, there is no supported way to access the data store within Excel as all PowerPivot for Excel APIs are private. Saying this, the data store backup is stored within Excel itself as per the posting For Excel PowerPivot, the database is IN the workbook.
Saying this, once you upload the workbook to SharePoint, you can then access the workbook as a SSAS database because that’s what it actually is (an Analysis Services in VertiPaq mode database). Any client that can query MDX can go ahead and query this database using the PowerPivot workbook within SharePoint as its data source (i.e. the data source is http://mySharePointSite/PowerPivot%20Gallery/MyWorkbook.xlsx as opposed to the traditional “driver=msolap.4;data source=server;initial catalog=database“).
I hope this helps!
We are using excel services to present in MOSS a set of pivot tables to each user. the stored procedure behind is the same for all users, but the parameters are different. I need a way to progrmatically change the parameters of the query (what I would do in table properties in side PP window) so I can autmate the files creation.
I figured out I could just hack the file by unzipping it, but I hope there is a better way to do it, or imitate this, either directly to the file or through web.
Sorry for my slow response on this one – it got lost in my spam filtering. Please check out the blogs.msdn.com/excel folks or the Excel Services Newsgroup as they will most likely have a far better answer than I can come up with.