.NET C# TM1API starter kit.

Introduction

One of the most powerful (and possibly overlooked) features of TM1 is that it supports a fully-featured programming API (Application Programming Interface).

This means that virtually any function supported by the server is exposed to external programming environments. Or, put more simply: you can write your own TM1 application, and your imagination is the limit!

Working with the TM1 API in Visual Basic is pretty easy, as the functions are available anytime the TM1 add-in is loaded. This is why a lot of developers gravitate toward working this way, as it is familiar and quick to get started.

Unfortunately, it also comes with the limitation of being hosted in the Excel environment, and cannot produce a stand-alone Windows or Web application.

Recent versions of TM1 include a .NET API that you can use in your applications, but this API does not support all the functions of the native COM API. It was initially developed to support the requirements of TM1Web and supports mainly read-only operations.

Microsoft’s .NET framework software development platform has grown the past decade to become one of the most comprehensive framework libraries out there. It’s free to use, supported by Microsoft, and is a great choice for quickly creating native Windows applications.

But how can we get the best of both worlds?

Note: this article assumes a basic level of C# programming capability and access to a 2010 version of Microsoft Visual Studio.

The TM1 COM API in .NET

It’s well-known that you can call COM dynamic link libraries from .NET. However, the protocol for doing so can be quite difficult to implement, especially when the DLL in question is not documented in an easy-to-translate way.

In .NET you need to know the underlying names and entry points of every single function call, and then map the COM data types to equivalent .NET data types.

You can analyze the tm1api.dll file using a tool like Dependency Walker, which gives you information about each function contained in the DLL. By default, the parameter and return types are not listed in any useful form, but if you right click in the window and select “Undecorate C++ Functions”, you’ll see that the output becomes a whole lot more interesting.

Here is an example output from Dependency Walker:

As you can see, all the functions are listed out with their associated function names and signatures. However, there are a lot of functions, so typing each one out would take a long time.

TM1 includes a full list of function signatures, but this is either a BAS file or a C header file. Those don’t help us with C#, but it turns out it’s pretty easy to convert this BAS to C# — I used an online VB.NET->C# converter, changed the longs to ints and switch any function with a _VB suffix to the C version. Since I’ve already gone through the process, you can use the one included in the sample package.

Disclaimer: I make no warranty or guarantee that the included code is complete, correct or bug free. Use it at your own risk.

Loading the TM1API DLL in .NET

Once you have the function signatures listed in a .NET class, you’re about halfway there. You still need to make your program load the TM1API DLL at runtime.

When I first started with the TM1 API, I used the pInvoke windows function “LoadLibrary” to load the TM1 DLLs. This was painful, as I had to load each DLL individually, including every library the TM1API was dependent on. As TM1 versions progresses, this list grew, and backward compatibility with previous version of TM1 became more difficult, as the list of required DLLs was different for each.

When I started working on the Flow Model Packager, I revised this approach and found a better way. Now I simply pInvoke the “SetDllPath” windows function, which adds the TM1API folder to the search path. This way, the program locates all dependent DLLs automatically, irrespective of how many dependent libraries there are.

Using the sample code

Loading the DLL and defining the API function calls is all taken care of in the Flow.TM1.API class provided in the sample code. To use it take the following steps.

1) Connect to TM1:

TM1API.TM1DLLPath = "[tm1 api path]";
TM1API.TM1APIInitialize();

// Initialize the user session and create a value pool
int hUser = TM1API.TM1SystemOpen();
int hPool = TM1API.TM1ValPoolCreate(hUser);

// Set the admin host to the value in the text box
TM1API.TM1SystemAdminHostSet(hUser, "[admin host]");

Log in to a given server:

int hServer = 
    TM1API.TM1SystemServerConnect(
        hPool, 
        TM1API.TM1ValString(hPool, "[server name]", 0), 
        TM1API.TM1ValString(hPool, "[username]", 0), 
        TM1API.TM1ValString(hPool, "[password]", 0)
    );

Once you have finished all your TM1 operations, you need to close the session:

TM1API.TM1SystemServerDisconnect(hPool, hServer);
TM1API.TM1ValPoolDestroy(hPool);
TM1API.TM1SystemClose(hUser);
TM1API.TM1APIFinalize();

You can look through the sample project to get a very simple example of a C# application working with the TM1 API.

Make sure you update the app.config file to include the path to your own installation of TM1.

 

Conclusion

The main barrier to entry to programming with the TM1 API is the initial set up of the API function calls, and I think this has been a limiting factor in the proliferation of custom TM1 applications.

Hopefully this sample code will get some creative developers pointed in the right direction so we can see some innovative new TM1 applications out in the wild!

You can download the sample here. If you have any issues or questions, don’t hesitate to drop me a line at team[at]flowolap.com, or make a post in the comments section below.