Month: December 2024

How find the data path?

December 20, 2024 by Roger·Comments Off

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

How check when database tabel was indexed

December 20, 2024 by Roger·Comments Off

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:

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

How in SQL divide with 1000

December 17, 2024 by Roger·Comments Off

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

How find value in list of values

December 17, 2024 by Roger·Comments Off

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

How export a dimension hierarki to csv file?

December 7, 2024 by Roger·Comments Off

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

How send mail with powershell

December 3, 2024 by Roger·Comments Off

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.

The error “Send-MailMessage : The specified string is not in the form required for a subject” means your email subject line contains forbidden characters like line breaks (CR/LF), leading/trailing spaces, or potentially incorrect formatting that violates email standards, preventing PowerShell’s Send-MailMessage cmdlet from sending the email. To fix it, trim whitespace, then replace any \r (carriage return) or \n (line feed) with a space or empty string using .Trim() and .Replace() methods in your PowerShell script before passing the subject to the cmdlet.

Common Causes & Solutions

Line Breaks/Hidden Characters: The most common culprit, causing the error because subjects must be single-line.

Solution: Use .Trim() and .Replace() in PowerShell:

powershell

$Subject = "My Subject With A Line Break"
$CleanSubject = $Subject.Trim().Replace("`r", "").Replace("`n", "")
# Or replace with a space:
# $CleanSubject = $Subject.Trim().Replace("`r", " ").Replace("`n", " ")
Send-MailMessage -Subject $CleanSubject ... # Use $CleanSubject here

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/

How connect selector widget to one card in Power BI?

December 3, 2024 by Roger·Comments Off

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

CogKnowHow

Just another WordPress site

Month: December 2024

How find the data path?

How check when database tabel was indexed

How in SQL divide with 1000

How find value in list of values

How export a dimension hierarki to csv file?

How send mail with powershell

How connect selector widget to one card in Power BI?