Product:

Cognos Controller 11.1.1003.12
Microsoft Windows 2022 server

Issue:

How change from non-production to production in the header of the cognos controller client program?

Solution:

In later versions of Cognos Controller this is controlled from a file on the controller server.

Login to the cognos controller server as administrator.

Go to folder D:\Program Files\ibm\cognos\ccr_64\ControllerProxyServer

Open file web.config in notepad

Change from 0 to 1 at:

<add key="OptimizeDefineAccountQuery" value="true" />
<!-- Indicate Production or Non-Production, Levels: 0=Non-Production, 1=Production" -->
<add key="InstallerType" value="1" />
<add key="OptimizeJournalCopyQuery" value="false" />

Save the file.

 

Try again in Cognos Controller client.

You control if the users should see the database and user in the toolbar inside cognos controller from user settings:

Go to Maintain – User – Personal Defaults.

Select Layout tab.

Mark Display Data source and User ID.

Click OK.

 

More Information:

https://www.ibm.com/docs/en/controller/11.1.0?topic=defaults-define-personal-layout-2-tab#E1K11_3_PersonalDefaults-theLayout2Tab

 

Product:
Planning Analytics 2.1.10
Microsoft Windows 2022 server

Planning analytics for excel  ( IBM_PAfE_x86_2.0.100.3.xll )

Issue:

How do i work with a old TM1 perspective report in PAFE (PAX)?

Solution:

Create a common file share e.g \\server\pax\master  (where all users that should update xlsm files should have access)

To take a existing TM1 perspective websheet to work with it in pafe (pax) you need to convert it.

Open PAFE in excel.

Ensure that you have a open blank workbook in Excel.

Ensure that you have “trust access to the VBA project object model” set.

In IBM Planning Analytics ribbon in Excel click on Options.
Scroll down to “upgrade action buttons”

Click upgrade action buttons.

Fill in a local folder and click OK.

Select you TM1 server connection – and login.

Go down to the folder where you have your report you want to convert, select it and click upgrade.

This will change the report.

Click OK

Close Excel.

 

Start TM1 perspective.

Login to your TM1 instance.

Explore you websheet under applications.

Open your websheet into excel, by click on it.

Inside Excel, go to Save As, and save the fil in your folder \pax\master as a .xlsm file.

Name the file to same name as your report show in TM1 applications.

Close Excel.

 

Start PAFE in Excel again.

Go to file – open – and open the .xlsm file you have saved above.

Click on Connect button in the IBM Planning Analytics Ribbon.

Select you TM1 server and your TM1 instance to connect.

Now you should have the report in PAFE, and on the right side you have a workbook tab. Here you can expand “action buttons” and change the existing buttons in your old Tm1 Perspective report.

After you have done any changes in the spread sheet, save it to the shared folder or a temporary local folder.

Use Excel format settings to change the text size in buttons etc.

When you are done, click on the Publish icon.

Then select under what folder you want to store the websheet.

This websheet will be visible in both TM1WEB and PAW.

Exit Excel.

 

When you work with PAFE (PAX) reports, you need to start from a xlsm file you have stored yourself.

(There are other ways to work with websheet in PAFE, above is a suggestion).

 

More Information:

PAfE Reporting Hints – QueBIT

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

https://quebit.com/askquebit/ibm-planning-analytics-for-excel-quick-start/ 

https://exploringtm1.com/how-to-download-ibm-planning-analytics-for-excel-from-planning-analytics-workspace/ 

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=tf-tm1-worksheet-functions-in-planning-analytics-microsoft-excel

Product:

Cognos Controller 11.1
Microsoft Windows 2022 server

Issue:

When you login to Controller Web you see only planning analytics.

Solution:

Go to folder d:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.glass

Open server.env in notepad++

Normally you only need to change the c: to d: if you have installed the cognos controller program to the d: drive with default paths.

If this point to wrong folder, the controller web does not work after login.

You need to restart windows service IBM Controller UI (FCMGLASS) for the change to take affect.

More Information:

https://www.ibm.com/docs/en/controller/11.1.0?topic=only-configuring-controller-web

Product:
Cognos Controller web 11.1
Microsoft Windows 2022 server

Issue:
What IBM service should be running on the server?

 

Solution:

When uninstalling the old version of Cognos Controller, reboot the server and ensure that the IBM Cognos Controller Web service is unregistered.  That is FCMWEB.

Go to the Command prompt (CMD). Manually unregister windows service with command:

sc delete FCMWEB

replacing FCMWEB with the exact name of the service you want to delete, if there are others.

After the reboot of the windows server, install the new version of Cognos Controller.

The anti-virus software can make the installation very slow – please consider to exclude the install folder from the anti-virus protection.

Now you should have a IBM Controller Web service.

