locked
Connect to PowerPivot engine with c# RRS feed

  • Question

  • I am developing an addin for excel 2010 and i need to interact with powerpivot engine. Is that possible? Can i interact with powerpivot engine using AMO? How can i do that? I am trying with this

    Server

     

    svr = new Server();

    svr.Connect(

    "Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$");

    but, i have "data source must be specified..." error.

    Thanks

    Saturday, October 23, 2010 11:24 AM

Answers

  • Some feedback.

    To get connected with Vertipaq engine using the AMO library next connectionstring can be applied:

    “Provider=MSOLAP;Data Source=$Embedded$;Locale Identifier=1033;Location=WorkbookFullName;SQLQueryMode=DataKeys”


    ( ‘WorkbookFullName’ is path and name of the workbook). Second condition to get connected is that the Vertipaq engine should be loaded in memory else a COM error will be raised. 


    Eddy N.
    Sunday, December 11, 2011 10:22 PM

All replies

  • Currently there is no API interface for PowerPivot or the vertipaq engine.  Hopefully it be included in the next release


    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Saturday, October 23, 2010 8:11 PM
    Answerer
  • Hi Javier

    I have the same question/problem as tfcarlos. Currently with ADOMD I can refresh the connections to the DB and retrieve PP-data from other workbooks and consolidate it in a new one. I got the inspiration from this url http://olappivottableextend.codeplex.com/

    My next objective is to retrieve the content of the querydefinitions from the partitions making use of AMO, but I don't manage to get an inproc connection to the in-memory VertiPaq engine. As I can read everywhere on the internet there is no API neither any support exists. According to M. Russo (http://sqlblog.com/blogs/marco_russo/archive/2011/08/08/workaround-for-lack-of-powerpivot-api-in-excel.aspx) if it is not supported it does not mean it is not possible. You may lose your workbook if not properly designed, but that is my problem. 

    According to the information I found http://powerpivotgeek.com/2009/11/11/a-peek-inside-the-client-architecture/ it should be possible.  

    Any idea how to get connected? With AMO i mean :-)

     


    Eddy N.
    Monday, December 5, 2011 12:07 PM
  • Hi Eddy

    I am assuming you are issuing an MDX query to other PowerPivot workbooks hosted in SharePoint. Is this correct?

    Specifically, your intent is to get metadata information from the in-process data store?  I am not certain this can be done or is even recommended given the risks associated (and lack of support for the approach).  However, perhaps you have seen the blog post by Chris Webb on querying the in-memory Vertipaq engine using Data Management Views (which are a 'freebie' on PowerPivot, given its SSAS nature).

    Using a similar technique , you should be able to discover object definitions on the multidimensional interface of PowerPivot.

     

     http://cwebbbi.wordpress.com/2011/02/23/querying-powerpivot-dmvs-from-excel/

     




    Javier Guillen
    http://javierguillen.wordpress.com/
    Monday, December 5, 2011 2:56 PM
    Answerer
  • Hi Javier

    The data are indeed retrieved by issuing MDX queries but SharePoint is not involved.

    I know Chris' blog posts and his article about the DMV's. With the DMV's I tried to automate my documentation by retrieving column information. But one of the most interesting information, the DAX formulas, cannot be retrieved neither by making use of DMV's. It would be handy to export the columns the column properties to Excel and explain the more complex formulas especially for the newbies of DAX.

    There is also the option of XLMA, to get the content of CustomContent in itemxxx.xml. But this is not really practical and demands a lot of coding. So making use of the AMO library to access the Vertipac engine would an elegant solution.  

     

     


    Eddy N.
    Monday, December 5, 2011 10:47 PM
  • So is your intent to have a call to the in-memory data model to retrieves each calculated column AND measure, and its underlying DAX calculation correct?  I am not aware of an approach to achieve this, but will certainly post one here if I run into one.

    I imagine this is intended for 'documentation purposes only' (of the Vertipaq data model)?




    Javier Guillen
    http://javierguillen.wordpress.com/

    Wednesday, December 7, 2011 3:09 PM
    Answerer
  • Javier

    Correct. Based on the article of Chris Webb I created the VBA routine as below to get an insight of what's available as information in PowerPivot. Like view DBSCHEMA_COLUMNS I use to retrieve the columns for each measuregroup and store it on a worksheet. Than I can start commenting the meaning of the columns. Sometimes formulas are more complex (when you start with ranking tricks) and need some extra explanation. Currently there are not a lot of people who have much experience with PowerPivot. This way we lower the barrier and help them to understand more easily the DAX functions and its power. 

    The problem with the DMV's I have are the missing formulas. This would be handy, now is copy and paste which is realy time consuming. 

    PS. I used the information of view MDSCHEMA_MEASURES to retrieve PowerPivot data from other workbooks.    

    The routine below creates for each DMV defined a worksheet.

    Some views will return an error:

    DISCOVER_DIMENSION_STAT - Application-defined or object-defined error
    DISCOVER_INSTANCES - Application-defined or object-defined error
    DISCOVER_LOCKS - Application-defined or object-defined error
    DISCOVER_PARTITION_DIMENSION_STAT - Application-defined or object-defined error
    DISCOVER_PARTITION_STAT - Application-defined or object-defined error
    DISCOVER_PERFORMANCE_COUNTERS - Application-defined or object-defined error
    MDSCHEMA_PROPERTIES - Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic.
    MDSCHEMA_PROPERTIES - Select method of Range class failed

    Sub GetDMVs()
     
        On Error GoTo Errhandler
     
        Dim sDMV       As String
        Dim sSheet     As String
        Dim vArray(56) As String
       
        vArray(0) = "DBSCHEMA_CATALOGS"
        vArray(1) = "DBSCHEMA_COLUMNS"
        vArray(2) = "DBSCHEMA_PROVIDER_TYPES"
        vArray(3) = "DBSCHEMA_TABLES"
        vArray(4) = "DISCOVER_COMMAND_OBJECTS"
        vArray(5) = "DISCOVER_COMMANDS"
        vArray(6) = "DISCOVER_CONNECTIONS"
        vArray(7) = "DISCOVER_DB_CONNECTIONS"
        vArray(8) = "DISCOVER_DIMENSION_STAT"
        vArray(9) = "DISCOVER_ENUMERATORS"
        vArray(10) = "DISCOVER_INSTANCES"
        vArray(11) = "DISCOVER_JOBS"
        vArray(12) = "DISCOVER_KEYWORDS"
        vArray(13) = "DISCOVER_LITERALS"
        vArray(14) = "DISCOVER_LOCKS"
        vArray(15) = "DISCOVER_MASTER_KEY"
        vArray(16) = "DISCOVER_MEMORYGRANT"
        vArray(17) = "DISCOVER_MEMORYUSAGE"
        vArray(18) = "DISCOVER_OBJECT_ACTIVITY"
        vArray(19) = "DISCOVER_OBJECT_MEMORY_USAGE"
        vArray(20) = "DISCOVER_PARTITION_DIMENSION_STAT"
        vArray(21) = "DISCOVER_PARTITION_STAT"
        vArray(22) = "DISCOVER_PERFORMANCE_COUNTERS"
        vArray(23) = "DISCOVER_PROPERTIES"
        vArray(24) = "DISCOVER_SCHEMA_ROWSETS"
        vArray(25) = "DISCOVER_SESSIONS"
        vArray(26) = "DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS"
        vArray(27) = "DISCOVER_STORAGE_TABLE_COLUMNS"
        vArray(28) = "DISCOVER_STORAGE_TABLES"
        vArray(29) = "DISCOVER_TRACE_COLUMNS"
        vArray(30) = "DISCOVER_TRACE_DEFINITION_PROVIDERINFO"
        vArray(31) = "DISCOVER_TRACE_EVENT_CATEGORIES"
        vArray(32) = "DISCOVER_TRACES"
        vArray(33) = "DISCOVER_TRANSACTIONS"
        vArray(34) = "DMSCHEMA_MINING_COLUMNS"
        vArray(35) = "DMSCHEMA_MINING_FUNCTIONS"
        vArray(36) = "DMSCHEMA_MINING_MODEL_CONTENT"
        vArray(37) = "DMSCHEMA_MINING_MODEL_CONTENT_PMML"
        vArray(38) = "DMSCHEMA_MINING_MODEL_XML"
        vArray(39) = "DMSCHEMA_MINING_MODELS"
        vArray(40) = "DMSCHEMA_MINING_SERVICE_PARAMETERS"
        vArray(41) = "DMSCHEMA_MINING_SERVICES"
        vArray(42) = "DMSCHEMA_MINING_STRUCTURE_COLUMNS"
        vArray(43) = "DMSCHEMA_MINING_STRUCTURES"
        vArray(44) = "MDSCHEMA_CUBES"
        vArray(45) = "MDSCHEMA_DIMENSIONS"
        vArray(46) = "MDSCHEMA_FUNCTIONS"
        vArray(47) = "MDSCHEMA_HIERARCHIES"
        vArray(48) = "MDSCHEMA_INPUT_DATASOURCES"
        vArray(49) = "MDSCHEMA_KPIS"
        vArray(50) = "MDSCHEMA_LEVELS"
        vArray(51) = "MDSCHEMA_MEASUREGROUP_DIMENSIONS"
        vArray(52) = "MDSCHEMA_MEASUREGROUPS"
        vArray(53) = "MDSCHEMA_MEASURES"
        vArray(54) = "MDSCHEMA_MEMBERS"
        vArray(55) = "MDSCHEMA_PROPERTIES"
        vArray(56) = "MDSCHEMA_SETS"

        For i = 0 To UBound(vArray, 1)
       
            sDMV = vArray(i)
            sSheet = Mid(sDMV, InStr(sDMV, "_") + 1, 22)
           
            Sheets.Add
            ActiveSheet.Name = sSheet
       
            Worksheets(sSheet).Range("A1").Select
            With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
                "OLEDB;Provider=MSOLAP.4;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Optimize Response=3;Cell Error Mode=TextValue"), Destination:=Range("$A$1")).QueryTable
                .CommandType = xlCmdDefault
                '.CommandText = Array("select TABLE_NAME, COLUMN_NAME from $system." & sDMV)
                .CommandText = Array("select * from $system." & sDMV)
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .ListObject.DisplayName = sDMV
                .Refresh BackgroundQuery:=False
            End With
      
       Next i
    Exit Sub
    Errhandler:

        Debug.Print sDMV & " - " & Err.Description
        Resume Next
       
    End Sub


    Eddy N.
    Friday, December 9, 2011 10:06 AM
  • Some feedback.

    To get connected with Vertipaq engine using the AMO library next connectionstring can be applied:

    “Provider=MSOLAP;Data Source=$Embedded$;Locale Identifier=1033;Location=WorkbookFullName;SQLQueryMode=DataKeys”


    ( ‘WorkbookFullName’ is path and name of the workbook). Second condition to get connected is that the Vertipaq engine should be loaded in memory else a COM error will be raised. 


    Eddy N.
    Sunday, December 11, 2011 10:22 PM
  • Does this work for Office 2013 64bit? It throws a com error for me and the Engine is loaded into memory.
    Microsoft.AnalysisServices.ConnectionException was caught
      _HResult=-2146233088
      _message=A connection cannot be made. Ensure that the server is running.
      HResult=-2146233088
      IsTransient=false
      Message=A connection cannot be made. Ensure that the server is running.
      Source=Microsoft.AnalysisServices
      StackTrace:
           at Microsoft.AnalysisServices.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
           at Microsoft.AnalysisServices.XmlaClient.Connect(String connectionString, Boolean beginSession)
           at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId)
           at Microsoft.AnalysisServices.Server.Connect(String connectionString)
           at MongoAddIn.SaturnRibbon.DataModelConnection_Click(Object sender, RibbonControlEventArgs e) in c:\Users\dtaylor\Documents\Visual Studio 2013\Repos\Saturn\MongoAddIn\MongoAddIn\SaturnRibbon.cs:line 112
      InnerException: Microsoft.AnalysisServices.XmlaStreamException
           _HResult=-2146232800
           _message=The '' local cube file cannot be opened.
           HResult=-2146232800
           IsTransient=false
           Message=The '' local cube file cannot be opened.
           Source=Microsoft.AnalysisServices
           StackTrace:
                at Microsoft.AnalysisServices.LocalCubeStream..ctor(String cubeFile, OpenFlags settings, Int32 timeout, String password, String serverName)
                at Microsoft.AnalysisServices.XmlaClient.OpenLocalCubeConnection(ConnectionInfo connectionInfo)
                at Microsoft.AnalysisServices.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
           InnerException: System.ComponentModel.Win32Exception
                _HResult=-2147467259
                _message=The handle is invalid
                HResult=-2147467259
                IsTransient=false
                Message=The handle is invalid
                Source=Microsoft.AnalysisServices
                ErrorCode=-2147467259
                NativeErrorCode=6
                StackTrace:
                     at Microsoft.AnalysisServices.LibraryHandle.ThrowOnError()
                     at Microsoft.AnalysisServices.LibraryHandle.CheckEmptyHandle(IntPtr handle)
                     at Microsoft.AnalysisServices.MsmdlocalWrapper.MSMDOpenLocal(String pszPathToFile, OpenFlags mskSettings, String pszPassword, String serverName)
                     at Microsoft.AnalysisServices.LocalCubeStream..ctor(String cubeFile, OpenFlags settings, Int32 timeout, String password, String serverName)
                InnerException: 
    


    • Edited by dtaylorw Wednesday, July 9, 2014 3:14 PM added exception
    Wednesday, July 9, 2014 3:07 PM
  • Does this work for Office 2013 64bit? It throws a com error for me and the Engine is loaded into memory.
    Microsoft.AnalysisServices.ConnectionException was caught
      _HResult=-2146233088
      _message=A connection cannot be made. Ensure that the server is running.
      HResult=-2146233088
      IsTransient=false
      Message=A connection cannot be made. Ensure that the server is running.
      Source=Microsoft.AnalysisServices
      StackTrace:
           at Microsoft.AnalysisServices.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
           at Microsoft.AnalysisServices.XmlaClient.Connect(String connectionString, Boolean beginSession)
           at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId)
           at Microsoft.AnalysisServices.Server.Connect(String connectionString)
           at MongoAddIn.SaturnRibbon.DataModelConnection_Click(Object sender, RibbonControlEventArgs e) in c:\Users\dtaylor\Documents\Visual Studio 2013\Repos\Saturn\MongoAddIn\MongoAddIn\SaturnRibbon.cs:line 112
      InnerException: Microsoft.AnalysisServices.XmlaStreamException
           _HResult=-2146232800
           _message=The '' local cube file cannot be opened.
           HResult=-2146232800
           IsTransient=false
           Message=The '' local cube file cannot be opened.
           Source=Microsoft.AnalysisServices
           StackTrace:
                at Microsoft.AnalysisServices.LocalCubeStream..ctor(String cubeFile, OpenFlags settings, Int32 timeout, String password, String serverName)
                at Microsoft.AnalysisServices.XmlaClient.OpenLocalCubeConnection(ConnectionInfo connectionInfo)
                at Microsoft.AnalysisServices.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
           InnerException: System.ComponentModel.Win32Exception
                _HResult=-2147467259
                _message=The handle is invalid
                HResult=-2147467259
                IsTransient=false
                Message=The handle is invalid
                Source=Microsoft.AnalysisServices
                ErrorCode=-2147467259
                NativeErrorCode=6
                StackTrace:
                     at Microsoft.AnalysisServices.LibraryHandle.ThrowOnError()
                     at Microsoft.AnalysisServices.LibraryHandle.CheckEmptyHandle(IntPtr handle)
                     at Microsoft.AnalysisServices.MsmdlocalWrapper.MSMDOpenLocal(String pszPathToFile, OpenFlags mskSettings, String pszPassword, String serverName)
                     at Microsoft.AnalysisServices.LocalCubeStream..ctor(String cubeFile, OpenFlags settings, Int32 timeout, String password, String serverName)
                InnerException: 

    Wednesday, July 9, 2014 3:22 PM
  • If the model is created in 64bit Excel

    is it accessible through a 32bit application using the given connection string?

    for me it works when running through a 64bit application but when I try to access the datamodel through a 32bit application it says " the connection string is not valid"

    Wednesday, July 30, 2014 10:05 PM