Planning Analytics Tm1 Perspective

Microsoft® Excel® for Microsoft 365 MSO 64 Bit


How add TM1 Perspectiv to always be started with Excel?


After you have installed TM1 Perspective from tm1client_winx64h_2.0.919.10_ml.tar

Start Excel

Go to file – options

Click on Add-ins

Click GO at Manage Excel add-ins

Click Browse

Go find the file C:\Program Files\IBM\cognos\tm1_64\bin64\tm1p.xla

Click OK.

Now it should look like above.

Press OK.

Close Excel.


More Information:

Planning Analytics 2.0.9
Microsoft Windows 2019 server


How read in the dimension elements and hierarchy from a text file?


Create a csv file where you have on every row one element, and all its sub-elements before it.

In our example we have a 4 sub-element deep dimension.

You have to use your name of the elements in the dimension.

Create a new TM1 TI process, that take parameters for the dimension to update and where the file is located.

Pick up the file in Data Source tab, and setup the variables as “other”.

Variable names could be of that kind that it is easy to understand what they show.

Create a proplog code to get the dimension or the path from a cube if the parameters is blank:

sSubset = '$.'|getProcessName();

# if parameter is blank - get value from sys cube
IF ( LONG (pDim) <= 0 );

# get the values from sys settings cube
pDim = cellgets('sys.Settings','DimtoUpdate','Value');
pPath = cellgets('sys.Settings','FiletoImport','Value');


# set the values from parameters
sDim = pDim ;
DataSourceNameForServer = pPath ;
DataSourceNameForClient = pPath ;

# set the format of the file
DatasourceASCIIQuoteCharacter = '';
DatasourceASCIIDelimiter = ';'; 


Create in metadata section, that insert the elements into the dimension:



There is more ways to do this – but this is maybe the simplest way.

More Information: 

Catalogue your dimension creation processes in Cognos TM1 


Planning Analytics
Microsoft Windows 2019 server


Do you have a checklist to help out with the upgrade/installation of Planning Analytics?


Best is if you create a checklist that cover the needs of your environment, but here is a suggested to be used as a starting point.

Download from here: 

Detail upgrade instruction here: 


Action Date
Copy the media to local hard disk on server d:\install
Export configuration from cognos configuration to backup folder d:\temp\backup
Backup samples folder D:\Program Files\ibm\cognos\tm1_64\samples\tm1
Backup the tm1web_config.xml for TM1WEB from:

D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\WEB-INF\configuration

Backup server.xml and jvm.options and files from folder:

D:\Program Files\ibm\cognos\tm1web\wlp\usr\servers\tm1web

Backup of your PAA Agent file in folder:

D:\Program Files\ibm\cognos\tm1_64\paa_agent\wlp\usr\servers\kate-agent

Export the TM1 applications as a backup – the data is saved in the TM1 data folder.

  1. Open the TM1 Cognos Applications portal.
  2. Click the Export Application icon under the Actions column.
  3. From the File Download dialog box, click Save.
  4. Navigate to the directory to where you want to save the export file.
  5. Click Save.
Backup home.jsp in folder D:\Program Files\ibm\cognos\tm1web\webapps\tm1web
Backup CommonMessages.js in folder D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\common\nls\sv
Backup Tm1WebAppCam.js in folder D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\standalone
Backup pa_header.svg file in D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\themes\carbon\standalone\images\login
If HTTPS is used, backup all certificate stores
Ensure that anti-virus or Windows Defender SmartScreen is turned off on the server during installation
Uninstall Planning Analytics Performance modeler from the windows server before you install the upgrade of TM1
Check that firewall ports between servers are open

Port 25, 80, 9300, 9362, 1433, 389 etc for BI. Port 5495, 5498, 5895, 5898, 8888,9510,9511,9012, 12345, 12346 , 12366 etc for PAL
Stop all related IBM Cognos TM1 and IBM Planning Analytics services and set to MANUAL
Reboot Microsoft Windows server
Install PAL from file tm1_winx64h_2.0.919.10_ml.tar (run as administrator)
Install TM1WEB from file tm1web-11.0.93-24020517-winx64h_bundle
Go into cognos configuration and click save
Reboot the Windows server
Modifications are required when FIPSOperationMode set to 1 (Basic)

