Product:
Planning Analytics 2.0.9.3
Microsoft Windows 2019 server

Problem:
After SSL setup in TM1WEB, then TM1WEB does not list any TM1 servers to connect to.
In TM1 APP WEB (pmpsvc) when you click on a contributor link you get a “an error has occurred”.

TM1 applications should use the internal Cognos certificate, ibmtm1.arm.

Please follow the official documentation, as it changes with every new version of PA.

Possible Solution:

As TM1Web is using a new keystore, you must include the TM1 Server certificates in the keystore file or you will be unable to see your TM1 Servers, point to that the SSL CERT import failed.

Redo the setup from; but first take a backup of all keystores files you use.

https://www.ibm.com/support/pages/how-configure-ssl-ibm-planning-analytics-spreadsheet-services-using-existing-keystore

If you got a PFX file with the certificate chain and key as you need you can use it to setup SSL.

For PMPSVC do this;
Start IKEYMAN.EXE as administrator from C:\Program Files\ibm\cognos\tm1_64\jre\bin folder
Open CAMkeystore file from C:\Program Files\ibm\cognos\tm1_64\configuration\certs folder
It is a PKCS12 file with password  NoPassWordSet

In Personal Certificates remove the encryption certificate
As you already have a correct PFX file, you click on IMPORT button.
Select you PFX file and enter the password you got with the PFX file from the Certified Authority who created the PFX for you.

You will be asked to rename the personal certificate, enter encryption and press OK.
Go then to Signer Certificates and check that you have also got the root and  intermediate certificates for your server. Double click on the certificates to check they have correct date and DNS alias.

You may need to import the \bin64\ssl\ibmtm1.arm file again as Signer Certificates.
Close IKEYMAN.

Go into Cognos Configuration for Planning Analytics (TM1).
Edit the TM1 Applications Properties.  Update all URI references to contain your fully qualified address, as well as change the http to https.
Edit the Local Configuration properties.  Add the property StandaloneCertificateAuthority and set it to True.

Edit the Cryptography > Cognos properties.  Change the Use third party CA? to True.
Save the Cognos configuration for Planning Analytics (TM1).
Start the TM1 service.

For new TM1WEB:

Export the Root and Intermediate certificates from your web browser, by go to the cognos site, and then select each certificate and save them as Base-64 encoded X.509 cer files.
Stop IBM Cognos TM1 service.
Copy the PFX file you got to C:\Program Files\ibm\cognos\tm1web\bin64\ssl folder

Open C:\Program Files\ibm\cognos\tm1web\wlp\usr\servers\tm1web\server.xml in notepad++
Update the ports you want to use, here 9510 for SSL and enter -1 to disabled HTTP.
<httpEndpoint id=”defaultHttpEndpoint” httpPort=”-1″ httpsPort=”9510″ host=”*” removeServerHeader=”true”>

Add as last line:
<keyStore id=”defaultKeyStore” location=”${wlp.user.dir}/../../bin64/ssl/customKeystore.pfx” password=”xxxxthepasswordxxxx” />

Replace with your PFX file name and the password for that file.
Go to C:\Program Files\ibm\cognos\tm1web\jre\bin\ in a DOS prompt.

Import the TM1 cert with command:
keytool -importcert -keystore ..\..\bin64\ssl\customKeystore.pfx -storepass xxxxthepasswordxxxx -storetype pkcs12 -noprompt -alias ibmtm1 -file ..\..\bin64\ssl\ibmtm1.arm

Copy your Root and Intermediate certificate files to your \tm1web\bin64\ssl\ directory

Import the Root and Intermediate certificates with this command:
keytool.exe -import -trustcacerts -file “..\..\bin64\ssl\ca.cer” -keystore “..\..\bin64\ssl\tm1store” -alias ca -storepass applix
keytool.exe -import -trustcacerts -file “..\..\bin64\ssl\intermediate.cer” -keystore “..\..\bin64\ssl\tm1store” -alias intca -storepass applix

