Product:

Planning Analytics 2.0.9.13

Microsoft Windows 2019 server

Issue:

How easy create a parameter cube, where we can store common values like file paths or servername, that change when we move the TM1 application between servers.

Suggested solution:

Create a TI process with this PROLOG – update the values to your need:

#--------------------------------------------------------------------
# process to create a simple parameter cube
#--------------------------------------------------------------------

CubeName1 = 'SYS.ParameterCube';
DimName1 = 'sys.value' ;
DimName2 = 'sys.function' ;

# create dimension
IF ( DimensionExists ( DimName1 ) = 0 ) ;
DimensionCreate ( DimName1 ) ;
ENDIF ;

IF ( DimensionExists ( DimName2 ) = 0 ) ;
DimensionCreate ( DimName2 ) ;
ENDIF ;

#add elements to dimension
Elname1 = 'ExportFileArea';
Elname2 ='String';
Elname3 ='Number';
Elname4 = 'Explanation' ;
ElType1 = 's'; 
ElType2 = 'n';

DimensionElementInsert (DimName1, '' ,ElName2, ElType1 ) ;
DimensionElementInsert (DimName1, '' ,ElName3, ElType2 ) ;
DimensionElementInsert (DimName1, '' ,ElName4, ElType1 ) ;
DimensionElementInsert (DimName2, '' ,ElName1, ElType1 ) ;

# then only add extra rows for other functions
# Elname5 = 'Servername';
# DimensionElementInsert (DimName2, '' ,ElName5, ElType1 ) ;

# create a cube
IF( CubeExists( CubeName1 ) = 0 ) ;
CubeCreate ( CubeName1 , DimName2 , DimName1 ) ;
ENDIF ;


# add default values to the matrix
sFirstPath = '\\servername\filesharename\' ;
sExplain = 'The path to be used in beginning of filepath used in file exchange' ;

CellPutS ( sFirstPath , CubeName1 , Elname1 , Elname2 ) ;
CellPutS ( sExplain , CubeName1 , Elname1 , Elname4 ) ;

Run the TI process to create the dimensions and cube.

Then on the TI process that should use the parameters, include a code like this:

# use the values to write a file

# define variables
CubeName1 = 'SYS.ParameterCube';
Elname1 = 'ExportFileArea';
Elname2 ='String';
vFile = 'magicfile.txt';

# get value from parameter cube
vReplacePath = cellgets ( CubeName1 , Elname1, Elname2 ) ;

# create the new path and file name
vPath = vReplacePath | 'data\' ;
vFilePath = vPath | vFile ;

# write to the file
ASCIIOUTPUT ( vFilePath , 'This is the text written to the file' );

This code can be improved – that is up to you.

More Information:

https://exploringtm1.com/asciioutput-tm1-function-use-syntax/ 

https://www.wimgielis.com/tm1_clearingbigchunksofdata_EN.htm 

https://exploringtm1.com/dimensionelementinsert-tm1-function-use-syntax-and-examples/ 

https://exploringtm1.com/cellputs-tm1-function-use-syntax/ 

Product:

Planning Analytics 2.0.9.3

Microsoft Windows 2019 server

Issue:

In tm1server.log file there is reference to wrong ODBC connection. Like this

TM1.Process Process “second.process” run from process “main.process” by user “AD/donald”
TM1.SQLAPI Checking Driver Capabilities for database “TEST”
TM1.SQLAPI Driver supports SQLFetchScroll

Possible solution:

As the Data Source tab is pointing to the TEST odbc connection, a test connection is made and written in the log file.

Change the ODBC name in the Data Source tab to the expected ODBC connection like: PROD

You can still have the TI process change the ODBC connection to correct one in the prolog tab, with code like this:

#--------------------------------------------------
# Set source with values from variables you have defined before
#--------------------------------------------------
DataSourceType='ODBC';
DatasourceNameForClient=sODBCConnection;
DatasourceNameForServer=sODBCConnection;
DatasourceUsername=sUser;
DatasourcePassword=sPassword;
DatasourceQuery = sDataSourceQuery ;


#-----------------------------------------------------------------------------
# open the connection to the database ODBC connection
#-----------------------------------------------------------------------------
ODBCOpen(sODBCconnection, sUser , sPassword );

# this will change the ODBC connection, and the metadata and data tab will use the new ODBC source.

