Element lock in v12

Post Reply
vilmos.csaszar
Posts: 6
Joined: Tue Oct 29, 2024 6:54 am
OLAP Product: Planning Analytics
Version: 2.x
Excel Version: M365 2409

Element lock in v12

Post by vilmos.csaszar »

Hi All,

Does anyone know how to test in a v12 (PAaaS) environment's TI process if an element is locked? Until know, I used simple CellGetS on the }ElementProperties cubes for this, but those control cubes don't exist on v12.

Related to this: any advice on how to lock/unlock elements with TI (also in v12)?

Thank you!
Wim Gielis
MVP
Posts: 3194
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Element lock in v12

Post by Wim Gielis »

Hello

Welcome !

Can you get some inspiration and codes from my overview article, written for V11 and current PAW ?

https://community.ibm.com/community/use ... lity-guide

Kindly let us know the outcomes !
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
vilmos.csaszar
Posts: 6
Joined: Tue Oct 29, 2024 6:54 am
OLAP Product: Planning Analytics
Version: 2.x
Excel Version: M365 2409

Re: Element lock in v12

Post by vilmos.csaszar »

Hi Wim,

I am familiar with your article about this topic. It's almost as useful as the one about the MDX statements. :-) Thank you for publishing these, they're really helpful!
I also know and use the "DimensionElementSetLockStatus" and it seems to be working, but as you wrote, it's undocumented. I wonder if there's another "safe" way to do it. But it's not that important, because at least we have this working solution, even if it's undocumented.

Testing the existence of the lock is a more pressing issue. You wrote:

# to test the existence of an element lock:
If( CubeExists( '}ElementProperties_' | vDim ) = 0 );
# the element is not locked
Else;
If( Dimix( '}Clients', CellGetS( '}ElementProperties_' | vDim, vElement, 'LOCK' ) ) > 0 );
# the element is locked
EndIf;
EndIf;

This is how we've done it earlier, but it doesn't work in v12. I was not able to find a workaround yet.
Wim Gielis
MVP
Posts: 3194
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Element lock in v12

Post by Wim Gielis »

FYI, The Cubes endpoint in V12 has a Locked property:

GET {{host}}/api/{{tenant}}/v0/tm1/{{tm1db}}/api/v1/Cubes

returns:

Code: Select all

"@odata.context": "$metadata#Cubes",
    "value": [
        {
            "@odata.etag": "W/\"b19afc8c86a7cf1bec61a666767aade200b60d78\"",
            "Name": "Allocation Calculation",
            "Rules": "...",
            "DrillthroughRules": null,
            "LastSchemaUpdate": "2024-10-03T09:18:43.234Z",
            "LastDataUpdate": "2024-10-16T12:58:19.790Z",
            "ViewStorageMaxMemory": 999999999,
            "ViewStorageMinTime": 1,
            "CalculationThresholdForStorage": null,
            "CellSecurityDefaultValue": null,
            "CellSecurityMostRestrictive": false,
            "AllowPersistentHolds": false,
            [b]"Locked": false,[/b]
            "Attributes": {
                "Caption": "Allocation Calculation",
                "Caption_Default": "Allocation Calculation"
            }
        },
So with the ExecuteHttpRequest() function in V12 this should be possible.


Or:

GET {{host}}/api/{{tenant}}/v0/tm1/{{tm1db}}/api/v1/Cubes('Allocation Calculation')?$select=Locked

Code: Select all

{
    "@odata.context": "$metadata#Cubes(Name,Locked)/$entity",
    "@odata.etag": "W/\"b19afc8c86a7cf1bec61a666767aade200b60d78\"",
    "Name": "Allocation Calculation",
    "Locked": false
}
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
MVP
Posts: 3194
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Element lock in v12

Post by Wim Gielis »

Use the same ExecuteHttpRequest() function to lock/unlock cubes/dimensions and possibly elements.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
MVP
Posts: 3194
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Element lock in v12

Post by Wim Gielis »

Hello all,

Having spent some time on this in V12, the changes with respect to my V11 overview are, to me:
- Locking and unlocking is much more streamlined: there is tm1.Lock and tm1.Unlock to each of the endpoints for Cubes, Dimensions, Elements
- }xxxProperties cubes are out, so we cannot know who is the owner of the lock (I did not look into this in much detail but the TM1 REST API, at first glance, does not show the information. Also no mentioning in the $metadata document for V12). Does anyone know ?
- because of the same reason, a process that wants to create an overview of all locked elements in the database, will take longer to complete. The reason is that the existence of such cubes in V11 could easily filter out many elements that are not locked. Locked elements in TM1 is typically very little compared to the element count in a TM1 database.
- the undocumented functions seem to survive the move to V12 but with the direct replacement the ExecuteHttpRequest() function, I would not advise to use it
- the ExecuteHttpRequest() is the way to go if you need a TI solution
- We don't need to bother with TM1 Architect/Perspectives obviously so I left it out

