Product:
Microsoft SQL server 2016

Issue:

SSIS job fail with error:

Could not find database ID 42, name ’42’. The database may be offline. Wait a few minutes and try again.

Solution:
Check if the database have change ID.

select * from sys.databases

Above will list the database name and id.

When you do a detach and attach on a database file, you can if you are unlucky, get a new ID on the database. Then old SSIS job that worked yesterday, will fail.

Repeat the detach and attach process to get the database back to correct number.

Move file locations with ALTER command instead; this script will list the commands for each database

SELECT DB_NAME(database_id) AS [Database] 
, name AS [LogicalName]
, type_desc AS [FileType]
, physical_name AS [FilePath]
, 'ALTER DATABASE ' + DB_NAME(database_id)
+ ' MODIFY FILE ( NAME = ' + name + ', FILENAME = ''' + physical_name + '''); 'AS Command
FROM SYS.master_files
WHERE database_id > 4
ORDER BY database_id ASC, type_desc DESC;

You have to change the path to new location before you run the command.

Also copy the database files to the new location before you run the script.

https://blog.coeo.com/moving-sql-databases

 

More Information:

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-ver15

Understanding different SQL Server database states

https://www.mssqltips.com/sqlservertip/1420/sql-server-system-databases/

 

Product:

SAS

Microsoft SQL server 2016

Issue:

Some tables are not visible in SAS client program, even do the same user can see the tables in Microsoft SQL Server Management Studio (SSMS) or Tableau.

As the same user can see the tables inside Microsoft SQL Studio, it is not a permission issue.

Solution:

Change the tables and views in SQL to have shorter names. SAS can not show SQL tables or views that have a name longer than 32 characters.

 

More Information:

http://support.sas.com/documentation/installcenter/en/ikforecastwofrsr/75572/HTML/default/index.html

https://www.tableau.com/sv-se/products/desktop

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