TM1 OLE DB MD Provider to connect Excel to TM1

[dsproffitt]

Does anyone know if there is any documentation that will guide me on this?

Checked the IBM website and they are saying that connection between the TM1 server and Excel using TM1 OLE DB MD Provider is not possible using MS 2010.

I know CDM uses this to connect to TM1 and the connection details that are used within CDM are working fine.

They are

Code: Select all

Name                    CDM TM1 RepGen  
Description             n/a
Expiration Policy       Daily at 12am
Provider                TM1
Server                  {server name}
Authentication type     Cognos Access Manager
User Id                 HQ\cbbc
Password                ****
Namespace ID            HQ
TM1 Server              Sdata
Cube                    AnyCube

[rmackenzie]

Super basic code sample:

Code: Select all

Sub TestMdx()

    Dim objCatalog As New ADOMD.Catalog
    Dim objCellset As New ADOMD.Cellset
    Dim strConnection As String
    Dim strMDX As String
    
    ' set connection string and mdx query
    strConnection = "Provider=TM1OLAP;Location=localhost;Data Source=planning sample;User ID=ADMIN;Password=apple;"
    strMDX = "SELECT [plan_controls].[Default] ON ROWS, [plan_measure_setting].[Default] ON COLUMNS FROM [plan_Control]"
    
    'open connection
    objCatalog.ActiveConnection = strConnection
    objCellset.Source = strMDX
    Set objCellset.ActiveConnection = objCatalog.ActiveConnection
    objCellset.Open
    
    'get some data from the query
    Debug.Print objCellset.Axes(0).Positions(0).Members(0).Caption
    
    'close the connection
    objCellset.Close

    'clean up
    Set objCellset = Nothing
    Set objCatalog = Nothing
    
End Sub

If you've installed the OLEDB MD provider on your client and referenced ADOMD within the Excel VBA project, this should work for the Planning Sample test server. What error do you get?

[rmackenzie]

PS looking closer at your config, I've no idea if that works with CAM. If the integrated security mode is 4 then maybe that's a blocker, but should be OK in mode 5?