by ·Comments Off on Install TM1 version 3.1.7

Product:

Planning Analytics (TM1 server) version 3.1.7 Technical Preview
Microsoft Windows 2022 server

Issue:

How install TM1 v 12?

https://www.ibm.com/support/pages/node/7280180

Solution:

Download the media file from fix central  ( tm1-v12.5.9-standalone-windows.zip)
Copy the file to the Windows 2022 server and unzip it in a folder.

The server need to have contact with internet, as the installations program above, will download other software parts from IBM.

Select the exe file, Right click and run as administrator.

Press Next

Press I Agree

Press Next

Press Next

Select the folder for the TM1 instance, maybe something else than the user folder ( for example c:\ibm\tm1), then press Install

Wait

During install MONGODB is downloaded and installed.

Press Next

Press OK

Copy the values to notepad, and press Next

Press Finish

The files you may need to change in the configuration is in this folder or C:\ibm\tm1

You have to search the IBM forum for more information about version 3.1

 

 

The TM1 service will contain all the other TM1 applications you create on this server.

You can from PAW 3.1 connect to this server with the information you saved in notepad.

In PAW create a new environment, and connect to this TM1 server. Then create a new database.

If you upload a csv file from inside PAW, then the file will be in a folder similar to this C:\ibm\tm1\databases\tm1-i-d9ob6uqqt3r0no1b4k4g-d-daum6j2qt3r1cc35agv0\files\ on the TM1 server.

 

More Information:

https://www.ibm.com/docs/en/planning-analytics/3.1.0?topic=12-configuring 

https://code.cubewise.com/software/products/atmosphere/atmosphere-integrations/

https://succeedium.com/space/

https://community.ibm.com/community/user/blogs/jessica-nicholls/2025/02/03/managing-paaas-with-apis 

Details about new features in Planning Analytics 3.1 can be found here:

Installation instructions for Planning Analytics Local 3.1 components can be found here:

by ·Comments Off on Install PAW 3.1.7 or later

Product:

Planning Analytics Workspace 3.1.7 Technical Preview

Microsoft Windows 2022 server

Issue:

How install PAW on Windows?

How create a new TM1 server in version 3.1.7?

 

Solution:

Download the media, ipa_workspace_win_2022_3.1.7.103.zip,  from IBM website:

https://www.ibm.com/support/pages/ibm-planning-analytics-local-technical-preview-317-available-download-fix-central 

If you run on a virtual machine, ensure you have support for PAE/NX and Nested VT-x/AMD-v. You may need at least 8 fast cpu core and fast SSD hard drives for the PAW server.

First install Mirantis Docker runtime and reboot server. https://www.mirantis.com/download/mirantis-cloud-native-platform/mirantis-container-runtime-trial/ 

To install Mirantis on air-gapped server , after you downloaded all files, enter: .\install.ps1 -Offline

To check version of docker, enter Docker version

Unzip the media to a local folder on the server ( c:\ibm\paw317 ).

Run the Start.ps1 script to install of Planning Analytics Workspace 3.1.7 (installation of PAW 3.1.7 is similar to previous PAW installations)

The install will check the server before it start the installation of PAW.

You will be asked if it should install to other drive – recommendation is to use D: drive or other than system drive.

The install starts automatic after the check.

When installation is done, you can copy the paw.ps1.sample to paw.ps1 file and edit it, in notepad++,

Ensure you have on a other computer with a old TM1 11 (planning analytics 2.1.9) with Native security active (IntegratedSecurityMode=1).  Note down the port used by your native application (planning sample HTTPPortNumber=12354). You need use this in the PAW.PS1 file.

Remove the section for Authenticate with OIDC. And update below two lines only:

## b) Authenticate using a TM1 server
## 
$env:PAAuthMode="tm1"

# URL of the TM1 server to be used for PA Workspace authentication
$env:TM1LoginServerURL="https://192.168.1.106:12354"

Save the file.  Ensure firewalls are open for port 12354 to make it possible for PAW server to talk to old TM1 server.

After the installation you may need to reboot server or manually go and run \scripts\paw.ps1 without parameters to create and start PAW.

(When installation is finish you should be able to login to PAW with the user of a old TM1 v11 as authenticator)

Login with Admin and apple.

Have a new TM1 v12 installed on other server – during that installation you get to note down Tm1 url and client ID and Client Secret. This is needed when you connect your new PAW 3.1 to a TM1 v12 ( PAL 3.1) database.

