TM1 and email (using SQL server)

[mikeveldboer]

First post, hopefully useful to some of you.

We have a TM1 Nightly Refresh that finishes at a certain time in the morning. But we do not have any notification from TM1 indicating the process finished and since we have offices in the EST and PST time zones we need this process to be done before a certain time.

We were able to implement a trick to make this work but it requires access to a SQL server that has mail set up.

Basically what we do is:
1. We created a TI process with an ODBC connection to a SQL server.
2. The TI process inserts a "getdate()" value into a specifically created table (with one column for datetime).
3. At the end of the Nightly refresh chore we fire of the TI process.
4. The table will get a new date time inserted.
5. We have set up a trigger (on insert) that monitors the field and once a record is inserted it fires of an email.
6. The email takes the date and time from the field and formats this in to html to make the email more user friendly.

The users receiving the emails now know when the process is finished and can then access TM1 knowing they are working with the latest data.

I'm sure it can be enhanced to receive more than just a date time record but haven't had a change to take a look.

If you are interested I can provide more details.

Thanks,

[declanr]

Interesting approach. Most of use just use tools like sendemail.exe or blat; or use vbs to send emails via command line from a TI but the SQL approach could be very handy for people who have their IT infrastructure pretty locked down.

[TrevorGoss]

This is indeed an interesting approach. Are you limited to SQL & TM1? Or can you use other tools or programming languages/enviroments?

For example, our team uses SQL as the driver behind PERL scripts. We use our ETL to put data into SQL and the PERL script is called to connect to the SQL and send an email to our end users, letting them know the results of the ETL.

We also use VBA along with SQL and PERL to send emails to our end users.

I am just interested to know how and why you took this approach, and if you have any other questions regarding our approach.

Thanks.

[Ptec]

First post, hopefully useful to some of you.


2. The TI process inserts a "getdate()" value into a specifically created table (with one column for datetime).

Hey mike, that point is very interesting for me!

Im currently struggling with passing a generated Timestamp :

Code: Select all

vNow=NOW();
vTimeStamp=TimSt (vNow, '\d\m\Y\h\i\s\');

to a table in SQL.

As SQL don't accept string or number formated timestamp I need to convert it do a "datetime" format!

Would be awesome if you could tell me your command for passing a Timestamp from TM1 to an SQL Table!

Thanks