Product:
Planning Analytics 2.0.9.13
TM1_version=TM1-AW64-ML-RTM-11.0.913.10-0
Microsoft Windows 2019 server
Issue:
Can not read data from ODBC connection in TI process.
Solution:
Check that the ODBC connection still exist, and that the TM1 service have access to the ODBC connection.
How setup a ODBC connection for TM1:
On the server go to control panel – administrative tools – ODBC data source (32 bit).
Click on system DSN tab.
Click on Add button.
Select SQL server native client 11.0 and press Finish button.
Fill in a name and a server-name, that you can connect to the database on.
Enter a SQL native login, that you can use to test the connection with.
Click next – next – finish.
Click Test data source button, you should get this if you did correct:
Microsoft SQL Server Native Client Version 11.00.5058
Running connectivity tests…
Attempting connection
Connection established
Verifying option settings
Disconnecting from server
TESTS COMPLETED SUCCESSFULLY!
Now go into you TM1 application, and create a new TI process.
Select ODBC as data source, and pick your ODBC from the list.
Enter a working SQL native username and password.
Enter a SELECT SQL statement, that you have tested in SSMS, that it works to bring some data back.
Press Preview to check that the SQL connection work – you should see your data.
Go to variables tab.
The variables name suggestion is created from first data line, you can change them to vNames that you think is more describing. Do not use spaces in variable names.
For the lines of data that you want to use, change the Contents column to ‘Other’ as shown above. Click on Advanced tab.
Go to prolog tab, and enter some code to test this process:
# variables setup - set them all here as empty at least sAccount = ''; # file name setup for debug text file sFileName= 'debugfile2.txt'; sFilePath = 'c:\temp\'; sDEBUGFILE = sFilePath | sFileName ; # get the date and time to set a stamp in the log file # https://edu.cubewise.com/tm1-function-for-rules-timst/ sNowTime = NOW(); sDATETIME = TIMST( sNowTime , '\Y-\m-\d \h:\i' );
Then go to the Data tab, to enter code to write out the data to a file for debugging.
# set the variable from the data source to a variable in the code and print it out sAccount = AccountDescription; ASCIIOUTPUT ( sDEBUGFILE, sAccount, sDATETIME );
Save the process with a name that describe the function.
Run the process.
Then to check that it works, go to c:\temp and open the text file.
The value in the database for column [AccountDescription] is first, and then a date-time stamp from the code.
As we use the TM1 data source tab, we do not need to use the ODBCopen command to get the data in the data tab.
Planning analytics should work with both 32 bit and 64 bit ODBC connections on the Windows server, as below parameter is on by default.
Parameter type: optional, dynamic
EnableODBCProxy is useful, if a 64-bit driver is not available. Each proxied connection creates a 32-bit tm1odbcproxy.exe process during the connection. EnableODBCProxy is true by default, but you can disable the feature by including EnableODBCProxy=false in the tm1s.cfg.
This give that your TM1 64 bit server can use 32 bit ODBC connections. But for best result you should use a 64 Bit connection in Windows server.
More information:
https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=tf-odbc-turbointegrator-functions
https://code.cubewise.com/odbc-connect
https://quebit.com/askquebit/IBM/how-to-speed-up-tm1-odbcoutput/