In PAW go to Administration and Environments.

Click on ADD button and enter the values you got from the installation. Give the environment a name (TEST).

Click save.  Select the new TEST environment in top right corner.

New TM1 create all TM1 database inside the existing TM1 service. Go to Database in Administration and click Create button.

Name the database (PLANNING) and click create.  Now the new database is created.

This TM1 database have on the v12 server access to some folders under the TM1 service.

Inside PAW workspace things are the same, depending on version of PAW 3.1.11 you have different new functions.

 

https://www.ibm.com/docs/en/planning-analytics/3.1.0?topic=offerings-planning-analytics-local-technical-preview 

https://www.ibm.com/support/pages/node/7287976

 

You have to search the IBM forum to get more information about PAW 3.1.

 

To connect PAW to TM1 version 12 database that use OIDC, you need to change paw.ps1 in folder C:\ibm\paw317\config

 

If you are using self-signed SSL certificates for TM1 servers or Cognos Analytics, you may need to add certificates
to the list of CA certificates used by Planning Analytics Workspace.

Place files containing certificate authority certificates in the config/certs directory.

Run the ‘scripts/process_certs.ps1’ script to add the CA certificates to the ‘cacerts’ file.

After the ‘cacerts’ file is successfully updated, restart the Planning Analytics Workspace services.

 

More Information:

https://community.ibm.com/community/user/blogs/stuart-king1/2026/01/26/planning-analytics-local-31-technical-preview

https://community.ibm.com/community/user/discussion/planning-analytics-3x-on-prem 

https://docs.mirantis.com/mcr/20.10/install/mcr-windows.html

https://exploringtm1.com/how-to-install-mirantis-container-runtime-docker-on-windows-server-2019/

 

Details about new features in Planning Analytics 3.1 can be found here:

Installation instructions for Planning Analytics Local 3.1 components can be found here:

by ·Comments Off on How sum a big csv file

Product:
DuckDB v1.5.5 (Variegata)

Issue:

How sum a big csv file?  Excel is to small.

Solution:

