A recent thread on Another TM1 Forum discussed the issues with using ODBCOpen to connect to external databases. I suggested there that TM1PY (of course) could be of use - since I saw that Mariusz recently introduced storing TM1 passwords in Windows Credential Manager. I will admit I didn't know much about WCM, but I took a look.
You can write passwords to WCM, and read them back in Python (they are ****-ed in the UI). The key (I believe, and my testing confirms) is that you need to store the credentials using the user under which the TM1Server, and (thus) the TM1PY script, is running. The user does not have to be logged on when you use it.
Accordingly I built a testbed for this:
a The standard GoNewStores server, adjusted to run under a named Windows user;
b A MYSQL database (running on my NAS), with a table reproducing the StoreSalesPlan cube, requiring a user and password for update;
c A TI with the ODBCOpen/ODBCOutput/ODBCClose, with the password as a parameter;
d a TM1PY script to read the password from WCM;
e A driver TI to call TM1PY.
I have tested this running under a different windows user from that running GoNewStores, and it works. Note that this user cannot get at the WCM content.
Feedback welcome. I will admit there are rather a lot of moving parts.
Snippets from the TIs:
UpdateODBC - takes Store Sales Plan view as source:
Password as parameter
Prolog:
Code: Select all
vdsn = 'Chowder2';
odbcopen(vdsn,'tm1server',password);
vcounter = 0;
Data:
vcounter = vcounter + 1;
if(vcounter > 10000);
processquit;
endif;
vsql =
'INSERT INTO StoreSalesPlan VALUES (''' |
retailers | ''',''' |
countries_currency | ''',''' |
products | ''',''' |
vmonth | ''',''' |
Version | ''',''' |
measure | ''',' |
numbertostring(value) |
')';
odbcoutput(
vdsn,
vsql
);
Code: Select all
scriptpath = 'd:\windev\tm1py\tm1py-samples\dutest\';
script = 'Credentials_DU.py';
executecommand('cmd /c python ' | scriptpath | script,1);
Note I have _not_ used WCM to hold the TM1 credentials, instead holding them in an INI file.
Code: Select all
import configparser
from getpass import getpass
import keyring
from TM1py.Services import TM1Service
INSTANCE = "GO_New_Stores"
config = configparser.ConfigParser()
configpath = 'd:/windev/tm1py/tm1py-samples/config.ini'
print (configpath)
config.read(configpath)
# interact with Windows Credential Manager through the keyring library
DBSERVER = "ChowderMySQL"
mysql = config[DBSERVER]
user = config[DBSERVER]["user"]
password = keyring.get_password(DBSERVER, user)
if not password:
password = getpass(f"Please insert password for user '{user}' and instance '{DBSERVER}':")
keyring.set_password(DBSERVER, user, password)
#with TM1Service(**config[INSTANCE]) as tm1:
with TM1Service(**config["GO_New_Stores"]) as tm1:
tiprocess = 'UpdateODBC'
parameters = {
'Parameters': [{
'Name': "Password",
'Value': password
}]
}
tm1.processes.execute(tiprocess, parameters)