Connecting to TM1 Using VB.Net and the .Net API

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
Alan Kirk
Site Admin
Posts: 5729
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Connecting to TM1 Using VB.Net and the .Net API

Post by Alan Kirk » Mon Dec 27, 2010 7:03 pm

Edit 25-Oct-13 Before you start developing a lot of code in the .Net API you should be aware of this Technote which I found today that IBM had posted last July:
TM1 10.1.x and up releases

The dotNet documentation is no longer delivered with the TM1 Product in version 10 and higher.

Please choose to develop software with the TM1 API in C++ and Java only.
My feelings about IBM's conduct in winding back support for an API are adequately covered here. Do not look at them directly without wearing welding goggles.

All things considered if you're just starting out in the .Net API you may find yourself heading down a dead end which may eventually be unsupported. With that caution in mind, feel free to read the rest of this.
===============================================================================================
We seem to have been getting a few questions lately along the lines of "How do I connect to TM1 using .Net". Obviously you need to do that via one of the TM1 Application Programming Interfaces (APIs), but the process isn't always straightforward. There are in fact several APIs for TM1:
  1. The classic VB6/VBA/C++ API which has been around for ages. This is NOT an object oriented API; instead you need to provide declarations to functions which are contained in the corresponding .dll files. You then call those functions in your code. You need to import a module called tm1api.bas (which will be found in the C:\Program Files\Cognos\TM1\API\TM1API folder (for a standard TM1 installation) ) into your project and then change all of the Long references in the function declarations to Integers since .Net uses 64 bit Longs and the classic API expects 32 bit Longs. (Edit: From version 10.1 onwards add an "ibm" folder above the Cognos one in the path shown.) There may be additional requirements if you're using C# rather than VB.Net, but since I don't use the former language I can't go into that. This post is not about using the classic API in .Net, though if I get around to it I may make such a post in the future. (Edit: Since done; the link is here.) However I'm not sure that I'd recommend developing in .Net using the classic API anyway. (Edit: {Cough}, boy did I change my mind about that since I wrote this...)
  2. Apparently there's a Java API for TM1, but I haven't even looked at it.
  3. It could be argued that the functions that you can call in an Excel workbook through VBA using the Application.Run method also constitute an API, but a rather less scary one than we're talking about here.
  4. There is a specific .Net API for TM1, which is both object oriented, and really, really badly documented. The "documentation", such as it is, consists of nothing more than a compiled help file (.chm) which will be found, for normal installations, as C:\Program Files\Cognos\TM1\API\DOTNETAPIDOC\TM1_NET_API_Doc.chm. First of all, because it's a .chm rather than being in a structured .pdf document there is no general introduction that you can follow, no "where do I start with this thing?" chapter. Second, every frappin' example is written for C#, which is just oh-so-very-useful for the majority of TM1 Admins who would doubtless come from Excel backgrounds, and therefore VBA backgrounds, and who could therefore be expected to program in VB.Net should they choose to go down the .Net path which we'll all have to eventually, albeit kicking and screaming. (If you have a substantial code base which needs migration or lack the time to learn a new language, anyway; I have to admit that I like a lot of things in .Net and Visual Studio which make life easier, and developing new code in there is often preferable to sticking with VB6 from what I've seen so far. However the transition learning curve is rather steeper than MS would have you believe.) This post is about using that API, and how to perform some simple operations with it (gathering information and executing a chore) using VB.Net. Unfortunately I can't offer something similar in C# since I don't use it.
The usual disclaimers apply:
  • Use this code at your own risk! Nobody else will be held responsible if you crash your server or alter the trajectory of a meteor causing it to crash into your building or cause any other data or life threatening action as a result of using this code or any code derived from it.
  • If you don't know, seriously and deep down, that you need to use any of the APIs to achieve a task, then don't. The use of any API adds a layer of risk and complexity to a solution and as with all problems, a solution should be no more complex than it needs to be.
With all that having been said, let's get on with the show.