Above is the windows service you should have if you install all Cognos Controller selections.

More Information:

https://www.ibm.com/docs/en/controller/11.1.0?topic=SS9S6B_11.1.0/com.ibm.swg.ba.cognos.ctrl_inst.doc/t_installfixpacks.htm 

https://www.ibm.com/docs/en/controller/11.1.0?topic=only-configuring-controller-web 

https://www.ibm.com/support/pages/download-ibm-controller-1110-fp2-fix-central 

Install IBM Controller 11.1.0.2 on Microsoft® Windows®
  1. Change to the directory where you have downloaded the install version.
  2. Decompress the .tar.gz file.
  3. To see the version of a component before you install it, unpack the tar file to disk, or read the table of contents of the tar file.
  4. Stop Internet Information Services (IIS) Manager (the Default Website).
  5. Stop all IBM Controller services.
  6. If your IBM Controller environment is customized, back up the entire IBM Controller location.
  7. Go to the location where you downloaded and extracted the files.
  8. Go to the win64h directory and double-click the issetup.exe file.
  9. Follow the directions in the installation wizard, installing in the same location as your existing IBM  Controller server components if already present.
  10. Start the following services:
    • IBM Controller Batch Service
    • IBM Controller Web Service
    • IBM Controller Web UI Service
    • IBM Controller UI Service
    • IBM Controller Web Spreadsheets Service
    • IBM Controller Reports Service (if applicable)
    • IBM Controller FAP Service (if applicable)
    • IBM Controller Web Batch Service (if applicable)
    • IBM Controller API Service (if applicable)
  11. If you have a distributed environment, repeat these steps for all remaining IBM Controller servers.
  12. Start the Internet Information Services (IIS) Manager (the Default Website).

 

https://www.ninjaone.com/blog/delete-windows-service-from-command-line/

https://www.action1.com/blog/how-to-delete-windows-service-in-command-line-or-registry/

 

Product:

Cognos Controller 11.1.1003.12
Cognos Analytics Build: 12.0.4.46
Object Model version: 202406

Microsoft Windows 2022 server

Problem:

The PDF report is totally black.

Solution:

This is a feature of the new reports in CA 2.0.4.

In IIS Manager select the virtual directory (ibmcognos if using the default) and double-click Mime Types.

Add the following mime types to your IIS configuration if they are not already present.

  • .svg : image/svg+xml
  • .woff : application/x-font-woff
  • .json : application/json
  • .woff2 : font/woff2
  • .template : text/html
  • .txt : text/plain
  • .properties : text/plain
  • .wasm : application/wasm
  • .mjs : application/javascript

Restart IIS

Try to run a CA report to PDF format.

Cognos Controller 11.1 only supports CA 12.0.3 and not CA 12.0.4

Above changes may not work in Cognos Controller 11.1, you can either install CA 12.0.3

from file ca_instl_win_3.7.29.exe and ca_srv_win64_12.0.3.zip,

or use Controller embedded reporting, with this setting on the Controller server:

Open C:\Program Files\IBM\cognos\ccr_64\ControllerProxyServe\Web.config file

Add this line to the file:
<add key=”ccrReports” value=”http://servername.domain.com:9082/fcm.reports/report” />

Browse to the folder C:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\etc\
Edit the file server.env:
Add the following line:
JAVA_HOME=C:/Program Files/IBM/cognos/ccr_64/fcmweb/jre

Save the file and restart the server.

More information:

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

https://www.ibm.com/docs/en/controller/11.1.0?topic=options-configuring-controller-embedded-report-library

Product:
IBM DB2 Data Studio
Microsoft Windows 2022 server

Issue:

Error when starting DS4.1.2 client

An error has occurred, See the log file d:\program files\ibm\ds4.1.2\configuration\1747225616665.log

org.osgi.framework.BundleException: Unable to acquire the state change lock for the module: osgi.identity; osgi.identity=”org.eclipse.core.runtime”

Solution:

Before starting the DB2 studio client program

  1. Go to your eclipse directory and open configuration directory. (can be D:\Program Files\IBM\DS4.1.2\configuration\org.eclipse.osgi\.manager )
  2. open org.eclipse.osgi directory.
  3. open .manager folder.
  4. Delete .fileTableLock file.
  5. Restart your DB2 data studio program.

Then start the program as “administrator”, right click on icon and select “run as administrator”.

and select the path to the folder to be something like d:\IBM\rationalsdp\workspace

Then you should be inside DB2 Data Studio, and see the list of database on the side.

 

 

More Information:

DB2 9.5 and IBM Data Studio: Building an SQL Statement

https://www.eclipse.org/forums/index.php/t/823236/

https://www.ibm.com/docs/en/db2/11.5.x?topic=objects-retrieving-data-from-tables-views 

