Product:
Planning Analytics 2.0.9.19 TM1
Microsoft Windows 2019 Server

Issue:

When user browse to the tm1web they get a error – random in different web-browsers e.g. chrome and edge.

Error msg:

This site can’t provide a secure connection
tm1sebservername.domain.com sent an invalid response.
Try running Windows Network Diagnostics.
ERR_SSL_PROTOCOL_ERROR

Solution:

Check the URL you use – it need to start with HTTP:// if you do not have a certificate on your TM1WEB service.

If you by accident enter;

https://servername.domain.com:9511/tm1web/

you get above error.

You need to enter

http://servername.domain.com:9511/tm1web/

 

More Information:

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=itw-configure-ssl-planning-analytics-tm1-webspreadsheet-services-existing-keystore 

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

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

 

Product:

Planning Analytics 2.0.9.19 Tm1 Perspective

Microsoft® Excel® for Microsoft 365 MSO 64 Bit

Issue:

How add TM1 Perspectiv to always be started with Excel?

Solution:

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:

https://www.ibm.com/docs/en/cognos-tm1/10.2.2?topic=ctcdti-installing-cognos-tm1-perspectives

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=configuration-installing-configuring-planning-analytics-microsoft-excel 

https://exploringtm1.com/how-to-upgrade-install-planning-analytics-for-excel-pax/

Product:
Planning Analytics 2.0.9
Microsoft Windows 2019 server

Issue:

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

Solution:

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');

ENDIF;

# 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:

DIMENSIONELEMENTINSERT( pDim ,'',V1,'c');
DIMENSIONELEMENTINSERT( pDim ,'',V2,'c');
DIMENSIONELEMENTINSERT( pDim ,'',V3,'c');
DIMENSIONELEMENTCOMPONENTADD( pDim ,V1,V2,1.000000);
DIMENSIONELEMENTCOMPONENTADD( pDim ,V2,V3,1.000000);
DIMENSIONELEMENTCOMPONENTADD( pDim ,V3,V4,1.000000);

 

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

More Information:

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

https://quebit.com/askquebit/using-turbo-integrator-to-build-dimension-hierarchy-in-planning-analytics/ 

https://vrgultom.wordpress.com/2013/08/13/updating-attribute/ 

https://intellipaat.com/blog/tutorial/cognos-tm1/dimensions-and-cubes/ 

https://www.bihints.com/dimensions_updates_mapping 

Catalogue your dimension creation processes in Cognos TM1

https://tm1up.com/3-dimensions-in-tm1.html 

 

Product:
Cognos Controller 11.0.1

Issue:

Do you have a checklist of what is needed to do a upgrade to Cognos controller 11?

Solution:

Best is if you create a own checklist for your environment, that you can use at every upgrade. Below is a suggested starting point, that you can develop further.

Download from here: 

Downloading IBM Cognos Controller 11.0.1 

 

Parts and platforms Details eImage
IBM Cognos Controller 11.0.1 Microsoft Windows Multilingual Required M0DH2ML
IBM Cognos Analytics Client 11.2.4.1.1 Multiplatform Multilingual Optional M0BRXML
IBM Cognos Analytics installer 3.6.46 Microsoft Windows Multilingual Optional M0BRWML
IBM Cognos Analytics Server 11.2.4.1.1 Microsoft Windows Multilingual     Optional M0BRVML

 

You must download the RTM program and then the fix packs.

IBM Cognos Controller 11.0.x Fix List 

IBM Cognos Analytics Fix Lists 

analytics-installer-3.7.21-win.exe (174.73 MB)

caclient-11.2.4-2401220500.zip (1.49 GB)

casrv-11.2.4-2401220500-winx64h.zip (6.98 GB)

Get file up_cntrl_winx64h_11.0.1200.36_ml.tar.gz that is for Cognos Controller 11.0.1 fix pack 2.

Detail upgrade instruction here: 

https://www.youtube.com/watch?v=NgNdM3EiPlA 

Upgrading IBM Cognos Controller – IBM Documentation 