Download DUCKDB, that is an embedded column-oriented database purpose-built for analytical workloads (OLAP).
DuckDB load your data in memory on your laptop, and then let you use SQL querys to find out information.
DuckDB’s SQL parser is derived from the pg_query library (https://www.sqlite.org/cli.html) , which is itself derived from PostgreSQL’s SQL parser that has been stripped down as much as possible.

On windows download the duckdb zip file, and unzip it to a folder like c:\duckdb. Go to that folder with powershell and enter DUCKDB to start the program.  https://duckdb.org/install/?platform=windows&environment=cli

 .\duckdb.exe

To import a csv file, that use European characters (note it use Linux syntax, forward slash for folders) , use a command like this:

create table budget as select * from read_csv_auto('c:/temp/db/datatabell.csv' , encoding='Latin-1') ;

This will create a in-memory databas called budget, that you can query, like to list number of rows:

select count(*) from budget ;

To get a sum for a month (note that it differs for small and capital letters  Month and month are two different columns):

select sum(amount) from budget where Månad='01' and År=2027;

Enter below to get a quick glance of what column names exist in the database table:

select * from budget ;

Then you can see the name and if it is int64 or varchar.   To find rows with zero as value, enter:

select * from budget where amount = 0;

 

To stop duckdb enter  .exit.  When you  exit duckdb the database is erased.

 

More Information:

DuckDB’s architecture delivers several distinct advantages over traditional databases and data analysis tools:

  • Vectorized query execution — Processes data in batches of 2048 rows, maximizing CPU cache efficiency and enabling SIMD acceleration
  • Columnar storage — Only reads the columns your query requests, dramatically reducing I/O compared to row-based systems
  • Direct file querying — Query CSV, Parquet, and JSON files directly without importing them first — a massive productivity boost
  • Hybrid execution — Supports both in-memory and on-disk processing, automatically spilling to disk when datasets exceed available RAM
  • Full SQL standard compliance — Supports complex queries including window functions, CTEs, PIVOT, and UNION that go far beyond what Excel or Pandas can do
  • Multi-language support — First-class bindings for Python, R, Java, Node.js, C++, Rust, and many more
  • Extension ecosystem — Load extensions for HTTP/S3 access, full-text search, spatial data, and more

How DuckDB Compares to Alternatives

Aspect DuckDB SQLite Pandas PostgreSQL
Storage model Columnar Row-based In-memory Row-based
Analytics perf ⚡ Fast Slow Fast (fits RAM) Moderate
Setup time Seconds Seconds Minutes (env) Hours (server)
Max dataset >RAM (spills) <RAM <RAM >RAM
SQL features Full Basic No native SQL Full

 

https://duckdblab.org/en/post/duckdb-beginners-guide-2026/ 

https://duckdb.org/docs/lts/data/csv/overview

https://duckdb.org/docs/current/guides/file_formats/csv_import

https://duckdb.org/docs/current/sql/introduction

https://motherduck.com/blog/duckdb-tutorial-for-beginners/

https://www.sqlite.org/cli.html

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

https://github.com/davidgasquez/awesome-duckdb 

https://duckdblab.org/en/cheatsheet/

https://duckdb.org/docs/current/sql/dialect/friendly_sql

Query Features

by ·Comments Off on [NVARCHAR](MAX)

Product:

Microsoft Azure SQL

Issue:

Store procedures / process take a long time to finish – than a few weeks ago to a lot less of time.
The usage of resources in the SQL server is flat in the monitor view.

Solution:

Check that you do not have any columns that use [NVARCHAR](MAX) in a stage table or other table. This can when data grow in your tables, make the SQL CPU /Memory resources – be to small for it to handle.

Check also if you can make a CREATE NONCLUSTERED INDEX on you largest table that are in use in the query, to help improve performance.

As a very short term solution, you can increase cpu and memory on your Azure SQL instance, but this is not a good solution.

When you combine an nvarchar(max) with an nvarchar(100), SQL Server often implicitly promotes the result to nvarchar(max). Once an expression becomes a LOB (Large Object) type, SQL Server may switch to much more expensive processing. Nvarchar(max) is treated differently than normal string types. nvarchar(100) is stored directly in-row.
nvarchar(max) may be stored off-row. “Stored off-row” means SQL Server may not keep the actual string data inside the data page that contains the rest of the row. A SQL Server data page is only 8 KB. Then SQL Server must maintain LOB pointers and perform additional I/O.

This increases CPU and memory usage. When SQL Server sees nvarchar(max), it must assume values could be huge (up to 2 GB).

Check whether SQL Server is using off-row storage

SELECT
OBJECT_NAME(object_id),
lob_used_page_count,
used_page_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('dbo.Test');

 

If lob_used_page_count is non-zero, SQL Server is storing LOB (nvarchar(max), varchar(max), xml, etc.) data on separate pages.

 

To see what SQL are doing, enter:

sp_who2

More Information:

https://www.brentozar.com/archive/2016/10/max-data-types/

https://www.brentozar.com/archive/2025/10/which-should-you-use-varchar-or-nvarchar/

https://www.brentozar.com/free-sql-server-training-videos/ 

https://vladdba.com/2024/05/26/the-curious-case-of-the-truncating-nvarcharmax-variable/

https://www.conradakunga.com/blog/how-to-index-a-nvarcharmax-table-in-sql-server/ 

https://learn.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver17 

https://erikdarling.com/using-sp_tableoption-to-store-lob-data-off-row/ 

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-memory-issues

by ·Comments Off on SQL sys.dm_exec_requests

Product:

Microsoft AZURE SQL database

Issue:

It is running a long time, what is the SQL doing?

Solution:

In SSMS, run below query’s to get some information:

 

SELECT
r.session_id,
r.status,
r.wait_type,
r.wait_time,
r.blocking_session_id,
r.cpu_time,
r.total_elapsed_time,
DB_NAME(r.database_id) AS database_name,
t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
ORDER BY r.total_elapsed_time DESC;

 

 

SELECT
r.session_id,
s.login_name,
s.host_name,
r.status,
r.command,
r.cpu_time,
r.total_elapsed_time,
r.logical_reads,
r.reads,
r.writes,
DB_NAME(r.database_id) AS database_name,
t.text AS sql_text
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID
ORDER BY r.cpu_time DESC;

 

 

The key columns are:

  • wait_type → what resource it is waiting for.
  • blocking_session_id → another session blocking it.
  • wait_resource → the specific resource.

 

More Information:

https://www.mssqltips.com/sqlservertip/5521/understanding-and-using-sql-server-sysdmexecrequests/ 

https://dev.to/shiviyer/mastering-performance-troubleshooting-with-sysdmexecrequests-in-azure-sql-3f0j 

 

by ·Comments Off on Select two elements to a view

Product:
Planning Analytics 2.1.19 workspace

Issue:

Select two elements to a view?

Solution:

In PAW create a new book and from Planning Sample instance create a view of plan_budgetPlan cube.

 

Hold CTRL down and click on two elements. Then right-click and from menu select keep.

If you want to select a dimension element by level, you can open set editor, by click on the tree dots.

Click on filter icon, and select level and the level you want.

Click Done, and Apply and the view have the selected elements shown.

 

 

More Information:

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

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=elements-selecting-adjacent

https://quebit.com/askquebit/how-to-use-one-title-dimension-selection-to-control-another-dimensions-subset-in-a-paw-cube-view/ 

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=views-visualizations-in-planning-analytics-workspace 

by ·Comments Off on \bin64\tm1sd.exe -install -n servicename -z “path_to_directory_containing_new_tm1s.cfg”

Product:
Planning Analytics 2.1.19
Microsoft Windows 2022 server

Issue:

How install planning sample?

Solution:

Copy the plansamp folder to a new folder like d:\tm1 data\utv\plansamp

Create a \config folder where you place the tm1s.cfg file

Also create a \logs and \data folder. Copy all files from plansamp folder to data folder.

Adjust the Tm1s.cfg to have correct path to folders:

DataBaseDirectory=D:\TM1 Data\Utv\plansamp\data\
LoggingDirectory=D:\TM1 Data\Utv\plansamp\Logs\

Create a ps1 file in notepad with below text:

cd "D:\Program Files\ibm\tm1_64\bin64\"
.\tm1sd.exe -install -n "Planning Sample" -z "D:\Tm1 Data\UTV\PlanSamp\config"

Save the file.

Start powershell as administrator and run the above script

The tm1 samples are not installed automatic in new versions.

Start the service from windows services.

Edit the tm1s.cfg to have the authentication you need. Example  IntegratedSecurityMode=5

 

More Information:

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=offerings-planning-analytics-local

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=later-registering-running-tm1-server-as-windows-service

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=configuration-tm1scfg-file

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=parameters-integratedsecuritymode

by ·Comments Off on Faulting module name: jvm.dll_unloaded

Product:

Cognos Controller 11.1.1003

Issue:

When you inside the Controller client program select a java menu, like groups – command center or maintain – jobs -define. The program exist without a error message.

If you check your Windows event log, you find this error:

Faulting application name: CCR.exe, version: 11.1.1003.12, time stamp: 0x67e3a0f4
Faulting module name: jvm.dll_unloaded, version: 8.0.0.0, time stamp: 0x66856ee4
Exception code: 0xc0000005
Fault offset: 0x0000000000008a90
Faulting process id: 0x3f3c
Faulting application start time: 0x01dd28ba25cff042
Faulting application path: D:\Program Files\ibm\IBM Controller Local Client\CCR.exe
Faulting module path: jvm.dll

..

Application: CCR.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: System.AccessViolationException

Solution:

A update of EDGE on the computer, have change the way WebView2 works. This gives a conflict with Controller java parts.
Create a text file in notepad with below text:

# Avoid contending with Chromium for low address space
-Xmx1024m
-Xnocompressedrefs
-verbose:sizes

 

Save the file as jvm.options

Place the file in folder C:\Program Files\IBM\IBM Controller Local Client\Integration\ on your laptop.

Start Cognos Controller client and test again.

 

More information:

Performance impact in Threads/Timers initialization while using -Xcompressedrefs

Java Memory Settings – JVM Heap Size – Code Curmudgeon

Verbose Garbage Collection in Java | Baeldung

Heap allocation – IBM Documentation

Critical Java JVM options and parameters

by ·Comments Off on TM1 predict the future

Product:

Planning Analytics 2.1.19

Issue:

How to get a Forecast for a cube view?

Solution:

To make a forecast happen, you need to have a few tings setup in your TM1 model. This is a list of suggestions that may be needed.

The cube you are doing the forecast must have a TIME dimension.  This are in TM1 Architect set on the cube properties.

The dimension for time, need to be in a format that TM1 understands.

Like Only years, or Year – quarters – months hierarchy.

Forecasting requires the time dimension to be placed on the column.

You must have data in all the periods, if you only have data in Quarters, but the dimension contain blank months, the predict may not work. You can get a message like:

Too many missing or zero values in the historical data.

The historical data must represent at least double what is being forecasted. If you forecast 2 years, you need 4 years of old data.

The view need empty periods where the prediction can be created. If you want to look 5 years forward, your TIME dimension need to contain empty elements for 5 years.

To begin forecasting, click an exploration view in a book, then click Forecast and click either Univariate or Multivariate, depending on which type of forecast you want to generate. A univariate forecast predicts the future values of a single variable based on historical data, using the Holt-Winters model.

Use the Set up tab to define the start date and end date for your forecast.

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=options-set-up-forecast-tab

You can preview only one time series at a time. The forecasting preview shows what the results for the forecast look like, based on your input.

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

 

If you do not have a }DimensionAttributes cube in your TM1 model, you need to create one from PAW.