Code: Select all

# Locks will block any user and any TI process.
# One way to circumvent it, is the function CubeLockOverride (see: https://cubewise.com/blog/things-might-not-know-tm1-security-part-2)


# NOTE: I cannot locate where to find the owner of any given lock ! V11 Properties cubes where it was stored, are deprecated.


# Locking/unlocking a cube ( this locks/unlocks the entire cube, no data changes are possible )

# Our options include:
# - in Turbo Integrator:
#      * ExecuteHttpRequest() implementation of the TM1 REST API solutions below
#      * CubeSetLockStatus( <vCube>, <1=lock|0=unlock> ); (this appears to be an undocumented function, it is not recognized in PAW but the process saves and runs) (Security access checkbox is not needed)
# - manually, in PAW:
#      * right-click a cube > Cube operations > Lock cube (if it is currently unlocked) / Unlock cube (if it is currently locked) (a lock icon will appear when locked)
# - in the TM1 REST API:
#      * Is it locked/unlocked ? GET /api/v1/Cubes('<vCube>')/Locked (200 OK)
#      * Or: GET /api/v1/Cubes('<vCube>')?$select=Locked (200 OK)
#      * To lock/unlock ? POST /api/v1/Cubes('<vCube>')/tm1.Lock|tm1.Unlock (204 No Content)


# Locking/unlocking an element in a hierarchy of a dimension ( this locks/unlocks all cubes that use this dimension, on the specified element )
# No data changes are possible on this element
# Also, attributes for a locked element cannot be set or changed

# Our options include:
# - in Turbo Integrator:
#      * ExecuteHttpRequest() implementation of the TM1 REST API solutions below
#      * DimensionElementSetLockStatus( <vDim>, <vElement>, <1=lock|0=unlock> ); (this appears to be an undocumented function, it is not recognized in PAW but the process saves and runs) (Security access checkbox is not needed)
# - manually, in PAW:
#      * right-click an element in the Dimension Editor > Lock (if it is currently unlocked) / Unlock (if it is currently locked) (a lock icon will appear when locked)
# - in the TM1 REST API:
#      * Is it locked/unlocked ? GET /api/v1/Dimensions('<vDim>')/Hierarchies('<vHier>')/Elements('<vElement>')/Locked (200 OK)
#      * Or: GET /api/v1/Dimensions('<vDim>')/Hierarchies('<vHier>')/Elements('<vElement>')?$select=Locked (200 OK)
#      * To lock/unlock ? POST /api/v1/Dimensions('<vDim>')/Hierarchies('<vHier>')/Elements('<vElement>')/tm1.Lock|tm1.Unlock (204 No Content)



# Locking/unlocking a dimension ( this locks/unlocks the entire dimension, no metadata changes are possible )
# This does not block data changes in cube cells that reference any of the elements within the dimension, rather, changes to the contents and properties of the dimension

# Our options include:
# - in Turbo Integrator:
#      * ExecuteHttpRequest() implementation of the TM1 REST API solutions below
#      * DimensionSetLockStatus( <vDim>, <1=lock|0=unlock> ); (this appears to be an undocumented function, it is not recognized in PAW but the process saves and runs) (Security access checkbox is not needed)
# - manually, in PAW:
#      * right-click a dimension > Lock dimension (if it is currently unlocked) / Unlock dimension (if it is currently locked) (a lock icon will appear when locked)
# - in the TM1 REST API:
#      * Is it locked/unlocked ? GET /api/v1/Dimensions('<vDim>')/Locked (200 OK)
#      * Or: GET /api/v1/Dimensions('<vDim>')?$select=Locked (200 OK)
#      * To lock/unlock ? POST /api/v1/Dimensions('<vDim>')/tm1.Lock|tm1.Unlock (204 No Content)
Last edited by Wim Gielis on Wed Oct 30, 2024 8:38 pm, edited 2 times in total.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
MVP
Posts: 3194
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Element lock in v12

