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:
Planning Analytics 2.0.5
Microsoft Windows server 2016

Problem:
When open the tm1server.log file, the time is 2 hour off the local time or so. This is because default all tm1 logging is done in GMT time.

Solution:
Create a tm1s-log.properties file with below content and place it in same folder as your tm1s.cfg file.

#
# Enable INFO level logging through the shared memory appender, by default. The server # will write informational messages, as well as errors and warnings to the log file.
#

log4j.logger.TM1=INFO, S1

# S1 is set to be a SharedMemoryAppender
log4j.appender.S1=org.apache.log4j.SharedMemoryAppender
# Specify the size of the shared memory segment
log4j.appender.S1.MemorySize=5 MB
# Specify the max filesize
log4j.appender.S1.MaxFileSize=100 MB
# Specify the max backup index
log4j.appender.S1.MaxBackupIndex=20
# Specify GMT or Local timezone
log4j.appender.S1.TimeZone=Local
# adds info about process locks to the tm1 server log file
Log4j.logger.TM1.Lock.Exception=DEBUG

More Information:

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

http://www.tquate.com/blog/configuring-and-enabling-logging-tm1/
https://exploringtm1.com/changing-tm1-server-log-time-zone-stamp/
https://www-01.ibm.com/support/docview.wss?uid=swg21459102

Product:
Planning Analytics 2.0.5
Microsoft Windows 2016 Server

Problem:
How move NTFS and File Share security to a new server? We have shared a MANUALINPUT folder on the TM1 server, where selected users can update text files with data. This csv file are read into the TM1 application with use of a TI process. Now we setup a bigger Windows server and want to move over the windows security setup.

Suggested solution:
Test this steps in a test server first, to ensure they work in your environment.

You have created the folder d:\tm1app\manualinput
Login on your old TM1 server and open a command prompt as administrator.
Enter this command to save the security:
icacls d:\tm1app\manualinput /save ntfspermissions.txt /c
Copy the txt file over to the new PAL server
Enter this to restore the security:
icacls d:\tm1app /restore ntfspermissions.txt /c

/t parameter to icacls will include all subfolders in the security file.

The user that have access to the file share is stored in the registry, so on your old TM1 server you need to open REGEDIT.

Expand Regedit to the following location:
HKLM\SYSTEM\CurrentControlSet\Services\LanmanServer\Shares
Right-click the Shares registry key and select Export. Enter a file name like sharepermissions.reg.
Copy the reg file to the new PAL server.
Open the reg file in notepad, to ensure you only got the SHARES you want to be there.
If there are extra shares you do not want on the new server, erase this lines and save the file.

Open Regedit and go to HKLM\SYSTEM\CurrentControlSet\Services\LanmanServer\Shares
Right-click the Shares registry key and select Export. Enter a file name like backuppermissions.reg. This to have a backup of the settings in registry before you apply the new values.
Double click on the reg file from the other server, to import the values.

After the file is imported, you have to reboot the windows server to make the change take affect.

Note that the servers need to be in the same domain, for this to work, so the users are the same in both servers.

More information:
https://www.itechtics.com/backup-restore-ntfs-permissions/
http://woshub.com/how-to-backup-and-restore-ntfs-permissions-using-icacls/

Product:
Planning Analytics Workspace 39
Linux Centos (similar to Red Hat)

Problem:
How upgrade PAW to version 39?

Solution:
https://cubewise.com/blog/planning-analytics-workspace-installation-guide/
Download the latest version of PAW from here
https://www-01.ibm.com/support/docview.wss?uid=swg27049597

Backup
Backup will restart the services.
Login to the Linux server with PUTTY
Change to the docker user with command sudo su – dockeruser
go to the paw folder, in our example that is /ibm/paw
cd /ibm/paw

go to the scripts folder, and run the backup script
cd scripts
./backup.sh

After the backup is done, the PAW should work as before.

The backup is stored in folders under /backup as shown above.

Create folders

To be able to change owner of file you need to be root, change to root user with command:
su –

Create the new folder for paw39 with command mkdir /ibm/paw39
Start WINSCP to copy the file to your linux server from windows.

https://winscp.net/eng/download.php
Move to the folder where the ipa file is stored, and to the folder where you want it. Drag the file over in the WINSCP program.

Set the rights for the file in the WINSCP program, so the others have access to the file.

Go back into PUTTY.

Go to the folder and unzip the file with command:
unzip ipa_workspace_local_2.0.39.1695.1.zip  -d /ibm/paw39

