A script to generate power pivot tables

In (1) I provided a GAMS/VBS script to generate an Excel spreadsheet containing a pivot table that used a CSV file as external data source. Essentially the pivot table provides a view of the data in the CSV file. Here I show a script that allows to store the data inside the Excel data model: no external CSV file is used after loading the data into the data model.

$ontext    Example code that creates a pivot table with large data loaded    into Excel's data model. $offtext sets c,cty,yr,attr; parameter psd(c,cty,yr,attr) 'production, supply and distribution'; $gdxin psd_alldata.gdx $loaddc c=dim1 cty=dim2 yr=dim3 attr=dim4 psd parameter TotalProduction(c,yr); TotalProduction(c,yr) = sum(cty,psd(c,cty,yr,'Production')); alias(Commodities,Countries,Year,Attributes,*); parameter Cube(Commodities,Countries,Year,Attributes); Cube(c,cty,yr,attr) = psd(c,cty,yr,attr); Cube(c,'World',yr,'Production') = TotalProduction(c,yr); $set gdxfile         cube.gdx $set csvfile         cube.csv execute_unload "%gdxfile%",Cube; execute "gdxdump %gdxfile% output=%csvfile% format=csv symb=Cube"; $set xlsfile         %system.fp%pivotcube.xlsx $set scriptname      script.vbs execute "cscript %scriptname% //Nologo"; $onecho > %scriptname% Wscript.Echo "Creating pivot table..." Wscript.Echo "This will take a minute." Wscript.Echo "" Set xl = CreateObject("Excel.Application") xl.DisplayAlerts=False Wscript.Echo "  Creating Workbook..." Set wb = xl.Workbooks.Add Wscript.Echo "  Creating Query..." wb.Queries.Add "cube", _   "let Source = Csv.Document(File.Contents(""%system.fp%%csvfile%""),[Delimiter="",""])," & _   "  #""Headers"" = Table.PromoteHeaders(Source)," & _   "  #""Type"" = Table.TransformColumnTypes(#""Headers"",{{""Val"", Number.Type}}) in #""Type""" Wscript.Echo "  Creating Connection (loading data)..." Set con = wb.Connections.Add2("Query - cube", _         "Connection to the 'cube' query in the workbook.", _         "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=cube" _         , """cube""", 6, True, False) Wscript.Echo "  Creating PivotCache..." Set pc = wb.PivotCaches.Create(2,con,6) If wb.Sheets.count = 0 Then    Set sh = wb.Sheets.Add() Else    Set sh = wb.Sheets(1) End If Wscript.Echo "  Creating PivotTable..." Set pt = pc.CreatePivotTable(sh.Range("A1")) pt.SmallGrid = False Wscript.Echo "  Initial Layout..." pt.CubeFields("[cube].[Commodities]").Orientation=3 pt.CubeFields("[cube].[Attributes]").Orientation=3 pt.CubeFields("[cube].[Year]").Orientation=2 pt.CubeFields("[cube].[Countries]").Orientation=1 xlSum = -4157 call pt.CubeFields.GetMeasure("[cube].[Val]",xlSum,"Sum of Val") call pt.AddDataField(pt.CubeFields("[Measures].[Sum of Val]"), "Sum of Val") pt.PivotFields("[cube].[Commodities].[Commodities]").ClearAllFilters pt.PivotFields("[cube].[Commodities].[Commodities]").CurrentPageName = _    "[cube].[Commodities].&[Coffee, Green]" pt.PivotFields("[cube].[Attributes].[Attributes]").ClearAllFilters pt.PivotFields("[cube].[Attributes].[Attributes]").CurrentPageName = _    "[cube].[Attributes].&[Production]" pt.TableStyle2 = "PivotStyleDark7" Wscript.Echo "  Saving %xlsfile%..." wb.SaveAs("%xlsfile%") wb.Close xl.Quit $offecho

The steps are as follows:

1. Load data into GAMS.
2. Create a 4 dimensional cube parameter suited for display as a pivot table. The dimensions are commodity, country, year, attribute.
3. Export the cube as GDX file.
4. Convert the GDX file to a CSV file.
5. Run a VBscript script that talks to Excel and performs the following steps:
   1. Load the data into the data model.
   2. Create a pivot table for this data.
   3. Create an initial layout that makes sense for a user of the data (opposed to an empty pivot table).

The result will look like:

We see three regions. On the left we see the actual pivot table:

The most right panel allows to change the layout of the pivot table:

Note that our 4 dimensional cube parameter has become a 5 column table: the values are a separate column.

The smaller panel in the middle shows the queries in data model:

Data model

The step to load the CSV data into the data model of Excel is actually written in what is called the M language:

1. Source = Csv.Document(File.Contents("cube.csv"),[Delimiter=","])
2. #"Headers" = Table.PromoteHeaders(Source)
3. #"Type" = Table.TransformColumnTypes(#"Headers",{{"Val", Number.Type}}) in #"Type"

Interestingly we can follow these steps after the fact in the Excel Query Editor. After the first step Source we see the raw CSV data has been imported:

The second step promotes the headers:

and the last step changes the type of the value column to numeric:

File Sizes

The method in (1) requires both an .xlsx file and a .csv file to be present. For this example, we end up with:

02/18/2017  12:49 PM         6,397,317 pivotcube.xlsx 02/18/2017  12:48 PM        62,767,308 cube.csv

The method shown here produces a self-contained .xslx file. We have:

02/18/2017  12:52 PM         2,958,573 pivotcube.xlsx

I am not sure why this version is even smaller than the pivot table with external CSV data, but apparently the internal data model can be squeezed very efficiently. Some of it may have to do with the internal Vertipaq Compression Engine which allows for very efficient column storage of the data in the data model (3). 

References

1. http://yetanothermathprogrammingconsultant.blogspot.com/2016/04/export-large-result-sets-to-pivot-table.html contains a script to generate a pivot table with a CSV file as an external data source.
2. http://yetanothermathprogrammingconsultant.blogspot.com/2016/07/excel-pivot-table-and-large-data-sets.html shows how to load CSV data into the data model and create a pivot table by hand.
3. Alberto Ferrari and Marco Russo, The VertiPaq Engine in DAX, https://www.microsoftpressstore.com/articles/article.aspx?p=2449192&seqNum=3 (chapter from The Definitive Guide to DAX, Business intelligence with Microsoft Excel, SQL Server Analysis Server, and Power BI, Microsoft Press, 2015).