Using PowerWindow table properties vs refresh...

I find using 'table properties' a better option for refreshing tables in the power window. Some of the tables are quite large -- and, some of our models have a lot of tables. Using table properties allows me to see _when_ a table was last updated; and, then, if choose to, I can _save_ to refresh the table. I'm investigating a way to capture this window information to add as a .csv imported table.

How to get to 'Advanced' tab in power pivot window:

When in PowerPivot window - click on the icon left/top under titlebar-- there should be a drop down that allows you to switch to the 'advanced mode' view tab.  I find this most useful for the managing 'perspectives'. I create two perspectives 'CORE' for tables that require regular updates and 'LKUP' for those tables that remain mostly static over time.  I then use AutoIt scripts to refresh the 'CORE' tables.

How-to PowerPivot (sans Sharepoint)

Without Sharepoint refreshing powerpivot sheets is an onerous task. To take some of the sting/pain out of it here's what I'm presently doing:

1) Setup DSN's locally (in my case MySQL with UID/PWD)

2) Setup CORE and LKUP perspectives (see advanced mode tab):

-CORE are core data tables that are used regularly
-LKUP are static (more or less) tables used in relations
-Both are lined up horizontally in each perspective

3) Create a series of AutoIt executables to:

- Open a passed powerpivot sheet (via a pivotxls var)
- Go to powerpivot window
- Go to diagram view choose 'CORE' view
- Refresh one or more tables(via a passed notabs variable)
- Save main sheet

4) Join the smaller executables in #3 into a single AutoIt executable call it RefPPivot.exe)

5) Now I have a script that functions in the form:

RefPPivot.exe pivotxlsx notabs

EXAMPLE:

RefPPivot.exe P:\accounting\zPowerPivot\myPowerStuff.xlsx 8

...would refresh the P:\accounting\zPowerPivot\myPowerStuff.xlsx's
8 core tables.

6) Schedule #5 through myLauncher.hta

This works tolerably well. I created another, pretty much identical script to #4 but use for LKUP updates(RefPPivotLKUP.exe).

KEY: When creating / building connections *don't* supply the uid/pwd - assign them rather in the DSN itself. This works well for mySQL -- but I think this may work for some other DB's as well (like Oracle). When you get to the 'next' prompt to create table / query - first chose the 'All' tab and persist the security info.

I have samples scripts and further suggestions if anyone's interested.

ADDITIONAL: If you're trying to see if a powerpivot table has been updated go to the powerwindow / design view then click table properties - should be in red/magenta in lower right corner. I usually check the last table in my 'CORE' list -- assume that the ones prior in the list bear the same or slightly earlier date/time.

I use table-properties so often I found it helpful to put that as well as a few other commands on the quick-access toolbar:

Sample PowerPivot.xlsx and PowerWindow

For those still struggling with the powerpivot disabled add-in

For those still struggling with the powerpivot disabled add-in ~ http://michaeltbeeitprofscript.blogspot.com/search?q=powerpivot.ps1