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