Installing fix packs – IBM Documentation 

Cognos Analytics 11.2.4 can be installed on top of CA 11.1.7.  The installation will overwrite the images folder, and upgrade the content-store database to a different schema version that will not work with the old CA11 version, therefore you must take a backup of the SQL database before.
Cognos Analytics 11.1.x end of support (EOS) is 2024-04-30 – but you can buy extended support.

Cognos Controller 11 needs to be installed fresh. You need to uninstall and remove the old version of CC 10 on your server before you install Cognos Controller 11.
Cognos Controller 10.4.2 end of support (EOS) is 30-Sep-2024.  Lifecycle policy Standard (S): minimum 3 years support with extension of 2 years.

Cognos Analytics upgrade  (if you decide to upgrade Cognos Analytics) Date
Copy the media to local hard disk on server d:\install
Export configuration from cognos configuration to backup folder d:\temp\backup
Backup the CA11 images folders and any custom images folders you use like D:\Program Files\ibm\cognos\analytics\webcontent\bi\images or D:\Program Files\ibm\cognos\analytics\webcontent\bi\samples\images 
Backup the controller config file D:\Program Files\ibm\cognos\analytics\templates\ps\portal\ variables_CCRWeb.xml 
Backup PA variables_TM1.xml files from D:\Program Files\ibm\cognos\analytics\templates\ps\portal
Backup PA planning.html and PMHub.html files from D:\Program Files\ibm\cognos\analytics\webcontent
Backup PA tm1web.html files from D:\Program Files\ibm\cognos\analytics\webcontent\bi\tm1\web 
Backup web.config from D:\Program Files\ibm\cognos\analytics\webcontent
Backup the SQL database for content store
Backup the SQL database for audit
Take a screenshot of cognos configuration environment page
Take a screenshot of the members of cognos administrator group
Take a screenshot of the members of cognos controller administrator group
Take a screenshot of the members of cognos controller user group
Check that firewall ports are open Port 25, 80, 9300, 9362, 1433, 389 etc for BI.  
Stop the Cognos Services
Run the setup program from IBM Cognos Analytics Server 11.2.4.1.1
Run the setup program from casrv-11.2.4-2401220500-winx64h.zip ( CA 11.2.4 FP3)  
Open Cognos Configuration and click save  
Ensure the SQL driver is in the folder D:\Program Files\ibm\cognos\analytics\drivers  
Start the Cognos BI service
Check that you can login to IBMCOGNOS
Check file exist in D:\Program Files\ibm\cognos\analytics\templates\ps\portal\ variables_CCRWeb.xml 

 

When CA11 is up and running, then you can install Cognos Controller.

https://www.ibm.com/docs/en/cognos-controller/11.0.1?topic=new-components-in-controller-1100

Cognos Controller 11 upgrade Date
Copy media to server d:\install
Backup all Controller database in SQL server
Export configuration from cognos configuration to backup folder d:\temp\backup
Backup the acl files in D:\Program Files\ibm\cognos\ccr_64\ControllerProxyServer 
Backup the web.config file in D:\Program Files\ibm\cognos\ccr_64\ControllerProxyServer
Take screenshots of application pools and iis timeout settings
Take screenshots of every setting in Cognos Controller configuration
Backup all under D:\Program Files\ibm\cognos\ccr_64\fcmweb
Backup all under D:\Program Files\ibm\cognos\ccr_64\frontend
Backup the udl files in D:\Program Files\ibm\cognos\ccr_64\Data 
Backup properties file from D:\Program Files\ibm\cognos\ccr_64\server\FAP
If HTTPS is used, backup all certificate stores
Stop all IBM Cognos Controller services
Uninstall the previous version of Cognos Controller
Reboot the server
Install the new version of Cognos Controller 
Stop Internet Information Services (IIS) Manager (the Default Web Site)

Stop IBM Cognos Controller Consolidation

Go to the folder C:\Program Files\ibm\cognos\ccr_64

Search recursively for all instances of the subfolder workarea.