The DatasourceNameForServer= variable will set the ODBC source used by the process when run from a chore.

More Information:

https://www.ibm.com/support/pages/tm1sqlapi-01000microsoftodbc-driver-manager-cursor-library-not-used-load-failed

https://www.ibm.com/docs/en/cognos-tm1/10.2.2?topic=variables-datasourcenameforserver 

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=tv-turbointegrator-local-variables 

Product:

Planning Analytics 2.0.9.3

Microsoft Windows 2019 server

Issue:

You have create a TM1 WEBSHEET in excel (Tm1 perspective) with a ACTION BUTTON that run a process.

This works fine in TM1WEB, but not in TM1 APP WEB (old contributor). When you in the web page (inside contributor session) click on the button icon, nothing happens.

Solution:

To be able to click on websheet buttons in Tm1 Application Web, you need to first take ownership.

Click on the icon  take ownership.

Then the buttons in the websheet will work in the Tm1 App Web (contributor session).

When you are done, you need to relase the owner ship, so other can access that company node.

More information:

https://www.ibm.com/docs/en/cognos-tm1/10.2.2?topic=applications-ownership-bouncing-releasing 

Data that you can edit has a white background. Read-only data has a gray background. If you are not the current owner, the data opens in a read-only view. To start adding or editing data or click on button, click Take Ownership .

You can edit data only if it has a workflow state of Available  or Reserved . The icons indicate the workflow state.

Ownership availability for a particular node can be changed depending on how the parent node is opened. For example, contributors and reviewers who open the parent node in IBM® Cognos® Insight are not able to take ownership of the node. See the TM1® Performance Modeler documentation and the Cognos Insight documentation for details on ownership and nodes.

After taking ownership, use the Release  icon to release the data so other people can use it . In Cognos TM1 Application Web, you must submit all nodes at the level at which you take ownership and you can only release ownership at the level you have taken ownership.

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=applications-working-data

You can insert an Action button into a worksheet so users can run a TurboIntegrator process and/or navigate to another worksheet. Users can access these buttons when working with worksheets in Microsoft Excel with TM1, or with Websheets in TM1 Web.

The action buttons will work in TM1 application Web, too, even not stated in the IBM documentation.

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=developers-using-tm1-action-buttons-build-worksheet-applications 

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=excel-action-buttons 

https://www.ibm.com/docs/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/tm1_inst.pdf 

Product:

Planning Analytics 2.0.9

Microsoft Windows 2019 server

Issue:

After change in Windows registry to prevent use of TLS 1.0 and TLS 1.1 communication, the ODBC driver to SQL server does not work.

ODBCOpen ( vSource, vClient, vPassword );  does not work.

Error can be: DCOM was unable to communicate with the computer SQLserver using any of the configured protocols; requested by PID

SQLState: 01000
SQL Server Error:1
Microsoft ODBC SQL Server Driver DBNETLIB ConnectionOpen SECCreateCredentials()
Connection Failed
SQLState: 08001
SQL Server Error:18
SSL Security Error

Solution:

Change the ODBC driver from Microsoft SQL Server ODBC Driver Version 10.00.14393 to a new, like Microsoft SQL Server Native Client Version 11.00.7462.

Backup the registry values under [HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBC.INI\ODBC Data Sources], to be able to restore it.

Go to Control Panel – Administrative tools – ODBC Data Sources (32-bit), to add the new driver with the same name and selected database.

You can run below commands to set the values for disabled TLS 1.0 and TLS 1.1 on the server:

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server" /v Enabled /t REG_DWORD /d 0 /f

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server" /v DisabledByDefault /t REG_DWORD /d 1 /f

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client" /v Enabled /t REG_DWORD /d 0 /f

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client" /v DisabledByDefault /t REG_DWORD /d 1 /f

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server" /v Enabled /t REG_DWORD /d 0 /f 

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server" /v DisabledByDefault /t REG_DWORD /d 1 /f

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client" /v Enabled /t REG_DWORD /d 0 /f 

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client" /v DisabledByDefault /t REG_DWORD /d 1 /f

More Information:

https://thesecmaster.com/how-to-enable-tls-1-2-and-tls-1-3-on-windows-server/

https://support.microsoft.com/en-us/topic/kb3135244-tls-1-2-support-for-microsoft-sql-server-e4472ef8-90a9-13c1-e4d8-44aad198cdbe