CER or PEM files should work both. Now the new tm1store is updated with your root certs.

Start your IBM Planning Analytics Spreadsheet Service

Test to browse to https://paservername.domain.com:9510/tm1web

Check for errors in file C:\Program Files\ibm\cognos\tm1web\wlp\usr\servers\tm1web\logs\console.log

More information:

https://www.ibm.com/support/pages/use-ikeyman-configure-custom-ssl-certificates-tm1web

https://www.ibm.com/support/pages/how-configure-ssl-ibm-planning-analytics-spreadsheet-services-using-existing-keystore

https://www.ibm.com/support/knowledgecenter/SSVJJU_6.3.0/com.ibm.IBMDS.doc/admin_gd175.htm

https://www.sslshopper.com/ssl-converter.html

To check a PFX file;

One your laptop install openssl from here https://github.com/git-for-windows/git/releases/tag/v2.23.0.windows.1 – get the file Git-2.23.0-64-bit.exe. Run the installation with all default values.

Start a command prompt as administrator. Go to folder C:\Program Files\Git\mingw64\bin

openssl  pkcs12  -in  c:\temp\customKeystore.pfx -out  c:\temp\good.pem  -nodes

Open good.pem  in notepad++ to check it contains 4 certificates.

The PEM format is the most common format that Certificate Authorities issue certificates in. PEM certificates usually have extensions such as .pem, .crt, .cer, and .key. They are Base64 encoded ASCII files and contain “—–BEGIN CERTIFICATE—–” and “—–END CERTIFICATE—–” statements. Server certificates, intermediate certificates, and private keys can all be put into the PEM format.

Product:
Planning Analytics 2.0.9
Microsoft Windows 2019 Server

Problem:
How many PVU is the CPU on my virtual TM1 server?

You can in Task Manager on the CPU tab, see number of sockets and number of cores.

Solution:
Check the number of sockets, if is less than or equal too 2 then each CPU CORE is 70 points.

In some cases the Windows 2019 task manager does not show correct number of sockets, so check the server hardware – what server are you running the virtual servers on? Then check that hardware, what number of sockets they have. For example, if you have a HPE ProLiant BL460c Gen9 server, check here

https://support.hpe.com/hpesc/public/docDisplay?docId=emr_na-c04434541

says it are Upgradeable to two (2) processors. Then we assume it have 2 sockets max.
This mean that each cpu core in task manager is 70 pvu points.
A windows server with 4 cpu cores will then be 280 pvu points.

Check your contract for lines like this;  IBM Planning Analytics Local TM1 Server for Non-Production Environment Processor Value Unit (PVU) Annual SW Subscription & Support Renewal to see how many PVU points you can spend on your server.

More Information:
https://www.ibm.com/software/passportadvantage/pvu_licensing_for_customers.html

https://infocube.com.au/ilmt

https://lodestarsolutions.com/what-the-heck-is-an-ibm-cognos-pvu

Product:
Planning Analytics Workspace 2.0.
Linux Red Hat 7

Problem:
After change/crash of PAW, the system is starting but user can not login. They get the message “Planning Analytics Workspace is unavailable. Try again in a few minutes”
On the Linux server you run command to check that all containers are up, and they all show the same number of seconds to be up. This make it look like the PAW is working.
sudo docker ps

If you check the logs for a container, with below command, you can see if there are any errors giving more information.

sudo docker logs share-platform

cp: failed to extend ‘bootstrap.properties’: No space left on device

cp: error writing ‘bootstrap.properties’: No space left on device

Check the date before in log file, above can be a fill up disk of log files that have crashed the PAW before.

Check space with command: df -h

sudo docker logs pa-gateway

This may show AH01114: HTTP: failed to make connection to backend: share-platform, that tell us that it does not speak with the share-platform container, who had a problem before.

Solution:

Recreate the images, they can have been corrupted of the space outage you had earlier. This may remove the books you have inside PAW, so take a backup first by run script /ibm/paw/scripts/backup.sh to create a backup file under backup folder.

Go to the /ibm/paw/scripts folder on Linux server. Enter below command to stop PAW

./paw.sh stop

Backup the config folder and it sub-folders;   cp -ivR config  /tmp/cognos  (will copy all files in config folder to /tmp/cognos folder)

Kill all running containers
docker kill $(docker ps -q)
Delete all stopped containers
docker rm $(docker ps -a -q)
Delete all images
docker rmi $(docker images -q)

Then go to the PAW folder of your installation on the Linux server and run start.sh to create the images/containers from scratch.

./Start.sh

Answer YES on both questions.

You can in Linux test that the PAW is up by entering command (replace with your server DNS alias):

curl -k https://planningworkspaceserver.domain.com

this will return the html page as plain text – check for errors.

More Information:
https://www.ibm.com/support/pages/troubleshooting-planning-analytics-workspace-related-docker-issues

https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/c_paw_trbl_cant_access_paw.html

https://www.ibm.com/support/pages/unable-access-ibm-planning-analytics-workspace

https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/t_paw_uninstall.html

To limit docker log files you can edit the daemon.json file in folder /etc/docker

This sample configuration will limit the json log files to 10 megabytes, and will only keep the 5 most recent logs:

{

“log-opts”: {

“max-size”: “10m”,

“max-file”: “5”

}

https://ss64.com/bash/cp.html

Product:
Planning Analytics Workspace 2.0.39
Microsoft Windows 2016 server

Problem:
After restart/upgrade of PAW – when going to the administrator tab over databases, some show “Threads blocked: unavailable”

Solution:
Clear the cache in the web browser first, or try with a different web browser (chrome).

It can be that the account you logged in to PAW is not administration in the TM1 instance, then the threads info will not be shown.

More information:
https://www.ibm.com/support/pages/changes-planning-analytics-workspace-database-administration-2041

https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_prism_gs.2.0.0.doc/c_paw_administer_servers.html

Product:
Planning Analytics 2.0.9
TM1SERVER_APP_version=TM1SERVER_APP-AW64-ML-RTM-11.0.93.28-0
TM1SERVER_APP_name=IBM Cognos TM1 Server Application
Microsoft Windows 2019 server

https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_nfg.2.0.0.doc/c_pa_nfg_introduction.html

Problem:
Company have moved from NATIVE TM1 security to Active Directory security – where all users have got a new ID in Cognos Connection. That means that inside Planning Analytics (Tm1) the user is a new person with a new ID, so all private views are lost.

When user Donald login with his new AD ID, he does not see his old private views in Tm1 Architect.

Solution:

Stop the TM1 service for that instance.

Good that TM1 use a file system to store information.
All private views and data are stored under the user name folder in \data — in above case that should be in folder \data\donald\.
You can then copy the vue files from C:\Program Files\ibm\cognos\tm1_64\samples\tm1\Rules_Guide_Data\donald\Currency}vues\testview1.vue to folder C:\Program Files\ibm\cognos\tm1_64\samples\tm1\Rules_Guide_Data\Currency}vues to make the testview1 public by place it direct under the cube view folder.

We login to TM1 application RulesGuide as Admin in native mode, and create a private view called testview1.

This view is saved under the Tm1 application data folder “rules_guide_data” under the user name and cube name as shown above.

If we copy only the testview1.vue file to the cube folder direct under data e.g. Currency}vues then the view becomes public.
To copy it from NATIVE user Admin to AD user Admin, you have to go under the folder with the same name as the Cognos Namespace, in our example AD. Under the user there you have to copy both the cube folder and the cubeview file.

Folder AD will be different in your environment, as it is the Cognos Namespace name.

You need to copy both cube name folder – in our example Currency}vues and the vue file there under to move the private view to a different user as a private view.

Start the TM1 server after you have copied the files and you should now see the views as other user.

More information:

https://blog.octanesolutions.com.au/views-integration-in-pax-and-paw

https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_rest_api.2.0.0.doc/t_tm1_rest_api_cubes_and_native_views.html

 

 

Product:
Planning Analytics 2.0.7
Microsoft Windows 2016 server

Problem:
How to make the log files not so big for TM1?
Can i have tm1top data logged to a file?

Solution:

Create the tm1s-log.properties file in the same folder as your tm1s.cfg file.
log4j.appender.S1.MaxFileSize=10 MB tells how big your tm1server.log file will be, default is much larger.
log4j.appender.S1.MaxBackupIndex=40 is the number of files that will be kept. Increase this to the value you need.

To enable TM1TOP data, without using the tm1top utility, add to your tm1s.cfg file
TopLogging=T
Then active and set the filename with this parameters in tm1s-log.properties file:

log4j.logger.Top=INFO, S_Top
log4j.appender.S_Top=org.apache.log4j.SharedMemoryAppender
log4j.appender.S_Top.MemorySize=5 MB

log4j.appender.S_Top.File=tm1top.log

log4j.appender.S_Top.Format=TM1Top

Then in the tm1s-log.properties file you adjust the size with the parameters:

log4j.appender.S_Top.MaxFileSize=10 MB
log4j.appender.S_Top.MaxBackupIndex=20

This is a great example of a tm1s-log.properties file that is copied from https://www.ykud.com/blog/cognos/tm1-cognos/tm1s-log-properties/

# main logging file
log4j.logger.TM1=INFO, S1
# it's good to have Locks details -- provides the names of the locked objects
log4j.logger.TM1.Lock.Exception=DEBUG, S1
# S1 is set to be a SharedMemoryAppender
log4j.appender.S1=org.apache.log4j.SharedMemoryAppender
# Specify the size of the shared memory segment
log4j.appender.S1.MemorySize=5 MB
# Specify the max filesize
log4j.appender.S1.MaxFileSize=10 MB
# Specify the max backup index
log4j.appender.S1.MaxBackupIndex=20
# Specify GMT or Local timezone
log4j.appender.S1.TimeZone=Local

# event logging configuration
# Meeds EventLogging=T in the tm1s.cfg
# https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/c_eventlogging.html
log4j.logger.Event=INFO, S_Event
log4j.appender.S_Event=org.apache.log4j.SharedMemoryAppender
log4j.appender.S_Event.MemorySize=1 MB
log4j.appender.S_Event.MaxFileSize=10 MB
log4j.appender.S_Event.MaxBackupIndex=10
log4j.appender.S_Event.File=tm1event.log
log4j.appender.S_Event.Format=TM1Event
log4j.appender.S_Event.TimeZone=Local

# tm1top logging configuration
# Set up TopLogging=T in the tm1s.cfg
# https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_op.2.0.0.doc/c_pa_top_logger.html
log4j.logger.Top=INFO, S_Top
log4j.appender.S_Top=org.apache.log4j.SharedMemoryAppender
log4j.appender.S_Top.MemorySize=5 MB
log4j.appender.S_Top.MaxFileSize=10 MB
log4j.appender.S_Top.MaxBackupIndex=20
log4j.appender.S_Top.File=tm1top.log
log4j.appender.S_Top.Format=TM1Top

# Logins file -- records every time a user logins, can be used for license evaluation or just checking user activity
log4j.logger.TM1.Login=DEBUG, S_Login
log4j.appender.S_Login=org.apache.log4j.SharedMemoryAppender
log4j.appender.S_Login.MemorySize=5 MB
log4j.appender.S_Login.MaxFileSize=10 MB
log4j.appender.S_Login.MaxBackupIndex=20
log4j.appender.S_Login.File=tm1login.log
log4j.appender.S_Login.TimeZone=Local

More Information:
https://www.ykud.com/blog/cognos/tm1-cognos/tm1s-log-properties/

https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_op.2.0.0.doc/c_pa_top_logger.html 

https://www.ykud.com/posts/

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

https://www.ibm.com/support/pages/write-tm1-server-debug-logging-alternate-file

Product:
Planning Analytics 2.0.6 TM1_version=TM1-AW64-ML-RTM-11.0.6.71-0
Microsoft Windows 2016 server

Problem:
Does it exist a better TI editor than TM1 Architect?

Solution:
Yes, there exist – one is PAW (planning analytics workspace) or you can download and use cubewise ARC for a trail period. Browse to https://code.cubewise.com/arc-download
Click on the “Accept License Agreement” and click on SERVER FOR WINDOWS 64 to download it.

 

Save the file to a empty folder like c:\arc on your tm1 server and unzip it in that folder.

Double click on the arc.exe file to start the web-server arc.

It will start up Internet explorer with the start page. Dismiss the news prompt, and click on the license icon.

Under Start Trail tab, select Individual, and scroll down to accept the license by click on “Agree and start Trail”. It will create the license file in you arc folder.
Now stop the arc.exe dos window – to stop the service.
Start a new COMMAND windows as administrator.
Go to the c:\arc folder and enter this command:

arc.exe   -install

This will install the program as a service. Go to services in windows control panel and start the arc service.

Now you can from you laptop browse to the ARC on the TM1 server, by entering http://tm1servername:7070
(you need to have port 7070 open in any firewalls between your server and your laptop)
Click on the TM1 instance you want to work with and you will be prompted to login.