This example was coded as a standard Windows Forms application in VB.Net using Visual Studio 2010 and the .Net API in TM1 9.5.1. Please note that the total development time (not counting a bit of "playing around) that I've invested in VB.Net prior to writing the code below was about two hours. (Add another couple of hours for this code though most of that was spent just trying to understand the object model.) This means that I can't guarantee that I have "state of the art" VB.Net coding skills and if you see any egregious syntax "undesirables", that will be why. However I've tested the code and it does work, though you'll need to read through the commentary carefully and make whatever changes are necessary (admin host name, server name, etc) for your own environment. The code has error handling but I haven't "prettied" the messages up to make them meaningful.

You need to first add a reference to the .Net API library. Ensure that the Solution Explorer window is visible (View menu), right click on your Project (not the Solution, the Project) and choose Add Reference.... Go to the Browse tab and locate Applix.TM1.API.dll which should be in C:\Program Files\Cognos\TM1\bin for a standard install.

Having done that, you should import the assembly. In the code module of your Windows Form, add:

Code: Select all

Imports Applix.TM1.API
Now comes the messy bit. Apparently for 9.5.1 the .Net API targets the .Net Framework version 3.5, which is why I do not recommend trying to develop using that API with anything earlier than Visual Studio 2008. By default, however, a VB.Net 2010 project will attempt to target version 4.0 of the Framework which will cause you all manner of compilation headaches. Change the targeted version as follows:
  • Right click on the Project in Solution Explorer and select Properties
  • Select the Compile tab, and scroll down to the [Advanced Compile Options...] button.
  • In the drop-down at the bottom change the Target framework to .Net Framework 3.5. This will require the project to be restarted, but that's painless.
In my case I've added a button named cmd_Launch to my form, which I've renamed Main. This is the code from the module, which should at least provide a pointer on how to do something (anything!) with the .Net API.

Code: Select all

Option Explicit On

'You need to have the target Framework set to 3.5 for this to work.
Imports Applix.TM1.API

Public Class Main

    Private Sub cmd_Launch_Click(ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles cmd_Launch.Click
        'Example for using the .Net API of TM1
        'V1.0 (Alan Kirk) 27-Dec-2010

        'Used to iterate through object collections. See my caution
        'below about a gotcha that can occur if you declare these
        'as Longs rather than Integers.
        Dim i_ObjIdx As Integer, i_ObjCnt As Integer

        Dim objAdminSvr As TM1AdminServer
        Dim objServersCollection As TM1ServerInfoCollection
        Dim objServerInfo As TM1ServerInfo
        Dim objsDataServer As TM1Server
        Dim objTM1Process As TM1Process
        Dim objaParams(1) As TM1ProcessParameter

        'I don't consider myself to be a believer in the new .Net
        '"structured" error handling (that is, Try/Catch blocks) and consider
        'the good ol' fashioned On Error Goto syntax to have more 
        'advantages overall. (Edit, Oct-13: I've since changed my opinion 
        'on this but this is not the place to discus it.)
        'However it seems to be the way of the future (and certainly it's
        'the only option available when coding in SQL Server 2005 / 2008,
        'my third favourite piece of software after TM1 and Rome:Total War)
        'so I'm adopting it here.
        Try
            'Get a reference to the Admin Host. The first argument appears to
            'be the machine name that the Admin Server is running on (so 
            'obviously change this to yours), though you can also 
            'use a string array to refer to multiple machines.
            'As far as I can tell the second is just a constant string which, 
            'if the Help file is any guide (stop laughing), refers to 
            'the SSL certificate.
            objAdminSvr = New TM1AdminServer("PCS10030001", "tm1adminserver")

            'Now we'll get a reference to the collection of servers that are
            'registered with the Admin Server.
            'The Servers property is actually a TM1ServerInfoCollection object.
            objServersCollection = objAdminSvr.Servers

            'Let's just check how many servers there are:
            Debug.Print("The count of servers is " & CType(objServersCollection.Count, String))

            'Just for fun, let's list the names of the servers that we found!

            'Potential gotcha here; in VB6/VBA I would normally use a Long (32 bit) for
            'this kind of operation because Int (16 bits) data types end up being converted
            'to Longs internally anyway. However if you try to access an Item() property
            'in the TM1 .Net API with a .Net (64 bit) Long, you'll get some 
            'gibberish about a "narrowing" error. Take that to mean that you need 
            'to use an Int (32 bit) variable, not a Long (64 bit) one.
            i_ObjCnt = objServersCollection.Count

            Debug.Print("List of servers")
            Debug.Print("-------------------------")

            'The collections are Base 0, not Base 1.
            For i_ObjIdx = 0 To i_ObjCnt - 1
                Debug.Print(objServersCollection.Item(i_ObjIdx).Name)
            Next

            Debug.Print(vbNewLine)

            'This means that the items in the collection are actually 
            'TM1ServerInfo objects, NOT TM1Server objects. You must therefore 
            'call the Login method of the ServerINFO object to get a reference 
            'to an actual SERVER object. Your brain should adapt to this
            'after the 20th time you fall into that trap.
            '"Sound good?", as Jeff Probst used to ask in Survivor challenges.
            'OK, let's try it. NOTE: I installed my sample database as sData1, yours
            'may be sData. Or you may not have installed the sample databases
            'at all, in which case change this to your Dev one.
            objServerInfo = objServersCollection.Item("sData1")

            'Obviously change this if you're working with a different
            'server.
            objsDataServer = objServerInfo.Login("admin", "apple")

            'OK, since "How do I run a process?" seems to crop up quite
            'often in API questions, let's do that. First, let's see
            'how you can determine which ones are there.
            Debug.Print("List of processes (" & _
             CType(objsDataServer.Processes.Count, String) & " in total.)")
            Debug.Print("-------------------------")

            'This should all be pretty familiar by now.
            i_ObjCnt = objsDataServer.Processes.Count

            For i_ObjIdx = 0 To i_ObjCnt - 1
                Debug.Print(objsDataServer.Processes.Item(i_ObjIdx).Name)
            Next

            'Executing a process with no parameters is easy. This
            'is one that I've created on my server; you need to substitute
            'the name of one on yours. First, I'm getting a reference
            'to the Process object. As with most of the examples
            'in this code, this isn't strictly necessary; you could call
            'the process object straight from a higher object in the hierarchy
            'but this is to demonstrate how the various objects fit together
            'into the tree.
            objTM1Process = objsDataServer.Processes.Item("TestDotNetAPI")

            objTM1Process.Execute()

            'But now if we need to execute one WITH parameters, we need to 
            'use an array of TM1ProcessParameter objects.
            'In this case I've dimensioned a fixed array of two elements since
            'my test process has two parameters, but a dynamic array should work
            'as well.

            'As you can see from the code below, when you assign new parameter objects
            'to the array, the New statement (or more technically, the constructor of the
            'TM1ProcessParameter object) can take two alternative argument pairs. The
            'first argument is always the parameter name. The second can be either a string
            'or a double precision floating point value, depending on whether the parameter
            'is string or numeric respectively.

            objaParams(0) = New TM1ProcessParameter("NumericParamExample", 608.1971)
            objaParams(1) = New TM1ProcessParameter("StringParamExample", "A string is the thing")

            'So now we can pass that array to the alternative syntax of the Execute
            'method of the TM1 Process object:

            objTM1Process = objsDataServer.Processes.Item("TestDotNetAPIWithParams")

            objTM1Process.Execute(objaParams)

            'You have to log out using the ServerInfo object, not the Server object.
            objServerInfo.LogoutAll()

        Catch ex As Exception
            MessageBox.Show("Failed to complete the example. Error " _
             & ex.ToString, "TM1 Dot Net API Example", MessageBoxButtons.OK, _
             MessageBoxIcon.Error)
        End Try

        Try
            'Most likely .Net Garbage Collection will take care of outstanding
            'references once all of the handles to them are released, but I've
            'been doing classic VB too long not to avail myself of the opportunity
            'to manually dump object references when I'm done with them. I'm guessing
            'that the TM1 API classes implement the IDisposable interface given 
            'the presence of a Dispose() method so if it's there, I'll use it. 
            objTM1Process.Dispose()
            objsDataServer.Dispose()
            objServerInfo.Dispose()
            objServersCollection.Dispose()
            objAdminSvr.Dispose()

        Catch ex As Exception
            'Do nothing; an error in cleanup is probably harmless.
            'We'll just have to rely on the Garbage Collector
            'to do its thing.
        End Try

    End Sub

End Class
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

winsonlee
Regular Participant
Posts: 180
Joined: Thu Jul 01, 2010 3:06 am
OLAP Product: Cognos Express
Version: 9.5
Excel Version: 2007
Location: Melbourne, Australia

Re: Connecting to TM1 Using VB.Net and the .Net API

Post by winsonlee » Tue Dec 28, 2010 11:30 pm

thanks for the tutorial. It is very helpful to get me started.

Are you able to provide an example on how i can use TM1DimensionElementInsert with .NET programming ?

Is that function part of Applix.TM1.API library ?

User avatar
Alan Kirk
Site Admin
Posts: 5729
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: Connecting to TM1 Using VB.Net and the .Net API

Post by Alan Kirk » Tue Jan 04, 2011 10:05 pm

winsonlee wrote:thanks for the tutorial. It is very helpful to get me started.

Are you able to provide an example on how i can use TM1DimensionElementInsert with .NET programming ?

Is that function part of Applix.TM1.API library ?
No, it isn't. The only API that can do that is the "classic" VB/C++ API which makes learning the .Net API rather pointless if you're writing an administrative tool. In the words of Support:
The TM1 .NET API was developed mainly for read-only functionality, and there are no plans to transition/include the full functionality of the current TM1 API over to the TM1 .NET API. If you require the ability to create cubes and dimensions, you'll need to use the current TM1 API for this functionality.
It is still possible to use the "classic" API libraries with .Net but there are many, many "gotchas" and bottomless pits to fall into and I'll bet substantial money that they'll never upgrade the standard API documentation to explain how to do it. I'll write up something on that when I've had a chance to experiment more. In the meantime I do suggest doing a search for any posts relating to the API written by Mike Cowie, who's our resident Classic API Guru; you may find something of use to you for your project in those.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
yyi
Community Contributor
Posts: 111
Joined: Thu Aug 28, 2008 4:42 am
Location: Sydney, Australia

Re: Connecting to TM1 Using VB.Net and the .Net API

Post by yyi » Thu Jan 06, 2011 1:02 am

I wished I saw this post a little earlier - as I'm also more familiar with VB/VBA.
anyway, ended up using the poorly documented C# example and got it to work

:? Still having trouble with VS2008 C# IDE with CSS, Positioning ...
If anyone has any good aspx VS tutorials - would appreciate it :mrgreen:

Code: Select all

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

// TM1 Libraries
using Applix.TM1.API;


namespace myWebApplication
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            //GetTM1Table();
            int loginSuccess;
            loginSuccess = tm1Main();   
        }

        // C# code sample from 'class ExampleApp' in TM1.NET API ?chm file
        //static int Main(string[] args)
        protected int tm1Main()
        {
            String adminHost, tm1Svr, tm1Uid, tm1Pwd;
            adminHost = TextBox1.Text;
            tm1Svr = TextBox2.Text;
            tm1Uid = "admin";
            tm1Pwd = "apple";
            int nRtn = 0;

            // Start by creating an Admin Server object 
            TM1AdminServer admin = new TM1AdminServer(adminHost, "tm1adminserver");

            try
            {
                // Obtain "sdata" server info object and then login 
                TM1Server server = admin.Servers[tm1Svr].Login(tm1Uid, tm1Pwd);

                if (server != null)
                {
                    // Start using database server 
                    TM1CubeCollection cubes = server.Cubes;
                    Label1.Text = "Number of cubes: " + cubes.Count;

                    foreach (TM1Cube cube in cubes)
                    {
                        ListBox1.Items.Add(cube.Name);
                    }

                }
                else
                {
                    // Handle failure to login 
                    nRtn = -1;
                }
            }
            catch (Exception exc)
            {
                // Handle exception 
                nRtn = -2;
            }

            // We're done with all database servers and the Admin Server object 
            admin.LogoutAll();
            admin.Dispose();
            return nRtn;
        }
    }
}
win2012 svr: tm1 v10.2.2 x64, client v10.2.2 x64/PA 2.0, win10, excel 365.