https://support.microsoft.com/en-us/topic/october-20-2020-kb4580390-os-build-17763-1554-preview-ac4799c9-838f-8665-a968-0f19b6cb1049

https://think.unblog.ch/en/how-to-use-tls-1-2-and-tls-1-3-on-windows-server/ 

https://support.site24x7.com/portal/en/kb/articles/how-to-check-if-tls-1-2-is-enabled

Product:
Planning Analytics 2.0.9

Issue:

What are the shortcut keys in TM1 Architect?

Partly solution:

CTRL+I  = indent (tab text)

CTRL+S = save the TI process

CTRL+Z = undo last action

Use PAW to get a better user experience. https://pmsquare.com/analytics-blog/2022/5/5/new-cube-viewer-set-editor-in-planning-analytics

To find where the keys are on your keyboard, use this layouts.

United Kingdom

USA

More Information:

https://en.wikipedia.org/wiki/QWERTY

https://revelwood.com/ibm-planning-analytics-tips-tricks-rule-editor-keyboard-shortcuts/

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=r-data-spread-keyboard-shortcuts

https://www.wimgielis.com/tm1_articles_EN.htm

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=features-keyboard-navigation

Product:

Planning Analytics 2.0.9.13 TM1_version=TM1-AW64-ML-RTM-11.0.913.10-0

Microsoft Windows 2019 server

Microsoft SQL Server 2012 Native Client ODBC driver version 11.2.5058.0

Issue:

Can not open a connection to a Microsoft SQL database, via ODBCopen command. You can open the connection in the TM1 datasource tab and read the data, but not open in a ODBCopen command in the prolog tab. When you test the ODBC connection in Windows, it works fine with your SQL login.

You get similar error, when you try a 32 bit ODBC or 64 bit ODBC connection.

In Windows event log you get this error:

Faulting application name: tm1odbcproxy32.exe, version: 0.0.0.0, time stamp: 0x62586410
Faulting module name: tm1odbcproxy32.exe, version: 0.0.0.0, time stamp: 0x62586410
Exception code: 0xc0000005
Fault offset: 0x000058f8
Faulting process id: 0xfc8
Faulting application start time: 0x01d93a5fe5481324
Faulting application path: C:\Program Files\ibm\cognos\tm1_64\bin64\tm1odbcproxy32.exe
Faulting module path: C:\Program Files\ibm\cognos\tm1_64\bin64\tm1odbcproxy32.exe
Report Id: cadfaa7a-2357-4370-9ecf-3c6500029586
Faulting package full name:
Faulting package-relative application ID:

Solution:

The connection needs to use UNICODE. Use ODBCOPENex instead.

Format is: ODBCOPENEx (dataset name, dataset client name, client password, (use-Unicode-interface flag) )

https://www.ibm.com/docs/en/cognos-tm1/10.2.2?topic=functions-odbcopenex

In TM1 Architect data source tab, you get same error if you uncheck the “use unicode” checkbox.

In prolog enter this code:

#--- debug setup
sDEBUGfile = 'debugfile3.txt';
sDEBUGpath = 'c:\temp\';
sDEBUG1 = sDEBUGpath | sDEBUGfile ;

#-- setup the database connection
sODBCname = 'windows2016';
sUser = 'donald';
sPassword = 'Password!';

#-- open the connection to the database with unicode by adding the 1 parameter
ODBCOpenEx(sODBCname, sUser, sPassword,1);

#-- create the sql statement - adjust for your test database
sSQL = 'TRUNCATE TABLE AdventureWorksLT2019.dbo.Lista';

ASCIIOutput ( sDEBUG1, sSQL, 'check sql syntax');

#-- execute the SQL statement
ODBCOutput (sODBCname , ( sSQL) );

You can open the SQL ODBC connection in PROLOG, even when other data sources are in use.

sODBCname = ‘windows2016’; should be the name of the ODBC connection you want to use, it can be case-sensitive.

Please try to change ODBC driver in Windows Control Panel on your Windows Server. A new ODBC driver can help.

 More Information:

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=tv-turbointegrator-local-variables

https://www.bi4all.pt/en/news/en-blog/cognos-tm1-dynamic-management-of-data-sources-and-connections-through-configuration-cubes/

https://www.tm1forum.com/viewtopic.php?t=11978

https://www.tm1forum.com/viewtopic.php?t=10947

https://www.tm1forum.com/viewtopic.php?t=16234

