Checking the data source of a TI process

[Wim Gielis]

Hello all,


UPDATED VERSION BELOW


In the category... the nicer TI processes

Attached is a generic process to check for the data source of a TI process. You know, text files, ODBC connections, cube views, dimension subsets, and so on. The tests for the text files (CHARACTERDELIMITED files) are the most elaborate:
- does the file exist ?
- can it be accessed / opened ?
- does it contain data ?
- does it contain the right number of columns / fields ?
- is the field separator correct ?
- and so on.

We all went through the pain of zero'ing out a cube when the text file isn't there, or destroy dimension structures where the metadata tab could not be reached, and similar cases. We want to avoid that!

Drop the TI process in your TM1 data directory and call it. When dropping the PRO file and bouncing the server, make sure that you edit the path of the data source of the PRO file, to match your TM1 data directory. For example, I have:

586,"D:\Wim\TM1\TI processes\TECH_check data source.pro"
585,"D:\Wim\TM1\TI processes\TECH_check data source.pro"

and you'll want to change the directory there before bouncing the TM1 model. Changing this path is not a requirement for the process to run, but just to have a variables tab.

There are examples in the Prolog section to show you how it works, but basically you add code in the Prolog whose data source you want to test.

Code: Select all

# You can simplify the call to e.g. for a text file:
vResult = ExecuteProcess( 'TECH_check data source'
  , 'pCheckFields', 1
  , 'pNumberOfFields', '=3 / >4 / >=2 / <6 / <=3 / <>7'
  , 'pEmptySourceIsAllowed', 'N'
  , 'pDataSourceType', DataSourceType
  , 'pDataSourceNameForServer', DataSourceNameForServer
  , 'pDataSourceAsciiDelimiter', DataSourceAsciiDelimiter
  , 'pDataSourceAsciiQuoteCharacter', DataSourceAsciiQuoteCharacter
  , 'pDataSourceAsciiHeaderRecords', DataSourceAsciiHeaderRecords );
If( vResult <> ProcessExitNormal & vResult <> ProcessExitByBreak );
   If( vResult = ProcessExitSeriousError ); LogOutput( 'ERROR', 'FAILURE: The source cannot be accessed / opened.' ); EndIf; ProcessError;
EndIf;

You plug this code in, and enter the 3 first parameter values, RIGHT AFTER setting the data source programmatically, or otherwise somewhere in the Prolog tab of your main process.

Note:
A maximum of 200 variables is allowed for in this generic process. You need TM1 10.2.2 or higher to execute this process successfully. If you get rid of the LogOutput function, almost any recent TM1 version will be fine.

Let me know all questions or remarks and FOREMOST: extensions or corrections.

Enjoy !

[Wim Gielis]

Hello,

For those of you who downloaded the process, did you use it ? Did it meet your expectations? Other feedback ?

Thanks,

Wim

[Wim Gielis]

Hi all,

Here is an updated version, much improved.

TECH_check data source.pro

(70.7 KiB) Downloaded 1156 times

New functionality is a simple choice for AsciiOutput and/or LogOutput and/or sending an email.
Sending an email is there, it would use a helper TI process and supply a number of parameters, that you can / have to whip up yourself. You can use your own processes / custom coding there.

Other new possibility is to check whether a text file that you load (in the main process and that needs to be checked before loading) was last modified / created today. If the date is not today the process stops with informative messages.

Various smaller improvements applied. I recommend using this version rather than the previous one.

Make sure to read the Prolog tab, it includes information, notes and remarks. Also, examples of how to call this process.

Don't forget to change the data source of this process in Notepad++ or similar, now it points to a folder on my PC but you should change it. Then drop it in the TM1 data directory and reboot the model. This should be the only change that is needed.

Enjoy !

Wim