Go into the folder /ibm/paw39 and set the owner of all the files in the folder and sub-folders with this command
chown -R  dockeruser:docker ./*

Copy config files from old installation

Copy the <paw_install_location>/config/paw.env file from your current installation to the new
installation folder.
cd /ibm/paw/config
cp /ibm/paw/config/paw.env   /ibm/paw39/config/paw.env

Copy the certs folders files
cp /ibm/paw/config/certs/*.* /ibm/paw39/config/certs/

Copy the pa-workspace.pem file to the new folder
cd /ibm/paw39/config
mv pa-workspace.pem pa-org-workspace.pem
cp /ibm/paw/config/pa-workspace.pem /ibm/paw39/config/pa-workspace.pem

Copy the privatekey.pem to the new folder
cd /ibm/paw/
cp privatekey.pem /ibm/paw39/privatekey.pem

The files you copy depend on your installation.
Stop PAW services
Go to the scripts folder and enter command:
./paw.sh stop

Check that all is stopped with command:
docker ps

Rename folders

mv paw paw35
mv paw39 paw

Start paw
Change to dockeruser with command
sudo su – dockeruser

Update the paw.env file with the ip address that should be used

nano -w paw.env
export ADMINTOOL_IP = 192.168.1.29

Update the Start.sh file with the path to the docker-compose folder if it is not accessible as default

nano -w Start.sh
export PATH=$PATH:/ibm/comp/

Enter ctrl+o to save the file, press enter.
Enter ctrl+x to exit nano text editor.

Enter command below in PAW folder to start paw upgrade.

./Start.sh

Press Y to upgrade
Press Y to start IBM Planning Analytics Workspace Administration Tool.

Scroll down and and accept the IBM License and Non-IBM License to continue.

Check that the TM1 Application Server Gateway URI and the other values is correct, and press Validate.

Click on Status tab and click on Restart button.  Wait until all is started and test that it is working.

Stop the Administration Tool from PUTTY before you leave the Linux server.

To go into the linux container to run ping from inside the docker, enter this command:
docker exec -ti admintool bash

Install the new Agent

https://www.youtube.com/watch?v=Nel5Ovh0-7Q&list=PLfq0ST5X3p-QfZoNXSkDCP-zyblxXmzMZ&index=36

Surf to you new Planning Analytics Workspace and login. If you are administrator there is a new icon to administrate the TM1 instances. Click on the icon.

On the left lower corner you can see the version of PA Agent at your server. If you run TM1_version=TM1-AW64-ML-RTM-11.0.6.71-0 TM1_name=IBM Cognos TM1, you have agent 10.0.36.736.
Click on the download link to download the PA Agent that ships with PAW 39 to your computer.

https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_prism_gs.2.0.0.doc/t_paw_download_paa_agent.html

Click on Download and save the file to a new folder on your computer.

Copy the file to a folder on the PAL server and unzip it. Open a CMD window as administrator, and go to the folder where you have the new PAA Agent files. Enter this command to install the new agent:

UpdatePAAAgent.bat  “c:\program files\ibm\cognos\tm1_64\”

You need to add the path to your TM1 instances folders in the bootstrap.properties file, the TM1 samples work as they are already added at default.

To make mail notifications to work, you need to update this lines in above file;

SMTP_EMAIL_PORT=587
SMTP_EMAIL_AUTH=true
SMTP_EMAIL_HOST=example.com
SMTP_EMAIL_USERNAME=user@example.com
SMTP_EMAIL_PASSWORD=Analytics123
PAA_EMAIL_ADDRESS=noreply@example.com

Enter values for your SMTP server, also update the PAA_EMAIL_ADDRESS ( ) field.
Restart the “IBM Planning Analytics Administration Agent” to make the changes take affect.

More information:
How set a static ip address on Linux
http://www.mustbegeek.com/configure-static-ip-address-in-centos/

https://www.cyberciti.biz/faq/howto-setting-rhel7-centos-7-static-ip-configuration/

https://www.techrepublic.com/article/how-to-configure-a-static-ip-address-in-centos-7/

https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_prism_gs.2.0.0.doc/c_install_paa_local_configure_event_notifications.html

How install docker on Linux
https://docs.docker.com/install/linux/docker-ce/centos/
To Install required packages before docker, enter this:
sudo yum install -y yum-utils device-mapper-persistent-data lvm2
sudo yum-config-manager –add-repo https://download.docker.com/linux/centos/docker-ce.repo

To download the latest version of docker download this:
sudo yum install docker-ce docker-ce-cli containerd.io

To start and make docker stay started after reboot enter this:
systemctl start docker
systemctl enable docker

To download docker-compose enter this exact command:
sudo curl -L “https://github.com/docker/compose/releases/download/1.23.2/docker-compose-$(uname -s)-$(uname -m)” -o /usr/local/bin/docker-compose

To change to execute, for this file:
sudo chmod +x /usr/local/bin/docker-compose

Check the version, with this command, to see that it works:
docker-compose version

Product:
Planning Analytics Workspace version 38
Planning Analytics 2.0.5
Linux Red Hat 7

Problem:
A security scan show that the PAW on port 443 try to use old ciphers sets.

The server is configured to support ciphers known as static key ciphers. These ciphers don’t support “Forward Secrecy”. In the new
specification for HTTP/2, these ciphers have been blacklisted.
Negotiated with the following insecure cipher suites:
TLS 1.1 ciphers:
TLS_RSA_WITH_AES_128_CBC_SHA
TLS_RSA_WITH_AES_256_CBC_SHA
TLS_RSA_WITH_CAMELLIA_128_CBC_SHA
TLS_RSA_WITH_CAMELLIA_256_CBC_SHA
TLS 1.2 ciphers:
TLS_RSA_WITH_AES_128_CBC_SHA
TLS_RSA_WITH_AES_128_CBC_SHA256
TLS_RSA_WITH_AES_128_GCM_SHA256
TLS_RSA_WITH_AES_256_CBC_SHA
TLS_RSA_WITH_AES_256_CBC_SHA256
TLS_RSA_WITH_AES_256_GCM_SHA384
TLS_RSA_WITH_CAMELLIA_128_CBC_SHA
TLS_RSA_WITH_CAMELLIA_256_CBC_SHA

Possible Solution:
Configure the server to disable support for static key cipher suites.

Login to the PAW Linux server with PUTTY.
Change to the user who have access to the paw folder (e.g. dockeruser).
Go to the /ibm/paw/config folder.
Check the content of defaults.env with the command  more defaults.env  to see the current used values.
Open paw.env file with command  nano paw.env
Add below two rows;
export SSLCipherSuite=”ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSAAES256-
GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256″
export SSLProtocol=”all -SSLv2 -SSLv3 -TLSv1 -TLSv1.1″
Save the file with ctrl+o
Exit nano program with ctrl+x
Restart the PAW server.

Check this link for what values to set in SSLCipherSuite:
https://mozilla.github.io/server-side-tls/ssl-config-generator/
If it does not work, revert back to the original settings shown below, by simple remove the two lines from the paw.env file.

export SSLProtocol=”all -SSLv2 -SSLv3 -TLSv1 -TLSv1.1″
above means all protocols except the one listed after the minus, so no SSLv2 or SSLv3 or TLSv1 or TLSv1.1, leaving only TLSv1.2 to show.

The way you created the certificate request when you setup SSL(TLS) for PAW, can affect the ciphers you can use.

More Information:
https://electricenergyonline.com/print_article.php?ID=779

http://www.nist.gov/manuscript-publication-search.cfm?pub_id=915295
https://wiki.mozilla.org/Security/Server_Side_TLS
http://support.microsoft.com/kb/245030/
https://tools.ietf.org/html/rfc7540/

https://github.com/OWASP/CheatSheetSeries/blob/master/cheatsheets/Transport_Layer_Protection_Cheat_Sheet.md

Product:
Planning Analytics 2.0.5
Microsoft Windows 2016 server

Problem:
A security audit list that the access to TM1 Admin service is not enough secure. Port 5498 and 5898 show this;
Negotiated with the following insecure cipher suites:
TLS 1.0 ciphers:
TLS_RSA_WITH_3DES_EDE_CBC_SHA
TLS_RSA_WITH_AES_128_CBC_SHA
TLS_RSA_WITH_AES_256_CBC_SHA
TLS 1.1 ciphers:
TLS_RSA_WITH_3DES_EDE_CBC_SHA
TLS_RSA_WITH_AES_128_CBC_SHA
TLS_RSA_WITH_AES_256_CBC_SHA

Vulnerability Solution:
Enable support for at least one of the ciphers listed below:
TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256
TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256
TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384
TLS_DHE_RSA_WITH_AES_128_GCM_SHA256
TLS_DHE_DSS_WITH_AES_128_GCM_SHA256
TLS_DHE_DSS_WITH_AES_256_GCM_SHA384
TLS_DHE_RSA_WITH_AES_256_GCM_SHA384

Possible solution:
Inside the Cognos Configuration the TM1 Admin server value for Support pre-TLS v1.2 clients are set to TRUE, this should be set to False to solve above issue.

Change to FALSE,
Save settings.
At the restart of the TM1 Admin Server, all the TM1 running instances will be restarted, this can take time. Do this at a planned date.

More information about ports:
http://www.practicallynetworked.com/sharing/app_port_list.htm
http://www.networksorcery.com/enp/protocol/ip/ports04000.htm

Ports used by CA11
https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.inst_cr_winux.doc/c_reviewthedefaultsettings.html

Ports used by PAL
https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/c_tm1_inst_defaultvaluesfortm1installation.html

 

https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/c_configuringthetm1adminservertousessl_n12010f.html

This can also help secure the TM1 Admin service;

TM1 Admin Server Certificate Version

Specifies which version of the TM1 generated certificates to use. By default, the 1024-bit encryption version of the TM1 generated certificates is used. Change this property only if you want to use the new 2048-bit encryption version of the default certificates. You can use the new version with old and new TM1 clients, but you must configure the clients to use the new certificate authority file. Note This property does not apply if you are using your own certificates. Valid values include: 1 – Enables certificate authority for 1024-bit encryption with sha-1 (default value) 2 – Enables certificate authority for 2048-bit encryption with sha-256

Product:
Planning Analytics 2.0.5
Planning Analytics Workspace 2.0.38

Problem:
Error message when I on some TI processes in PAW, right click and select Edit Process. Other process work fine to open in PAW.

Error Message:

{“errorMessage”. “Error: Internal Server Error\r\nSystemOutOfMemory\r\n\r\n”, “/api/v1/Cubes (‘cubename’)/Views(‘All’)/tm1.Execute”,”httpStatusCode”:500)

Background:
The paw is try to show a to large preview of the selection. If the process have a cube view as data source, and that is large, like All, then you can get this error. It will work for a smaller cube view. When you edit a TI process in TM1 Architect, this error does not show.
If you check the TM1SERVER.LOG you will find this message;
8024 [34] WARN 2019-03-04 09:30:44.083 TM1.Server.Memory al_Alloc() outOfMemory Exception <<< MEMORY_TEMP_POOL_EXCEEDED >>> MaximumViewSize memory exceeded – apifunc# “0” – pool# “0” – poolsize “201318656.000000”

You can get same behavior in TM1 Architect, if you open a cube, and try to view all content.
TM1 Error
All: Maximum memory for action exceeded.
View may be too large.
Operation aborted.

Then in TM1SERVER.LOG the error is like this:
4908 [22] WARN 2019-03-04 11:52:24.469 TM1.Server.Memory al_Alloc() outOfMemory Exception <<< MEMORY_TEMP_POOL_EXCEEDED >>> MaximumViewSize memory exceeded – apifunc# “326” – pool# “0” – poolsize “201318656.000000”

You can still edit the TI process, so this warning should not be of a concern.
Check your TM1S.CFG file so the value MaximumViewSize is not set to a low value. In most cases you should manage with the default values.

More Information:
https://www-01.ibm.com/support/docview.wss?uid=swg21380704
https://www-01.ibm.com/support/docview.wss?uid=swg21639609
https://www.ibm.com/support/knowledgecenter/en/SS9RXT_10.2.2/com.ibm.swg.ba.cognos.tm1_inst.10.2.2.1.doc/c_maximumviewsize_1.html

Product:
Planning Analytics 2.0.5
internet Explorer 11
Firefox Quantum

Problem:
After apply SSL in TM1WEB, the node for a contributor application show a blank page in IE. But it works fine in Chrome.
No error message in IE, but in Firefox you get this message:
The information you have entered on this page will be sent over an insecure connection and could be read by a third party. Are you sure you want to send this information?

Background – how to setup SSL in TM1WEB for Planning Analytics;
http://www-01.ibm.com/support/docview.wss?uid=swg22004239

Solution:
Go to the pmpsvc start page at
https://pawebservername.domain.com:9510/pmpsvc/applications.jsp?portal=1
Click on the icon for “Administrator IBM Cognos TM1 Applications”

Under clients mark the “TM1 Application Web” and click on blue Edit link.

Change the URL from
http://pawebservername.domain.com:9510/tm1web/Contributor.jsp
to
https://pawebservername.domain.com:9510/tm1web/Contributor.jsp

Click OK
Click OK

Try now again to open the node in TM1 Application Web with Internet Explorer.

The servername and port number should be changed to the ones you are using in your setup of PA2.

More Information:
https://www.wireshark.org/#download