Right click on the Dimension and select Dimension Attributes.

Enter the attribute name: DIMENSION_TYPE

Click Add.

Then in the view of the DimensionAttributes cube, find the row for your year dimension and enter TIME as DIMENSION_TYPE.

This is the TIME dimension that can be used by the forecast.

https://revelwood.com/ibm-planning-analytics-tips-tricks-planning-analytics-forecasting-configuring-the-time-dimension/ 

 

You can also use PAW to create a TIME dimension for you – that can be on monthly level.

The TIME dim created by PAW will look like this,  you may use it in your new models.

 

 

Above is only suggestions, depending on the version of PAW this can behave or look different.

More Information:

https://revelwood.com/use-the-planning-analytics-ai-assistant-for-smarter-data-analysis/ 

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=dimensions-time-in-planning-analytics-workspace 

  1. Right-click the Dimensions node in your database on the Databases tree, then click Create time dimension.

    Alternatively, you can create a time dimension by clicking the Configure time dimension option when you create a new dimension. The Configure time dimension option is only available for a dimension with one hierarchy and no members.

  2. If required, enter a name for the dimension.
  3. Select the Start year for the dimension.
  4. Select the End year for the dimension.
  5. Answer the How frequently do you plan? question by selecting the granularity of the dimension.
    Months
    This option gives you three levels: Months, Quarters, Years.
    Quarters
    This option gives you two levels: Quarters, Years.
    Years
    This option gives you one level: Years.
  6. Check the preview to confirm that it matches your expectation, then click Save.
    You can modify the time dimension in the dimension editor after you create it.

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