Post by Wim Gielis »

Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
vilmos.csaszar
Posts: 6
Joined: Tue Oct 29, 2024 6:54 am
OLAP Product: Planning Analytics
Version: 2.x
Excel Version: M365 2409

Re: Element lock in v12

Post by vilmos.csaszar »

Thank you for your help, Wim!
Wim Gielis
MVP
Posts: 3194
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Element lock in v12

Post by Wim Gielis »

vilmos.csaszar wrote: Wed Oct 30, 2024 6:41 am Thank you for your help, Wim!
You're welcome !
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
vilmos.csaszar
Posts: 6
Joined: Tue Oct 29, 2024 6:54 am
OLAP Product: Planning Analytics
Version: 2.x
Excel Version: M365 2409

Re: Element lock in v12

Post by vilmos.csaszar »

Hi Wim,

I've tried the solution. Interestingly, the query part works
(GET /api/v1/Cubes('<vCube>')/Locked (200 OK))

, but the lock part does not
(POST /api/v1/Cubes('<vCube>')/tm1.Lock).

Where did you find the tm1.Lock function? Is there a REST API documentation specific for v12?
Wim Gielis
MVP
Posts: 3194
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Element lock in v12

Post by Wim Gielis »

Hello,

Do you get an error message ?
What is the HTTP response number returned ?
What are the rights of the user executing the query ?
You can look in the $metadata document. Also you can lock a cube manually and retrieve from the REST logging what is being done to ask the action from the server.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
vilmos.csaszar
Posts: 6
Joined: Tue Oct 29, 2024 6:54 am
OLAP Product: Planning Analytics
Version: 2.x
Excel Version: M365 2409

Re: Element lock in v12

Post by vilmos.csaszar »

The user should have admin access.

The code:
sHeader = '-h Authorization:Basic ' | sPwd;

sURL = 'https://xxxxxxsaas.ibm.com/api/'| sTenant | '/v0/tm1/API_Test_Area/api/v1/Dimensions(''' | sDimName | ''')/Hierarchies(''' | sDimName | ''')/Elements(''' | sElName | ''')/Locked';

sRes = ExecuteHttpRequest( 'GET', sURL, '-k', sHeader, '-o _locktest1.json' );

The http response code here is "200" (as expected) and the json file correctly shows the lock value (False). (BTW, it's going to be fun to create a "Locked elements" subset based on this method. I guess we'll have to loop through all elements, and read the lock property value from the json output one by one.)

Locking attempt:

sURL = 'https://xxxxxxsaas.saas.ibm.com/api/'| sTenant | '/v0/tm1/API_Test_Area/api/v1/Dimensions(''' | sDimName | ''')/Hierarchies(''' | sDimName | ''')/Elements(''' | sElName | ''')/tm1.Lock';
sRes = ExecuteHttpRequest( 'POST', sURL, '-k', sHeader );


The HTTP response code here is "400," and the element is not locked after the process finishes. I'm at the very beginning of my REST API journey, so it's probably some rookie mistake. Unfortunately, I haven't found a good reference guide from IBM yet (similar to the rules guide or the TI functions guide), so I'm somewhat in the dark.
Wim Gielis
MVP
Posts: 3194
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Element lock in v12

Post by Wim Gielis »

Can you confirm your results in Postman ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
vilmos.csaszar
Posts: 6
Joined: Tue Oct 29, 2024 6:54 am
OLAP Product: Planning Analytics
Version: 2.x
Excel Version: M365 2409

Re: Element lock in v12

Post by vilmos.csaszar »

Hi Wim,
It worked. I forgot the include the content type in the header. That's how rookie I am with this. Thanks again for the directions!
Wim Gielis
MVP
Posts: 3194
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Element lock in v12

Post by Wim Gielis »

Glad you have it working now !
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply