PowerPivot and Access Services Tables

It’s been a long time since I’ve Blogged about PowerPivot.  That is not for lack of interest but moreso for lack of time and also because it has taken me a while to get my head around where PowerPivot will REALLY fit in in the Office 2010 environment.

While all the new cool features of PowerPivot available through DAX and through its SharePoint component are all very exciting, my concern has been “How can regular users get some value out of this app as soon as possible and in a way that fits their current needs and expectations.”

The concerns I have about Powerpivot involve:

1. DAX and the various advanced capabilities of PowerPivot could be a major block to acceptance of the product despite their obvious value down the road once PowerPivot gets more understood and entrenched.

2. Getting Excel users up to speed on the concepts and realities of relational databases is not easy – otherwise it would have happened years ago.

3. I have to think there is a Microsoft Access story to be appended to the PowerPivot one and especially one that integrates data from the new Access Services Tables that will live on SharePoint 2010.

4. I’d like to see how the “client” version of PowerPivot can be used to the maximum without need for integration through SharePoint Services (and thereby empowering users directly to source their own solutions).

So I have researched these issues hard and have found what I was looking for.  In this article I will discuss integrating Access Services data into PowerPivot.

Getting At Access Services Data

Unless someone knows a way (anyone?), it looks like PowerPivot V1 will not connect directly to Access Services Tables from the Client version of PowerPivot.  But it CAN connect to a local (or network) ACCDB file. 

If that ACCDB file is a traditional Access database file then the tables and queries in there are all available to PowerPivot directly through a Connection.  But if the file is a Web Project and relies on Access Services Tables that actually reside on SharePoint Server (but are cached to a local file if requested) then only the queries are available to link to – no tables. 

This means though that by creating a query for each Table or even by creating a query that Joins several tables you can expose the data in the Access Services Tables to Powerpivot directly.  No need for a SharePoint Server PowerPoint “Cube” to be created – it can be used for a “grow your own” PowerPivot cube inside a client-side Excel file.

I changed a record from another source by opening my Access Services application through another VM.  When I refreshed the PowerPivot cache on the original machine the tables were syncrhronized by the call to the query and the changes were reflected in my data in PowerPivot – so ACE did the synch based on my request alone !!!

This means that I can use Access Services Data inside a client-side PowerPivot file by routing the data through a local copy of the ACCDB file.  That means that PowerPivot can be used to create a OLAP Cube inside an Excel file in PowerPivot using Access data from an Access Services database on SharePoint Server 2010.  It means that an single ACCDB file strategically placed on a Network Share can provide a single-source single version ofn the truth for users ina department or throughout an organiztion.

This is cool IMHO …

My next PowerPivot comment will discuss a way to make use of the CUBE functions inside Excel simply and easy as a SUMIFS function to place PowerPivot cache data into a traditional spreadsheet design.  How to retrofit PowerPivot data into your existing management reporting spreadsheets in a straightforward and understandable way.

Dick