ARC can remember your login, so next time you do not need to enter it again.
For a example to work with ARC, we will load a dimension from a text file (using this process https://www.wimgielis.com/tm1_recreatedimensionfromexport_EN.htm)
In the rare occasion when we have in the dimension name the same character as the separator for the list, we need to adjust it a little.

Click on new process, by right click on the process line.

Enter a name:  import.dim and click Create

Under Parameters, click on string to add below two parameters:

Under Data Source tab, do this steps:

Select ASCII as type from drop down list.
Enter the path and file name to import.
Select the comma separator (if that is what you use in the file).
Set Headers records to Zero.
Click on preview.

When above is OK, click on create variables. Change variables names to be below names.

You can here continue with the default setup from https://www.wimgielis.com. But we are going to change to only read one line.

In data source tab, change to use only a not used separator like pipe, to read each line only.
Click on preview and Variables tab change type name to FullLine:

In prolog tab enter this code: (press CTRL+SPACE to get code help in the editor)

In Metadata tab enter this code:

You need to adjust the code to fit your files.

ASCIIOUTPUT place txt files in your \data folder if no path is submitted.
Click on the SAVE icon to save your changes to the TM1 instance.
Click on “lightning” icon to run the TI process for a test.

Enter the values and click on Execute button at the bottom of the dialog window.

Click the refresh icon, and expand the Dimensions list.
Double click on the new accounttest  to see that the values have been imported correct.

You can solve this in many different ways, but the cubewise ARC editor looks nice.

More information:
https://code.cubewise.com/arc-docs/how-to-start-arc

https://code.cubewise.com/arc-docs/setting-up-arc-as-a-windows-service

https://code.cubewise.com/arc-docs/getting-started-with-arc-desktop-for-windows
https://exploringtm1.com/asciioutput-tm1-function-use-syntax
https://exploringtm1.com/scan/
https://exploringtm1.com/subst-tm1-function-use-syntax/

Product:
Planning Analytics 2.0.6
Microsoft Windows 2016 server

Problem:
After change of Certificate for Cognos Analytics 11 dispatcher level. The user can not login in TM1 Architect.  This when you use CAM security (IntegratedSecurityMode=5).
You get error message like: SystemServerClientNotFound

Solution:
When you update the CA11 Websphere (dispatcher) with a custom certificate, you need to add the root and intermediate certificate to the other parts like TM1 servers (planning analytics).

Download the root and intermediate certificate to BASE-64 cer files.
Copy the files to the TM1 server.
Go to a COMMAND prompt as administrator.
Go to folder C:\Program Files\ibm\cognos\tm1_64\bin64
Run a command similar to this:

gsk8capicmd_64 -cert -add -db “D:\Program Files\ibm\cognos\tm1_64\bin64\ssl\ibmtm1.kdb” -stashed -label caRoot -file “C:\temp\rootcert.cer” -format ascii -trust enable

gsk8capicmd_64 -cert -add -db “D:\Program Files\ibm\cognos\tm1_64\bin64\ssl\ibmtm1.kdb” -stashed -label caIntermediate -file “C:\temp\intercert.cer” -format ascii -trust enable

Then you need to restart the TM1 service instances, for the change to take effect.

More Information:
https://www.ibm.com/support/pages/how-configure-planning-analytics-connect-ssl-secured-cognos-dispatcher

https://www.ibm.com/support/pages/use-ikeyman-configure-custom-ssl-certificates-tm1web

Product:
Planning Analytics 2.0.9.3
Microsoft Windows 2019 server

Problem:
How setup SSL (TLS) in Planning Analytics Spreadsheet Services?

Solution:
https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/t_tm1_inst_tm1_web_ssl_existing_keystore.html

Get a custom pfx file from your certification authority for your server.
Go to your PA TM1WEB server and place the file in folder C:\Program Files\ibm\cognos\tm1web\bin64\ssl

Stop the IBM Planning Analytics Spreadsheet Service.
Open C:\Program Files\ibm\cognos\tm1web\wlp\usr\servers\tm1web\server.xml.new in notepad++

Update this row to set your https port
<httpEndpoint id=”defaultHttpEndpoint” httpPort=”-1″ httpsPort=”9510″ host=”*” removeServerHeader=”true”>
</httpEndpoint>
Add this row to point out the certificate pfx file to use
<keyStore id=”defaultKeyStore” location=”${wlp.user.dir}/../../bin64/ssl/cert.pfx” password=”cognos” />
Change cognos to your password.
Save the file as server.xml
In a command prompt go to folder C:\Program Files\ibm\cognos\tm1web\jre\bin
Enter this command to import the standard TM1 server cert to the new keystore
keytool -importcert -keystore ..\..\bin64\ssl\cert.pfx -storepass cognos -storetype pkcs12 -noprompt -alias ibmtm1 -file ..\..\bin64\ssl\ibmtm1.arm

Start IBM Planning Analytics Spreadsheet Services

Update the C:\Program Files\ibm\cognos\analytics\webcontent\bi\tm1\web\tm1web.html file on your Cognos Analytics server to have the new HTTPS value:

Save the file.
If you miss above step you get the error:

The TM1Web service parameter was not specified or is not one of the configured locations

Test from Chrome web browser by go to https://yourservername.domain.com:9510/tm1web

If it works, you have done a good job.

If you use the self sign test certificate you get below screen, as the certificate is not trusted by the browser. Self signed certificate works best with TM1 native security.


Do this https://www.ibm.com/support/pages/node/879929 to get away from above error in testing.
To encrypt the password in the server.xml file do this steps:

Ensure that the cert.pfx file is in folder C:\Program Files\ibm\cognos\tm1web\bin64\ssl

Open C:\Program Files\ibm\cognos\tm1web\wlp\usr\servers\tm1web\bootstrap.properties in Notepad++
Add the line (with your own key password):
wlp.password.encryption.key=VeryStrongandSecurePasswordKey
Start a command prompt as administrator.

Run set JAVA_HOME=C:\Program Files\ibm\cognos\tm1web\jre\ to temporary set the JAVA_HOME for next command
Move to folder C:\Program Files\ibm\cognos\tm1web\wlp\bin
Run command (to encrypt the value in key-store)
securityUtility.bat encode –encoding=aes –key=VeryStrongandSecurePasswordKey cognos
(you add the -key password you defined in bootstrap file, and then the password used today to access the cert.pfx file)

Copy the response to notepad
Open C:\Program Files\ibm\cognos\tm1web\wlp\usr\servers\tm1web\server.xml in notepad++
Update the line (to include the new password)
<keyStore id=”defaultKeyStore” location=”${wlp.user.dir}/../../bin64/ssl/cert.pfx” password=”{aes}AIm6d2W+Hk0JBXaWVrJSvq+AGyBDkec/kdUiXAu5nKoI” />

Save the file and restart Planning Analytics Spreadsheet Services.

Now the password to the keystore (pfx) is not in cleartext in the server.xml file.

You can check for errors in file C:\Program Files\ibm\cognos\tm1web\wlp\usr\servers\tm1web\logs\console.log

Launching tm1web (WebSphere Application Server 20.0.0.7/wlp-1.0.42.cl200720200625-0300) on IBM J9 VM, version 8.0.6.15 – pwa6480sr6fp15-20200724_01(SR6 FP15) (sv_SE)
[AUDIT ] CWWKE0001I: The server tm1web has been launched.
[err] log4j:WARN No appenders could be found for logger (org.apache.axis.transport.http.AxisServlet).
[err] log4j:WARN Please initialize the log4j system properly.
[err] log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.

How to create a keystore for testing:
One your laptop install openssl from here https://github.com/git-for-windows/git/releases/tag/v2.23.0.windows.1 – get the file Git-2.23.0-64-bit.exe. Run the installation with all default values.

Create a new folder (c:\workarea)

Create text file with above content, replace with your servername and location.
Save the file in c:\workarea folder.
Start a command prompt as administrator. Go to folder C:\Program Files\Git\mingw64\bin
Enter to create the self signed certificate:
openssl.exe req -x509 -nodes -days 1095 -newkey rsa:2048 -keyout C:\workarea\cert.pem -out C:\workarea\cert.pem -config C:\workarea\san.txt

Enter to create the pfx file:
openssl.exe pkcs12 -export -out C:\workarea\cert.pfx -in C:\workarea\cert.pem -name “win2019pa” -passout pass:cognos

Replace win2019pa with your servername, and cognos with your password of choice.

Copy the cert.pfx file to your PA server and place in folder C:\Program Files\ibm\cognos\tm1web\bin64\ssl, then do the rest at top of this page.

-x509

This option outputs a self signed certificate instead of a certificate request.

 

More information:
https://www.ibm.com/support/pages/how-configure-ssl-ibm-planning-analytics-spreadsheet-services-using-existing-keystore

https://www.ibm.com/support/pages/how-disable-port-9080-planning-analytics
https://www.ibm.com/support/pages/planning-analytics-ssl-configuration-tm1web-or-any-web-tier-components-does-not-work-expected

https://blog.devolutions.net/2020/07/tutorial-how-to-generate-secure-self-signed-server-and-client-certificates-with-openssl

https://www.phildev.net/ssl/opensslconf.html

https://geekflare.com/san-ssl-certificate/

https://certificatetools.com/

TM1s.cfg & How to Create a TM1 Model – A Best Practice Guide

Product:
Planning Analytics 2.0.9
Planning Analytics Workspace 55
Microsoft Windows 2016 server

Problem:
What to add cube security for new cognos groups from file.
You have created some cognos groups – GroupA and GroupB and filled them with Active Directory users.

You have added the group into TM1 Architect, to see that they are visible. This is done by right click on the Tm1 application and select security – clients/groups.

You have tested to add manually in TM1 architect, the values in the security cube.

Solution:

This can be solved in many ways, this is one example.
You have a text file with the groups and the new values. Here you add the other groups and there values you want to be setup.

Columns are cube to update, cube, cognos group, access rights.

Go to PAW. Login to your TM1 Instance. Go to Processes and right click and select Create Process.

Enter a name, in our example ImportSecurityTI.

Click Create.

Click on file.

Drag you text file to the drop area, to load the file into the system.
This will copy the file to a folder under your data folder.


Click Next.  Select the delimiter you have in your file. Here we use comma.
Click preview.

Here we have a simple file, all columns are strings and we keep the default variables values of V1 to V4.
Click validate and save. Click on script.

Now enter code similar to this to make it populate the cube;

#Section Prolog
#****Begin: Generated Statements***
#****End: Generated Statements****

#——————————————————–
# setup the file to import
#——————————————————–

DataSourceType=’CHARACTERDELIMITED’;
# ASCII for comma is 44   http://www.asciitable.com/
DatasourceASCIIDelimiter=Char(44);
DatasourceASCIIHeaderRecords=0;
# ASCII for quates is 34
DatasourceASCIIQuoteCharacter=Char(34);

# place the file in below folder and paw will find the file
DatasourceNameForServer=’model_upload\CubeSecurity3.txt’;
# full path to the file and name – this is for Tm1 architect to find the file
DatasourceNameForClient=’C:\Program Files\ibm\cognos\tm1_64\samples\tm1\24Retail_CAM\data\model_upload\CubeSecurity3.txt’;

# set default values
sNAMESPACE= ‘CAMID(“:’;
sEND = ‘”)’;
sCUBE= ‘}CubeSecurity’;

