Product:

Planning Analytics 2.0.9.11
Microsoft Windows 2019 Server

Issue:

Newly installed TM1, when start the TM1 application, the first starts, but the second application does not start.

Following error in tm1server.log while starting the second Planning Analytics server : ” TM1.Server E15) Server unable to listen on port 5001. Please check the log file for further details ”

Possible solution:

The Tm1 instance can not read the tm1s.cfg file, and therefor defaults to the standard value of HTTPPortNumber; that is port 5001.

The first starts, the second tm1 instance try to use same port and crash.

Can be that you have missed to include HTTPPortNumber in the tm1s.cfg file.

Add HTTPPortNumber to every tm1s.cfg file with a unique number.

Can be that you inside the tm1s.cfg file you have “Configuration Directory” set to the data folder, but the tm1s.cfg file is in a different folder.

Remove “Configuration Directory” from the tm1s.cfg file.

Let only the path for the Tm1 instance in cognos configuration point out where the tm1s.cfg file is located.

 

More Information:

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=tcf-parameters-in-tm1scfg-file

https://pmsquare.com/analytics-blog/2020/6/7/planning-analytics-configuration-file-breakdown

https://www.ibm.com/support/pages/tm1server-e15-server-unable-listen-port-5001

TM1/PA REST API Part 1: Introduction – Getting it Right

Product:
Planning Analytics 2.0.9.11
TM1_version=TM1-AW64-ML-RTM-11.0.911.20-0
Microsoft Windows 2019 server

Issue:
Can not start IBM Planning Analytics Spreadsheet Services service (tm1web).

Error in windows event log:

The IBM Planning Analytics Spreadsheet Services service terminated with the following service-specific error:

Incorrect function.

 

Suggested solution:

Change the user account that run the windows service to local system and try if that works.

Can be that a windows service account – do not have full access to files or memory.

Check the log files in folder D:\Program Files\ibm\cognos\tm1web\wlp\usr\servers\tm1web\logs

More information:

  1. Planning Analytics 2.0.9.11 – December 21, 2021
    IBM Planning Analytics Local version 2.0.9.11 and the cloud release of IBM Planning Analytics with Watson version 2.0.9.11 includes updates for IBM TM1 Server version 11.8.9.

Product:
Microsoft Power BI
Microsoft Windows 2016 server

Issue:
You have a working Dynamic SQL query, but when you schedule it, it stop working.

Suggested solution:
Rebuild your query to not use Dynamic SQL, to make it possible to schedule it in PowerBi.

See if you can change to use fewer different data sources.

More Information:

https://docs.microsoft.com/en-us/power-query/dataprivacyfirewall

https://medium.datadriveninvestor.com/setting-a-scheduled-refresh-on-a-dynamic-data-source-in-power-bi-409ccec7337b

https://community.powerbi.com/t5/Service/Dynamic-SQL-through-ODBC-is-breaking-refresh-capability/m-p/276408

https://powerbitalks.com/2020/06/refresh-dataset-using-button-powerbi.html

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data

Product:
Microsoft PowerBI report server  Version 1.10.7737.32652 (January 2021)
Microsoft Windows 2016 server

Issue:
Some of the powerbi reports does not refresh from the portal or on schedule. You get a error message like this;

2022-01-19 : |ERROR|63|Error Processing Data Model Refresh: SessionId: , Status: Error Failed to refresh the model, Exception Microsoft.AnalysisServices.OperationException: Failed to save modifications to the server. Error returned: ‘COM error: Microsoft.PowerBI.ReportServer.ASEngineManagedRoot, Datasource … is not found..
COM error: Microsoft.PowerBI.ReportServer.ASEngineManagedRoot, Datasource … is not found..
The current operation was cancelled because another operation in the transaction failed.

The powerbi report can be refreshed from PowerBI for Desktop. Also the report works from a different PowerBI server.

You can check error messages in folder c:\Program Files\Microsoft Power BI Report Server\PBIRS\LogFiles

 

Suggested solution:

Restart the windows PowerBIReportServer service, to release memory or cache that is used by PowerBI server.

 

More information:

ERROR|19|Failed to get connection string for model | RequestID =  ClientSessionID =  Microsoft.PowerBI.ReportServer.AsServer.AsConnectionException: Failed to get connection string for model

