Product:
Planning Analytics 2.0.6
Microsoft Windows 2016 Terminal Server

Problem:
On new install of TM1 Architect on a Terminal Server, the list of TM1 instances is not showing up. They show up if you run TM1 architect direct on the TM1 server.

Solution:
Check Windows Firewall one both the Terminal Server and on your TM1 (PAL) server.
Go to Run from the start menu, enter wf.msc and press OK
Add a inbound rule that opens port 5495, 5498.
Save it as Cognos TM1 rule and check if it works again.

If you get a list of TM1 instances, but can not connect to them, then you miss the higher ports you have defined in TM1S.CFG
go back into Windows firewall, and add a port range like 12345-12360 to your cognos firewall rule.
Save and try again.

You need ports 5495, 5498 for TM1 Architect. 5895, 5898 is the Admin Server REST interface port (like HTTPPort for Tm1 servers). 5898 is when using TLS1.2
Then you need the ports you specify in TM1 applications – best to set a range like 12345-12360 then update in every TM1S.CFG file to unique values;
PortNumber=12345
HTTPportnumber=12346
ClientMessagePortNumber=12347

More Information:
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/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.inst_cr_winux.doc/c_reviewthedefaultsettings.html
https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/c_paw_install_free_ports.html
http://cogknowhow.tm1.dk/archives/2055

Port 9300 is for Cognos Analytics (BI dispatcher).
Port 9510 is for TM1WEB and PMPSVC.
Port 5495,5498,5895,5898 is for TM1 Admin service.
Port 9012 is for the TM1 Agent.
Port 80 and 443 is for PAW or CA11 web-gateway ibmcognos.
There are other ports, but they are for communications between servers.

Product:
Planning Analytics Workspace 36
Microsoft Windows 2016 Server

Problem:
When open PAW and create a new book. There are no TM1 servers/applications listed under databases.

If you only miss access to the TM1 instances, then you can see a error like unable to fetch data for content tree. Session can not be established for server: Planning Sample 500 (internal server error) “code”,”Explorer”,”refID”,”message”,”Failed to get children for Server Cubes”,”Cause”…. ConnectTimeoutException….

When you validate the IBM Planning Analytics Workspace Administration Tool, and get time out on the TM1 Admin Server URI – it can be because the Windows Firewall is blocking port 5898.

Solution:
The Microsoft Windows firewall is blocking the traffic to the TM1 Admin service, and the rest api port to the TM1 instances.
Go to Run from the start menu, enter wf.msc and press OK.
This will start the Windows Firewall.
Expand Inbound Rules
Click New Rule
Select Port and click next
Enter the needed ports, can be depending on TM1 instance, below for the standard samples;
9300, 9510, 5898, 9012, 8888, 12354, 8014, 5010, 44312, 8011, 5011, 8010
Click Next
Mark Allow the connection click Next
Click Next to apply rule everywhere
Enter name to Cognos and click Finish

Try again.

If you still get 401 errors, you may need to restart the Windows server to make the Firewall changes kick in.

Port 9300 is for Cognos Analytics (BI dispatcher)
Port 9510 is for TM1WEB
Port 5898 is for TM1 Admin service
Port 9012 is for the TM1 Agent

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

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 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:
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