If PowerPivot data is “in memory”, why can I not distribute an Excel file loaded up with a DataModel WITHOUT the supporting Access data?

[mtaylorON]

I have built a report using a PowerPivot datamodel. I am using office 2013 pro plus. There are about 650,000 lines of data; 30 measures and it drives 3 pivot tables. The source data comes from Access. All is well. My question is... if the powerpivot datamodel is "in memory" why can I not distribute it without the Access database being available? Am I doing something wrong, or am I misunderstanding what is meant by "in memory"? If 50 users will be bouncing this report against the Access database potentially at various times I'm afraid this approach isn't going to work out! Thanks.

<tbody>
</tbody>

 

[akice]

"In memory" means just that - the entire Power Pivot data model is loaded into RAM for super fast execution when the workbook is started. If the data is imported into the model of the excel workbook, then it should run fine without the Access database. However, if you distribute copies of the excel files to multiple people, then refreshing the data could become problematic depending on where the users are in relation to the access database and how the permissions are set. (Same for adding or refactoring measures. A new copy of the workbook would need to be sent out.) Are you running into issues when they are not kept together?

 

[mtaylorON]

... Are you running into issues when they are not kept together?

Yes. Not only does it need to refresh from the original access database upon first load (i.e., I tested by moving the database, then opening the Excel file to confirm)... but also, every time you manipulate the pivot table in any way (change row/column order, filter, sort, etc), it goes back to hit the Access DB again (again.. tested by moving the DB after the PowerPivot was initially loaded into excel).

That will be a performance disaster if it actually needs to hit the DB for every single user adjustment to what they are viewing.
Am I misusing the tools or does the DataModel in fact need to hit the source DB for every such user change to the pivots?

 

[mtaylorON]

I should also note; the intent/hope for those 50 users is that they each open up a read only copy that is disconnected from the source data. In this case performance is limited only by the machine they are opening the report on.

 

[akice]

How did you connect to the Access DB? Power Query? The only other thought i have is do you have the workbook set to "Refresh data when opening the file". Toggle box is located at: Data -> Connections -> Properties... Otherwise the data should be imported to the workbook and should need Access only during data refresh.

 

[mtaylorON]

I loaded the data originally directly through the PowerPivot "Manage" window, "Get External Data", then Access, and selected the appropriate tables and filtered out a few columns.

On your suggestion I check "connections", "properties" and turned off "refresh this connection on refresh all"... "Refresh this connection when opening the file" was already turned off.

Interestingly though, when I look at the datasource for the pivot table, it does not say "ThisWorkBookDataModel" but instead it lists the name of my access database. Despite this, all of the measures that I created in powerpivot are available, and it behaves as though it is coming from a datamodel (i.e, I can select from multiple tables in the PivotTable field list).

All of this despite the fact I generated the PivotTable directly from the PowerPivot Manger window. So, now it appears that the pivot table is connected to the access database rather than the DataModel; and I have no idea why that would be (or if it actually is or if it is just displaying that way.

 

[mtaylorON]

Perhaps the easiest way for me to ask this is to ask what the exact order of operations is, using Excel 2013, to load a datamodel from an Access database, create a few measures, generate a PivotTable, then share the Excel file with other users in a way that they can filter/sort/re-order the PivotTable WITHOUT needing a connection to the Access database.. if this is possible. Each method I use to load data (direct load through PowerPivot, import through PowerQuery, or via Connections) fails to work without Access.

 

[akice]

My first tool to reach for these days for etl is Power Query. I would give that a try.