TM1 Forum

Posted: **Wed Feb 09, 2011 5:21 am**

hi Everyone,

I was looking for a function to read the time-stamp on a source file that's used to update a
cube several times during the day. If the chore 'looks' at the file and sees that it's late - ie:
skipped a drop file, then send an email alert to admin user.

Couldn't find any DateTimeStamp functions within TM1 so I used a long way:-
* ExecuteCommand() a vbs -> write-out timestamp to a text file
* Read text file to a parameters cube to record the times
* TI: read parameters and send email

There's probably a much better solution so if anyone knows of a pre-built function,
or find any flaws, pls let me know.

the *.vbs script

Code: Select all

On Error Resume Next

sPath = "\\serverName\FTPdata\SAPftp"
i = 0

Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder(sPath)
Set iTxt = fso.CreateTextFile(sPath & "\TM1_timeKeeper.cma", True, False)

dtEnd = Now()

For Each fn In fldr.Files
  If fn.Name Like "*.txt" Then
    i = i + 1

    fnDts = fn.DateLastModified
    dtStart = DateValue(fnDts) + TimeValue(fnDts)
    fnDtIntv = DateDiff("n", dtStart, dtEnd) - (DateDiff("h", dtStart, dtEnd) * 60)

    iTxt.WriteLine fn.Name & "," & fn.DateLastModified & "," & i & "," & fnDtIntv
  End If
Next

iTxt.Close

the TI - pls ignore some superflous lines in the prolog

Code: Select all

562,"CHARACTERDELIMITED"
586,"\\serverName\FTPdata\SAPftp\TM1_timeKeeper.cma"
585,"\\serverName\FTPdata\SAPftp\TM1_timeKeeper.cma"

#****GENERATED STATEMENTS START****
#****GENERATED STATEMENTS FINISH****
# PROLOG
fPathFile = 'E:\TM1\Admin\dw_ComposeMail.txt';
cYear = CellGetS('Parameters', 'Plan', 'wCurrentYear');
CheckSum = CellGetN('General Ledger', 'EBIT', 'ALLCCTR', 'All Entities', 'Total Year', cYear, 'Actual', 'Amount');

If (roundp(CheckSum, 2) < 0.50 & roundp(CheckSum, 2) > -0.50);
  ASCIIOutput(fPathFile, NumberToString(CheckSum));
Else;
  sRecipient = 'yyi@mydomain.com.au';
  sSubject = 'SAP GL - Trial Bal';
  sMessage = 'Balance = ' | NumberToStringEx(CheckSum, '#,##0.00', '.', ',');
  S_Run = 'cmd /c E:\TM1\Admin\SendMail.vbs' | ' "' | sRecipient | '"' | ' "' | sSubject | '" ' | '"' | sMessage | '"';
  ExecuteCommand(S_Run,0);
EndIf;

  S_Run = 'cmd /c E:\TM1\Admin\sapFtpTimeKeeper.vbs';
# try 1 for file lock
  ExecuteCommand(S_Run,1);
# --------------------------------------------------------------------

#****GENERATED STATEMENTS START****
#****GENERATED STATEMENTS FINISH****
# METADATA
574,16

#****GENERATED STATEMENTS START****
#****GENERATED STATEMENTS FINISH****
# DATA
If ( vFileName @= 'TM1_ACTUALS.txt' );
  CellPutS(vTimeStamp, 'Parameters', 'A', 'sapGLFile');
  CellPutS(vFileName, 'Parameters', 'B', 'sapGLFile');

  CellPutN(vLapse, 'Parameters', 'A', 'sapGLTimeDiff');
EndIf;

# --------------------------------------------------------------------
575,24

#****GENERATED STATEMENTS START****
#****GENERATED STATEMENTS FINISH****
# EPILOG

timeStamp_Now = TimSt (Now, '\h:\i:\s');
timeStamp_File = CellGetS('Parameters', 'A', 'sapGLFile');
timeStamp_Lapse  = CellGetN('Parameters', 'A', 'sapGLTimeDiff');

If (timeStamp_Lapse  > 9);
  sRecipient = 'yyi@mydomain.com.au';
  sSubject = 'SAP - FTP Fail ' | timeStamp_Now;
  sMessage = 'Time = ' | timeStamp_File | ' ~ ' | NumberToString(timeStamp_Lapse) | ' mins';
  S_Run = 'cmd /c E:\TM1\Admin\SendMail.vbs' | ' "' | sRecipient | '"' | ' "' | sSubject | '" ' | '"' | sMessage | '"';
  ExecuteCommand(S_Run,0);
EndIf;

Posted: **Wed Feb 09, 2011 8:44 am**

if the file is only ever called "TM1_timeKeeper.cma" then why not use a script to delete the file once it's been uploaded to TM1 instead? That way you could use FileExists in the TI rather than vb to get a date stamp. If it doesn't exist, do nothing otherwise run the import then run a vb script at the end to delete the file afterwards.

Posted: **Wed Feb 09, 2011 10:21 am**

Yeon, there aren't any native TI functions to do anything with flat files other than check if they exist, read them, write to them and delete them. To check date stamps from the OS file system workarounds with batch files and/or vb scripting such as what you have come up with is more or less typical.

Posted: **Wed Feb 09, 2011 11:10 pm**

thanks guys for the feedback.

Steve, the del then 'fileExists' function's a good idea - actually the source file is gl balances and it get's picked up by sql svr ssis before it gets read in by tm1, so i didn't want to remove it.

Lotsa, how do you know me

maybe if executeCommand can get return values like executeProcess, Â¿i could skip the text-stream routine ..

TM1 Forum

TI ascii datasource - getTimeStamp(?)

TI ascii datasource - getTimeStamp(?)

Re: TI ascii datasource - getTimeStamp(?)

Re: TI ascii datasource - getTimeStamp(?)

Re: TI ascii datasource - getTimeStamp(?)