Planning Analytics

 View Only

 MDX syntax in TM1py?

Quin Kan's profile image
Quin Kan posted 12/17/24 02:23 PM

Hi

For those of you who have used MDX in TM1py, did you write MDX differently?  I copied the MDX from an explorations view in PAX to my Python code, but there was a syntax error.
It said the error was near certain place and at character in certain position.  But at that position, I did not see anything unusual.  Sometimes, the position was within the cube name that I am certain is correct.
 
Tried making it a one line command did not work.
Tried copying it to notepad and recopying/pasting did not work.
 
By the way, my connection works, but I cannot see the cube (for a different unknown reason). 
Could the fact I cannot see the cube trigger the MDX syntax error?
 
I know they are different issues, but in some rare occasion, one error can trigger something strange.
 
Thanks

Martin De Giovanni's profile image
Martin De Giovanni posted 12/18/24 04:18 AM

hi @Quin Kan, good morning.

Could you please share the MDX statement with us? we can have a quick look on it.
I can also recommend you to test the MDX statement in PAW, if possible.

Sometimes the script works with "on Rows" or "on Columns" command, and some other time with "on 0" or "on 1"... but is better if we can have a look in your code first.

thanks!

Martin

Quin Kan's profile image
Quin Kan posted 12/18/24 11:45 AM

Good morning, Martin:

My MDX looks like:

SELECT { 
[Cube1 Measures].[Cube1 Measures].[M1] 
} DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 0, 
{ 
{TM1FILTERBYLEVEL(TM1SubsetToSet([Structures].[Structures], 'Default', 'public'), 0)}} DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER,  
CHILDREN_CARDINALITY ON 1 
FROM [Cube1] WHERE ([VERSIONS].[V1])

ON COLUMNS and ON ROWS does not work in this case.

Thanks.

Marius Wirtz's profile image
Marius Wirtz IBM Champions posted 12/19/24 03:50 AM

This could be due to security and permissions.

Perhaps the PAW user has permission to interact with "Cube1", but the TM1py user doesn't?

If you run the Python script below with your permissions, does the list of cubes include "Cube1"?

from TM1py import TM1Service

tm1_params = {
    "address": "localhost",
    "port": 12354,
    "user": "admin",
    "password": "...",
    "ssl": True
}
with TM1Service(**tm1_params) as tm1:
    print(tm1.cubes.get_all_names(skip_control_cubes=True))
Quin Kan's profile image
Quin Kan posted 12/19/24 11:21 AM

Good morning, @Martin De Giovanni,

The code returned an empty array [].  Since I am a regular user, who does not have an admin account, I did not run it as an admin.

Agree with you that it could be security and permission.  Am trying to find out who our server admin is to see if the admin configured TM1 or if the admin knew who configured it to check the security and permission.

Thanks. 

Related Content