— Remove or deprioritise TLS RSA CipherSuites

— TLS V1.2 or higher must be enabled

— EDCHE CipherSuites must also be enabled 

Restore configuration files that were backed up before
Start the IBM planning services  (start CA11 if needed)
Check that you can login to TM1 Architect
Check that you can login to TM1WEB
Set all related IBM Cognos TM1 and IBM Planning Analytics services to AUTOMATIC start
Reboot windows server
Check that you can run Cognos Analytics reports against the upgrade TM1 cubes


In older versions, this was also needed to be done:

Planning Analytics often uses Cognos Analytics for authentication. If CAM security is used and an SSL certificate has been enabled in Cognos, then we need to add the certificate from Cognos Analytics into Planning Analytics. This post explains the steps required to integrate Cognos Security with SSL for Planning Analytics LocalTM1Web (Planning Analytics Spreadsheet Services) and Planning Analytics Workspace.
Extract from above websites:
Navigate to the Planning Analytics server and to the directory < PA install location> \bin64\ssl and copy the ibmtm1.arm file to the Cognos Analytics server. You can copy the file anywhere, but I recommend creating a directory for it in the installation location.
In order to import this certificate we use an executable on the Cognos Analytics server called ikeyman.exe which resides in the <CA install location> \ibm-jre\jre\bin directory. Simply double click this file to open it.
Click the Open icon on the toolbar, ensure the Key Database type is set to JKS, navigate to the directory <CA Install Location> \ibm-jre\jre\lib\security, and open the file called cacerts.
Now you have loaded the certificate file it’s time to import the Planning Analytics certificate. Under the ‘key database content’ section change the drop-down value to ‘Signer Certificates’ and press the ‘Add’ button….



Planning Analytic Workspace upgrade Date
Copy the media to the server d:\install
Unzip the file
Check free disk space on the PAW windows server
Rename folder to PAW92 and place it at root
Exclude new folder d:\paw92 from anti-virus scanning
Copy the <paw_install_location>/config/paw.ps1 file from your current installation to the new installation location
Copy the <paw_install_location>/config/certs directory from your current installation to the new installation location.
If you configured SSL, copy the <paw_install_location>/config/ssl directory from your current installation to the new installation location.
Take screen capture of the configuration today in PAW administration – any existing errors will not be solved by upgrade.
Take a backup
Stop the docker containers Paw.ps1 stop
Check that no running dockers containers Docker ps
Go to the new folder
Run installation ./start.ps1 Start.ps1
Check that admin settings are correct
Check you can login to PAW website
Stop the docker containers Paw.ps1 stop
Reboot windows server, this to ensure the system will work after a reboot.
Check you can login after 30 min
Download the new kate agent
Unzip to a temp folder
To update the Planning Analytics Administration agent Windows service, run UpdatePAAAgent.bat, passing in the full path to <PA_install_location>, that is, the parent of the paa_agent directory.
Check that contain the settings for smtp email as before
Running the /clean.ps1 script removes images for the release in which it is run. Run from old folder e.g. d:\paw90
Check free disk space



More information: 

Due to a security update included in Planning Analytics, customers who have the non-default FIPSOperationMode=1 set in their Tm1s.cfg should remove or deprioritize the TLS RSA CipherSuites for a successful handshake to occur. This includes all CipherSuites that are prefixed with TLS_RSA_. This also means that TLS V1.2 or later must be enabled, as TLS 1.1 and prior do not support alternatives. In the case of TLS V1.2, the ECDHE CipherSuites must also be enabled.

Planning Analytics TM1 Perspective

Microsoft® Excel® for Microsoft 365 MSO (Version 2308 Build 16.0.16731.20542) 64-bit


After installing a new TM1 Perspective, you get a message when you start excel:

Microsoft Office has identified a potential security concern. The digital signature is valid, but the signature is from a publisher whom you have not yet chosen to trust.


Press “Trust all from Publisher” and TM1 perspective will work in your excel installation.


More information:


