Product:
Planning Analytics 2.0.6
Planning Analytics Workspace version 36
Microsoft Windows 2016 server

Problem:
How add many users to the PAW?

Solution:
Ensure that SSO and CAM security is working in TM1 (planning analytics).
Check the domain name in Cognos Configuration, this need to be enter in the file.
If the cognos namespace ID is Pacman, then it must be part of the file. It is case sensitive.
Open notepad (or excel) and enter the names like this:
Pacman/Donald Duck,Donald,Duck,administrator,donald.duck@pacman.com,active
Pacman/Daisy Duck,Daisy,Duck,analyst,daisy.duck@pacman.com,active

Save the file as a comma separated list.
Surf to your PAW installation.
Click on Administration
Click on Users
Click on upload users

Click on the icon of the file to browse for the file.
Select and Open the file
Now the users should be added to your PAW, so they can start working.
Please use this to add new administrators, direct after your first setup of PAW.

One user per line, and you must separate columns with comma. Should contain Login ID, First Name, Last Name, Role, Email, Status. Only the 3 first are required.

More information:
https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_prism_gs.2.0.0.doc/t_paw_add_users_locally.html

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

https://www.ibm.com/support/pages/blank-empty-white-screen-when-launch-controller-web-inside-internet-explorer-typically-directly-application-server-caused-using-compatibility-view

How setup SSL with Controller Web

https://render-prd-trops.events.ibm.com/support/pages/how-configure-controller-web-use-ssl-https

Product:
Cognos Command Center 10.2.4 (ICCCStarterEdition.10.2.40100.20.exe)
Microsoft Windows 2012 R2

Problem:
How install Cognos Command Center? This program should be installed on a central server from everything is controlled. You then install Cognos Command Center Agent on your TM1 servers you want to control.

Preparation:
Download the Command Center software from https://www-01.ibm.com/support/docview.wss?uid=ibm10715631
Download NET Framework 4.7.1 from https://www.microsoft.com/en-us/download/details.aspx?id=56116
Install NET framework 4.7.1 on all your servers.
Setup a Microsoft SQL database to hold the information the system uses. https://www.microsoft.com/en-us/download/details.aspx?id=30438
Create a empty database (CCC_LOCAL), and a SQL user e.g. ccc and a password. That have full access to the database (db_owner).
IIS need to be installed and running, on your central command center server, before start of the installation.

Installation on central server:
The Starter Edition is free to use together with Planning Analytics license, check with you software partner.
Login to the server as a administrator and start the installation from the exe file.
Click next and accept any license information dialogs.
Select all components, on the central server, and click next.
Click next on the destination folder.
Select the database type and enter the information needed to access the database.
Keep the default values for communication to the central server.

You must enter a password to the admin user for the commando server.

You must enter a password to the admin for the agent on the server.

You must enter a password to the web user for the web server.

Note down all user and passwords on paper.
Press on to install.

When installation is done, you should have a lot of tables in the database.

 

Installation of agent (client) on TM1 server:
Before you install an IBM Cognos Command Center agent on a Microsoft Windows operating system, create a dedicated local Windows account. Name the account cccAgent and run the IBM Cognos Command Center Agent service under this account.


Click Next to start the installation.

Accept the license and click next.


Select Agent and click next.

Click Next, and use default folder for Command Center.

Enter password and click next.

Click next.

Click Install, to start installation of the agent.
When done, you add the agent to your central command center.

Start Cognos Command Center Client, and login with Default user.

Then add a new agent, by right click on Agents and select new.

Fill in the server name, and the user and password.
Click on Test the Agent.

Now you have connection with your first agent. Install and add agents on all your Tm1 servers.

Ecosystem:
Next step are to create a ecosystem, right click ecosystem and select new. Enter a name, could be your company name.

Enter name for your Environments, you maybe have a DEV, TEST and PROD setup with similar servers.

Enter global variables, you will use in your scripts, here – like a folder where your scripts will run e.g. c:\temp\ccc

On Computing resources, you add the servers as HOST – select the agent that represent your server. When you run a script on PROD, it will be run by TM1Agent42 and when you select DEV it will run on your local host. Setup more hosts, and name them different like TM1WEB, 2016_Server and so one. Home directory is where you have run your Command Center scripts.

