Product:

Planning Analytics 2.0.9.19
Microsoft Windows 2019 server

Issue:

How can i find data path to TM1 session?

Solution:

Search the internet and use the code to build something like this, it will check if the TM1S.CFG file is in the data or a \config\ folder, and then load the data from TM1S.CFG file to a dimensions, that you later can ask in your code for information from.

#Section Prolog

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

# -- get the data folder if the logs folder is logs or logfiles ---
sLogDirName1 = LOWER ('Logfiles\' ) ;
sLogDirName2 = LOWER ('Logs\' ) ;
sConfigDirName = LOWER ('Config\') ;
sDataDirName = LOWER ( 'Data\' ) ;
sBackupDirName = LOWER ( 'Backup\' );
sLogDirPath = LOWER( GetProcessErrorFileDirectory );

nLScan1 = SCAN (sLogDirName1, sLogDirPath) ;
nLScan2 = SCAN (sLogDirName2, sLogDirPath) ;
IF ( nLyckadScan1 <> 0 );
# sDataDirPath = DELET (sLogDirPath, nLScan1, LONG (sLogDirName1)) | sDataDirName;
# sBackupDirPath = DELET (sLogDirPath, nLScan1, LONG (sLogDirName1)) | sBackupDirName;
sConfigDirPath = DELET (sLogDirPath, nLScan1, LONG (sLogDirName1)) ;
ELSEIF ( nLyckadScan2 <> 0 );
# sDataDirPath = DELET (sLogDirPath, nLScan2, LONG (sLogDirName2)) | sDataDirName;
# sBackupDirPath = DELET (sLogDirPath, nLScan2, LONG (sLogDirName2)) | sBackupDirName;
sConfigDirPath = DELET (sLogDirPath, nLScan2, LONG (sLogDirName2)) ;
ELSE;
# the log folder can be the same as the datafolder 
sConfigDirPath = sLogDirPath ;
ENDIF;

CFGpath = sConfigDirPath ;

pParamsList=UPPER(':adminhost:ServerName:DataBaseDirectory:PortNumber:ClientMessagePortNumber:ServerCAMURI:LoggingDirectory:MTQ:HTTPPortNumber:IntegratedSecurityMode');
Dlmtr=';';
LenDtr=LONG(Dlmtr);
DataSourceType='CHARACTERDELIMITED';
# -- change the folder options to match your setup ---
sAfolder = 'config\' ;
ConfigFile = 'Tm1s.cfg';

IF (FileExists(CFGpath | sAfolder | ConfigFile ) = 1 ) ;
DatasourceNameForClient= CFGpath | sAfolder | ConfigFile;
DatasourceNameForServer= CFGpath | sAfolder | ConfigFile;

ELSEIF (FileExists(CFGpath | ConfigFile ) = 1 ) ;
DatasourceNameForClient= CFGpath | ConfigFile;
DatasourceNameForServer= CFGpath | ConfigFile;
ELSE;
# -- will use tm1s.cfg in the data folder --
DatasourceNameForClient= 'Tm1s.cfg';
DatasourceNameForServer= 'Tm1s.cfg';
ENDIF;


DatasourceASCIIDelimiter='=';
DatasourceASCIIHeaderRecords=1;

DimName='SYS_ServerParameters';
IF( DimensionExists(DimName) = 0 );
DimensionCreate( DimName );
ENDIF;

IF( CubeExists( '}ElementAttributes_'|DimName )=0 );
AttrInsert( DimName , '' , 'Value' , 'S' );
ENDIF;

IF( CubeExists( '}ElementAttributes_'|DimName )=1 );
IF(DIMIX( '}ElementAttributes_'|DimName , 'Value' )=0 );
AttrInsert( DimName , '' , 'Value' , 'S' );
ENDIF;
ENDIF;

#Section Metadata

#****Begin: Generated Statements***
#****End: Generated Statements****
#Section Data

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


IF( SUBST( Param , 1 , 1)@<>'#' & Param @<>'' & SCAN(UPPER(Param) , pParamsList)>0 );

IF( SCAN( ';' , Value )>0 );
CurPos=1;
ValueList=Value|Dlmtr;
ParamNum='';
i=0;
WHILE(CurPos>0&LONG( ValueList )>0);
CurPos=SCAN( Dlmtr , ValueList );
Value=SUBST( ValueList , 1, CurPos-1);
ValueList=DELET( ValueList, 1, CurPos+LenDtr-1);
#LOGOUTPUT( 'INFO' , Param | ParamNum |' = '| Value );
DimensionElementInsertDirect(DimName, '', Param | ParamNum ,'N');
AttrPutS(Value, DimName, Param | ParamNum , 'Value' );
i=i+1;
ParamNum=NUMBERTOSTRING(i);
END;
ELSE;

#LOGOUTPUT( 'INFO' , Param |'='| Value ); 
DimensionElementInsertDirect(DimName, '', Param ,'N');
AttrPutS(Value, DimName, Param , 'Value' );
ENDIF;

ENDIF;
#Section Epilog

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

More Information:
https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=trf-delet-1

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=pctf-getprocesserrorfiledirectory 

https://www.ibm.com/docs/en/cognos-tm1/10.2.2?topic=chores-running-chore-server-startup 

Product:
Planning Analytics 2.0.19
Microsoft Windows 2019 server

Issue:
How get the parent and child of a dimension to a text file?

Solution:

Create a new TM1 TI process with the data source to the dimension.

And a parameter where you enter the dimension:

And the TM1 code as below:

#Section Prolog


# -- check the enter value to not be blank --
IF (LONG(pDimension) = 0 ) ;
sDim = 'account' ;
ELSE;
sDim = pDimension ;
ENDIF;
sOutputFile = 'D:\temp\' | sDim | '.csv';

# -- set the dimension as source --
DatasourceNameForServer = sDim ;
DatasourceNameForClient = sDim ;
DATASOURCEDIMENSIONSUBSET = 'all';

#Section Metadata

#Section Data

# ----- export the dimension ---

sElementType = DTYPE ( sDim, v1 );
# always output the element and its type
AsciiOutput ( sOutputFile, sElementType , v1 );
# cycle through children if it is a consolidated element
IF ( sElementType @= 'C' );
iCount = 1;
iMax = ElCompN ( sDim, v1 );
While ( iCount <= iMax );
sChild = ElComp ( sDim, v1, iCount );
sWeight = NumberToString ( ElWeight ( sDim, v1, sChild ) );
AsciiOutput ( sOutputFile, '', sChild , sWeight );
iCount = iCount + 1;
End;
EndIf;

#Section Epilog

This need to be adjusted to meet your needs.

More Information:

https://exploringtm1.com/how-to-use-a-dynamic-path-in-a-ti-for-a-data-source/ 

https://www.ibm.com/docs/en/cognos-tm1/10.2.2?topic=variables-datasourcedimensionsubset

https://lodestarsolutions.com/tag/tm1-dimension-subsets/

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

Product:
Planning Analytics 2.0.9.19

Microsoft Windows 2019 server

Issue:
How send mail with powershell from Tm1?

Solution:

Create a folder on your tm1 server called d:\script\ , and in there create a file SENDMAIL.PS1 in Notepad++ with this content:

# Define the paths to your text files
$messageFilePath = "d:\script\message.txt"
$recipientFilePath = "d:\script\recipient.txt"

# Read the content of the text files
$messageBody = Get-Content -Path $messageFilePath -Raw
$recipient = Get-Content -Path $recipientFilePath -Raw

# Define the email parameters
$smtpServer = "smtp.yourserver.com"
$from = "your-email@domain.com"
$subject = "Your Subject Here"

# Send the email
Send-MailMessage -From $from -To $recipient -Subject $subject -Body $messageBody -SmtpServer $smtpServer

Update the $messageFilePath and $recipientFilePath variables with the paths to your text files.
Make sure to replace “smtp.yourserver.com” and “your-email@domain.com” with your actual SMTP server and email address.

Create a file “message.txt” with the text you want to send as body.
Create a file “recipient.txt” with the email person with the receiver of the mail.

Then create a TM1 TI process with above prompt, and below code:

#Section Prolog

#--- set variables ----
vFileMessage = 'd:\script\message.txt' ;
vFileRecipient = 'd:\script\recipient.txt' ;
toPerson = 'tm1@company.com' ;
toMessage = 'This is a test - something went wrong' ;

#--- check the prompts -----------
isMail = scan ( '@' , pReciver) ;
IF ( isMail <> 0 ) ;
toPerson = pReciver ;
ENDIF ;

IF ( LONG (pMessage) <> 0 ) ;
toMessage = pMessage ;
ENDIF ;

# --- Change to get clean text in the text file
DatasourceASCIIQuoteCharacter='';
DatasourceASCIIDelimiter = ',';
#--- try change this value to support other characters than english ------
setOutputCharacterSet (vFileMessage, 'TM1CS_ISO_8859_1');

#-------write the files-------------
TextOutput( vFileRecipient, toPerson ) ;
TextOutput( vFileMessage, toMessage ) ;

#Section Metadata

#Section Data

#Section Epilog

# call the powershell file to send the mail
ExecuteCommand( 'Powershell -ExecutionPolicy ByPass -file "D:\script\sendmail.ps1" ', 1 );

Run the process, to test it.

More Information:

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=attf-setinputcharacterset-1

https://edu.cubewise.com/functions-library/tm1-function-for-ti-setoutputcharacterset

https://www.ibm.com/docs/it/cognos-tm1/10.2.2?topic=SS9RXT_10.2.2/com.ibm.swg.ba.cognos.tm1_ref.10.2.2.doc/r_tm1_ref_tifun_asciioutput.htm

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=tf-ascii-text-turbointegrator-functions 

https://lazyadmin.nl/powershell/send-email-powershell/ 

https://mailtrap.io/blog/powershell-send-email/

Product:

Planning Analytics 2.0.9.19
Microsoft Windows 2019 server

Issue:

How to add extra withlist on prompts, to ensure the value is somewhat correct, before processing?

Suggested solution:

If you have a prompt pYear and pMonth, you can have below code in prolog:

# if year is blank enter current year
IF (pYear  @= '') ;
pYear = NumberToString( Year ( Today (1) ) ) ; 
ENDIF;

# if year is not 4 characters - stop the process
IF (LONG (pYear ) <> 4);
sErrorMessage = 'Year is not correct value';
ItemReject ( sErrorMessage ); 
ProcessQuit;
ENDIF;

# add zero if only one digit
IF (LONG (pMonth) = 1) ;
pMonth = '0' | pMonth  ;
ENDIF;

# check that month is 2 digits

IF (LONG (pMonth) <> 2) ; 
ProcessQuit ;
ENDIF;

More Information:

https://www.ibm.com/docs/ru/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_ref.2.0.0.doc/tm1_ref.pdf 

https://nvlpubs.nist.gov/nistpubs/specialpublications/nist.sp.800-167.pdf

https://exploringtm1.com/year-function-tm1-syntax-use/

Product:
Planning Analytics 2.0.9.19
TM1 perspective
Microsoft® Excel® for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20550) 32-bit

Issue:

Then create a websheet with formulas, when it refresh the formulas change to =@TM1RPTROW.

Excel will treat any value containing an @ as an email address.

Solution:

For a single cell right click and go down the menu which appears to link -> “remove hyperlink.”
Release mouse and the hyperlink is disabled in that cell.

To prevent it to happen in future:

Go to menu File > Options > Proofing > AutoCorrect Options > Click Tab AutoFormat As You Type > Uncheck Internet and network paths with hyperlinks > OK

More Information:

https://support.microsoft.com/en-us/office/remove-or-turn-off-hyperlinks-027b4e8c-38f8-432c-b57f-6c8b67ebe3b0 

Product:
Planning Analytics 2.0.9.19 TM1 Architect

Microsoft Windows 2019 server

Issue:

When open a cube view in TM1 Explorer from TM1 Perspective on my laptop, and click on the dimension to get the subset editor, A beep hears but no dialog is shown. The cube view is frozzen, and only way out is to exit TM1 architect.

Solution:

When moveing your laptop between desks at the office, and connect it to diffrent displays with diffrent resultions, this error will be more common.

Turn of Tm1 Architect.

Start Windows file explorer.

Go to folder C:\Users\%username%\AppData\Roaming\Applix\TM1

Open file tm1p.ini in notepad

Remove the line with the text:   SubsetWindowLayoutInfo

Save the file.

Start Tm1 Architect/Tm1Perspective and test again to open the subset editor.

More information:

TM1 Perspectives subset editor missing when opened on remote desktop system with multiple displays
https://www.ibm.com/support/pages/apar/PM95711

https://www.ibm.com/support/pages/unable-view-all-open-tm1-objects-after-disconnecting-secondary-monitor 

Product:
Planning Analytics Workspace version 96
Microsoft Windows 2019 server

Issue:

How do I schedule backup of the PAW to disk?

Solution:

Try to use the scheduler inside Windows server.

Go to control panel.

Go to Task Scheduler.

Click on create a basic task

Click Next

Select weekly (if you want that) and click next

Set a time and day of week, and click next

Select “start a program” and click next

Enter “powershell.exe” as the program, and in arguments dialog enter the script you want to run; after -file parameter.

-file   D:\PAW96\scripts\backup.ps1

Above path can be different in your Environment. Click next.

Click finish.

Now this should restart the PAW services and make a backup for you, in folder D:\PAW96\backup\backup_2024_10_18_23_30_00

To get it to work, you may have to go back into “properties” and change the task to run for a systemaccount.

Also you should limit the task, to not run to long.

More Information:

https://blog.netwrix.com/how-to-automate-powershell-scripts-with-task-scheduler 

https://exploringtm1.com/how-to-upgrade-planning-analytics-workspace/ 

https://blog.atwork.at/medium.aspx?id=9a0dc8a0-5b51-40d4-8dd7-c0cab4e5564c&date=/post/2020/06/25/ 

https://blog.danskingdom.com/Run-PowerShell-as-another-user/

Product:
Planning Analytics Workspace 96
Microsoft Windows 2019 Server

Issue:
How sync two objects in PAW?

Solution:

Go into PAW, and create a new Book.
Expand the cube view you want to show.
Add the view to the book.

If you want the plan_report dimension (budget) to be selectable, click on the dots, and go to add widget as – selector list.

Put the box above the view, in a nice format.

Select the Open Set editor, to select what element should be possible to select.

After you select the element they should be able to select, click apply.

Now you need to set up the Synchronization. First when no item selected, go to the Properties and Synchronize and check that the group is Sheet.

Then click on the budget widget and go to Properties and Synchronize

Ensure that synchronize set is on, as shown above.

Then click on the view and go to Properties and Synchronize

Ensure the dimension plan_report is selected, and none of the others. Then active the Synchronize hierachies.

Save the book with a good name, and now it should work to select “budget” to get a display of that numbers.

More Information:

https://revelwood.com/ibm-planning-analytics-tips-tricks-how-to-set-up-synchronizations-in-ibm-planning-analytics-workspace/ 

Quick Tips – Selection Widget Synchronization

Product:
Planning Analytics 2.0.9.19

Microsoft Windows 2019 server

Issue:

How use variables in data tab in ASCIIOUTPUT from string in prolog tab?

Solution:

Create the string with the variables with % around them.

In data tab use command EXPAND to convert the varibles to the correct value.

To export a cube content, build a view of the cube first, you then need to create the variables, good to name them like v1, v2 etc.

In prolog – build up your string like this:

# add above subset to the view
cCounter2 =1;
WHILE(cCounter2 <= nSteps); 
sDimName = TABDIM(Cube, cCounter2) ;
ViewSubsetAssign ( Cube, ViewName, sDimName, SubName );
# build up the string to print out in data tab
IF (cCounter2 = 1) ;
stexttoexecute = ('%v' | numbertostring(cCounter2) | '%');
ELSE; 
stexttoexecute = stexttoexecute | ',' | ('%v' | numbertostring(cCounter2) | '%' );
ENDIF;
cCounter2 = cCounter2 + 1;
END;

Then print it out in data like this

WHILE (cCounter3 > 0 ) ; 
# write the header in the file
ASCIIOutput( vFilePath, sHeaderLine ) ;
cCounter3 = 0;
END ;

ASCIIOutput ( vFilePath, EXPAND (stexttoexecute) , NumberToString( NValue ) ) ;

In this example we export the data from the revenue cube in sample 24retail database.

Create a dummy cube with 15 dimensions, that you have as datasource.

Create prompts that give you what data to export, you need to enter the correct element names and measures.

Enter below code in PROLOG:

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

# set variabler
Cube = pCube;

sName = GetProcessName();
ViewName = sName ;
SubName = sName ;

sHeaderLine = '';
cCounter1 = 1;
cCounter2 = 1;
cCounter3 = 1;

# erase view from before
ViewDestroy ( Cube, ViewName );

# create the path - you can get the values from a cube or made the filename up from the prompts
sPath = 'D:\temp\';
# create file name
vFile = 'Aexportdatafromcube.csv';
vFilePath = sPath | vFile;

# check number of dimensions
nSteps = CubeDimensionCountGet (cube) ;

# destroy the view first
ViewDestroy ( Cube, ViewName );
# erase all subset to the view
cCounter2 =1;
WHILE (cCounter2 <= nSteps); 
sDimName = TABDIM(Cube, cCounter2) ;
SubsetDestroy ( sDimName, SubName );
cCounter2 = cCounter2 + 1;
END;


# Create the new view and subset of selected data
ViewCreate ( Cube, ViewName );

WHILE(cCounter1 < nSteps);
# get the dimension name
sDimName = TABDIM(Cube, cCounter1) ;
IF ( sDimName @= 'Year' );
SubsetCreate ( sDimName, SubName );
SubsetElementInsert ( sDimName, SubName, pYear, 1 );
ELSEIF ( sDimName @= 'Month' );
IF (pMonth @= 'AllMonths');
SubsetCreateByMDX ( SubName, '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [' | sDimName | '] )}, 0)}' );
ELSE;
SubsetCreate ( sDimName, SubName );
SubsetElementInsert ( sDimName, SubName, pMonth, 1 );
ENDIF;

ELSEIF ( sDimName @= 'Version' );
SubsetCreate ( sDimName, SubName );
SubsetElementInsert ( sDimName, SubName, pVersion, 1 );
ELSE;
SubsetCreateByMDX ( SubName, '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [' | sDimName | '] )}, 0)}' );
ENDIF;
# create the header line to the file with dim names
IF (cCounter1 = 1);
sHeaderLine = sDimName;
ELSE; 
sHeaderLine = sHeaderLine | ',' | sDimName ; 
ENDIF;

cCounter1 = cCounter1 + 1;
END;

# add the measures to the last dimension
IF ( cCounter1 = nSteps) ;
sDimName = TABDIM(Cube, cCounter1) ;
SubsetCreate ( sDimName, SubName );
sHeaderLine = sHeaderLine | ',' | sDimName ;

# find the measure in last parameter
pMeasurePart = pMeasure ;
vScan_1 = SCAN ( ':' , pMeasurePart);
sMeasure = pMeasure ;
IF (vScan_1 = 0) ;
sMeasure = pMeasurePart ;
SubsetElementInsert ( sDimName, SubName, sMeasure, 1 );
ELSE;

WHILE ( vScan_1 > 0 );
# find the next colon 
vScan_1 = SCAN ( ':' , pMeasurePart);
IF (vScan_1 = 0) ;
sMeasure = pMeasurePart ;
ELSE ;
sMeasure = SUBST ( pMeasurePart, 1, vScan_1-1 );
# truncate the text to be only part after the colon
pMeasurePart= SUBST ( pMeasurePart, vScan_1+1, LONG (pMeasurePart ) - vScan_1);
ENDIF ;


# extra check if value is blank - do not insert the sMeasure
IF ( sMeasure @<> '' );
SubsetElementInsert ( sDimName, SubName, sMeasure, 1 );
ENDIF;
END;
ENDIF;
ENDIF;

# add the last column value 
sHeaderLine = sHeaderLine | ',' | pMeasureText ;

# add above subset to the view
cCounter2 =1;
WHILE(cCounter2 <= nSteps); 
sDimName = TABDIM(Cube, cCounter2) ;
ViewSubsetAssign ( Cube, ViewName, sDimName, SubName );
# build up the string to print out in data tab
IF (cCounter2 = 1) ;
stexttoexecute = ('%v' | numbertostring(cCounter2) | '%');
ELSE; 
stexttoexecute = stexttoexecute | ',' | ('%v' | numbertostring(cCounter2) | '%' );
ENDIF;
cCounter2 = cCounter2 + 1;
END;

###  set what to exclude from the view 
ViewExtractSkipCalcsSet ( Cube, ViewName, 1);
ViewExtractSkipRuleValuesSet ( Cube, ViewName, 0 );
ViewExtractSkipZeroesSet ( Cube, ViewName, 1 );


# Set source 
DataSourceType='VIEW';
DataSourceNameForServer=Cube;
DataSourceCubeview=ViewName;

# Change to get comma in the text file
DatasourceASCIIQuoteCharacter='';
DatasourceASCIIDelimiter = ',';

Enter below code in DATA:

WHILE (cCounter3 > 0 ) ; 
# write the header in the file
ASCIIOutput( vFilePath, sHeaderLine ) ;
cCounter3 = 0;
END ;

# print out the values from the cube - check if text or number on value
IF (Value_Is_String = 0);
ASCIIOutput ( vFilePath, EXPAND (stexttoexecute) , NumberToString( NValue ) ) ;
ELSE;
ASCIIOutput ( vFilePath, EXPAND (stexttoexecute) , ( SValue ) ) ;
ENDIF;

Should give you a csv file in d:\temp folder.

More Information:

https://community.ibm.com/community/user/businessanalytics/blogs/declan-rodger/2021/09/10/time-to-stop-using-expand 

The Expand Function in TI processes is used to convert Variable names enclosed in % symbols to the contents of the variable at run time.

Below code (on my metadata or data tabs) to export the Country variable for the data record that is being processed:

TextOutput ( cFileName, Expand ( %vsCountry% ) );​

The Expand function also converts its entire contents into 1 concatenated string, the 2 examples below would do the same thing:

TextOutput ( cFileName, vsCountry, vsStore, vsProduct, NumberToString ( vnVolume), NumberToString ( vnRevenue), NumberToString ( vnCost ), NumberToString ( vnProfit ) );​

TextOutput ( cFileName, Expand ( %vsCountry%, %vsStore%, %vsProduct%, %vnVolume%, %vnRevenue%, %vnCost%, %vnProfit% ) );​​

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=functions-expand

https://www.wimgielis.com/tm1_reworkingexcellayoutII_EN.htm

https://quebit.com/askquebit/debugging-a-turbointegrator-process/

Product:
Planning Analytics 2.0.9.19
Microsoft Windows 2019 server

Issue:

What does the .cub$ files in the data folder mean? They increase the zip file for the backup rather much.

Solution:
They are left over from a crashed SaveDataAll function. Did you have a hung TM1 instance recently?

They’re generated when you do a data save. The cube in memory is initially saved to the .cub$ file. If the save is successful then the .cub file is replaced by the .cub$ file and the latter is deleted.

If the file is still there then either:
– You’re currently doing a data save in which case deleting the file would be highly inadvisable; or
– It’s left over from a past failed data save, in which case it can be deleted. (you might want to take a backup of the files before you delete it)

More Information:

https://www.ibm.com/support/pages/savedataall-best-practices 

https://exploringtm1.com/tm1-file-extensions-understand-the-files-in-your-tm1-model/

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=smtf-savedataall