by ·Comments Off on How find the data path?

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 

by ·Comments Off on How check when database tabel was indexed

Product:
Microsoft SQL server

Cognos Controller 11.0.1

Issue:

Open account define take long time to open – can it be that we need to index the database?

If the issue is only for one user – please ask that user to inside cognos controller client run “clear local cache”, to see if it helps.

Check if you move the user to a diffrent subnet, e.g. move from LAN to WIFI at the office, does it help?

Does it help to run it over VPN to the office?

If the issue is for all users, but on a specific network, it can be a windows group policy to the client computer that give the issue.

Create a ticket to your IT department, and ask them to check intune policy and firewall and network, when you reproduce the issue.

 

Solution:

Enter below script in SSMS for your cognos controller SQL server database, to check when it was last updated:

    • SELECT *,
    • STATS_DATE(object_id, stats_id) AS LastStatsUpdate
    • FROM sys.stats
    • WHERE left(name,4) = ‘NC_X’ order by laststatsupdate

 

Below SQL script will list the size of the tables in the database:

 

;with cte as ( 
SELECT 
t.name as TableName, 
SUM (s.used_page_count) as used_pages_count, 
SUM (CASE 
WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) 
ELSE lob_used_page_count + row_overflow_used_page_count 
END) as pages 
FROM sys.dm_db_partition_stats AS s 
JOIN sys.tables AS t ON s.object_id = t.object_id 
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id 
GROUP BY t.name 
) 
,cte2 as(select 
cte.TableName, 
(cte.pages * 8.) as TableSizeInKB, 
((CASE WHEN cte.used_pages_count > cte.pages 
THEN cte.used_pages_count - cte.pages 
ELSE 0 
END) * 8.) as IndexSizeInKB 
from cte 
) 
select TableName,TableSizeInKB,IndexSizeInKB, 
case when (TableSizeInKB+IndexSizeInKB)>1024*1024 
then cast((TableSizeInKB+IndexSizeInKB)/1024*1024 as varchar)+'GB' 
when (TableSizeInKB+IndexSizeInKB)>1024 
then cast((TableSizeInKB+IndexSizeInKB)/1024 as varchar)+'MB' 
else cast((TableSizeInKB+IndexSizeInKB) as varchar)+'KB' end [TableSizeIn+IndexSizeIn] 
from cte2 
order by 2 desc

If it is a SQL Index issue, then normally all cognos controller users are affected.

 

More information:

https://www.ibm.com/support/pages/suddenly-slow-example-3-minutes-compared-10-seconds-open-data-entry-form-even-if-only-one-user-active-caused-out-date-statistics

https://www.ibm.com/support/pages/xdbaudit-database-table-used-auditing-instead-xdbtrickle-controller-102-onwards

Manually purge the ‘xdbaudit’ table from audit information by performing the following steps:
1. Obtain a short period of downtime (no other users on the system)
2. Logon to Controller as an administrator
3. Click “Maintain – User- Single Mode”
4. Click “Maintain – System Audit Log – Configuration”
5. Choose how recent the audit data they wish to keep
6. Enter that chosen value (for example ’30’) into the section “Delete Data change details older than (days)”:

7. Click “delete”.

https://www.ibm.com/support/pages/node/303323

by ·Comments Off on How in SQL divide with 1000

Product:

Microsoft SQL server

Issue:

How update a value in a column in a existing table – for example divide with 1000?

Solution:

 

UPDATE table_name
SET value1 = value1 / 1000
WHERE condition ;

Keep in mind to check the key dim table, so you include the correct rows in the update.

  The key value for the version need to be collected from the version table.

The account value you want to update in fact table may have more than one key value, that you need to check and include.

In the fact table you can use the update command, but you need to carefully test the where clause so you update the correct rows.

Use below code to update the column value where the account is the defined value

 UPDATE [DM].[fact]
SET value = value / 1000
WHERE 1=1
and key_dimversion = 3
and ( key_dimTabellkonto = 55180 or 
key_dimTabellkonto = 61453 or 
key_dimTabellkonto = 62504 or 
key_dimTabellkonto = 66683 or 
key_dimTabellkonto = 67730 or 
key_dimTabellkonto = 69823 )

 

Use below code to get a sum of column value:

 SELECT