To send a mail notification if a Command Center process have succeeded or not, you first need to setup SMTP information in Command Center System Configuration.  From inside the Command Center Client program go to menu Tools – System Configuration.

Enter mail server and port, and your credentials to access the mail server.

Create a new process, name it something, like “check log files”, now you can add the different task to it. Click on blue ADD TASK icon in left top corner.

Each process consist of many task, the last one is the Email Notification. Here you mark if a mail should go out if the process fail.

The email notification is a simple way to send status of the Command Center process to the Administrator.

 

 

After all software is installed, you should update to latest service pack to get the security fixes for java.
https://www-01.ibm.com/support/docview.wss?uid=ibm10886239
More information:
https://www-01.ibm.com/support/docview.wss?uid=swg21983018
https://www.ibm.com/support/knowledgecenter/SSPLNP_10.2.4/com.ibm.swg.ba.cognos.ag_ccc.10.2.4.doc/c_ig_ccc_proc_ovrvw.html

https://www-01.ibm.com/support/docview.wss?uid=swg27049429
https://www.youtube.com/watch?v=fMC6GwX05eE

More about TM1 logging:
https://www.element61.be/en/resource/advanced-logging-ibm-planning-analytics-tm1

Product:
Planning Analytics 2.0.6
Cognos Controller 10.4 FAP
Microsoft Windows 2016 server

Problem:
How export FAP cube data to csv file in the FTD format?

https://www.ibm.com/support/knowledgecenter/SS9S6B_10.4.1/com.ibm.swg.ba.cognos.ctrl_ug.doc/c_wp0041.html

Suggested Solution:
The Cognos Controller standard export of data to file, from inside the cognos controller client, menu transfer – export data, create files of the cognos controller data. It will export the data you select, commonly limit by period and group/company.
https://www.ibm.com/support/knowledgecenter/en/SS9S6B_10.4.0/com.ibm.swg.ba.cognos.ctrl_ug.10.4.0.doc/c_e1k15_2_exportingdata.html#E1K15_2_ExportingData

You can get the output in unencrypted ASCII format, to import the data to other systems (e.g. SAP).
The output file format is similar to the columns of the XDB tables in the Cognos Controller database.

There is one XDB table for each year (xdb00-xdb99). These tables contain the period values. The amount is stored in xdb.belopp. The transaction amount is stored in xdb.trbelopp. These amounts are accumulated year to date value. The field vtyp is used to tell how data has been entered into the system.

XDBxx
Perakt Period + Actuality
Bol Company
Vkod Currency Code
Konto Account
Dim1 Extended Dimension 1
Dim2 Extended Dimension 2
Dim3 Extended Dimension 3
Dim4 Extended Dimension 4
Btyp Journal type
Etyp Automatic Journal type
Ktypkonc Consolidation Type + Group
Vernr Journal Number
Motbol Counter Company
Ursbol Original Company
Travkd Transaction Currency Code
Motdim Counter Dimension
Belopp Amount
Trbelopp Amount in Transaction Currency
vtyp Currency Type (G=Group Currency, C=Converted Currency, K=Consolidated)
ino Unique record identifier
HID “Happening id” = Functional area that created the record.
Null indicates unknown.
PDF “Prepared for delete” = Other value than 0 only while Controller is processing data.

 

The FAP service, will copy data from the Cognos Controller database to a cube in your TM1 (Planning Analytics) installation. Here doing some adjustment to the structure to make it work in TM1. You must have the FAP service working before you can export any data.

Start TM1 Architect, and check the cubes that your Cognos Controller FAP solution have created.

Create a TM1 TI process to export data from a view of the cube FAPMonthlyCompanyDetails (this cube is mostly used to export period values). Use a view that take all dimensions, and then edit the TI process to select only the dimensions element you need in your export ( for example only for a specific company ). Change the variable names to more understandable names, as shown in below picture.

Set all to be “string” and Contents to be “other” in the variables tab of the TI process.
Create parameters to make it possible for the user who run the TI process to select period and actuality to limit the output.


In our example we will hard-code the company that is exported.

In PROLOG tab enter code similar to this (you need to updated the script for your data):

#****Begin: Generated Statements***
#****End: Generated Statements****

#————————————————————————————————
# one single process to create view and export data to csv file
# for export from controller fap cube FAPMonthlyCompanyDetails
# updated date: 2019-08-12
# ———————————————————————————————–

