Information about Cognos Controller
Product:
IBM Cognos Controller 10.3.1
Microsoft Windows 2008 R2 server
Microsoft Excel 2008
Problem:
When inside Cognos Controller click on the icon to start excel, you get a error;
APPCRASH. CCR.exe
Error:
Solution:
Install a newer version of Excel.
Cognos Controller 10.3.1 only supports excel version 2010, 2013 and 2016.
https://www.ibm.com/support/pages/ibm-cognos-controller-1031-supported-software-environments
Product:
Cognos Controller 10.4.2
Microsoft Windows 2012 R2 server
Microsoft SQL server
Problem:
How upgrade a new restored database to the latest Cognos Controller version?
Solution:
When a database backup from a previous version is restored on the SQL database server, it needs to be upgrade after it is connected.
You need also to update the data sources in CA11 by click on repair button, in Controller Configuration.
Login to the Controller server (if you have more than one controller server, you need to repeat the first step to configure the new controller database in controller configuration on all servers).
Right click on Controller Configuration and open it as an Administrator.
Go to Database Connections
Click on the icon for a new connection
At database type select SQL Server
Fill in the name of the database, as the users in cognos controller will see it.
Enter the provider to be SQLNCLI11.1
Enter the user id, to the SQL login you have for the database.
Enter the SQL user password.
At Initial catalog, you enter the name of the database as seen on the SQL server.
At data source you enter the name of the windows server that run SQL server.
(if unsure of what to enter, check on the previous database setup parameters)
Click on the test icon, to ensure you got it all right.
Click save. (Only Above need to be done on the other controller servers too.)
Click on the green arrow to start the DBConv program.
Click on Connect Button to connect to the new database and see what version it have today.
Click on Upgrade Button to upgrade the database to latest Cognos Controller version.
When done, and no errors are shown, click on Close.
Now you have to update the BI database connections, by go to Report Service, in Controller Configuration.
Click on Check icon.
Click on Repair button.
Close Controller Configuration.
Then on the Controller Web server, you need to start a CMD prompt as an administrator.
Inside CMD you need to move to folder C:\Program Files\IBM\cognos\ccr_64\fcmweb
cd C:\Program Files\IBM\cognos\ccr_64\fcmweb
Type the following command to update Controller web:
SyncDBConf.bat ..\Data wlp\usr\shared\config\datasources
Now the ControllerWeb application should know about the new Controller Database.
Best is to restart the Windows servers where Cognos Controller is installed, to make the changes noticed. You need to restart the CA11 servers in correct order.
At least you need to restart the IBM Cognos Controller Batch Service.
If you have several controller servers, the Controller Batch Service must only be running on the Controller Master server.
More information:
https://www.ibm.com/support/pages/how-migrate-upgrade-upsize-existing-database-new-later-version-controller
https://www.ibm.com/support/knowledgecenter/SS9S6B_10.4.2/com.ibm.swg.ba.cognos.qrc_ctrl_inst.doc/t_qsg_ctrl_config_controllerdb.html
https://www.ibm.com/support/pages/missing-database-connection-choices-when-using-controller-web
Product:
Cognos Controller 10.4.1 web
Microsoft Windows Server 2016
Problem:
Change to use DNS alias to the cognos controller server, and now can not login to the Controller Web,
“Server is not reachable or still initializing, please refresh the page in a few seconds”
If you surf direct to servername, then controller web works, it is only with dns alias that give the error.
Solution:
You need to change to the DNS alias in the file for Controller Web front end.
Stop the IBM Cognos Controller Web UI service
Start NOTEPAD as administrator
Open file D:\Program Files\ibm\cognos\ccr_64\frontend\config.js
Change the server name at line 25 to be the dns alias
“expressJs”: {
“host”: “dnsalias.domain.com”, //interface used by Controller Web UI Service
“port”: “9080”, //port used by Controller Web UI Service
Save the file.
Start the IBM Cognos Controller Web UI service
Test to surf to http://dnsalias.domain.com:9080/ to see if Controller Web works.
Now to surf to server name, should not work instead.
More Information:
https://www.ibm.com/support/pages/server-not-reachable-or-still-initializing-please-refresh-page-few-seconds-error-launching-controller-web-caused-incorrect-tcp-port-inside-serverxml
https://render-prd-trops.events.ibm.com/support/pages/troubleshooting-server-not-reachable-or-still-initializing-please-refresh-page-few-seconds-error-launching-controller-web
https://www.ibm.com/support/knowledgecenter/en/SS9S6B_10.4.1/com.ibm.swg.ba.cognos.ctrl_inst.doc/t_contrweb_configure.html
In cmplst.txt you will find this version for controller 10.4.1:
LICENSE_CONTROLLER_version=LICENSE_CONTROLLER-AW64-ML-RTM-10.4.1100.1-0
LICENSE_CONTROLLER_name=IBM Cognos 8 License
SWTAG_CONTROLLER_version=SWTAG_CONTROLLER-AW64-ML-RTM-10.4.1.1-0
SWTAG_CONTROLLER_name=SWTAG CONTROLLER
CONTRL_version=CCR-AW64-ML-RTM-10.4.1100.133-0
CONTRL_name=IBM Cognos Controller
Product:
Cognos Controller 10.4.1
Microsoft Windows 2016 server
Problem:
Controller admin try to update users in Maintain – Rights – User dialog, for a installation where CAM security is used. He can not see the connected CAM users in the list.
Solution:
You must be part of Cognos Connection security group Controller Administrators. Only people in this group can change things inside controller security dialog.
When you open the user dialog, you maybe got a message “The user currently logged in is not authorized to use this method”. That means you are not part of the Controller Administrator group.
Add the person to the Controller Administrator group in Cognos. The Controller Administrator group is not the same as the ibm Cognos Controller administrator authorized user license. This is controlled by access to the Maintain menu.
More Information:
Product:
Cognos Controller 10.4.1 FAP service
Planning Analytics 2.0.6
Microsoft Windows 2016 server
Problem:
You get a error when run first IP in a new setup of FAP and TM1 instance.
Error in log file D:\Program Files\ibm\cognos\ccr_64\Server\FAP\error.log
10:00:08,849 | ERROR [fap.service.schedule.Scheduler] [schedulerThread], Could not logon to TM1
javax.naming.ConfigurationException: The TM1Server fap is neither in BASIC or in CAM mode
at com.ibm.cognos.fap.common.persistence.tm1.TM1Context.login(TM1Context.java:110)
at com.ibm.cognos.fap.service.schedule.Scheduler.updateDatamarts(Scheduler.java:567)
at com.ibm.cognos.fap.service.schedule.Scheduler.run(Scheduler.java:257)
at com.ibm.cognos.fap.service.schedule.Scheduler$1.run(Scheduler.java:162)
at java.lang.Thread.run(Thread.java:812)
Solution:
Change to IntegratedSecurityMode=5 from IntegratedSecurityMode=2 in TM1s.cfg file for the TM1 application.
During setup you have been in mode 2 to be able to add the windows service account that is going to be used by the FAP service to login to the TM1 solution. Keep in mind when you setup the DataMart (TM1 server) you need to specify the user login to TM1 with namespaceid\user name as NamespaceID looks in Cognos Configuration (CA11) and user name as you can find it in Cognos Connection. Something like this:
Domain\Lastname Firstname
The field in Edit Data Mart is case sensitive and space sensitive.
More Information:
Product:
Cognos Controller 10.4.1
CONTRL_version=CCR-AW64-ML-RTM-10.4.1100.133-0
CONTRL_name=IBM Cognos Controller
Microsoft Windows 2012 Server
Problem:
How setup Cognos Controller Web with SSO?
Solution:
Ensure that SSO is working from your Cognos Controller client first, when you login.
Stop the Controller web services
Start NOTEPAD in ADMINISTRATOR mode, and open up the file C:\Program Files\IBM\cognos\ccr_64\fcmweb\wlp\etc\server.env
Change the JAVA HOME line to
JAVA_HOME=C:/Program Files/IBM/cognos/ccr_64/fcmweb/jre
Save the file.
Start a CMD prompt as ADMINISTRATOR, go to the folder C:\Program Files\IBM\cognos\ccr_64\fcmweb and enter below command:
SyncDBConf.bat ..\Data wlp\usr\shared\config\datasources
The number of database updated depend on the number of database you have setup in Cognos Controller
From notepad open file C:\Program Files\IBM\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web\com.ibm.cognos.fcm.web.properties
Change the lines to like this;
ccrwsUrl=http://win-ca.company.com/ibmcognos/controllerserver/ccrws.asmx
biUrl=http://win-ca.company.com/ibmcognos/bi/v1/disp
biDispatchEndpoint=http://win-ca.company.com:9300/p2pd/servlet/dispatch
loginMode=CAM
change win-ca to your controller/cognos analytics server.
Save the file.
Change the memory used by controller web by open from notepad file C:\Program Files\IBM\cognos\ccr_64\fcmweb\wlp\etc\jvm.options
You must test different values to find the best – maybe start with -Xmx12g
# Java Heap size.
# In production, setting min and max to same value can provide the best performance by avoiding heap expansion and contraction.
# But high min value increases startup time, which may be undesired in a dev environment.
-Xms2g
-Xmx4g
Save the file.
Start the Windows service IBM Cognos Controller Web
Open a new document in notepad – and paste in below text
<CRNenv c_cmd=”http://win-ca.company.com:9080/#!/CamLogin”>
<cookies>
<param name=”cam_passport”/>
</cookies>
</CRNenv>
save the file to C:\Program Files\ibm\cognos\analytics\templates\ps\portal\variables_CCRWeb.xml on your Cognos Analytics server.
On your Controller server open in NOTEPAD this file C:\Program Files\IBM\cognos\ccr_64\frontend\config.js
Change the hostname and publicInterface to your controller servers FQDN name e.g. win-ca.company.com
Save the file.
Start the Windows service IBM Cognos Controller Web UI
Surf to http://win-ca.company.com:9080/ to get into the Cognos Controller Web
Select your database and login.
Cognos Controller 10.4.1 only supports this versions
https://render-prd-trops.events.ibm.com/support/pages/downloading-ibm-cognos-controller-1041
You download CA11.0.13 that is part of the Controller package
IBM Cognos Controller 10.4.1 Microsoft Windows Multilingual CC0URML
IBM Cognos Analytics Limited Use 11.0.13 Microsoft Windows Multilingual CNV2WML
IBM Cognos Framework Manager 11.0.13 Microsoft Windows Multilingual CNV2EML
IBM Cognos Analytics Samples 11.0.13 Microsoft Windows Multilingual CNV2LML
Download PA if you are going to use the FAP service
IBM Planning Analytics 2.0.6 Microsoft Windows Multilingual CNWN1ML
IBM Planning Analytics Client 64-bit 2.0.6 Microsoft Windows Multilingual CNWN2ML
Download if you are going to use the PAX plugin to Excel
IBM Planning Analytics Workspace 2.0.6 Microsoft Windows Server 2016 Multilingual CNWN4ML
IBM Planning Analytics for Microsoft Excel 64-bit 2.0.6 Microsoft Windows Multilingual CNWN6ML
How to setup CA11 instructions here
https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.inst_cr_winux.doc/c_settinguptheenvironment.html
https://render-prd-trops.events.ibm.com/support/pages/how-install-cognos-analytics-111x
How to setup SSO with CA instructions here
https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.inst_cr_winux.doc/t_stp_sso_active_drctry_remote_user.html
How install CA samples
https://revelwood.com/installing-samples-cognos-analytics/
More Information:
https://www.ibm.com/support/pages/how-configure-controller-web-use-cognos-cam-authentication
https://www.ibm.com/support/knowledgecenter/en/SS9S6B_10.4.1/com.ibm.swg.ba.cognos.ctrl_inst.doc/t_contrweb_configure.html
How setup SSL with Controller Web
https://render-prd-trops.events.ibm.com/support/pages/how-configure-controller-web-use-ssl-https
Product:
Cognos Controller 10.4
Microsoft Windows 2016 server
Problem:
During reporting period when using data entry, one some forms, you get a message that the sheet is protected. When you try to unprotected it in excel you are ask for a password. This is a sign that the form is corrupt and need to be created again.
Error message:
Solution:
First open the form for edit, and then select standard colors, update layout, save layout.
Exit Excel and Cognos Controller. Then test again if the data entry form works.
In some cases you need to run optimize database from inside Cognos Controller.
If above does not work, then it may be faster for you to create a new data entry form. Go to Maintain – Form Structure – Define.
Create it with a new name, you can reused the rows definition, but most likely need to manually recreate the columns definition under new name. Save it and attach it to the right groups for linked structure. If it works, remove the old not working form, and rename this to the old name (make it easier for the users to use same name as before). You need to update the linked structure again.
More information:
https://www-01.ibm.com/support/docview.wss?uid=swg21666512
https://www-01.ibm.com/support/docview.wss?uid=swg21666563
Product:
Cognos Controller 10.4
Microsoft Windows 2016 server
Oracle database 12c
Problem:
How backup and restore a oracle database (to send to cognos support).
Solution:
To make a backup run this command:
expdp SYSTEM/password@databaseServiceName schemas=FRANGO directory=data_pump_dir dumpfile=controllerlive.dmp logfile=controllerlive.log
Zip the files before sending them to cognos support.
After unzip the files, restore them using this command:
impdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=expdp_file1.dmp,expdp_file2.dmp,expdp_file3.dmp,expdp_file4.dmp,expdp_file5.dmp,expdp_file6.dmp,expdp_file7.dmp,expdp_file8.dmp logfile=log01.log SCHEMAS=FRANGO
Replace scott tiger with your user/password for your oracle database.
“data_pump_dir” should be your folder where the unzipped files are stored on your oracle server.
Change the name of the dmp file to match the filename you are using.
More information:
https://www-01.ibm.com/support/docview.wss?uid=swg21347756
https://oracle-base.com/articles/10g/oracle-data-pump-10g#DatabaseExpImp
Product:
Cognos Controller 10.4 local
Microsoft Windows 2016 server
Oracle Database 12c
Problem:
When try to run the store procedure to load data into Cognos Controller, with use of staging table, you get a error.
Error in SQL developer:
Error report –
ORA-01861: literal does not match format string
ORA-06512: at line 5
Solution:
Include formatting information in the call to the store procedure for oracle.
Inside Oracle SQL developer enter this command to execute:
xx number;
begin
— Call the function
xx := FRANGO.usp_triggerimportbatchjobs(‘1a’,’EUROIMSP’,’D’,”,’ADM’,1,to_date(‘2019-07-03′ ,’yyyy-mm-dd’));
end;
Replace FRANGO with the schema you want to update.
The 1 say it will run now. Replace 2019-07-03 with today date.
EUROIMSP is the name of the controller import specification, that should be used during the process.
Background:
Inside the cognos controller database are some tables that you use to load data into Cognos Controller.
One table is XSTAGEFACT, that is used to load data into Cognos Controller.
Before loading data into the table truncate it with this command:
TRUNCATE TABLE FRANGO.XSTAGEFACT;
(replace frango with the schema name of your database)
Prepare a csv/xls file with the same columns as in the table.
To make a easy import from file, ensure the first rows name exactly match the columns names, e.g. ST_PERIOD
Column ST_ID should be a alphanumeric identifier, in our example we use “1a”.
Column BATCH_ID should be blank, as here will Cognos Controller add the batch work number.
Use the wizard in SQL Devloper to import your data from file to the xstagefact table.
https://www.thatjeffsmith.com/archive/2012/04/how-to-import-from-excel-to-oracle-with-sql-developer/
Ensure you have “import specification” that works with a csv file first, then you need to update that (make a copy) to be ready for use with Controller Staging Tables. Two of the things you need to do, to make it work for staging tables are;
Change the general settings to “Controller Staging Table”
Go through the fields and set the to origin to “Controller Staging Table” and operation to “None”.
Save the new Controller Import Specification. https://www.ibm.com/support/knowledgecenter/en/SS9S6B_10.4.0/com.ibm.swg.ba.cognos.ctrl_web_ug.10.4.0.doc/t_defining_import_spec_web.html
Then you need to start the store procedure, for testing direct from SQL developer.
xx number;
begin
— Call the function
xx := FRANGO.usp_triggerimportbatchjobs(‘1a’,’EUROIMSP’,’D’,”,’ADM’,1,to_date(‘2019-07-03′ ,’yyyy-mm-dd’));
end;
The parameters for the usp_triggerimportbatchjobs is:
@pImpId, this is the value in the st_id column identifying the lines are to be imported.
@pImpSpec, this is the NAME of the import specification that is used for this import. This must exist in Cognos Controller.
@pImpSpecType, this is the code to identify the information that is to be loaded, it could be any of the following values: D=Data A=Account R=Currency Rates C=Company 1=Dim 1 2=Dim 2 3=Dim 3 4=Dim 4 . D means values will be read from xstagefact table.
@ImpSepcParams, this is the list of any additional parameters that need to be sent to the import spec, we leave it blank.
@CtrlUser, this is the user ID in Controller to be used for the import, in most cases ADM, even when you use CAM security.
@SchedType, this is the parameter that decides to import immediately or schedule for later date, it can include the following values: 0= Hold 1=Immediately 2=One time only. We use 1.
@ExecTime, this is the date and time for scheduled import to be carried out. The format should match the database server date format.
If you want to test run the SP again, with same data, then you need to clear the BATCH_ID column first with the command:
UPDATE FRANGO.XSTAGEFACT SET BATCH_ID = NULL;
For other database, you can use command like this to start the controller store procedure:
On the DB2 server, launch ” DB2 Command Window – Administrator”
2. Type the following command:
db2 connect to <database_name> user <username> using <password>
NOTE: <username> must be the exact same user (for example ‘fastnet’) that Controller is configured to use. In other words, the same user that is specified inside the ‘database connection’ inside Controller Configuration.
3. Type a command similar to the following:
db2 call usp_triggerimportbatchjobs(‘110′,’#ST_DATA’,’D’,”,’ADM’,1,to_date(‘2007-11-01’));
On Microsoft SQL server:
Execute the stored procedure via the following SQL Script:
EXECUTE db_owner.usp_triggerimportbatchjobs ‘<parameter1>’, ‘<parameter2>’, ….
Next step, is to make the loading of data automatic with some tool. Like Microsoft SSIS, IBM Data Stage or other tool.
Then you from that tool schedule the start of the store procedure to active the batch load of data into Controller database.
More information:
https://www-01.ibm.com/support/docview.wss?uid=swg21458934
https://www-01.ibm.com/support/docview.wss?uid=swg21370812
https://www-01.ibm.com/support/docview.wss?uid=swg21374718
https://www.ibm.com/support/knowledgecenter/en/SS9S6B_10.4.1/com.ibm.swg.ba.cognos.ctrl_ug.doc/c_ctrl_ug_expimp_importingdatafromanexternalapplication.html#ctrl_ug_expimp_ImportingDatafromanExternalApplication
https://en.wikipedia.org/wiki/SQL_Server_Integration_Services
Product:
Cognos Controller 10.4
Microsoft Windows 2016 server
Problem:
How show a nice company background picture inside the Cognos Controller client?
Solution:
Take your nice team.jpg picture and copy it to the Cognos BI webserver.
Place the file in folder C:\Program Files\ibm\cognos\analytics\webcontent\team.jpg
(that is in most cases the default webserver for CA11)
Start Internet Explorer (IE) from your computer and surf to http://ca11server.domain.com/ibmcognos/team.jpg
Did it work?
Nice, now you have the picture in place.
Now you need to go into Cognos Controller Client.
Click ‘ Maintain – Installation – Local Preferences’
Inside the field ‘Key’ enter the value: startpageurl
Inside the corresponding box ‘Values’ enter the relevant website (i.e. the one that your web designer has created for this purpose), for example: http://ca11servername.domain.com/ibmcognos/team.jpg
Please keep the picture small in pixels. You can use a online tool to make the picture less than 1024×768 pixels.
https://www.reduceimages.com/
Click save.
Close Cognos Controller client program.
Login again, and now you should have the nice background picture inside Cognos Controller.
The value is stored inside the local file C:\Users\%username%\AppData\Roaming\Cognos\ccr\ccr.config
<LocalPref>
<add key=”startpageurl” value=”http://ca11servername/ibmcognos/team.jpg” />
</LocalPref>
If you want to prevent users to have this, you enter this in Cognos Controller Client:
Logon as a super-user (administrative) account
Click ‘Maintain – Configuration – General’
Click on tab ‘Server Preference’
Inside the field ‘Variable Name’ enter the text: ENABLESTARTPAGE
Inside the corresponding box ‘Variable Value’ enter the value: F
This will prevent the use of this function for the Controller users.
More Information:
https://www-01.ibm.com/support/docview.wss?uid=swg21347128
https://www.unitconverters.net/typography/millimeter-to-pixel-x.htm