Can I access PowerPivot via a data access API?

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!

2 Comments

  1. Martin Terreni

    Hi,

    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.

    1. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s