# specify the location of the file – .. moves up one step from starting folder for tm1 data
sFilepath = ‘..\temp\’;

# specifiy the file name
sFileName= ‘scriptview.txt’;

# check that it is correct length
vcheck = LONG ( numbertostring ( pPeriodMonthly ) ) ;
if ( vcheck <> 6 ) ;
textoutput (‘..\temp\debug1.txt’, ‘prolog break – wrong date’);
ProcessBreak;
endif;
sPeriod = SUBST ( numbertostring (pPeriodMonthly) , 3,4);

# add the period to the file name
# check if a period is in the filename, if not add FTD at end of filename
nFileName = SCAN ( ‘.’ , pFileName );
if (LONG(pFileName) < 20);
if (nFileName <> 0 );
sFileName = sPeriod | pFileName ;
else;
sFileName = sPeriod | pFileName | ‘.FTD’;
Endif;
Endif;

# create the full file name including path
sFileName = sFilePath | sFileName;

# ————- create a view with script —————————————
# pCube: Specify cube name
# ——————————————————-
pCube =’FAPMonthlyCompanyDetails’;

#pDim1: Specifiy first dimension name – region = country code
pDim1=’Region’;
#pElement1: Specifiy element name for pDim1
pElement1=’@Aggr_@TOT’;

#pDim2: Specifiy second dimension name
pDim2=’Counter Dimension’;
#pElement2: Specifiy element name for pDim2
pElement2=’@Aggr_@TOT’;

#pDim3: Specifiy second dimension name
pDim3=’Activity’;
#pElement3: Specifiy element name for pDim3
pElement3=’@Aggr_@TOT’;

pDim4=’Currency’;
pElement4=’EUR’;

pDim5=’Transaction Currency’;
pElement5=’@none’;
pDim6=’Consolidation Perspective’;
pElement6=’@none’;
pDim7=’Closing Version’;
pElement7=’CL’;
pDim8=’Contribution Version’;
pElement8=’BASE’;

pDim9=’Account’;
pElement9=’1100′;

pDim10=’ActualityMonthly’;
# check that it is correct length
vcheck = LONG ( ( pActualityMonthly ) ) ;
if ( vcheck <> 2 ) ;
textoutput (‘..\temp\debug2.txt’, ‘prolog break – wrong ActualityMonthly’);
ProcessBreak;
endif;
pElement10= pActualityMonthly ;

#  — we list all variables here – but use them not in the code – so you can remove some of the rows
pDim11=’Origin Company’;
pElement11=’AllOrigComp’;
pDim12=’Counter Company’;
pElement12=’AllCounterComp’;

pDim13=’Company’;
pElement13=’4526′;

pDim14=’Journal Number’;
pElement14=’AllJournals’;

pDim15=’PeriodMonthly’;
# format should be this
# pElement15=’201907′;
#
# add it to the period from parameter
pElement15= numbertostring( pPeriodMonthly ) ;

pDim16=’Measures’;
pElement16=’Monthly’;

#————————————————————————————————-
# set the name of the subset and view
# are time stamped to prevent collision when several users run process at same time
#————————————————————————————————–
sView = ‘FAP_Export_’ | TIMST(NOW(), ‘\Y\m\d_\h\i’, 1) ;
sSubset = sView;

#—————————————————————————
# not all dimensions need to be specified in a subset view
# remark ViewSubsetAssign line to show all elements for that dimension
#—————————————————————————

# Step 1: create View
If( ViewExists( pCube, sView ) = 1 );
ViewDestroy( pCube, sView );
EndIf;
ViewCreate( pCube, sView );

# Step 2: create first subset
If( SubsetExists( pDim1, sSubset ) = 1 );
SubsetDeleteAllElements( pDim1, sSubset );
Else;
SubsetCreate( pDim1, sSubset );
EndIf;
# Step 3: add the first element to dimension subset
SubsetElementInsert( pDim1, sSubset, pElement1, 1 );

# ————— do not include region subset (first variable) by remark out ViewSubsetAssign ——————–
# Step 4: Assigned the subset to view
## ViewSubsetAssign( pCube, sView, pDim1, sSubset );

# Step 5: create second subset and assigned it to view if needed
If( SubsetExists( pDim2, sSubset ) = 1 );
SubsetDeleteAllElements( pDim2, sSubset );
Else;
SubsetCreate( pDim2, sSubset );
EndIf;
SubsetElementInsert( pDim2, sSubset, pElement2, 1 );