error in the log file can be because you have 2 data sources in the model pointing to the same database, where the database name was all lower case in one source and upper case in the other. Use the advanced editor in PowerBI desktop to rename them to be unique and then there was one source and data refreshes fine. You must use the same letter format on all fields.

https://docs.microsoft.com/en-us/power-bi/report-server/scheduled-refresh 

Product:
Planning Analytics 2.x

Microsoft Windows 2016 Server

Issue:
User have removed some sheet in a excel workbook and publish it again to TM1WEB.

But can now not open the application view in TM1WEB. User gets a error “Failed to open the target workbook. Make sure that the file has not been renamed or deleted”.

Solution:
Check the excel workbook for reference errors.
If there are formulas that referee to the deleted sheet, this can give this error.

Ensure that all formulas point to existing columns and sheets in the workbook.

Also ensure you do not have inserted unsupported object into the workbook, like word documents and links to pictures that may not work in TM1WEB.

Redo the workbook, and publish it again.

Also ensure you do not have empty columns on the right side in your spreadsheet, that can also affect TM1Web performance.

More Information:

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

https://www.ibm.com/support/pages/list-microsoft-excel-supported-functionality-menu-ibm-cognos-tm1-web-net

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=reference-worksheet-functions

https://www.ibm.com/support/pages/how-hide-zeros-tm1web-websheet

https://www.ibm.com/support/pages/apar/PH17596

Product:

Planning Analytics Workspace
Microsoft Windows 2016 server

Issue:
PAW does not respond and most users do not get reports shown.

On the TM1WEB server you find this in log file D:\Program\ibm\cognos\tm1_64\webapps\tm1web\WEB-INF\logs\message.log

….

Caused by: java.lang.OutOfMemoryError: Java heap space

at com.ibm.cognos.tm1.websheet.SheetCell.<init>(SheetCell.java:98)…..

Solution:

Go to Cognos Configuration for TM1

Go to Environment – IBM Cognos TM1 – Maximum memory for WebSphere liberty

And increase value to 8192.
Right click and restart IBM Cognos TM1 service.

Check in task manager that you still have free RAM memory on your server.

Check if the websheet and reports now work in TM1WEB or PAW.

More information:

https://www.ibm.com/support/pages/still-getting-javalangoutofmemoryerror-whatever-value-maximum-memory-mb

https://www.ibm.com/docs/fr/cognos-analytics/10.2.2?topic=profile-adjusting-memory-resources-cognos-service

Product:
Planning Analytics Workspace 53
Microsoft Windows 2016 server

Issue:
Blank report in paw or not loading.

 

Error message in error.log file under pa-gateway folder

A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond……. AH00898: Error reading from remote server returned by /tm1web/dwr/engine.js, referer: ….

Suggested Solution:

On your Windows 2016 server where PAW is installed.
Go to the config folder e.g. d:\ibm\paw\config and update the file paw.ps1 in notepad++

add last to file

env$:ProxyTimeoutSeconds='900'

Save the file.

Restart the Planning Analytics Workspace.

Open a Powershell window (with “Run as Administrator”) on the machine you have installed PAW
change into the installation directory of your PAW installation
move into the “scripts” folder: cd scripts
– to stop PAW execute the command: ./paw.ps1 stop
– to start PAW execute the command: ./paw.ps1 start

 

Some views of PAW is dependent on settings in TM1WEB, so you can also update the webapps\tm1web\WEB-INF\configuration\tm1web_config.xml file to get it to work.

add values like

<add key=”WorkbookMaxCellCount” value=”500000” />

More information:

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=local-what-timeout-settings-can-i-set

https://www.ibm.com/support/pages/action-has-been-terminated-because-it-exceeds-configured-maximum-memory-limit-error-received-while-opening-planning-analytics-workspace-book 

https://www.ibm.com/support/pages/how-stop-and-start-planning-analytics-workspace-command-line

For linux https://blog.octanesolutions.com.au/session-timeout-for-tm1web-paw-and-pax

Product:
Microsoft SQL 2016 server
Microsoft Windows 2016

Issue:

List when all the SQL agent jobs are run.

