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