# Step 6: Assigned the subset to view
## ViewSubsetAssign( pCube, sView, pDim2, sSubset );

If( SubsetExists( pDim10, sSubset ) = 1 );
SubsetDeleteAllElements( pDim10, sSubset );
Else;
SubsetCreate( pDim10, sSubset );
EndIf;
SubsetElementInsert( pDim10, sSubset, pElement10, 1 );
# for ActualityMonthly – this is already selected in FAP export program
# for the export vi only run one at a time

ViewSubsetAssign( pCube, sView, pDim10, sSubset );

#————————————————————————————————-
# defined a limit number of company — we try with a MDX
#————————————————————————————————-
If( SubsetExists( pDim13, sSubset ) = 1 );
SubsetDeleteAllElements( pDim13, sSubset );
SubsetDestroy ( pDim13, sSubset );

Else;
## should not be created as the subsetcreatebymdx demands that the subset does not exists
## SubsetCreate( pDim13, sSubset );
EndIf;
## SubsetElementInsert( pDim13, sSubset, pElement13, 1 );

# —  improve code in future by use variables for repeated values.
pAlias = ‘Company Code’;

# —————————————————————————————————–
# we want to export only companies that have STO, ST1, ST2… ST7 in there name.
# mdx filter to include some groups by search for letters in name
# use this format { FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [MyDim] )}, 0)}, INSTR([MyDim].[MyAlias], “SubString” ) > 0 ) }

# —————————————————————————————————–

StringMDX = ‘{ FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [ ‘ | pDim13 | ‘ ] )}, 0)}, ( INSTR([ ‘ | pDim13 | ‘ ].[ Company Code ], “STO” ) > 0 ) OR ( INSTR([ ‘ | pDim13 | ‘ ].[ Company Code ], “ST1” ) > 0 ) OR ( INSTR([ ‘ | pDim13 | ‘ ].[ Company Code ], “ST2” ) > 0 ) OR ( INSTR([ ‘ | pDim13 | ‘ ].[ Company Code ], “ST3” ) > 0 ) OR ( INSTR([ ‘ | pDim13 | ‘ ].[ Company Code ], “ST4” ) > 0 ) OR ( INSTR([ ‘ | pDim13 | ‘ ].[ Company Code ], “ST5” ) > 0 ) OR ( INSTR([ ‘ | pDim13 | ‘ ].[ Company Code ], “ST6” ) > 0 ) OR ( INSTR([ ‘ | pDim13 | ‘ ].[ Company Code ], “ST7″ ) > 0 ) ) } ‘;

# next step – you need to modify this for your company structure in your cognos controller data

# ——————————————————————————

# apply the MDX to the dimension
SubsetCreatebyMDX( sSubset ,StringMDX);

# add subset to view
ViewSubsetAssign( pCube, sView, pDim13, sSubset );

If( SubsetExists( pDim15, sSubset ) = 1 );
SubsetDeleteAllElements( pDim15, sSubset );
Else;
SubsetCreate( pDim15, sSubset );
EndIf;
SubsetElementInsert( pDim15, sSubset, pElement15, 1 );
# this is PeriodMonthly that should be selected
ViewSubsetAssign( pCube, sView, pDim15, sSubset );

If( SubsetExists( pDim16, sSubset ) = 1 );
SubsetDeleteAllElements( pDim16, sSubset );
Else;
SubsetCreate( pDim16, sSubset );
EndIf;
SubsetElementInsert( pDim16, sSubset, pElement16, 1 );
# this is the Measures that should be same month
ViewSubsetAssign( pCube, sView, pDim16, sSubset );

# IMPORTANT: set view parameters
# Specify 1 to exclude consolidated values from the view extract. Specify 0 to include consolidated values.
ViewExtractSkipCalcsSet( pCube, sView, 1 );
ViewExtractSkipZeroesSet( pCube, sView, 1 );

# change the subset view to be used by the TI process to be the one you have defined above
DATASOURCECUBEVIEW = sView;

 

In METADATA tab enter code similar to this (you need to updated it for your data):

#****Begin: Generated Statements***
#****End: Generated Statements****

