Is it possible to connect to a PowerPivot model in an Excel .xlsx file? (Not hosted on a SharePoint site... just the local file).
It must be, since Tableau can do it.
Anyone have any clues?
Power Pivot primarily uses Data Analysis Expressions (DAX) as its expression language, although the model can be queried via Multidimensional Expressions (MDX) in a row set expression.
First, import your dataset into your Power Pivot workbook. Next create a Power Pivot table. Then, on the Power Pivot tab, click the arrow below PivotTable and select PivotChart. Select “Existing Worksheet” and click “OK.” Excel will add an empty PivotChart to the same worksheet.
Short answer:
Long answer:
References/Credits:
Details:
Like @gobansaor, we found that starting with a workbook which already had a connection to the PP cache was helpful (necessary?). Before connecting to the PP cache via AMO for instance, we make sure the connection is alive:
ThisWorkbook.Connections["PowerPivot Data"].Reconnect()
or
ThisWorkbook.Connections["PowerPivot Data"].Refresh()
The connection string template we used for AMO was: Provider=MSOLAP;Data Source=$Embedded$;Locale Identifier=1033;Location={0};SQLQueryMode=DataKeys
and we filled that in with ThisWorkbook.FullName
Following @gobansaor, we connected to the cube via ADO using:
ADODB.Recordset recordSet = new ADODB.Recordset();
recordSet.Open("SELECT [Measures].[Min of Field1] ON COLUMNS FROM [Model]",
ThisWorkbook.Connections["PowerPivot Data"].OLEDBConnection.ADOConnection);
You can build a VSTO addin.
Here's a site that helps explain working with PowerPivot and VSTO.
http://blogs.msdn.com/b/analysisservices/archive/2011/08/04/how-to-build-a-vsto-based-powerpivot-workbook.aspx
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With