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