Connecting to TM1 via VB.net

[damientaylorcreata]

Hi Guys,

I am wanting to build an integration app using vb.net, however I am having trouble using the API. Is VB.NET supported by TM1? My code as attached is returning the following error: "Unable to load DLL 'tm1api.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)". I have included the tm1api.dll in both the directory of the project and also in the system32 directory. The code is as follows:

Code: Select all

Partial Class _Default

    Inherits System.Web.UI.Page

    Declare Function TM1SystemOpen Lib "tm1api.dll" () As Integer


    Declare Sub TM1APIInitialize Lib "tm1api.dll" ()
    Declare Sub TM1APIFinalize Lib "tm1api.dll" ()

    Declare Function TM1ValPoolCreate Lib "tm1api.dll" (ByVal hUser As Long) As Long
    Declare Function TM1SystemServerConnect Lib "tm1api.dll" (ByVal hPool As Long, ByVal sServer As Long, ByVal sClient As Long, ByVal sPassword As Long) As Long
    Declare Function TM1ValString Lib "tm1api.dll" (ByVal hPool As Long, ByVal InitString As String, ByVal MaxSize As Long) As Long

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim hPool As Long
        Dim hUser As Long
        hPool = TM1ValPoolCreate(hUser)
     
    End Sub
End Class

Any suggestions would be helpful.

Thanks,
Damien

[tomok]

Make sure TM1 is actually installed on the machine you are running the code on. It's not enough to just include all the DLL's in the same folder as your app. TM1 has to be installed because the DLL's have to "registered" and when you install TM1 all that is done for you. You can try manually registering the DLL's (with the REGSVR/REGSVR32 command) .

[Mike Cowie]

... TM1 has to be installed because the DLL's have to "registered" and when you install TM1 all that is done for you. You can try manually registering the DLL's (with the REGSVR/REGSVR32 command) .

Actually, this hasn't been true, historically, for TM1 API-related DLLs. None of the TM1 API file set are COM DLL's and, thus, they do not get registered (they'll fail to register if you try). Certainly, doing an install will get you all of the files that you could possibly need (and more), but it isn't typically required if you have a full set of them from another installation on another machine.

Damien,

The TM1 API actually consists of multiple files. Only including TM1API.DLL is not enough. You will only ever see that error message, however, because that is your entry point DLL for your function declarations. You will, therefore, see "TM1API.DLL not found" even if the problem is actually that it cannot find other TM1 API-related files like:

• libeay32.dll
• ssleay32.dll
• tm1lib.dll
• tm1ULibDll.dll
• tm1api.dll

In addition, the \SSL subfolder is important to the TM1 API. NOTE: The TM1 API file set has changed across versions and the documentation is usually not quite up-to-date.

As far as making sure your application can find the TM1 API you have some options:
1) Include all of the necessary files in the same folder from which you launch your application. This can be a little tricky with .NET compiled apps, particularly if you were building an Office or some other add-in that will be loaded within another application.
2) Add the folder containing TM1 API files to the machine path environment variable. Obviously you'll want to do this carefully, and it may require a bit of trial and error because a couple of the DLLs (libeay32.dll and ssleay32.dll) are open source DLLs that may exist elsehwhere on your machine.
3) Use Windows API functions like SetDLLDirectory or LoadLibrary to make it easier for your app to find the TM1 API files.

Note this thread has some similar information that you might find useful:
http://forums.olapforums.com/viewtopic. ... 8&start=20

And, as always, if you can find alternatives to using this particular TM1 API, you'll probably be a lot less frustrated at the end of the day. This entire post involved simply telling your app how to find the TM1 API, and that's probably the easier part!

Regards,
Mike

[MSidat]

Hi,

Sorry to hijack this thread. But we are looking at doing a similiar kind of thing whereby currenty we have an XLA via Excel which links into TM1 but due to governance issues we need to move away from VBA.

VB.net is the approved solution alongside Tm1 9.5.1 in a 64bit environment. As such Mike, you mentioned it is not necessary true to manually register the DLL's, is it also a necessity to have a TM1 Client actually installed on your users machine or would the API have all that is necessary to link into the TM1 Server to log the client in to obtain the necessary rights to execute the Ti's and obtain other info from the Server?

[Mike Cowie]

Hi:

If you're asking whether or not you need the TM1 client installed on a machine in order to use the TM1 C API with some custom application (in VB.NET, C#, etc), the answer is no. You would simply need to copy the relevant TM1 API files from another TM1 client or server installation - usually the easiest way to do this rather than worry about the specific files is to copy the BIN folder (and subfolders - especially SSL), which contains all the needed files. The only exception might be when you're trying to connect using the newer CAM connection options; I don't know all the inner workings of CAM connections using the TM1 API yet, but it seems like you may not be able to get away with only copying a BIN folder and may need to do a TM1 install. In any event, I have never had to register any of the TM1 API files because they aren't COM DLLs (registration not required).

In order to get your VB.NET app to see the TM1 API files when running your application you have some options:
1) Add the folder to the machine PATH
2) Use Windows API functions like SetDLLDirectory to specify the path to the API files
3) Use Windows API functions like LoadLibrary to specifically load the needed TM1 API files, in the correct order