# add below to remove ” from output file
DatasourceASCIIQuoteCharacter = ”;
DatasourceASCIIDelimiter=’;’;

# print the first column as a value of two columns
sFirst = subst ( vPeriodMonthly ,3,4) | ( vActualityMonthly );

# debug print out to show all values in original order
#  we hard code the last columns in the FTD file to 000000 and L.
textoutput (‘..\temp\debug3.txt’, vPeriodMonthly , vActualityMonthly , vCompany , vCurrency , vAccount , vRegion , vActivity , vCounterDimension , vTransactionCurrency ,
vConsolidationPerspective , vClosingVersion , vContributionVersion , vOriginCompany ,
vCounterCompany , vJournalNumber , vMeasures , vValue , ‘0,000000’ , ‘L’ );

# format to fixed decimals
nResult1 = SCAN ( ‘,’ , vValue ) ;
if (nResult1 = 0) ;
sResult1 = vValue | ‘,000000’ ;
else;
# values with more than 6 decimal values is rounded up — this can affect the result.
vValue = numbertostring( ROUNDP( stringtonumber(vValue) , 6) ) ;
nResult1a = 6 – ( LONG (vValue) – nResult1) ;
## textoutput (‘..\temp\scriptview3debug2.txt’, numbertostring (nResult2), vValue);
sResult1 = FILL (‘0’, nResult1a) ;
## textoutput (‘..\temp\scriptview3debug2.txt’, sResult1 );
sResult1 = vValue | sResult1 ;
endif;

# add spaces to name so we have a fixed with column
sResult2 = FILL(‘ ‘, 6 – LONG (vOriginCompany));
sResult2 = vOriginCompany | sResult2 ;

# set to blank if specific values e.g. values that start with @
# the FTD file often contain blank values for middle columns
if ( vCounterDimension @= ‘@TOT’);
vCounterDimension = ”;
endif;

if ( vTransactionCurrency @= ‘@None’);
vTransactionCurrency = ”;
endif;

if ( vConsolidationPerspective @= ‘@None’);
vConsolidationPerspective = ”;
endif;

if ( vClosingVersion @= ‘REPOBT’);
vClosingVersion = ”;
endif;

if ( vContributionVersion @= ‘BASEET’);
vContributionVersion = ”;
endif;

if ( vJournalNumber @= ‘0_’);
vJournalNumber = ‘0’;
endif;

if ( vCounterCompany @= ‘@None’);
vCounterCompany = ”;
endif;

if ( vMeasures @= ‘Monthly’);
vMeasures = ”;
endif;

# set to space if it is blank in cell
if ( vTransactionCurrency @= ”);
vTransactionCurrency = ‘ ‘;
endif;

#———————————————————————————
# change the formatting to match that of a FTD file
# ———————————————————————————
slongline = char(34) | sFirst | char(34) | char(59) | char(34) | vCompany | char(34) | char(59) | char(34) |
vCurrency | char(34) | char(59) | char(34) |
vAccount | char(34) | char(59) | char(34) | vRegion | char(34) | char(59) | char(34) | vActivity | char(34) | char(59) | char(34) |
” | char(34) | char(59) | char(34) | ” | char(34) | char(59) | char(34) | vClosingVersion | char(34) | char(59) | char(34) |
vContributionVersion | char(34) | char(59) | char(34) |
vMeasures | char(34) | char(59) |
vJournalNumber | char(59) | char(34) | vCounterCompany | char(34) | char(59) | char(34) |
sResult2 | char(34) | char(59) | char(34) | vTransactionCurrency | char(34) | char(59) | char(34) | vCounterDimension | char(34) | char(59) | sResult1
| char(59) | ‘0,000000’ | char(59) | char(34) | ‘L’ | char(34) ;

textoutput ( sFileName , slongline);

In EPILOG tab enter code similar to this:

#****Begin: Generated Statements***
#****End: Generated Statements****

if (pView @= ‘Y’) ;

# remove the view
ViewDestroy( pCube, sView );

# destroy all subsets
i = 1;
# loop through all dimensions of the cube
while (tabdim(pCube,i) @<> ”);
SubsetDestroy(tabdim(pCube,i), sSubset);
i = i + 1;
end;
endif;

# ======= end of code =========

