Tuesday, June 3, 2014

Generating Larger Pivot tables in Excel from GAMS

I was working on generating some very large pivot tables to view model results. Some issues came up. Users tend to use things differently than expected: they may download and install 64 bit versions of Excel and they may run with different language settings (decimal point vs. decimal comma). And they may generate huge output: data tables with more than a million rows can not be stored inside Excel, so we need to store these outside (external data source). The reason for the large data is that we have a multi dimensional structure: results(impactparameter,scenario,commodity,region,productiontype,year). Indeed looking at the CSV file I see for a result set with just two scenarios:

image

or for the Windows aficionados:

image


i.e. 4 million records.

Here are some notes.
image
Management GUI in Excel
image
Generated Excel worksheet
image