User avatar
yyi
Community Contributor
Posts: 111
Joined: Thu Aug 28, 2008 4:42 am
Location: Sydney, Australia

Re: Connecting to TM1 Using VB.Net and the .Net API

Post by yyi » Sun Apr 22, 2012 7:26 pm

been a while since i had looked at this but recently looking at it using 9.5, found an unusual error when accessing cube data.
it didn't recognise (not all) just some of the elements of a dimension.
eg:

Code: Select all

string[] iDimEl = new string[2];
iDimEl[0] = "2012"; 
iDimEl[1] = "Actual";
iCell = xCube.Cells[iDimEl];
after trying things like targeting older .net framework, running t.i. to export and import dimension element which failed,
found that the dimension was from a 9.5.1 and when it loaded into 9.5, worked fine from architect/perspectives but has
a glitch with read/write from .net api; after loading it back to 9.5.1 and re-attaching it's api to asp.net, it read just fine.
win2012 svr: tm1 v10.2.2 x64, client v10.2.2 x64/PA 2.0, win10, excel 365.

andersknudsen
Posts: 8
Joined: Thu May 07, 2009 2:10 pm
Version: 8.4
Excel Version: 2007

Re: Connecting to TM1 Using VB.Net and the .Net API

Post by andersknudsen » Wed Jul 03, 2013 10:24 pm