https://quebit.com/askquebit/IBM/how-to-speed-up-tm1-odbcoutput/

Product:

Planning Analytics 2.0.9x

Issue:

When is metadata tab executed?

Solution:

If the data view contain calculated cells, then you may need to add this in the prolog:

ViewExtractSkipCalcsSet ( sCubeName, sViewName, 0 );

to make the metadata be processed with the calculated values.

Metadata and data is only processed, if you have any data in the variables.

From Alan Kirk at https://www.tm1forum.com/viewtopic.php?t=15670

These are the roles of the tabs in a TI process:

Prolog
This runs ONCE, before the data source (if any) is connected to, read or even looked at by the process.
You can use it to:

• Create a data source by defining a view and its subsets;
• Check for the existence of a file and run a batch file to rename or move it;
• Change the data source from a view to a text file to a dimension subset or whatever you need it to be;
• Dynamically assign a different data source (a different file name, a new view name or whatever) to the process at run time;
• Define any constants;
• Write information to a log file or to the server log;
• Insert new elements to a dimension if you have ones that do not come from your data source;
• Pretty much anything that you need to do before the process even looks at the data source, if any.
• The fact that it runs before you connect to the data source is what allows you to change the data source on this tab as mentioned above using functions like DataSourceType and DatasourceNameForServer.

There is one proviso with this; one thing that you can’t do within a TI itself is to change its variable list or the data types. Normally you would define those by using an example file or view at the time that you write the process.

Metadata
This loops through the data source (if any) ONCE. That is, for every record in the data source, the values in each column will be read into the corresponding process variables, then the code on the metadata tab will be executed, then the process will move onto the next row of the data source and the whole process repeats.

The purpose of the tab is to create any metadata (cubes, dimensions etc) that you will need to store the data that you upload on the Data tab.

When you use functions like DimensionElementInsert, changes are made to a copy of the dimension.

After the last record has been processed on the Metadata tab, the real dimension will be overwritten by the copy. If you did any insertions on the Prolog tab, these will also be added at that point.

Typically you will be using element names from your data source’s variables to do the element insertion. If you have a hard coded element name as you have in your example code, the TI will add the element on the first pass if it needs to, and spend every other pass saying “Nope, it’s already there, Nope, it’s already there, Nope, it’s already there, etc”. This is not what we call “optimum code efficiency”. That’s why insertions like that are generally done on the Prolog tab.

There are also newer functions like DimensionElementInsertDirect which will push the elements straight into the dimension without creating a copy. Information about such functions will be found in the Reference Guide.

IMPORTANT NOTE FOR NEW PLAYERS: If you don’t have a data source, or if you have a data source which has no records, then nothing that you have written in the Metadata tab will ever be executed. Ever.

Data
This will again loop through each row in the data source one at a time, assigning the values in each column to variables, and doing whatever you tell it to. This may be loading values into a TM1 cube, or it may be writing values from a TM1 cube to a text file, or to another database. If you are loading values into a cube it’s assumed that you have created any necessary elements in the Metadata tab.

Note that any attributes of elements (alias names, etc) are regarded as being data for the purposes of this exercise and need to be written on the Data tab (unless you used the Direct functions mentioned above).

Epilog
This is run AFTER the last Data record is processed. It is usually used to clean up whatever needs cleaning up, and maybe writing results into a control cube, according to taste.

More Information:

https://blogs.perficient.com/2015/04/29/ibm-cognos-tm1-updating-metadata-in-ti-submit-time-explore/

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=tf-odbc-turbointegrator-functions

https://exploringtm1.com/viewextractskiprulevaluesset/

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=vmtf-viewextractskipcalcsset

Product:

Planning Analytics 2.0.9.13

Microsoft Windows 2019 server

Issue:

If i get a text file with the AD usernames that should be inside my TM1 application, how do i remove the users from TM1 that is not in the list?

This for a TM1 application that uses CAM security, and is connected to CA11 for login.

If you have a AD group called “Tm1people” you can with this command create a list:

IN CMD

dsquery group -name "Tm1people" | dsget group -members > D:\user_handling\keepusers.txt

IN POWERSHELL

Get-ADGroupMember -identity "Tm1people" | select SamAccountName

Suggested Solution:

Create two TI processes, one that load the list of user in a temp dimension, and a second process, that check every user in the TM1 application against that temp dimension list.  Login to TM1 Architect as administrator.  Create a new TI process.