Planning Analytics
Microsoft Windows 2019 server

In a TI process check that the parameter is not empty?


If the parameter is pFirst then enter code like this in prolog:

IF ( ~( pFirst @= '' ) ) ;

    ProcessQuit ;

ProcessQuit will stop the process totally,  itemreject will go to the next tab and process that – if run from prolog. In MetaData and Data tab the itemreject will act different.

# sErrorMessage = ‘The ‘ | pFirst | ‘ is not empty’;
# ItemReject ( sErrorMessage );

More Information: 


Planning Analytics Workspace version 88


How add or change text to the front page?


Login to PAW and go to administration.

Click on customization.

Click on themes.

Select system default and click download at the right. should you save in a empty folder.

Unzip to a folder sample-theme.

Rename folder sample-theme to DEV-theme.

Go into folder, and open file spec.json in notepad++

Edit the text, to something like this

“name”: “DEV-theme”,

Change the path in “brandIcon”: “DEV-theme/images/brand.svg”, to be the same as your folder name.

Change any color values – like “appbarBackground”:”#66a3ff”,

Save the file.

In file manager, use 7zip, to zip the folder to a new file:

Go back to PAW Admin page in your browser.

Click on ADD, and upload you new theme.

Select the new theme, and click Active on the right.

The SVG files can be edit with inkscape 

More Information: 

JSON key name
name The name for the theme. This name is displayed on the Themes tab of the Excel and Customizations page in Planning Analytics Workspace Administration. You cannot use the name System default, which is reserved for the system default theme.
brandText The branding text to display on the Planning Analytics Workspace home page.
brandIcon The branding image to display on the Planning Analytics Workspace home page. This image is displayed immediately prior and near the brandText.
favicon Not currently supported.
uiShellTheme Determines the appearance of items in the Home menu Home menu. Specify either light (carbon 10) or dark(carbon 100) background.
appbarBackground Application bar background color. This color is applied to the entire application bar, both clickable and non-clickable regions.
appbarForeground Application bar foreground color. This color is applied to text and icons on the application bar.This setting is not applied to the Switcher widget on the application bar. The Switcher displays a list of open items. The text of the Switcher always appears White.
appbarSelectLine Application bar selection line color. This color is applied to the border that surrounds a selected or clicked item on the application bar.
appbarPushButtonBackground Application bar push-button background color. This color is displayed when you hover or click an item on the application bar.
navbarBackground Navigation bar background color. This color is applied to the entire navigation bar, both clickable and non-clickable regions.
navbarForeground Navigation bar foreground color. This color is applied to text and icons on the navigation bar.
navbarSelectLine Navigation bar selection line color. This color is applied to the bar that appears on a selected navigation bar item.
navbarPushButtonBackground Navigation bar push-button background color. This background color is displayed when you click on an item on the application bar.
personalMenuBackground Specifies the background color of the Personal menu icon User icon.Specify inherit to inherit the navbarBackground and navbarPushButtonBackground colors.

See Application of theme keys in Planning Analytics Workspace for a graphical depiction of the application of theme keys in the user interface. 

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

Microsoft Windows 2019 server

How do i give a message to Tm1Web users that they will always see?


Edit the CommonMessages.js file and the home.jsp files in Notepad++.

The first is the name of the tab in the web browser – here you can add text, like 1984 in our example by edit the file home.jsp in folder C:\Program Files\ibm\cognos\tm1web\webapps\tm1web. Change the row 47 to the text you want:

<title>IBM Cognos TM1 Web 1984</title>

Save the file and refresh your web browser to see the change.

The second value is in a different folder for each language. The language setting in your web browser will tell what text you will show.

Go to folder C:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\common\nls\sv for Swedish language.

Edit the 378 or 379 row in file CommonMessages.js to your message text:

OFFERING_NAME:"Cognos® TM1® Web (please use chrome)",

Save the file and refresh the web browser. If you do not see the text, go into the settings and change the language for the web browser.

For English language, you may need to edit in the Tm1WebAppCam.js file, direct in folder C:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\standalone. Search for the word cognos to find where you can edit the file.