Thanks for a usefull .net api starter guide - I think it is most needed :shock:

I have been working a bit further, and I hope someone cane explain why this is ok
a = server.Processes.Item(0).Name
a = server.Cubes.Item(0).Name

but

a = server.Dimensions.Item(0).Name

retuns
[NullReferenceException: Object reference not set to an instance of an object.]
Applix.TM1.API.TM1AttributeDefinitionCollection.initialize(_TM1Object obj) +200
Applix.TM1.API.TM1AttributeDefinitionCollection..ctor(_TM1Object obj) +74
Applix.TM1.API.TM1DimensionCollection.Applix.TM1.API.ICollectionClient.InternalFetch(Int32 nIndex, Int32 nFetchSize) +230
Applix.TM1.API.CollectionCore.PopulateFully() +145
Applix.TM1.API.FilteredCollectionCore.get_Count() +50
Applix.TM1.API.CollectionCore.IsValidIndex(Int32 nIndex) +24
Applix.TM1.API.FilteredCollectionCore.get_Item(Int32 nIndex) +25
Applix.TM1.API.TM1DimensionCollection.get_Item(Int32 index) +13
_Default.Page_Init(Object sender, EventArgs e) in D:\Inetpub\wwwroot\tm1web\lev\tm1test2\Default.aspx.vb:47
System.Web.UI.Control.OnInit(EventArgs e) +131
System.Web.UI.Page.OnInit(EventArgs e) +15
System.Web.UI.Control.InitRecursive(Control namingContainer) +142
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1476

