TM1 as ETL tool

[Krish]

Hi Folks,

Current status of our project:

Data from various Lotus Notes databases are then extracted on a weekly basis via the ETL code and pushes the data in DB2 database. Cognos reports uses the DB2 back-end data to produce various reports. The ETL code is written in Java within the Lotus Notes database and it uses some threads that processes the records and pushes to DB2.

Now we want to replace the existing ETL with specific ETL tool.

Please could anyone of you please explain the below queries.

1. Can we use TM1 Turbo Integrator as a ETL tool for this project?
2. And how far it supports? Any limitations?
3. Any parameters need to identify if we want to use this as ETL?
4. What are the advantages and disadvantages with it?
5. About the maintainability?

Please suggest me that can we go for TM1 as ETL or not?

If TM1 is not suitable for this, please explain the scenarios that we can choose TM1 as ETL tool.

Thanks,
Kris

[Kyro]

1. Can we use TM1 Turbo Integrator as a ETL tool for this project?

Its likely TM1 could do the job, there are some limitations though in doing it this way.

2. And how far it supports? Any limitations?

TM1 would be good for importing and processing any mappings that need to be done but the ODBCOutput function (which is TM1's was of pushing data outward to another ODBC Database) must be executed once per every data line which could be time consuming, It would probably take some tricky programming to make it formulate a long batch insert SQL statement which would be more efficient. Likewise you will again have to be a bit creative if you want to do any multithreaded data manipulation as TM1 can only do this by calling multiple processes at once, and when the user calls one process his session is usually locked while waiting for a response. I have a friend who wrote a little application which can execute multiple TI Processes at once (must be used on a version of TM1 which supports Multithreading and must not access the same cubes -- at all).

If you are exporting data to text files the default by TM1 is a CSV file, Fixed Width files are a chore to get working (excuse the pun).

3. Any parameters need to identify if we want to use this as ETL?

...

4. What are the advantages and disadvantages with it?

Advantages:

• Performance wise, TM1 TI is amazing in how many records it can process per second even with tricky data manipulations etc.
• TI Processes can be scheduled to run whenever which is probably a requirement for your project.
• TI Processes can execute other processes which could make running one process multiple times for a few same-format tables would be very easy, and as you would be using a single process for many tables, it would be more maintainable.

Disadvantages:

• The programming language is proprietary, it has been developed specifically for this software so it can be hard to maintain.
• The user interface doesn't look like its been upgraded since the early 90s.
• See the Limitations above.

5. About the maintainability?

Shouldn't be too hard to maintain if its built correctly.

All the best with this project.

[lotsaram]

Just to add to Kyro's comments. TI is a great ETL tool ... for TM1. I have heard of TI being used for ETL at sites that don't really use TM1 for anything else purelly due to it's speed advantages over more traditional or complete ETL tools but these instances would have to be extremely rare. One thing I'm not sure of that is worth mentioning is Lotus Notes as a data source. As long as you have a 64 bit ODBC driver then this shouldn't be a problem but without a driver I don't see how you would be able to connect in the first place. I don't think that Notes <-> TM1 interoperability would be too high on IBM's priority list!