Search code examples
c#exceladopowerpivotdatamodel

2013 C# connection to PowerPivot DataModel

This article Connecting to PowerPivot with C# is the closest to my question but it seems that it is out of date. I attempted the methods they showed in the answer but the example

 ThisWorkbook.Connections["PowerPivot Data"].OLEDBConnection.ADOConnection

Does not return the connection information, rather an exception.

But I was able to access the PP Data Model using:

ThisAddIn.Current.Application.ActiveWorkbook.Connections[1]. ModelTables[1].ModelTableColumns[1].Name`

But the object only allows you to look at the column names in the model. I wasn't able to get the connection information or anything.

ThisAddIn.Current.Application.ActiveWorkbook.Connections[1].ModelConnection.ADOConnection`

causes an exception and so does:

ThisAddIn.Current.Application.ActiveWorkbook.Connections[1].OLEDBConnection.ADOConnection

MY QUESTIONS

  • Have I made a mistake in my code?
  • Is there another method to connect to the datamodel that I haven't listed above?
  • Is it possible to read the records in the datamodel? (I was able to read column names)

Thank you for your time!

Solution

  • string ConnString = "Provider=MSOLAP;Data Source=$Embedded$; Locale Identifier=1033;Location=" + ThisAddIn.Current.Application.ActiveWorkbook.FullName + ";SQLQueryMode=DataKeys";

    Microsoft.AnalysisServices.Server OlapServer = new Microsoft.AnalysisServices.Server();

    OlapServer.Connect(ConnString);