Checking the data source of a TI process

Ideas and tips for enhancing your TM1 application
Post Reply
Wim Gielis
MVP
Posts: 2054
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Checking the data source of a TI process

Post by Wim Gielis » Mon Apr 09, 2018 8:19 pm

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 !
Attachments
TECH_check data source.pro
(43.86 KiB) Downloaded 83 times
Last edited by Wim Gielis on Wed May 22, 2019 8:46 pm, edited 1 time in total.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

Wim Gielis
MVP
Posts: 2054
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Checking the data source of a TI process

Post by Wim Gielis » Wed Apr 25, 2018 11:04 pm

Hello,

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

Thanks,

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

Wim Gielis
MVP
Posts: 2054
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Checking the data source of a TI process

Post by Wim Gielis » Wed May 08, 2019 12:53 pm

Hi all,

Here is an updated version, much improved.
TECH_check data source.pro
(70.7 KiB) Downloaded 11 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
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

Post Reply