Solution:   (https://www.mssqltips.com/)

In SQL Management Studio enter this query:

-- list jobs and schedule info with daily and weekly schedules

-- jobs with a daily schedule
select
sysjobs.name job_name
,sysjobs.enabled job_enabled
,sysschedules.name schedule_name
,sysschedules.freq_recurrence_factor
,case
when freq_type = 4 then 'Daily'
end frequency
,
'every ' + cast (freq_interval as varchar(3)) + ' day(s)' Days
,
case
when freq_subday_type = 2 then ' every ' + cast(freq_subday_interval as varchar(7)) 
+ ' seconds' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 4 then ' every ' + cast(freq_subday_interval as varchar(7)) 
+ ' minutes' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 8 then ' every ' + cast(freq_subday_interval as varchar(7)) 
+ ' hours' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
else ' starting at ' 
+stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
end time
from msdb.dbo.sysjobs
inner join msdb.dbo.sysjobschedules on sysjobs.job_id = sysjobschedules.job_id
inner join msdb.dbo.sysschedules on sysjobschedules.schedule_id = sysschedules.schedule_id
where freq_type = 4

union

-- jobs with a weekly schedule
select
sysjobs.name job_name
,sysjobs.enabled job_enabled
,sysschedules.name schedule_name
,sysschedules.freq_recurrence_factor
,case
when freq_type = 8 then 'Weekly'
end frequency
,
replace
(
CASE WHEN freq_interval&1 = 1 THEN 'Sunday, ' ELSE '' END
+CASE WHEN freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
+CASE WHEN freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
+CASE WHEN freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
+CASE WHEN freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
+CASE WHEN freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
+CASE WHEN freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END
,', '
,''
) Days
,
case
when freq_subday_type = 2 then ' every ' + cast(freq_subday_interval as varchar(7)) 
+ ' seconds' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 
when freq_subday_type = 4 then ' every ' + cast(freq_subday_interval as varchar(7)) 
+ ' minutes' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 8 then ' every ' + cast(freq_subday_interval as varchar(7)) 
+ ' hours' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
else ' starting at ' 
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
end time
from msdb.dbo.sysjobs
inner join msdb.dbo.sysjobschedules on sysjobs.job_id = sysjobschedules.job_id
inner join msdb.dbo.sysschedules on sysjobschedules.schedule_id = sysschedules.schedule_id
where freq_type = 8
order by job_enabled desc

 

More Information:

https://www.mssqltips.com/sqlservertip/5019/sql-server-agent-job-schedule-reporting/

https://database.guide/4-ways-to-get-a-list-of-schedules-in-sql-server-agent-t-sql/

Product:
Microsoft SQL server 2016

Microsoft Windows 2019 server

Issue:
List size and location of SQL database files?

Solution:

In SQL management studio enter this query;

SELECT
    db.name AS DBName,
    type_desc AS FileType,
    Physical_Name AS Location,mf.size/128 as Size_in_MB
FROM
    sys.master_files mf
INNER JOIN 
    sys.databases db ON db.database_id = mf.database_id
ORDER BY  Size_in_MB DESC,DBName

More information:

How to determine free space and file size for SQL Server databases

SQL SERVER – Find Location of Data File Using T-SQL

Product:
Microsoft SQL server 2016
Microsoft Windows 2019

Issue:
How do i see what roles a user is member of? The roles show what kind of user access he gets in the database.

Solution:

In SQL management studio, enter this query, to find roles the user is part of;

exec xp_logininfo 'DOMAIN\username', 'all'

In powershell you can enter this command to find content of groups

Get-ADGroupMember -Identity adgroupname | select name, objectclass

Or also expand to find users in groups

Get-ADGroupMember -Identity adgroupname -Recursive| select name, samaccountname

 

 

More information:

https://4sysops.com/wiki/how-to-install-the-powershell-active-directory-module/

https://docs.microsoft.com/en-us/powershell/module/activedirectory/get-adgroupmember?view=windowsserver2022-ps

https://ss64.com/ps/get-adgroupmember.html

https://www.sqlserver-dba.com/2016/01/how-to-query-active-directory-with-xp_logininfo.html

https://www.mssqltips.com/sqlservertip/1252/auditing-windows-groups-from-sql-server/