Delete all instances of the workarea folder that appear in the search results.

Install the new fix pack of Cognos Controller
Place the SQL driver sqljdbc42.jar in D:\Program Files\ibm\cognos\ccr_64\server\integration 
Place the SQL driver sqljdbc42.jar in D:\Program Files\ibm\cognos\ccr_64
Add back the information in Cognos Controller configuration and click save
Create the virtual directories in WWW for Cognos Controller https://www.ibm.com/docs/en/cognos-controller/11.0.1?topic=icccoc-configure-web-server 
Update the files for Controller Web with old information https://www.ibm.com/support/pages/node/791535
Upgrade your Controller application databases https://www.ibm.com/support/pages/ibm-cognos-controller-builds-ccr-name-and-database-version
Create a UDL file and Upgrade your FAP database
If FAP service is on other server, install FAP there
Add back the FAPService.properties file
Upgrade the Cognos Controller client on the server – can you login? https://www.ibm.com/docs/en/cognos-controller/11.0.1?topic=installers-installing-stand-alone-controller-client
Start FAP service – any error in log file?
Create the Cognos Controller Client package for silent installation
Reboot windows server and check you can login to cc

 

https://www.ibm.com/docs/en/cognos-controller/11.0.1?topic=administration-fap-web-configuration

More information:

https://www.ibm.com/support/pages/cognos-analytics-continuing-support 

https://www.ibm.com/support/pages/how-install-deploy-controller-client-silently-without-user-input

https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=analytics-silent-installation-uninstallation-configuration 

https://www.aramar.co.uk/latest-version-of-ibm-controller-version-11-0-1/ 

https://www.ibm.com/support/pages/how-configure-financial-analytics-publish-fap-allow-publishing-controller-data-planning-analytics 

https://www.element61.be/en/resource/why-engage-ibm-tm1-fap-top-your-consolidation

https://www.ibm.com/docs/en/cognos-controller/11.0.1?topic=only-whats-new

https://www.ibm.com/support/pages/ibm-cognos-controller104x

Product:
Planning Analytics 2.0.9.19
Microsoft Windows 2019 server

Issue:

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

Solution:

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: https://www.ibm.com/support/pages/ibm-planning-analytics-local-20919-now-available-download-fix-central-0 

Detail upgrade instruction here: https://www.ibm.com/docs/bg/planning-analytics/2.0.0?topic=configuration-upgrading-planning-analytics-local 

 

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 bootstrap.properties files from folder:

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

Backup of your PAA Agent bootstrap.properties 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

https://cogknowhow.tm1.dk/archives/4147

https://www.ibm.com/support/pages/client-shows-list-tm1-servers-cannot-connect-any-them

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

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=pa2f82-change-default-value-fipsoperationmode-in-tm1scfg-cogstartupxml 

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.

https://exploringtm1.com/configure-planning-analytics-to-recognise-cognos-ssl-certificate/
https://exploringtm1.com/cognos-analytics-configure-ssl-certificate/

https://www.sempreanalytics.com/blog/increasing-reporting-capabilities-from-planning-analytics-using-cognos-analytics/
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 ipa_workspace_win_2019_2.0.92.156.zip
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  https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=idt-install-configure-planning-analytics-administration-agent-local-only
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 bootstrap.properties 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:

https://www.ibm.com/support/pages/ibm-planning-analytics-20-fix-lists

https://code.cubewise.com/blog/determine-the-version-of-ibm-planning-analytics/ 

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=features-whats-new-in-planning-analytics 

https://exploringtm1.com/planning-analytics-version-management/ 

Due to a security update included in Planning Analytics 2.0.9.19, 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.

https://www.ibm.com/support/pages/whats-coming-next-ibm-planning-analytics-2093-sc

https://www.ibm.com/support/pages/troubleshooting-tm1bi-data-source-connections

https://exploringtm1.com/forcing-cognos-bi-use-native-tm1-engine/

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=itw-configure-ssl-planning-analytics-tm1-webspreadsheet-services-existing-keystore 

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