In process ONE, go to data source and select text and select the file D:\user_handling\keepusers.txt

Click preview, and you should get a list of the users Active Directory account names.

Change the variable name to vUser and set the content to other. This to get the variable to be accessible in the Advanced tab.

In Parameters tab, create a pSure parameter, that you need to answear JA to make the process run.

In advanced prolog tab, enter this code:

# only execute if parameter pSure is JA
if (pSure @<> 'JA');
ProcessQuit;
endif;


#--- variables section ---
sCube = '}ClientGroups';
sDimName1 = '}Clients';

#-- enter the domain name of your company
sDomainName = 'adname/' ;
sDimName2 = 'TempUsersList';

#--- file name setup for debug text file
sFileName= 'debugfile1.txt';
sFilePath = 'd:\temp\';
sDEBUGFILE = sFilePath | sFileName ;

# create a temporary dimension to hold all users to keep
if (DimensionExists( sDimName2 ) =1);
DimensionDestroy(sDimName2);
endif;
DimensionCreate( sDimName2 );

In the Metadata tab, enter this code:

#-- add domain name to username
sFullUser = sDomainName | vUser;
# add users from list to the temp dimension
DimensionElementInsertDirect( sDimName2, '' , sFullUser, 'S' );

In the Epilog tab, enter this command to start the second process:

# call the other process to clean out users
ExecuteProcess( 'second processname');

Save the process, and create a new process, the second process TWO.

In Advanced – prolog tab, enter this code:

#--- variables section ---
sCube = '}ClientGroups';
sDimName1 = '}Clients';
#-- enter the domain name of your company
sDomainName = 'adname/' ;
sDimName2 = 'TempUsersList';

#--- file name setup for debug text file
sFileName= 'debugfile2.txt';
sFilePath = 'd:\temp\';
sDEBUGFILE = sFilePath | sFileName ;


#-- in a while loop check if the user clients exist in the dimension list --

iElm = 1;
#-- get number of users in clients dimension
ElmCount = DIMSIZ(sDimName1);
#-- do this for each user in client dimension
WHILE(iElm <= ElmCount);
#-- get the user alias from the list
sElment = DIMNM( sDimName1, iElm );
sElmentAlias = ATTRS( sDimName1, sElment, '}TM1_DefaultDisplayValue' );

#-- check if he is not in the list from the file
nResult = (DIMIX (sDimName2, sElmentAlias) );
if( nResult=0 );
#-- get if the user is admin
vGroupAdmin = CellGetS( sCube , sElment , 'ADMIN' );
If (vGroupAdmin @<> 'ADMIN' );
sPrincipalName = DimensionElementPrincipalName( sDimName1, sElmentAlias );

# check that name is not blank
if (sPrincipalName @<> '');
##--- remove user from security cube in tm1
##--- only if user is not part of ADMIN group

#--- only debug, write out data to file
# ASCIIOUTPUT ( sDEBUGFILE, sPrincipalName, sElment, sElmentAlias );

DeleteClient ( sPrincipalName );
endif;

endif;

endif;

iElm = iElm + 1;
END;

In the epilog tab, enter this code:

#-- Remove the temp list of users to prevent accidental runs
DimensionDestroy(sDimName2);

Save the process.

Create the keepusers.txt file and test run the process in a sample application with many users.

You may need to have below command in the Epilog:

SECURITYREFRESH;

More Information:

https://blog.technotesdesk.com/export-a-list-of-members-from-an-active-directory-group-to-a-text-file 

https://everanalytics.wordpress.com/2015/09/15/manage-user-sessions-on-your-ibm-cognos-tm1-server/ 

DIMNM TM1 Function: Use and Syntax

https://exploringtm1.com/dimsiz-tm1-function-use-and-syntax/ 

https://www.wimgielis.com/tm1_clientsgroups_EN.htm

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=stf-deleteclient

How list users in TM1 with python:

https://github.com/wimgielis/Count-and-list-TM1-users 

https://www.tm1forum.com/viewtopic.php?t=15531

Product:

Planning Analytics 2.0.9.13

Microsoft Windows 2019 server

Problem:

If i remove a user from the TM1 Security dialog, will he come back if he have access in Cognos Analytics, when we use CAM security in TM1?

Solution:

