Excel::PowerPivot::Utils - utilities for scripting Power Pivot models within Excel workbooks
use Excel::PowerPivot::Utils; my $ppu = Excel::PowerPivot::Utils->new(UTF8 => 1); # operations on the whole model ... print $ppu->whole_model_as_YAML; $ppu->inject_whole_model({QUERIES => ..., RELATIONSHIPS => ... MEASURES => ...}); # .. or specific operations on queries, relationships or measures print $ppu->queries_as_YAML; $ppu->inject_queries ([{Name => 'New_table', Formula => $M_formula_for_new_table}]); print $ppu->relationships_as_YAML; $ppu->inject_relationships([ {ForeignKey => 'Album.ArtistId', PrimaryKey => 'Artist.ArtistId', Active => 1}, ... ]); print $ppu->measures_as_YAML; $ppu->inject_measures([{Name => 'Invoice Lines Total Amount', AssociatedTable => 'InvoiceLine', Description => 'sum of quantities multiplied by unit price', FormatInformation => [qw/Currency USD 2/], Formula => 'SUMX(InvoiceLine, InvoiceLine[UnitPrice] * InvoiceLine[Quantity]' }, ... ]);
This module uses OLE automation to interact with an Excel Power Pivot model. It can be used for example for
documenting existing models
scripting series of updates or inserts on measures or queries as batch operations -- useful for propagating similar changes to a series of models.
use a version control system on textual exports of the model
Obviously, this module only works on a Windows platform with a local installation of Microsoft Office 2016 or greater.
The exposed interface hides details about the interaction with the Excel object model as documented in https://learn.microsoft.com/en-us/office/vba/excel/concepts/about-the-powerpivot-model-object-in-excel; nevertheless, some knowledge of that model and of the Win32::OLE module is recommended to fully understand what is going on.
my $ppu = Excel::PowerPivot::Utils->new(%options);
Creates a new instance. Options are :
A Win32::OLE object representing an Excel workbook. If none is supplied, it will connect to the currently running Excel instance and take the active workbook as default.
Win32::OLE
A logger object equipped with debug, info and warning methods. If none is supplied, a simple logger is automatically created from Log::Dispatch.
debug
info
warning
A boolean flag for setting the Win32::OLE codepoint option to UTF8, so that strings are properly encoded/decoded between Perl and the OLE server. It is highly recommended to systematically set this option to true, since this module is mostly used together with YAML, which uses UTF8 encoding.
This option will automatically trigger Win32::OLE->Option(CP => CP_UTF8) at object construction time, and will set it back to the previous value at object destruction time. Beware however that this is a change in global state, so if your program performs other operations through Win32::OLE during the lifetime of the Excel::PowerPivot::Utils object, string handling might be affected. This is the reason why we require this option to be set explicitly instead of being enabled automatically by default.
Win32::OLE->Option(CP => CP_UTF8)
Excel::PowerPivot::Utils
Returns information about queries in the Excel workbook. This is a list of hashrefs containing
the name of the query
the M formula (Power Query language)
optional description text
Content of the "queries" method as a YAML string, nicely formatted so that it is easily readable by humans.
$ppu->inject_queries($queries, %options);
Takes an arrayref or query specifications. Each specification must be a hashref with keys Name, Formula and optionally Description. For names corresponding to queries already in the workbook, this is an update operation; other queries in the list are added to the workbook.
Name
Formula
Description
Options are :
If true, queries not mentioned in the list are deleted from the workbook. False by default.
If true, queries are automatically associated with workbook connections. This is equivalent to manually checking "Add this data to the Data Model" in the "Close and Load To" dialog of Power Query. True by default. When adding or deleting queries in the model, Excel recomputes the whole model, so this operation may be quite slow.
Activates the FastCombine property -- see https://learn.microsoft.com/en-us/office/vba/api/excel.queries.fastcombine.
FastCombine
Deletes the OLEDB connection associated with the given query name.
Returns information about relationships in the Excel model. Due to the inner constraints of Power Pivot, all relationships are many-to-one. The returned structure is a list of hashrefs containing :
A single string of form $table.$column, describing the "many" side of the relationship.
$table.$column
A single string of form $table.$column, describing the "one" side of the relationship.
A boolean stating if the relationship is active or not.
Content of the "relationships" method as a YAML string, nicely formatted so that it is easily readable by humans.
$ppu->inject_relationships($relationships, %options);
Takes an arrayref or relationship specifications. Each specification must be a hashref with keys ForeignKey, PrimaryKey and Active. For pairs (foreign key, primary key) corresponding to relationships already in the model, this is an update operation on the Active property; otherwise the relationships are added to the model.
ForeignKey
PrimaryKey
Active
If true, relationships not mentioned in the list are deleted from the model. False by default.
Returns information about measures in the Excel model. This is a list of hashrefs containing
the name of the measure
the name of the table to which this measure is associated
the DAX formula
an arrayref describing the format for displaying that measure. Formats are documented in https://learn.microsoft.com/en-us/office/vba/api/excel.model. The first member of the array is the name of the ModelFormat object, followed by the values of its properties. The properties for each format are listed in the table below :
ModelFormat Property 1 Property 2 =========== ========== ========== Currency Symbol DecimalPlaces Date FormatString DecimalNumber UseThousandSeparator DecimalPlaces General PercentageNumber UseThousandSeparator DecimalPlaces ScientificNumber DecimalPlaces WholeNumber UseThousandSeparator
Content of the "measures" method as a YAML string, nicely formatted so that it is easily readable by humans.
$ppu->inject_measures($measures, %options);
Takes an arrayref or measure specifications. Each specification must be a hashref with keys Name, AssociatedTable, Formula and optionally Description and FormatInformation. Values for those keys are strings, except for FormatInformation which takes an arrayref according to the table above.
AssociatedTable
FormatInformation
For names corresponding to measures already in the model, this is an update operation; other measures in the list are added to the model.
If true, measures not mentioned in the list are deleted from the model. False by default.
Returns a single YAML string containing descriptions for queries, relationships and measures.
my $model_to_inject = YAML::Load($whole_model_as_YAML); $ppu->inject_whole_model($model_to_inject, %options);
Takes as input a hashref with keys QUERIES, RELATIONSHIPS and MEASURES, and calls methods "inject_queries", "inject_relationships" and "inject_measures" on the corresponding subtrees.
QUERIES
RELATIONSHIPS
MEASURES
Unfortunately this module cannot add DAX computed columns to a model table ... because there is no available method for this task in the VBA interface for Excel.
For the same reason, there is no support either for manipulating hierarchies (i.e groups of columns in a dimension table).
In principle the OLE mechanism allows one to open a connection to an Excel workbook through
my $workbook = Win32::OLE->GetObject($pathname);
However, launching Power Query or Power Pivot operations on such connections does not work well -- I experienced several crashes or file corruptions. So the recommended way is to connect to a running Excel instance, and use that connection to open the workbook. The t/02_chinook.t file in this distribution contains a full example; here is the excerpt doing the connection :
t/02_chinook.t
my $xl = Win32::OLE->GetActiveObject("Excel.Application") or skip "can't connect to an active Excel instance"; my $workbook = $xl->Workbooks->Open($fullpath_xl_file) or skip "cannot open OLE connection to Excel file $fullpath_xl_file";
File t/02_chinook.t this distribution is a full example dealing with the Chinook database, an open source dataset. A diagram of the relational schema can be seen at https://schemaspy.org/sample/relationships.html.
The test script performs the following operations :
download the sqlite database
generate an Excel file with an Excel table for each database table (through the companion module Excel::ValueWriter::XLSX).
inject the model :
Power Queries to connect the Excel tables to the Power Pivot model. Here is an example of the YAML description :
#====================================================================== - Name : Album #====================================================================== Description : Formula : |- let Album_Table = Excel.CurrentWorkbook(){[Name="Album"]}[Content], #"Modified type" = Table.TransformColumnTypes(Album_Table,{ {"AlbumId", Int64.Type}, {"Title", type text}, {"ArtistId", Int64.Type}}) in #"Modified type"
Power Pivot relationships between tables loaded into the model. Here is an example of the YAML description :
#====================================================================== - ForeignKey : Album.ArtistId PrimaryKey : Artist.ArtistId Active : 1 #======================================================================
Power Pivot measures.Since this is just for demonstration purposes, only 3 measures are defined. Here is the YAML description :
#====================================================================== - Name : Invoice Lines Total Amount #====================================================================== AssociatedTable : InvoiceLine Description : FormatInformation : [Currency, USD, 2] Formula : |- SUMX(InvoiceLine, InvoiceLine[UnitPrice] * InvoiceLine[Quantity]) #====================================================================== - Name : Invoice Total Amount #====================================================================== AssociatedTable : Invoice Description : FormatInformation : [Currency, USD, 2] Formula : |- SUM(Invoice[Total]) #====================================================================== - Name : Invoice Lines Percentage Sales #====================================================================== AssociatedTable : InvoiceLine Description : FormatInformation : [PercentageNumber, 1, 0] Formula : |- DIVIDE([Invoice Lines Total Amount], [Invoice Total Amount])
Once the Power Pivot model is in place, we can start building pivot tables based on the DAX measures. For this task the Perl module has no added value, it is done through standard OLE automation :
# create a Pivot Table (percentage of sales per genre, for each customer country) my $pcache = $workbook->PivotCaches->Create(xlExternal, $workbook->Connections("ThisWorkbookDataModel")); my $ptable = $pcache->CreatePivotTable("ComputedPivot!R5C1", 'Sales_by_genre_and_country'); $ptable->CubeFields("[Measures].[Invoice Lines Percentage Sales]")->{Orientation} = xlDataField; $ptable->CubeFields("[Genre].[Name]") ->{Orientation} = xlColumnField; $ptable->CubeFields("[Customer].[Country]") ->{Orientation} = xlRowField;
Then it is possible to write Excel formulas that extract values from the pivot cache. So for example here is the formula that retrieves the percentage of sales for the "Classical" genre in Austria :
= CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Invoice Lines Percentage Sales]", "[Genre].[Name].[Classical]", "[Customer].[Country].[Austria]")
Laurent Dami, <dami at cpan.org>
Copyright 2023 by Laurent Dami.
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
To install Excel::PowerPivot::Utils, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Excel::PowerPivot::Utils
CPAN shell
perl -MCPAN -e shell install Excel::PowerPivot::Utils
For more information on module installation, please visit the detailed CPAN module installation guide.