Thanks Douard! Have used this already as a PA on Cloud customer thanks to your assistance (TM1 11 - note that the syntax is different for 12). Would like to elaborate on a few things for clarity due to some issues I faced in case others face similar, as well as providing some further info I found whilst investigating.
Please note that a lot of the docs have the domain section of the URL wrong for the environments, at least compared to ours (.ibmcloud.com vs cloud.ibm.com). This tripped me up initially!
I would recommend a slightly different initial query to get people started. You can generate an MDX view as mentioned but initially validating the connection is probably the best thing to do. I personally used a separate python tool in Alteryx to do the initial packages install. Worth noting here that you will need to run Alteryx as administrator to give it windows file write permissions to install tm1py:
from ayx import Package
Package.installPackages(['pandas','numpy','tm1py','requests','requests-negotiate-sspi'])
The requests and requests-negotiate-sspi are unlikely necessary but I included them just in case, as dependencies for tm1py.
In all examples below, replace the bits in square brackets (including the brackets) with the necessary entry. E.g. if your environment name is MyCompanyDev, and your instance/server is InstanceOne, your base_url will look like:
base_url='https://MyCompanyDev.planning-analytics.ibmcloud.com/tm1/api/InstanceOne/'
Next comes a simple connection check (I ripped this from Errol Brandt's post).
### import the TM1py libraries
from TM1py.Services import TM1Service
from TM1py.Exceptions import TM1pyException
try:
with TM1Service(
base_url='https://[YourEnvironmentName].planning-analytics.ibmcloud.com/tm1/api/[YourServerName]/',
user="[YourAutomationUser]",
namespace="LDAP",
password="[YourAutomationUserPassword]",
ssl=True,
verify=True,
async_requests_mode=True) as tm1:
print ('Connected!')
except TM1pyException as e:
if e.status_code == 401:
print('The user credentials were rejected. Check username and password.')
elif e.status_code == 404:
print('The connection was rejected. Check the connection string.')
else:
print('Something else went wrong. Check error code ' + str(e)).
Once you are all connected, a "self-serve" method would be to pull a view from TM1 (allows users with both Alteryx and TM1 to create their own public/private views, and less difficult to modify than MDX). Just flip the private Boolean to True if a private view. Marius has created optimised statements that avoid the need to call a view/mdx view and separately convert to a dataframe with pandas, which makes it a bit simpler too:
from TM1py.Services import TM1Service
from ayx import Alteryx
with TM1Service(
base_url='https://[YourEnvironmentName].planning-analytics.ibmcloud.com/tm1/api/[YourServerName]/',
user="[YourAutomationUser]",
namespace="LDAP",
password="[YourAutomationUserPassword]",
ssl=True,
verify=True,
async_requests_mode=True) as tm1:
df = tm1.cubes.cells.execute_view_dataframe(
cube_name="[YourCubeName]",
view_name="[YourViewName]",
private=False,
parse_dates=[0],
dtype={'Value': float})
Alteryx.write(df,1)
You can read about all of the classes and methods here.