Forecasting requires the time dimension to be placed on the column. The algorithm requires the time dimension members to be organized in order of increasing time, where each member of a level represents a consistent unit of time. Members within the dimension definition that do not conform to this requirement, for example calculated year-to-date member, can be hidden. Relevant time periods can be chosen to be ignored, resulting in a linear interpolation of values to be predicted from. Forecasting also supports hierarchical time dimensions and nested dimensions as time: year, quarter, and month dimensions.

One consequence of this is that leaves with multiple parents along the time series would result in non-contiguous time, so special care must be taken in these cases. If possible, it is advised to consider adding a special hierarchy for time series forecasting to support the required time configuration.

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

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=forecasting-error-messages 

Something went wrong while generating the forecast for one or more series
This is a general message for any unsuccessful prediction. Please try again.

IBM Planning Analytics Tips & Tricks: Planning Analytics Forecasting – Configuring the Forecast Settings

 

IBM Planning Analytics Tips & Tricks: Planning Analytics Forecasting – Previewing the Forecast

 

IBM Planning Analytics Tips & Tricks: Planning Analytics Forecasting – Setting up the Forecast

 

by ·Comments Off on How generate a valid certificate

Product: 
TM1 10

Issue:

TM1 SSL expire again in June 2026.

Solution:

Follow IBM recommendations.

 

TM1 SSL Certificate Generation – ApplixCA – Instructions

This document provides a script and/or step-by-step manual instructions for generating and deploying new applixca TM1 SSL certificates.  The script can be used to automate the generation of new certificates.  The manual steps are available to help with an understanding of the steps that the script has automated.

Prerequisites

1. Download and Install OpenSSL

Download Win64 OpenSSL v4.0.1 from:
https://slproweb.com/products/Win32OpenSSL.html

Install to the default location: C:\Program Files\OpenSSL-Win64

The OpenSSL executable will be located at: C:\Program Files\OpenSSL-Win64\bin\openssl.exe

