#REM powershell script
#REM enter first the command then the tm1 instance
#REM Get-Service -ComputerName computername -Name servicename | Start-Service

#REM get the server name from a text file that you prepare before
$computers = Get-Content c:\temp\servers.txt

#REM get the command parameters
$action = $args[0]
$tm1instance = $args[1]

#REM if stop do this else do that
If ($action -eq “stop” )
{Get-Service -Computername $computers -Name $tm1instance | Stop-Service }
ElseIf ($action -eq “start” )
{Get-Service -Computername $computers -Name $tm1instance | Start-Service }

#REM if none of above, then enter a line in windows event log
Else
{
write-eventlog -Computername $computers -logname Application -source EventSystem -eventID 1000 -message “something else happened.”
}

Create a text file with the servername for the windows server that you have tm1 installed on.

Place the text file in c:\temp folder on the computer where you want to run the script.

The account running the script need to be administrator on the target host machine to be able to connect and stop windows services.

Enter command: ./tm1script.ps1 stop “planning sample”

The tm1 instance need to be inside ” when it have a space in the name.

More information:

PowerShell Basics: ElseIf Statement

https://learn-powershell.net/2012/01/15/startingstopping-and-restarting-remote-services-with-powershell/

https://ss64.com/ps/write-eventlog.html

How restart the FAP service from TM1 Architect

March 27, 2021 by Roger·Comments Off

Product:
Planning Analytics 2.0.9
Cognos Controller FAP service 10.4.2
Microsoft Windows 2019 server

Problem:
I want my tm1 developer be able to restart the Cognos Controller FAP service without login to the Windows server.

Suggested Solution:
Create two batch files (cmd) to run to start and stop of the FAP service.
Place the batch files in folder c:\scripts on the server where you have both Tm1 instance and FAP service installed.
Then create TI process to call the batch (cmd) files.

Enter below in notepad and save as STARTFAP.CMD

ECHO OFF
REM program to start fap service
REM https://ss64.com/nt/sc.html
REM print — to file
echo ———— >> “c:\Program Files\ibm\cognos\ccr_64\Server\FAP\fapstart.txt”
REM start the service
SC start “IBM Cognos FAP Service”
REM SC start “sdata”
REM print time to file
echo FAP STARTED >> “c:\Program Files\ibm\cognos\ccr_64\Server\FAP\fapstart.txt”
echo %date% %time% >> “c:\Program Files\ibm\cognos\ccr_64\Server\FAP\fapstart.txt”

Enter below in notepad and save as STOPFAP.CMD

ECHO OFF
REM program to stop fap service
REM https://ss64.com/nt/sc.html
REM print —- to file
echo ——— >> “c:\Program Files\ibm\cognos\ccr_64\Server\FAP\fapstart.txt”
REM stop the service
SC STOP “IBM Cognos FAP Service”
REM SC STOP “sdata”
REM print time to file
echo FAP STOPPED >> “c:\Program Files\ibm\cognos\ccr_64\Server\FAP\fapstart.txt”
echo %date% %time% >> “c:\Program Files\ibm\cognos\ccr_64\Server\FAP\fapstart.txt”

Test the files by run them from a administrator command prompt. Check that the log file fapstart.txt is created.

Set the “IBM Cognos FAP service” to Manual.
Start TM1 architect and create a new TI process, enter below text, and save as FAPSTOP

Then create other TI process, enter below text, and save as FAPSTART

Ensure you use correct ‘ or ” at the difference places.

If you share your FAP folder as FAP$ , on the windows server, to the TM1 developer group, then they can check both the error.log file and your fapstart.txt log file.

More information:
https://www.tm1forum.com/viewtopic.php?t=12599

https://www.toolbox.com/tech/big-data/question/run-an-external-command-from-tm1-052112/

https://www.ibm.com/support/pages/how-perform-fap-reset-sequence-re-synchronise-controller-data-publishing-tm1

https://www.ibm.com/support/pages/safe-method-restart-tm1-servers-ensure-it-does-not-break-controller-fap-publish

TM1WEB, an error has occurred when using TLS

March 17, 2021 by Roger·Comments Off

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.

Each XEON cpu core is often 70 or 100 PVU points

March 8, 2021 by Roger·Comments Off

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

Planning Analytics Workspace is unavailable

February 11, 2021 by Roger·Comments Off

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

Threads blocked: unavailable

February 10, 2021 by Roger·Comments Off

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

Missing private views after change of authentication source

February 5, 2021 by Roger·Comments Off

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

tm1top log

February 1, 2021 by Roger·Comments Off

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
log4j.additivity.TM1.Login=false

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

Cubewise ARC

January 12, 2021 by Roger·Comments Off

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.