aMD4P is a library that permits to manipulate the Power Pivot model inside a Excel workbook.
You known that Power Pivot has got its main UI by which one can authoring a generic business model.
Power Pivot is a part of Personal BI initiative launched some years ago by Microsoft
An user can prepare a business workbook importing data from external source and installing some calculation in DAX language.
But can we extend this intent?
Can Power Pivot be considered also an Enterprise BI tool not just Personal?
Let's give a meaning to ENTERPRISE
In my experience every enterprise BI suite has own modeling tool first.
I am talking about BI suite on Microsoft stack, which is the field that I known well.
Tipically in modeling activity we have the following requirements:

1. Authoring: Create, Modify, Delete business objects ( tables, links, dimensions, facts,... )


2. Deploy: Install or reinstall a subset or all of business objects in destination platform ( in our case an Excel workbook )


3. Abstraction: Keep separate the logical modeling from the physical.

Mixing standard technique and aMD4P it is possible to install inside a 'blank' workbook a full PowerPivot model.
Starting from Excel 2013 the class WorkbookConnection exposes a new method 'Add2' by which you can import data into the data model, i.e. you can create table in the model.
There is a beautiful chapter in book 'Microsoft Excel 2013: Building Data Models with PowerPivot' (Alberto Ferrari and Marco Russo) explaining importing/exporting techniques beetween Excel and PowerPivot.
By aMD4P you can complete the deploy process adding computed columns, relationships and measures.


Technically aMD4P is a .NET library that esposes a set of API to manage computed columns, relationships and measures of PowerPivot model.
It works inprocess in Excel and it can be referenced by an add-in VSTO or an Excel add-in XLAM
In order to extend the usability of aMD4P, a COM interface has been published.

Main Interface

public interface IModelDriver
{
void Initialize(Excel.Workbook thisWorkbook);

void AddComputedColumn(string tableName, string ColumnName, string expression);

void RemoveComputedColumn(string tableName, string ColumnName);

void UpdateComputedColumn(string tableName, string ColumnName, string expression);


void AddMeasure(string tableName, string measureName, string expression);

void RemoveMeasure(string tableName, string measureName);

void UpdateMeasure(string tableName, string measureName, string expression);


void AddRelationShip(string manyTableName, string manyColumnName, string oneTableName, string oneColumnName, bool active);

void RemoveRelationShip(string manyTableName, string manyColumnName, string oneTableName, string oneColumnName);

}

Using .NET framework

You need to add a reference to your project



Example of code c#
private void test_amd4p()
{
AModelDriver4PowerPivot.IModelDriver md = new AModelDriver4PowerPivot.ModelDriver();

md.Initialize(Globals.ThisAddIn.Application.ActiveWorkbook);

md.AddComputedColumn("V Qty", "Now", "NOW()");

md.AddMeasure("V Qty", "M One", "1");

}

Using in VBA environment

You need to register the library by regasm utility in order to publish the type library by a command like this:

<.NET path>\regasm.exe amd4p.dll /codebase /tlb

and attach a reference


Sub test_amd4p()

Dim md As New ModelDriver

md.Initialize ThisWorkbook

md.AddRelationShip "V Qty", "Option", "Option", "Option", True

md.AddComputedColumn "V Qty", "Now", "NOW()"

md.AddMeasure "V Qty", "M_One", "1"

Set md = Nothing

End Sub