I prefer options 1 or 2, but your mileage may vary. Note that you will get the same "File not found: TM1API.DLL" message if one or more files in the TM1 C API file set are missing (or not found by your VB.NET app). In other words, you could have TM1API.DLL, but be missing other files that TM1API.DLL depends on and still get the message that TM1API.DLL is missing (because it's the entry point for all TM1 API functions) - this is the reason I prefer copying the entire BIN folder (hard to miss a file that way!).

The only other wrinkle you can run into is if you've copied over a 32-bit TM1 BIN folder, but your VB.NET app is running as a 64-bit app. You'll see something like a BadImageFormatException, I think, when this happens and will need to get a 64-bit TM1 API file set (usually from a 64-bit TM1 Server install) or compile your VB.NET app as a 32-bit app.

Hope that helps.

Regards,
Mike

[MSidat]

Thanks for the info Mike.

It seems like a definate possibilty with development in a VB.Net envronment. What are you own thoughts as to a prefered development tool. We currently have a TM1 Model which utilises Excel based proformas for user inputs and the usual Ti based loads of source files from external systems. The Data is accessed by users via an XLA based system in Excel.

We now have an additional requirement to be able to provide a functionality to users where they can take low level data out of source systems (via something like an Oracle Database) and map this data via the use of mapping tables e.g. any Cost ID with 12345 in it goes to X element in this cube etc. What we envisage happening is once the users provide the mapping rules the Database can be queried and the relevant numbers grouped and exported into TM1.

However the crux of the issue is how we can provide the userbase with a decent front-end to provide this mapping functionality. As the client will not only be expected to enter data into TM1 but also view and manipulate data into an Oracle Database, moving away from VBA surely becomes a must.

[Mike Cowie]

Thanks for the info Mike.

It seems like a definate possibilty with development in a VB.Net envronment. What are you own thoughts as to a prefered development tool. We currently have a TM1 Model which utilises Excel based proformas for user inputs and the usual Ti based loads of source files from external systems. The Data is accessed by users via an XLA based system in Excel.

We now have an additional requirement to be able to provide a functionality to users where they can take low level data out of source systems (via something like an Oracle Database) and map this data via the use of mapping tables e.g. any Cost ID with 12345 in it goes to X element in this cube etc. What we envisage happening is once the users provide the mapping rules the Database can be queried and the relevant numbers grouped and exported into TM1.

However the crux of the issue is how we can provide the userbase with a decent front-end to provide this mapping functionality. As the client will not only be expected to enter data into TM1 but also view and manipulate data into an Oracle Database, moving away from VBA surely becomes a must.

Hi:

In general, I tend to prefer solutions that don't require custom software development - in other words, try to keep it simple, if possible and use existing application tools, where possible. Otherwise, you potentially need to get a software developer involved every time something needs to change in the application and also spend time deploying that application to users whenever it changes. If there's any way to accomplish this through native Oracle or TM1 functionality then that is usually a better solution, in my opinion. Maybe the user interface won't be as flashy, but if users can only be motivated by a fancy/flashy mapping UI I'd be worried. When TM1 or another database don't offer some functionality that is required, then you can't avoid it. For example, I've needed to make a request to a web service from something like a TI process - TM1 doesn't have any native way of doing that so we had to build an EXE that could be called from TI to call the web service.

Mapping from a source system to TM1 basically gives you two options:
- Update mappings in the source system and give mapped and/or summarized data to TM1
- Extract from source system to TM1 and do the mapping within TM1

For something like PeopleSoft GL data, for example, I've tended to prefer first populating a "PeopleSoft GL" cube in TM1. If that data needs to be further mapped/aggregated within TM1 to a "Financial Reporting" cube that has a more summarized chart of accounts we'd usually accomplish that with something like mapping attributes/cubes and/or rollups in TM1 that get from "PeopleSoft GL" to "Financial Reporting". Could you build the same mapping in a relational database? Definitely, but often you get some benefit from having that more detailed "PeopleSoft GL" cube around as well as the mapping detail in TM1.

If you're building a friendlier interface to capture the mapping data in TM1, you may have some options via native TM1 Excel workbooks, cube views, web sheets, etc that don't require much, if any, coding in VBA or something else. You may be able to allow users to run other operations using TI processes via Action Buttons, too, which is fairly easy to manage and improve over time. And, perhaps drill-through processes from TM1 will give them the level of Oracle visibility they need, when they need it. If you're planning to capture this mapping information and other functionality within a relational database then you may very well need to develop a friendlier interface around that in something like VB.NET. If you do need to go that route you may be able to minimize the software development effort through some stored procedures that could reduce the chances you'll need to get a software developer involved and deploy any future changes to the application.

The bottom-line is that (like many things) it all depends on the business problem(s)/objectives and any constraints you are facing (e.g., you aren't allowed to use VBA solutions or are only allowed to use Java developers); I don't necessarily see a big problem with VBA depending on what it is you're trying to do - that's not to say it doesn't have its shortcomings (e.g., controlling changes to source code), but for certain applications it is still very useful. As far as what developer tools to use, I don't think it really matters that much - whatever tools are approved for use, whatever tools offer the performance and development agility needed, and (very important) whatever tools have the most available internal resources around who know how to use them would be my preferences, generally.

Hope that helps - good luck!

Regards,
Mike