https://www.raghu-on-tech.com/2020/02/29/db2-finding-top-10-most-active-tables/

Db2 Basics: Getting Data Out of Db2

Product:

Microsoft Windows 2022 server
DB2 driver

Issue:

Can not connect to DB2 server with ODBC, the dbalias is not found.

Solution:

Inside the ODBC program, you need to setup a alias.

This is controlled by the db2cli.ini file.

If you run a bat file to catalog the sql db2 settings this file will end up in your profile folder, e.g. c:\users\yourname

Copy the file to the common folder c:\programdata\ibm\db2\db2copy1\cfg

Search for db2cli.ini to find the folders to use. The db2cli.ini have lines like below:

[db2instance]
DBALIAS=db2instance
UID=donald
DESCRIPTION=db2instance - PROD

But first, you need to install the db2 drivers from IBM Data Server Client Packages (12.1, All platforms)

Run the setup.exe from inside folder \wib_data_server_client_winx64_v11.1\client\image.

Create a catalog.sql file with the content you need; (you must adjust to your environment)

UNCATALOG NODE db2instance;
CATALOG TCPIP NODE db2instance
REMOTE db2servername.domain.com
SERVER 50000
REMOTE_INSTANCE db2instance
OSTYPE LINUX;

The run a command like this from a bat file:

db2cmd db2setcp ” db2 -tvf C:\install\catalog.sql ”

This will give you the User DSN setup.

You can find the values in the registry, download to a reg file, and edit it to be HKLM as below;

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\db2instance]
"Driver"="C:\\PROGRA~1\\IBM\\SQLLIB\\BIN\\DB2CLIO.DLL"
"Description"="db2instance"

Load this file, and you will get it in System DNS.

Then you need to update the db2cli.ini in the folder c:\programdata\ibm\db2\db2copy1\cfg to get the dbalias.

If it does not work, check the links below.

 

More information:

https://www.ibm.com/docs/en/db2-warehouse?topic=installing-windows

https://www.ibm.com/support/pages/db2-odbc-cli-driver-download-and-installation-information

Installing and configuring an DB2 ODBC CLI-driver on Windows

https://www.ibm.com/docs/en/db2/11.5.x?topic=commands-catalog-database

https://help.hcl-software.com/bigfix/9.2/platform/Platform/Config/c_creating_db2_dsn.html

https://www.ibm.com/docs/en/db2/11.5.x?topic=odbc-cliodbc-configuration-keywords

https://www.columbia.edu/sec/acis/db2/db2iy/db2iy47.htm

https://www.ninjaone.com/blog/registry-editor/

https://www.techtarget.com/searchwindowsserver/tip/Command-line-options-for-Regeditexe

Product:

Microsoft Excel 365 32 bit version

Microsoft Windows 11

Issue:

Inside a excel report, you have a power query that get data from a other excel file on sharepoint.

When you do refresh to update you get a error.

 

 

Possible cause:

The Excel file have to many rows.

When using Excel, it’s important to note which file format you’re using. The .xls file format has a limit of 65,536 rows in each sheet, while the .xlsx file format has a limit of 1,048,576 rows per sheet. For more info, see File formats that are supported in Excel and Excel specifications and limits.

If you load a csv file, you may only get a part of the rows of data. And the sum result may not be what you expect, if you reach a limit in excel program.

 

More Information:

The Excel Row Limit is 1,048,576 Rows | Row Zero

Loading CSV/text files with more than a million rows into Excel – Jose Barreto’s Blog

https://flatfile.com/blog/top-6-csv-import-errors-and-how-to-fix-them/ 

https://flatfile.com/blog/why-isnt-my-csv-file-importing/

Product:
Cognos Controller CONTRL_UPDATE_version=CCR-AW64-ML-RTM-11.1.1003.31-0

Microsoft Windows 2022 server

Issue:

On a new installation, when run the FAP IP, you get a error.
If you check in the log folder for the TM1 application, you find this error:

Error: MetaData procedure line (0): Error executing SQL query: “select parent, child, parentname, childname from nrtr_extdim_temp_1”

Solution:

Check that the FAP ODBC connection is connected to the correct Controller FAP database.

Check that the ODBC connection is 64 bit.

  1. Logon to the TM1 server as an administrator
  2. Launch the following file: C:\Windows\SysWOW64\odbcad32.exe  (to open the 32 bit version)
  3. Click on tab “System DSN
  4. As a precaution, open the ODBC connection “FAP” and note down all the settings
  5. Afterwards, highlight the ODBC connection “FAP” and click “Remove
  6. Click “Yes” to confirm”
  7. Click OK
  8. From the Start Menu, click “All Programs – Administrative Tools – Data Sources (ODBC) (64 bit)
  9. Click on tab “System DSN
  10. Create an ODBC connection called “FAP” which connects to your FAP database (which should be separate from your “Controller database”.)