Yes, If a user can login to CA11, that is used as the validator for TM1 access ( with the IntegratedSecurityMode=5 parameter in tm1s.cfg file), then the user will pop up inside the Tm1 architect security clients/groups dialog.

The AD group he is part of will not be listed, but if you have before added a AD group into TM1, then he will be marked with a X that he belongs to that AD group.

This give that you can remove a user from security clients/groups dialog in Tm1 architect, and he will next time he logins to TM1 come up again. He will also get back the security he had, as he belongs to the same AD groups as before.

You can not remove the AD groups from security clients/groups dialog, as then the security you have set on that group in TM1 is gone.

If you set security direct on the user, that security is also gone when you remove the user from Tm1.

To remove several users at once from a tm1 application with CAM security, create a text file, with each AD account name on a row (that you want to be removed). Save the file as D:\user_handling\removeuser.txt on the TM1 server.

Login to Tm1 Architect on the Tm1 server direct, and create a new TM1 process.

Select Text as datasource, and select the D:\user_handling\removeuser.txt file.

Click Preview, and you should see a list of the names.

Go to the Variables tab, and change the name of the first (and only) variable to vUser.

Change the contents to be Other.

Go to the Advanced – prolog tab, and enter below code:

sCube = '}ClientGroups';
sDimName = '}Clients';

#-- enter the name of the company domain as it looks in CA11 below --
sDomainName = 'CompanyAD/' ;

#-- file name setup for debug text file --
sFileName= 'debugfile1.txt';
sFilePath = 'd:\temp\';
sDEBUGFILE = sFilePath | sFileName ;

In the metadata tab, enter below code:

#--- add domain name to username name
sFullUser = sDomainName | vUser;

#--- find users alias in client dimenstion and get the tm1 internal name
sPrincipalName = DimensionElementPrincipalName( sDimName, sFullUser );
#--- check that user is not admin
vGroupAdmin = CellGetS( sCube , sPrincipalName , 'ADMIN' );
#--- remove remark to debug write out data to file
# ASCIIOUTPUT ( sDEBUGFILE, sPrincipalName, vGroupAdmin );

If (vGroupAdmin @<> 'ADMIN' );
#--- remove user from security cube in tm1
#--- only if user is not part of ADMIN group
DeleteClient ( sPrincipalName );
endif;

Save the TM1 TI process, and test run it in a sample database first.

More information:

https://www.ibm.com/docs/en/cognos-tm1/10.2.2?topic=groups-deleting-user#DeletingaUser_N1704BD 

https://succeedium.com/teamone/doc/users.html#deleting-users 

Product:
Planning Analytics 2.0.9.13  ( IBM_PAfE_x64_2.0.82.5.xll )
Microsoft Windows 2019 server

Issue:
How publish a excel sheet with a dynamic report to TM1WEB?

Solution:

You publish the excel sheet to PAW, and it will be availble in both TM1WEB and PAW.

To be able to create Action Button inside excel for Planning Analytics, you need to enable trust for VBA.

Inside Excel – go to file – options – trust center – trust center settings button – macro settings.

Mark “trust access to the VBA project object model”.  Click OK.

You need also in many cases have “Optimize for compatibility” marked.

Go to Excel Options – General – User Interface options, mark “Optimize for compatibility“.

Click OK.

To be able to publish a excel dynamic report, you must save the report first, before you publish it. Recommended to save it with the same name, as you would like to have it shown in TM1WEB.

Click on Publish icon in Excel ribbon.

Select the application folder, where you want your websheet and enter a name. Above we have expanded Planning Sample applications.

Mark “make public” if you want other users to see the websheet in TM1WEB. Press publish button.

If you get a blank page in PAW when you insert a Excel sheet to a workbook, that works fine in TM1WEB, you need to ensure that the firewall ports between the TM1 web server and the planning analytics workspace server is open on ports 9510 and 9511. The websheet that show inside PAW; is rendered from TM1WEB.

Access to TM1WEB for both end user and the PAW server is essentiell to make it (PAfE) work.

More information:

https://www.ibm.com/support/pages/blank-white-page-rendered-while-opening-websheets-within-planning-analytics-workspace

https://www.ibm.com/support/pages/planning-analytics-excel-task-pane-empty-and-grayed-out-when-connecting-cam-secured-tm1-server

https://qmetrix.com.sg/ibm-planning-analytics-for-excel-pax-vs-tm1-perspectives/