Sum(value)
FROM [DM].[fact]
WHERE 1=1
and key_dimversion = 3
and ( key_dimTabellkonto = 55180 or 
key_dimTabellkonto = 61453 or 
key_dimTabellkonto = 62504 or 
key_dimTabellkonto = 66683 or 
key_dimTabellkonto = 67730 or 
key_dimTabellkonto = 69823 )

 

This can be written easier with a SQL JOIN between the tables.

UPDATE a
SET a.value= a.value / 1000
FROM [DM].[fact] a
INNER JOIN [DM].[dimTabellkonto] b ON a.[key_dimTabellkonto] = b.[key_dimTabellkonto]
INNER JOIN [DM].[dimVersion] c ON a.[key_dimVersion] = c.[key_dimVersion]
WHERE 1=1
and c.[version] = 'budget'
and b.[account] = '95000' ;

 

To see the sum

SELECT SUM (a.value)
FROM [DM].[fact] a
INNER JOIN [DM].[dimTabellkonto] b ON a.[key_dimTabellkonto] = b.[key_dimTabellkonto] 
INNER JOIN [DM].[dimVersion] c ON a.[key_dimVersion] = c.[key_dimVersion]
WHERE 1=1
and c.[version] = 'budget'
and b.[account] = '95000' ;

 

More Information:

https://www.w3schools.com/sql/sql_update.asp 

https://learnsql.com/blog/sql-division-operator/

https://www.w3schools.com/sql/sql_join.asp

https://www.sqlshack.com/learn-sql-join-multiple-tables/ 

https://www.geeksforgeeks.org/sql-update-with-join/ 

https://www.datacamp.com/tutorial/sql-update-with-join

https://www.sqlservertutorial.net/sql-server-basics/sql-server-update-join/ 

https://www.javatpoint.com/sql-update-with-join

by ·Comments Off on How find value in list of values

Product:

Microsoft Excel

Issue:

You have a list of values (maybe dimensions) in column A and you want to find if they exist in column C. How do it in Excel?

Solution:

Mark column C, and enter the name “searcharea” in the top left name area (called the name box) , press enter. Now you have created a “range”.

Go to cell D1.

Enter Formula = COUNTIF ( searcharea; A1 )

Mark cell D1, and with the mouse drag down all the way to the last cell.

The formula should update automatic, if you do right, to in cell D2 be = COUNTIF ( searcharea; A2 )

This should give a 1 in the rows where the value in cell A exist in the C column.

 

More Information:

https://www.wps.com/academy/check-if-value-is-in-list-in-excel-(3-easy-methods)-quick-tutorials-1865235/ 

https://www.exceltip.com/lookup-formulas/check-if-value-is-in-list-in-excel.html

https://excel-tutorial.com/why-excel-formulas-use-comma-or-semi-colon/ 

Force Excel to use commas (,) instead of semicolons (;) in separating formulas

https://exceljet.net/glossary/list-separator 

https://exceljet.net/glossary/named-range 

by ·Comments Off on How export a dimension hierarki to csv file?

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

by ·Comments Off on How send mail with powershell

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/

 

by ·Comments Off on How connect selector widget to one card in Power BI?

Product:
Microsoft Power BI Desktop

Issue:

How do in Power BI connect a card (that show a single number) to only a few selector widgets (slicers) on a page?

Solution:

To connect a card to a selection widget (like a slicer) in Power BI Desktop without affecting other visuals on the same page, you can use the Edit Interactions feature.

Create Your Card and Slicer:

First, ensure you have both the card visual and the slicer visual on your report page.
Select the Slicer:

Click on the slicer visual to select it.
Edit Interactions:

Go to the Format tab in the ribbon.
Click on Edit Interactions. This will show interaction controls on all other visuals on the page.
Set Interaction for the Card:

On the card visual, you will see two icons: a filter icon and a no filter icon.
Click the filter icon to ensure the slicer affects the card.
For other visuals that you don’t want to be affected by the slicer, click the no filter icon.
Turn Off Edit Interactions:

Once you’ve set the interactions as desired, click Edit Interactions again to turn off the interaction mode.
This way, the slicer will only filter the card visual and not the other visuals on the same page

 

More Information:

You can get good information from the different CHAT AI tools today about Microsoft Products, try:

https://copilot.microsoft.com/chats 

https://openai.com/chatgpt/overview/

https://www.getmerlin.in/ask-ai

by ·Comments Off on Check that parameter year is 4 characters

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/

by ·Comments Off on Click on cell give that outlook starts

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 

 

by ·Comments Off on Subset editor does not show in TM1 Perspective

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