2. Other Requirements

  • Administrative access to TM1 installation directories
  • TM1 services stopped before beginning

Important Notes

  • Password for CA key: applix
  • Password for Java cacerts: changeit
  • Password for tm1store: applix
  • All commands should be run from a Command Prompt with administrative privileges

Automated Script:

For automated execution of these steps, use the included update-applixca-certs.bat script. The script performs all the above steps. To run the automated script:

  1. Download the update-applixca-certs.bat file here
  2. Place the update-applixca-certs.bat file in C:\Program Files\OpenSSL-Win64\bin\
  3. Open Command Prompt as Administrator
  4. Navigate to the C:\Program Files\OpenSSL-Win64\bin\ directory
  5. Run: update-applixca-certs.bat
  6. Follow the prompts to specify your TM1 SSL directory path, default C:\Program Files\ibm\cognos\tm1_64\bin64\ssl
  7. After certificates have been generated, the updated ssl folder will need to be provided to any users of Architect / Perspectives
  8. If using distributed installs, the updated ssl folders will need to be copied to additional installation directories
    1. Ensure all ssl folders are updated, including tm1store and cacerts files

Manual Instructions (what the automated script does):

Step 1: Set Environment Variables and Working Directory

  1. Open Command Prompt as Administrator
  2. Set the TM1_ROOT environment variable (replace with your actual TM1 installation path): 
    set "TM1_ROOT=C:\Program Files\ibm\cognos\tm1_64"
  3. Verify tm1sd.exe exists in the bin64 directory: 
    dir "%TM1_ROOT%\bin64\tm1sd.exe"
  4. Navigate to the OpenSSL bin directory (this will be your working directory): 
    cd "C:\Program Files\OpenSSL-Win64\bin"

Note: The TM1_ROOT environment variable will be used in all subsequent commands. From this root path, the following paths are derived:

  • bin64 directory: %TM1_ROOT%\bin64
  • bin64 SSL: %TM1_ROOT%\bin64\ssl
  • bin SSL: %TM1_ROOT%\bin\ssl
  • Webapps SSL: %TM1_ROOT%\webapps\pmpsvc\WEB-INF\bin64\ssl
  • Java keytool: %TM1_ROOT%\bin64\jre\7.0\bin\keytool.exe
  • Java cacerts: %TM1_ROOT%\bin64\jre\7.0\lib\security\cacerts

Step 2: Backup Existing SSL Directories

Create backups of all SSL directories before making changes:

xcopy "%TM1_ROOT%\bin64\ssl" "%TM1_ROOT%\bin64\ssl_bkp\" /E /I /H /Y
xcopy "%TM1_ROOT%\bin\ssl" "%TM1_ROOT%\bin\ssl_bkp\" /E /I /H /Y
xcopy "%TM1_ROOT%\webapps\pmpsvc\WEB-INF\bin64\ssl" "%TM1_ROOT%\webapps\pmpsvc\WEB-INF\bin64\ssl_bkp\" /E /I /H /Y

Step 3: Delete Old Certificate Files

Delete the following files from the bin64\ssl directory:

del /q "%TM1_ROOT%\bin64\ssl\tm1admsvrcert.pem"
del /q "%TM1_ROOT%\bin64\ssl\tm1svrcert.pem"
del /q "%TM1_ROOT%\bin64\ssl\applixca.pem"
del /q "%TM1_ROOT%\bin64\ssl\applixca.der"
del /q "%TM1_ROOT%\bin64\ssl\applixca.crl"

Step 4: Create Certificate Authority (CA) Directory Structure

Create the CA structure in the OpenSSL bin directory (you should already be here from Step 1):

mkdir applixCA
mkdir applixCA\private
mkdir applixCA\newcerts
echo. > applixCA\index.txt
echo 01 > applixCA\serial

Step 5: Generate CA Private Key

Generate the CA private key (password: applix). Since you’re in the OpenSSL bin directory, use openssl.exe directly:

openssl.exe genrsa -des3 -out applixCA\private\applixcakey.pem -passout pass:applix 2048

Step 6: Generate CA Certificate

Create the CA certificate (password: applix):

openssl.exe req -new -x509 -days 3650 -key applixCA\private\applixcakey.pem -out applixCA\applixca.pem -subj "/C=US/ST=State/L=City/O=Organization/OU=Unit/CN=TM1 CA" -passin pass:applix