Also check that the tm1api.dll from bin64 folder are in the path on the windows server where you run the FAP service, and TM1 instance.

  1. On the Controller FAP application server, go to control panel, system
  2. Click on Advanced system settings at the bottom of the dialog
  3. Click Environment Variables
  4. Under System Variables, select Path, and click Edit
  5. Append this with an extra entry at the end, for the path to the TM1\bin directory, for example: ;D:\Program Files\IBM\tm1_64\bin64
  6. Click OK to all dialog boxes that follow
  7. If you have changed your PATH, then you to restart the FAP service.

The TM1 client includes the .dll files that are required for the Financial Analytics Publisher server and the client to communicate with the TM1 server. Therefore it is necessary to install the TM1 client on the server where the Financial Analytics Publisher (FAP) service is installed.
In addition, the PATH variable must include the folder where these DLL files are located.
Controller 10.2 onwards is a 64-bit server, so requires 64-bit TM1 DLL files
Controller 10.1.1 and earlier is a 32-bit server, so requires 32-bit TM1 DLL files

Above can also give the error : Could not login to TM1. Could not load tm1api.dll due to: The specified module could not be found’ when starting FAP Initial Publish.

Please also check that the password entered in IBM Controller Financial Analytics Publisher client, for the datamart – edit – client (domain\username) have the correct password. Test by login to the TM1 instance from TM1 architect, with that user and password.

You find more information in the FAP log file at D:\Program Files\IBM\cognos\ccr_64\Server\FAP folder.

More information:

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

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

 

Product:
Planning Analytics 2.0.9.19
Microsoft Windows 2019 server

Issue:

What version match each other between the line of PAL 2.0 and PAL 2.1?

When upgrading from Planning Analytics Local 2.0 to Planning Analytics Local 2.1, each component should be upgraded to the corresponding version or greater.

Solution:

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=21-understanding-planning-analytics-local-component-versions 

A version of PAL 2.0, should have a corresponding version in PAL 2.1 with the same functions.

Hopefully all “bugs” corrected are the same in the matching versions.

Planning Analytics 2.0 Planning Analytics 2.1 TM1 Build
TM1 Server  

Planning Analytics Workspace
Spreadsheet Services/TM1 Web
Planning Analytics for Excel

 

 

Planning Analytics Workspace
Spreadsheet Services/TM1 Web
Planning Analytics for Excel
TM1 Server

 

2.0.9.19 IF3 2.0.94 2.1.1 11.8.02300.10
2.0.9.19 IF5 2.0.95 2.1.2 11.8.02400.7
2.0.19 IF8 2.0.96 2.1.3 11.8.02500.3
2.0.9.20 2.0.97 2.1.4 11.8.02700.4
2.0.9.20 2.0.98 2.1.5 11.8.02700.4
2.0.9.20 2.0.99 2.1.6 11.8.02800.9
2.0.9.20 2.0.100 2.1.7 11.8.02800.9
2.0.9.21 2.0.101 2.1.8 11.8.02900.8
2.09.21 2.0.102 2.1.9 11.8.02900.8
2.09.21 2.0.103 2.1.10 11.8.02900.8

 

Planning Analytics Local 2.1 is a direct upgrade from Planning Analytics Local 2.0. The upgrade to version 2.1 does not require changes to the TM1 databases, Planning Analytics Workspace content, Planning Analytics for Excel reports, or Websheets.

Planning Analytics Local 2.1 does not include TM1 Architect, TM1 Perspectives, TM1 Applications, Performance Modeler, or Cognos Insight. These components are not supported in Planning Analytics Local 2.1.

IBM plans for introducing a non-containerized V12 Planning Analytics Local server called version 3.1, expected around late 2025. This version will be more similar to the Planning Analytics version found on AMZ and AZURE.

 

More Information:

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=21-understanding-planning-analytics-local-component-versions

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

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

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

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

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

https://lodestarsolutions.com/moving-from-perspectives-to-pafe-in-planning-analytics/ 

https://www.linkedin.com/pulse/ibm-ama-planning-analytics-march-2024-denis-barchukov-ypylc 

https://www.linkedin.com/pulse/ibm-ama-planning-analytics-april-2024-denis-barchukov-3qtvc 

https://www.linkedin.com/pulse/ibm-ama-planning-analytics-june-2024-denis-barchukov-r2kfc

https://www.ibm.com/new/announcements/ibm-planning-analytics-now-on-azure-the-only-solution-in-the-market-with-unmatched-deployment-flexibility 

https://aws.amazon.com/marketplace/pp/prodview-pzt5r2kg6mmmg