#Section Metadata
#****Begin: Generated Statements***
#****End: Generated Statements****

#——————————————————–
# remove the ### for the debug lines to write variables to text file
#——————————————————–

### ASCIIOutput (‘c:\temp\debugout1.txt’, v1, v2, v3, v4 );
# check if string contain : (colon)
# SCAN(find , in string)
nSTART= scan ( ‘:’,v1);
if (nSTART <> 0);
# remove all before
# SUBST(string, beginning, length)
v1 = subst (v1, nSTART +1, (LONG( v1) – nSTART));
endif;

# add CAMID to the group (column 3)
# check that it does not already have : (colon)
nSTART= scan ( ‘:’,v3);
if (nSTART = 0);
# add value before to look like this “CAMID(“:GroupA”)”
# SUBST(string, beginning, length)
v3 = sNAMESPACE | v3 | sEND;
endif;

### ASCIIOutput (‘c:\temp\debugout2.txt’, v1, v2, v3, v4 );
# write values to the cube
# CellPutS (String, Cube, element1, element2, elementn )
CellPutS(v4,v1,v2,v3);

#Section Data
#****Begin: Generated Statements***
#****End: Generated Statements****

#Section Epilog
#****Begin: Generated Statements***
#****End: Generated Statements****

Click on validate – save – run buttons.

If all apostrophes are correct it should work fine.

More information:

nSTART= scan ( ‘:’,v1);
if (nSTART <> 0);

This will find the position in variable v1 where there are a colon. If there is none, then the value in nSTART is zero. At if we test that if not zero then do next line.

https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_ref.2.0.0.doc/r_tm1_ref_scan.html

v1 = subst (v1, nSTART +1, (LONG( v1) – nSTART));

Here we replace variable v1 with a part of its content, we take one character to the right from the nSTART position and until end of string ( length of sting minus the start position).

v3 = sNAMESPACE | v3 | sEND;

The pipe character is to add strings together in TI processes. We add the predefined variables sNAMESPACE and sEND around the variable v3, to get it to look correct.

Concatenating Data in TM1 – How to Concatenate Variables in a TI or Rule

https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_ref.2.0.0.doc/c_miscellaneousturbointegratorfunctions_n72f55.html

If you do not add the groups in security dialog before you run the script you get this error:

Process completed with errors
“24Retail_CAM:}CubeSecurity”,”Capital”,”GroupA”,”WRITE”,Data Source line (1) Error: MetaData procedure line (26): Invalid key: Dimension Name: “}Groups”, Element Name (Key): “CAMID(“:GroupA”)”