Step 7: Convert CA Certificate to DER Format

Convert the PEM certificate to DER format:

openssl.exe x509 -in applixCA\applixca.pem -outform DER -out applixCA\applixca.der

Step 8: Generate TM1 Server Certificate

Create the TM1 server certificate and key (password: applix):

openssl.exe req -new -nodes -out tm1svrcert.csr -keyout tm1svrcert.key -subj "/C=US/ST=State/L=City/O=Organization/OU=Unit/CN=localhost"

openssl.exe x509 -req -in tm1svrcert.csr -CA applixCA\applixca.pem -CAkey applixCA\private\applixcakey.pem -CAcreateserial -out tm1svrcert.crt -days 3650 -passin pass:applix

type tm1svrcert.crt tm1svrcert.key > tm1svrcert.pem

del tm1svrcert.csr
del tm1svrcert.crt
del tm1svrcert.key

Step 9: Generate TM1 Admin Server Certificate

Create the TM1 admin server certificate and key (password: applix):

openssl.exe req -new -nodes -out tm1admsvrcert.csr -keyout tm1admsvrcert.key -subj "/C=US/ST=State/L=City/O=Organization/OU=Unit/CN=localhost"

openssl.exe x509 -req -in tm1admsvrcert.csr -CA applixCA\applixca.pem -CAkey applixCA\private\applixcakey.pem -CAcreateserial -out tm1admsvrcert.crt -days 3650 -passin pass:applix

type tm1admsvrcert.crt tm1admsvrcert.key > tm1admsvrcert.pem

del tm1admsvrcert.csr
del tm1admsvrcert.crt
del tm1admsvrcert.key

Step 10: Create Certificate Revocation List (CRL)

Generate an empty CRL file (password: applix):

openssl.exe ca -gencrl -keyfile applixCA\private\applixcakey.pem -cert applixCA\applixca.pem -out applixCA\applixcacrl.pem -config nul -passin pass:applix

If this fails, create a basic empty CRL:

echo -----BEGIN X509 CRL----- > applixCA\applixcacrl.pem
echo -----END X509 CRL----- >> applixCA\applixcacrl.pem

Step 11: Copy Certificates to TM1 SSL Directories

Copy all generated certificates from the OpenSSL bin directory to the TM1 SSL directories:

To bin64\ssl:

copy /y applixCA\applixca.pem "%TM1_ROOT%\bin64\ssl\applixca.pem"
copy /y applixCA\applixca.der "%TM1_ROOT%\bin64\ssl\applixca.der"
copy /y applixCA\applixcacrl.pem "%TM1_ROOT%\bin64\ssl\applixcacrl.pem"
copy /y tm1svrcert.pem "%TM1_ROOT%\bin64\ssl\tm1svrcert.pem"
copy /y tm1admsvrcert.pem "%TM1_ROOT%\bin64\ssl\tm1admsvrcert.pem"

To bin\ssl (if exists):

copy /y applixCA\applixca.pem "%TM1_ROOT%\bin\ssl\applixca.pem"
copy /y applixCA\applixca.der "%TM1_ROOT%\bin\ssl\applixca.der"
copy /y applixCA\applixcacrl.pem "%TM1_ROOT%\bin\ssl\applixcacrl.pem"
copy /y tm1svrcert.pem "%TM1_ROOT%\bin\ssl\tm1svrcert.pem"
copy /y tm1admsvrcert.pem "%TM1_ROOT%\bin\ssl\tm1admsvrcert.pem"

To webapps SSL:

copy /y applixCA\applixca.pem "%TM1_ROOT%\webapps\pmpsvc\WEB-INF\bin64\ssl\applixca.pem"
copy /y applixCA\applixca.der "%TM1_ROOT%\webapps\pmpsvc\WEB-INF\bin64\ssl\applixca.der"
copy /y applixCA\applixcacrl.pem "%TM1_ROOT%\webapps\pmpsvc\WEB-INF\bin64\ssl\applixcacrl.pem"
copy /y tm1svrcert.pem "%TM1_ROOT%\webapps\pmpsvc\WEB-INF\bin64\ssl\tm1svrcert.pem"
copy /y tm1admsvrcert.pem "%TM1_ROOT%\webapps\pmpsvc\WEB-INF\bin64\ssl\tm1admsvrcert.pem"

Step 12: Update Java cacerts Keystore