Third, you may want that the users of TM1WEB should not see the cubes views at all. This is done by a change in the tm1web_config.xml file in folder

C:\Program Files\ibm\cognos\tm1web\webapps\tm1web\WEB-INF\configuration.  Edit the line 25 to this:

<add key="NavTreeDisplayServerView" value="N" />

Save the file and restart the service “IBM Planning Analytics Spreadsheet Services”.

To change the background picture in TM1WEB you may need to edit the file C:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\themes\carbon\standalone\images\login\PA_Header.svg.

These changes is not supported by IBM.

More information:

Planning Analytics
Microsoft Windows 2019 server

How get the month before today?


In TM1 TI code you can write something similar to this in prolog:

## sMonth = 1 ;
## remove sMonth = below and test with above line

sMonth = ( MONTH ( TODAY ) ) ;
sYear = ( YEAR (TODAY) ) ;
IF ( sMonth = 1) ;
pMonth = numbertostring ( 12 ) ;
pYear = sYear - 1;
pMonth = numbertostring (sMonth - 1 ) ;
pYear = sYear ;

IF (LONG (pMonth) = 1) ;
p2Month = '0' | pMonth ;
p2Month = pMonth ;

pVersion = (p2Month) | ' - ' | numbertostring(pYear) ;

ASCIIOUTPUT ( 'd:\temp\debugtestdate.txt', numbertostring (sMonth) , numbertostring(sYear) , (p2Month) , numbertostring(pYear) ) ;

Will give a test file with this result:


If you want to have the year in 4 digits, you can add it like this;

IF ( sMonth = 1) ;
pMonth = numbertostring ( 12 ) ;
pYear = '20' | numbertostring ( sYear - 1 ) ;
pMonth = numbertostring (sMonth - 1 ) ;
pYear = '20' | numbertostring ( sYear ) ;



More Information:

Dates and Time in TM1


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

Microsoft Windows 2019 server


How make a drill process?


Inside sample application proven_techniques login as ADMIN and create a drill from cube “Drivers Alloc” to cube “employeedetails” (not the best example as they do not contain much information).

First right click on cube “Drivers Alloc” and select drill – create drill process.

Click Next, and then select a cube view as source and select any view from that target cube.

Click finish, and select a name like “TOemployees”. The hidden process }Drill_TOemployees is created.
Right click on the cube “Drivers Alloc” and select drill – create drill assignment rule.

Enter in below in rule, to make all cells drill-able to the TI process TOemployees :

[]=s:'TOemployees' ;

Then save it.

Then open the TI process to edit it. The first tabs i automatic populated from the creation cube.

In prolog add this code:

#****Begin: Generated Statements***
#****End: Generated Statements****

ByPass = 'YES';

# set the name of the target cube where the view is build
pCube = 'employeedetails' ;

# Specify dimension name
DimName1= 'employees' ;
DimName2 = 'budget' ;
DimName3 = 'departments' ;
DimName4 = 'employeedetails' ;

#Set Constants for Inserting into View

# Element1 = 'Total Employees' ; we should get only lowest member by MDX
Element2 = 'Budget' ;

# set the parameter we get from the drill function to make a selection in the view
Element3 = Receivers ;

# Element4 = 'Current Salary' ; we get all members by not including it in the code

# set view name to a uniq value
sView = 'Drill__' | pCube ;
sSubset = sView;

If( ViewExists( pCube, sView ) = 1 );
ViewDestroy( pCube, sView );
ViewCreate( pCube, sView );

# delete subset if it exist
If( SubsetExists( DimName1 , sSubset ) = 1 );
SubsetDestroy( DimName1 , sSubset );

# add all leaf elements to the dimension
sMDX = '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [ ' | DimName1 | ' ] )}, 0)}' ;
SubsetCreateByMDX( sSubset, sMDX );

ViewSubsetAssign( pCube, sView, DimName1, sSubset );

# only include on elemet of the dimenstion in the view
If( SubsetExists( DimName2 , sSubset ) = 1 );
SubsetDeleteAllElements( DimName2 , sSubset );
SubsetCreate( DimName2 , sSubset );
SubsetElementInsert( DimName2, sSubset, Element2, 1 );