https://www.ibm.com/support/pages/how-configure-ssl-planning-analytics-web-tier-using-gskit-using-existing-signed-certificate

Product:
Planning Analytics 2.0.9.19 TM1 Perspective

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

Issue:

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.

Solution:

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

 

More information:

https://www.ibm.com/support/pages/opening-tm1-perspectives-generates-digital-signature-message

 

Product:

Microsoft SQL server 2019

Issue:

How find what SQL Query is it that i is working one?

Solution:

When you have a long list of Query’s you work with, click on the little PIN one each and they will stay at the top. And maybe it is easier for you to find back to the one you want to work with.

 

Other tip is to use WHERE 1=1 in your statements, to easy then remark the lines you need or not need when you adjust your selection.

SELECT TOP (1000) [GeographyKey]
,[City]
,[StateProvinceCode]
,[StateProvinceName]
,[CountryRegionCode]
,[EnglishCountryRegionName]
,[SpanishCountryRegionName]
,[FrenchCountryRegionName]
,[PostalCode]
,[SalesTerritoryKey]
,[IpAddressLocator]
FROM [AdventureRestore].[dbo].[DimGeography]
WHERE 1=1
and CountryRegionCode = 'AU'
--and city = 'Alexandria'
and PostalCode = 2015

 

More Information:

https://www.red-gate.com/simple-talk/wp-content/uploads/imported/1307-keystrokes.pdf

https://learn.microsoft.com/en-us/sql/ssms/sql-server-management-studio-keyboard-shortcuts?view=sql-server-ver16

https://www.mssqltips.com/sqlservertip/2542/display-line-numbers-in-a-sql-server-management-studio-query-window/

https://sqlstudies.com/2022/07/21/ssms-put-pinned-tabs-in-their-own-row/

Product:
Cognos Controller 10.4.2200.88
Microsoft Excel 64 bit Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20684) 64-bit
Microsoft Windows 11

Issue:

After upgrade to Excel 64 bit, Excel crash at start. The add in for Cognos controller can be for the 32 bit version of the Excel program.

Solution:

Inside Excel, go to file – options.

Go to Add-ins dialog.

Manage‘ is set to ‘Excel Add-ins‘, and click ‘Go’.

Remove the mark for the first Cognos Controller line (adxloader.controller.excellink). Should look like this when it is correct.

Click OK.

Manage‘ is set to ‘COM Add-ins‘, and click ‘Go’.

The Cognos controller link should point to the 64 bit version of the dll, as above.

Click OK.

Recommendation, is to start Cognos Controller program first, and from inside Cognos Controller click on the icon for excel, to start Excel.

Be patient, Excel take a long time to start.

If you check Excel add-ins when Cognos controller is running it can look like this:

 

More Information:

https://www.ibm.com/support/pages/controller-excel-link-disappears-after-excel-crashes

https://exploringtm1.com/use-architect-or-perspectives-10-2-x-to-planning-analytics/ 

https://www.ibm.com/support/pages/office-365-supported-ibm-cognos-controller

https://www.ibm.com/support/pages/ibm-cognos-controller-has-fired-exception-when-updating-layout-caused-microsoft-excel-2016-defect

https://www.add-in-express.com/creating-addins-blog/release-excel-com-objects/

Product:
Microsoft Windows

Issue:

How rename many files in a folder by change the first letter to a other letter?

Solution:

Go to the command prompt.

Go to the folder where the files are.

Enter below to change all files to start with the letter b

ren  *.* b*.*

 

More Information:

https://www.alphr.com/how-to-batch-rename-files-in-windows-10/

https://www.partitionwizard.com/partitionmagic/batch-rename-files.html

 

Product:
Planning Analytics 2.0.9.18
Microsoft Windows 2019 server

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

Solution:

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

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

    ProcessQuit ;
ENDIF;

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:

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

https://exploringtm1.com/operators-tm1-ti-if-statements/ 

https://exploringtm1.com/operators-tm1-rule-statements/

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

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

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