Checking the data source of a TI process

Ideas and tips for enhancing your TM1 application
Post Reply
Wim Gielis
MVP
Posts: 1669
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
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,

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 20 times
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Wim Gielis
MVP
Posts: 1669
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
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 ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Post Reply