I hope to hear from you

Thanks,
Anders

Aphr0dite
Posts: 5
Joined: Mon Aug 24, 2015 1:12 pm
OLAP Product: Cognos TM1
Version: 10_2
Excel Version: 2010

Re: Connecting to TM1 Using VB.Net and the .Net API

Post by Aphr0dite » Mon Jan 11, 2016 1:11 pm

i tried using the C# code and its not connecting i think there are some configurations i am missing , i added the reference for the tm1.applix and i also added the log4net but some how it still does not connect. please help because i have a C# project that needs to connect to a TM1 application as well

here is the code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Web.Security;
using System.Data;

using Applix.TM1.API;

namespace Api
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

protected void Button1_Click(object sender, EventArgs e)
{
//getTm1Table();
int loginSuccess;
loginSuccess = tm1Main();

}

protected int tm1Main()
{
string adminhost, tm1Svr, tm1Uid, tm1Pwd,namespaceId;
adminhost = TextBox1.Text;
tm1Svr = TextBox2.Text;
namespaceId = TextBox3.Text;
tm1Uid = "";
tm1Pwd = "";
int nRtn = 0;



TM1AdminServer admin = new TM1AdminServer("adminHostName","Tm1AdminServer");
//try

//TM1Server server = admin.Servers[tm1Svr].Login(tm1Uid, tm1Pwd);
TM1ServerInfo serverinfo = admin.Servers[tm1Svr];
TM1Server server = serverinfo.Login(tm1Uid, tm1Pwd);-------THIS LINE THROWS AN ERROR INSTANCE OF OBJECT NOT SET WITH THE KEYWORD NEW
if (server != null)
{
TM1CubeCollection cubes = server.Cubes;
Label1.Text = "Number of cubes:" + cubes.Count;

foreach (TM1Cube cube in cubes)
{
ListBox1.Items.Add(cube.Name);

}

}
else
{
nRtn = -1;
}

//catch (Exception exc)
// {
// nRtn = -2;
//}

admin.LogoutAll();
admin.Dispose();
return nRtn;

}

private void getTm1Table()
{

}
}
}

Post Reply