Turbo Integrator is a powerful ETL tool, mainly focused on actions within IBM PA itself. In TM1 V12 we can do more since the addition of JSON functions and ExecuteHttpRequest. Support for let's say non-standard data sources, management of text files beyond the basics, etc. is more limited. In addition, the TM1 REST API has been opened with Python (and other programming languages).
This article shows the syntax in Turbo Integrator to execute your custom Python scripts. We discuss 2 cases for the syntax:
- running Python scripts, basically text files with extension .py, and Python is installed/running on the machine.
- running Python executable scripts, .py files compiled to .exe files, Python is not necessarily installed/running on the machine.
We would do in Turbo Integrator:
# python_application = '[installation path]\python.exe';
python_application = 'python.exe';
vCommand_Mask = '"%python_application%" "%python_file%"';
python_file = 'D:\python_from_ti\cube_count.py';
ExecuteCommand( Expand( vCommand_Mask ) | ' "SCH_*" 3', 0 );
The code above will launch the file cube_count.py with Python. We explicitly pass 2 arguments to the script:
- a string parameter for a name mask when counting cubes
- a numeric parameter for the minimal number of dimensions in the cube, if to be counted
The cube_count.py script uses TM1py and (under the covers) the TM1 REST API.
Granted, what the script does could be done in Turbo Integrator and it would run much faster.
However, the Python script could allows the name mask to be written with regular expressions. This simple change in itself would mean that a solution in TI would be very hard to write, potentially.
Put differently, all the power and functionality and flexibility that Python and its ecosystem expose, can be leveraged. Arrays in TI, anyone ? :-)
String parameter values must be provided within double quotes.
If your Python script does not use parameters, or we can live with the default parameter values in the script (if any), then the call will be easier:
ExecuteCommand( Expand( vCommand_Mask ), 0 );
Should you want or need an executable to run, then we can use a Python library like pyInstaller. Then you can turn the script and needed resources, into an .exe file. Note that the filesize can be big, depending on what the program needs to be able to run standalone (called by IBM PA through TI).
An example call is as follows (with a variable for the numeric value):
python_file = 'D:\python_from_ti\dist\cube_count.exe';
pMinNrOfDims = 3;
ExecuteCommand( '"' | python_file | '" "' | 'SCH_*' | '" ' | NumberToString( pMinNrOfDims ), 0 );
Note that you call the .exe file without specifying python.exe.
The file cube_count.exe can be produced using for instance:
pyinstaller --onefile --hidden-import=pathlib script.py
For this to work, pip install pyInstaller, then navigate to the folder of the Python .py script first.
Some notes to the reader:
- The value of 1 or 0 as the last argument to the ExecuteCommand function tells TI to wait or not when calling the script. If this is needed in your TI process, waiting can be done with the value of 1.
- That function ExecuteCommand was removed in TM1 V12. Other solutions need to be created. For one thing, ExecuteHttpRequest() can potentially replace some of the scripts we would now require in Python. It will definitely run quicker too.
- Targeting files and folders can be done with absolute file paths like above. Another option is relative paths, which is paths containing . and/or ..
- Make sure the rights to file access/folder access/read/write etc. is fine before you test the scripts. Retest the same script from TI as well to guarantee the correct working. Antivirus and related topics could also need to be checked.
- Python is used as an example, of course other scripts and executables will work in a similar way.
- You are not obliged to run Python from Turbo Integrator. It could also run standalone or through other calling mechanism. E.g. the arrival of a file in a certain folder could be intercepted and call a Python script. This solution would be more efficient than having TI poll every few seconds using a chore. Polluted tm1server.log file, anyone ? :-)
- Obviously, you can write other Python code and not use TM1py. This is just as an example of the one of the more popular use cases nowadays in the IBM PA landscape.
In the IBM PA library I add the script for you to test/experiment: here is the link
As always, any feedback is welcome.
#IBMChampion