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!