There is better and nicer ways of doing the export of data from a TM1 cube, but this is only to show an example of it. Keep in mind that the FAP cube is rebuild at every IP (Initial publish) from the FAP service. So you must recreate the views for the export, therefor use a TI script to do it.  You need to change above code examples to work in your setup of Cognos Controller FAP. This is only a starting point.

The output file should have a format like this, to be like FTD:

“1908F6″;”STOZYX”;”EUR”;”5600″;”AT”;”AO”;””;””;””;””;””;0;””;”STOZYX”;” “;””;123,560000;0,000000;”L”
“1908F6″;”STOZYX”;”EUR”;”5900″;”AT”;”AO”;””;””;””;””;””;0;””;”STOZYX”;” “;””;789,000000;0,000000;”L”
…..

Use this tool to make your work in TM1 Architect easier: https://code.cubewise.com/ti-helper

More information on MDX

http://www.wimgielis.com/tm1_neatmdxtricks_EN.htm

TM1 MDX FILTER BY SUBSTRING

http://www.wimgielis.com/tm1_mdxstatements_EN.htm

More information on FAP

https://www.ibm.com/support/knowledgecenter/en/SS9S6B_10.3.1/com.ibm.swg.ba.cognos.ctrl_fap_ug.10.3.1.doc/c_appendixa.html

https://www.ibm.com/support/knowledgecenter/en/SS9S6B_10.3.1/com.ibm.swg.ba.cognos.ctrl_fap_ug.10.3.1.doc/c_fapcubes.html

https://ibm-data-and-ai.ideas.aha.io/ideas/BACC-I-147

https://www-01.ibm.com/support/docview.wss?uid=swg21661294

Product:
Planning Analytics 2.0.6
Microsoft Windows 2016 server

Problem:
The vValue need to have 6 decimals in the output and the string vOrigincompany need to have fixed width of 6 characters.

Solution:
In TM1 write TI code like this:

# format to fixed length of 6 decimals
nResult1 = SCAN ( ‘,’ , vValue ) ;
if (nResult1 = 0) ;
sResult1 = vValue | ‘,000000’ ;
else;
# values with more than 6 decimal values is rounded up
vValue = numbertostring( ROUNDP( stringtonumber(vValue) , 6) ) ;
nResult1a = 6 – ( LONG (vValue) – nResult1) ;
sResult1 = FILL (‘0’, nResult1a) ;
sResult1 = vValue | sResult1 ;
endif;

# add spaces to name so we have a fixed width column
sResult2 = FILL(‘ ‘, 6 – LONG (vOriginCompany));
sResult2 = vOriginCompany | sResult2 ;

# There exist other ways to solve this, but this is a suggestion.

textoutput ( ‘c:\temp\filename.txt’ , sResult1 , sResult2 );

More information:
https://exploringtm1.com/fixed-width-file-format-from-tm1/

Product:
Planning Analytics 2.0.6
Microsoft Windows 2016 server

Problem:
How to remove temporary views in TI code?

Solution:

Use a parameter in the TI process, to ask if the views should be removed. pView will have the value Y if they should be removed.

Create the views in Prolog

# pCube: Specify cube name
pCube =’FAPMonthlyCompanyDetails’;

# set the name of the subset and view
# with a time stamped
sView = ‘FAP_Export_’ | TIMST(NOW(), ‘\Y\m\d_\h\i’, 1) ;
sSubset = sView;

Use the views in your code

# Step 1: create View
If( ViewExists( pCube, sView ) = 1 );
ViewDestroy( pCube, sView );
EndIf;
ViewCreate( pCube, sView );

# Step 2: create first subset
If( SubsetExists( pDim1, sSubset ) = 1 );
SubsetDeleteAllElements( pDim1, sSubset );
Else;
SubsetCreate( pDim1, sSubset );
EndIf;

SubsetElementInsert( pDim1, sSubset, pElement1, 1 );

remove the views in Epilog

if (pView @= ‘Y’) ;

# remove the view
ViewDestroy( pCube, sView );

# destroy all subsets
i = 1;
# loop through all dimensions of the cube
while (tabdim(pCube,i) @<> ”);
SubsetDestroy(tabdim(pCube,i), sSubset);
i = i + 1;
end;

endif;

More information:
https://www.bihints.com/creating_temporary_views

http://cognosknowhow.blogspot.com/2014/07/how-to-create-tm1-view-using-ti-script.html

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

https://picresize.com/