ViewSubsetAssign( pCube, sView, DimName2, sSubset );

If( SubsetExists( DimName3 , sSubset ) = 1 );
SubsetDeleteAllElements( DimName3 , sSubset );
SubsetCreate( DimName3 , sSubset );
SubsetElementInsert( DimName3, sSubset, Element3, 1 );

ViewSubsetAssign( pCube, sView, DimName3, sSubset );

# as dimname4 does not have a total, 
# we leave it out and get all values in the view for that dimension.

# set what dimensions that should be in row and columns
ViewRowDimensionSet( pCube, sView, DimName1 , 1 );
ViewColumnDimensionSet( pCube, sView, DimName4 , 1 );
ViewTitleDimensionSet( pCube, sView, DimName3 );

# set standard values to exclude some cells
ViewExtractSkipCalcsSet( pCube, sView, 1 );
ViewExtractSkipRuleValuesSet( pCube, sView, 1 );
ViewExtractSkipZeroesSet( pCube, sView, 1 );

in epilog add this code:

IF (ByPass @= 'NO' ) ;

#****Begin: Generated Statements***
#****End: Generated Statements****




Click on the lowest cell and select Drill from the popup menu.

The view is shown (that is built by the TI process) – only value that change is the receivers dimension, as that parameter is used to build the view.

You can do a better solution by checking out below links.

More Information:


Planning Analytics Workspace 88
Microsoft Windows 2019 server


How setup the smtp mail function?


Check that the port 25 is open from the TM1 server to the SMTP EXCHANGE server first.

Check that the SMTP EXCHANGE server accepts mail from the TM1 server – talk to the e-mail people at the company.

Update the bootstrap properties file located in folder D:\Program Files\ibm\cognos\tm1_64\paa_agent\wlp\usr\servers\kate-agent


Set these properties for un-encrypted mail internal at the company:








After changes – you need to restart the windows service: IBM Planning Analytics Administration Agent

Then go to the PAW and the administration agent tab and click on the Test email button – enter your mail address at the company.

Then you can setup Alerts, so you will get a email when the disk is full.

To get the share book function to have a email option, you need to update the paw.ps1 file on the PAW server, with information like this:



You need to stop and start the PAW for the changes in paw.ps1 should take effect. run  .\paw.ps1 stop   ,then  .\paw.ps1  in the scripts folder.

if you get error – check the log file (app.log) in folder D:\PAW88\log\share-app on the PAW server.

Error [ERR_TLS_CERT_ALTNAME_INVALID]: Hostname/IP does not match certificate’s altnames:  can be that the EMAIL_SMTP_URL server name not match the DNS altnames. Change to other server name.

From Declan, Tm1 champion:

Setup in paw server

The obvious starting points are to gather details of your SMTP server, the port it uses (for sending email) – it can often have multiple ports available in which case – find out the “preferred” one. Generally speaking:

  • 587 – for TLS
  • 465 – For SSL
  • 25 – This might work (it does for office 365 at least) but general consensus is to not use it as it’s often blocked due to SPAM.

Also, gather the relevant account’s email and password.

Relevant Material

Based on googling the examples provided in the documentation:

I believe that node.js is being used to send the emails, and has documentation covering most of the parameters used in the examples (e.g. ?Secure=true)

This doesn’t cover the tls.rejectUnauthorized=false that is used in the example, but looking that up suggests that in the event that your server can’t be verified it will go ahead and send the email anyway… so that sounds like a parameter that I would be cautious about using!

What Worked


  • $env:EMAIL_SMTP_URL=”smtp://<MyUserName>:<MyPassword>″
  • $env:EMAIL_SMTP_URL=”smtps://<MyUserName>:<MyPassword>″
  • $env:EMAIL_SMTP_URL=”smtps://<MyUserName>:<MyPassword>”

In all cases:

  • $env:ENABLE_EMAIL=”true”
  • $env:SENDGRID_API_KEY=””
  • $env:EMAIL_FROM=”<An email address that the credentials used in $env:EMAIL_SMTP_URL have access to send from>”