Update the Java cacerts keystore with the new CA certificate (password: changeit):

cd "%TM1_ROOT%\bin64\jre\7.0\bin"

REM Remove existing applixca alias (ignore errors if not present)
keytool.exe -delete -alias applixca -keystore "..\lib\security\cacerts" -storepass changeit

REM Import new CA certificate
keytool.exe -import -trustcacerts -noprompt -file "%TM1_ROOT%\bin64\ssl\applixca.pem" -keystore "..\lib\security\cacerts" -storepass changeit -alias applixca

Find and Update All Other cacerts Files

Search for all cacerts files in the TM1 installation (excluding backups):

cd "%TM1_ROOT%"
dir /s /b cacerts | findstr /v /i "_bkp\ _backup\ backup\"

For each cacerts file found (other than the one just updated), copy the updated cacerts:

copy /y "%TM1_ROOT%\bin64\jre\7.0\lib\security\cacerts" "path\to\other\cacerts"

Step 13: Update tm1store Keystore

Update the tm1store keystore with the new CA certificate (password: applix):

cd "%TM1_ROOT%\bin64\jre\7.0\bin"

REM Remove existing applixca alias (ignore errors if not present)
keytool.exe -delete -alias applixca -keystore "%TM1_ROOT%\bin64\ssl\tm1store" -storepass applix

REM Import new CA certificate
keytool.exe -import -trustcacerts -noprompt -file "%TM1_ROOT%\bin64\ssl\applixca.pem" -keystore "%TM1_ROOT%\bin64\ssl\tm1store" -storepass applix -alias applixca

Find and Update All Other tm1store Files

Search for all tm1store files in the TM1 installation (excluding backups):

cd "%TM1_ROOT%"
dir /s /b tm1store | findstr /v /i "_bkp\ _backup\ backup\"

For each tm1store file found (other than the one just updated), copy the updated tm1store:

copy /y "%TM1_ROOT%\bin64\ssl\tm1store" "path\to\other\tm1store"

Step 14: Replace All SSL Folders

Search for all SSL folders in the TM1 installation (excluding backups):

cd "%TM1_ROOT%"
dir /s /b /ad ssl | findstr /v /i "_bkp\ _backup\ backup\"

For each SSL folder found (other than bin64\ssl which is the source):

  1. Create a timestamped backup: 
    xcopy "path\to\ssl\folder" "path\to\ssl\folder_bkp_YYYYMMDD_HHMM\" /E /I /H /Y
  2. Copy updated certificates to the folder: 
    xcopy "%TM1_ROOT%\bin64\ssl\*.*" "path\to\ssl\folder\" /Y

Step 15: Restart TM1 Services

After all certificates are deployed, restart your TM1 services

Verification

After completing all steps:

  1. Verify certificates are present in all SSL directories
  2. Ensure any distributed server or client installs have been updated with the newly generated certificates
  3. Test TM1 connections to ensure SSL is working properly

Troubleshooting

OpenSSL Command Not Found

Permission Denied Errors

  • Run Command Prompt as Administrator
  • Ensure TM1 services are stopped before modifying files

Certificate Import Failures

  • Verify the certificate file exists and is readable
  • Check that passwords are correct
  • Ensure keystore files are not corrupted

Services Won’t Start

  • Check TM1 logs for SSL-related errors
  • Verify certificate files are in the correct locations
  • Ensure certificate and key formats are correct

Enabling SSL Debug Logging

TM1 Admin Server:

  • Edit %TM1_ROOT%\bin64\tm1admsrv-log.properties
  • Update the rootLogger and logger sections from INFO to DEBUG: 
    log4j.rootLogger=DEBUG, R1
log4j.logger.TM1=DEBUG
  • SSL debug logging is written to tm1admsrv_<datetimestamp>.log files in the bin64 directory

TM1 Server:

  • Configure tm1s-log.properties file (located in same directory as tm1s.cfg)
  • Add the following line: 
    log4j.logger.TM1.Comm.SSL=DEBUG
  • If file doesn’t exist, copy from sample directory: 
    copy "%TM1_ROOT%\samples\tm1\PlanSamp\tm1s-log.properties" "path\to\your\tm1server\tm1s-log.properties"
  • No restart required – changes take effect immediately
  • SSL debug logging is written to tm1server.log file

 

More Information:

https://www.ibm.com/support/pages/node/7276835