Define Data Mart save, make Cognos Controller not responding

Product:
Cognos Controller 10.4.2
Microsoft Windows 2016 server

Problem:
When a user make a change and save a value inside “Define Data Mart” in Cognos Controller Client, the program will freeze.
The client will wait for a SQL statement to finish.

select @@trancount; SET FMTONLY ON select * from ##Fa189b4c SET FMTONLY OFF exec tempdb..sp_tablecollations_100 N’.[##Fa189b4c]’

You need to restart the Cognos Controller windows server to release the process.

Kill the controller client program will not help. You get same issue when you go back inside Define Data Mart dialog.

A restart of IIS looks like it release the process, and you can work again in Cognos Controller.

Solution:

Install a fix pack to Cognos Controller 10.4.2 that solves the problem.
https://www.ibm.com/support/pages/client-hangsfreezes-not-responding-when-click-transfer-publish-data-and-structures-define-data-mart-after-creating-new-data-mart-caused-apar-ph27153
Not supported workaround is to update the SQL table XOLAP direct in the SQL database. Ensure you have a backup of the SQL database before you do any changes.

You can run a export of existing data mart, but not update them from inside Cognos Controller. If you want to change the Structure Version on a existing data mart definition, you need to open SQL management studio and edit table XOLAP. Here you can update some of the values that are in the define dialog.

In Microsoft SQL Server Management Studio you can create a query like this to update definition CBI to version 200812:

UPDATE dbo.xolap
SET structversion = 200812
WHERE cubeid = ‘CBI’;

More information about SQL:  https://www.w3schools.com/sql/sql_update.asp

The selection of forms are stored in table XOLAPFORM

You have the list from you define data mart dialog at the form field.

You can check values in database with Cognos Controller function BROWSE DATA if you are a administrator.
Go to MAINTAIN – SPECIAL UTILITIES – BROWSE DATA
Enter table name and click on Arrow to see tables data.

More information:

https://www.ibm.com/support/pages/client-hangsfreezes-not-responding-when-click-transfer-publish-data-and-structures-define-data-mart-after-creating-new-data-mart-caused-apar-ph27153

https://www.ibm.com/support/pages/cognos-controller-builds-ccr-name-and-database-version

https://www.ibm.com/support/pages/ibm-cognos-controller-104x-fix-list