Reconstruct database data into a PivotTable

Pulling information out of a database and having a need to see it arranged likewise puts a peculiar strain on the end-user. Not so in analytic terms, but in the daily process of churning, rearranging and balancing ephemeral products, digital transcripts of a remote, tangible process. One of the first tasks in this context is getting the data out of denormalized form and back into a human-manipulable artifact.

I’ve covered PivotTable generation with VBScript already, so that should be straightforward: VBA macros executed from the command-line upon invisible spreadsheets, the EXCEL.EXE process the only clue of the magic behind. Once that’s finished, you can expand your sovereignty into domain-specific rules.

So with PivotTables, I’ve taken on a “if you can’t beat them, join them” tack: now their creation is the crux of further automation. If only someone could take Excel’s PivotTable four-quadrant interface and apply it to R cross-tabulation! That is a worthwhile problem to tackle.