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 

by ·Comments Off on Cognos Controller Client hang when you select accounts

Product:
Cognos Controller 11.0.1200.28

Microsoft Windows 2022 Server

Issue:

Most users get that the Cognos Controller client hang, when they open some selection dialogs. For example – go to Maintain – Account Structure Reports – select codes – and click on the … for the account field, to get a list of all the accounts.  Here the Cognos Controller client hang, and you have to stop cognos controller from the task manager.

Possible Solution:

Do a optimize on the database from inside cognos controller client.

  1. To run the optimization immediately, the system must be in single user mode. Go to Maintain > User > Single Mode and set the system to single mode.
  2. Click Maintain > Database > Optimize. The Optimize Database window opens.
  3. Select the Rebuild Structure Tables check box to rebuild the structure tables.
  4. Select Rebuild Indexes (advanced option).
  5. Click Run

 

Other things you can try to solve the issue are:

Clear the cache in cognos controller

Inside Controller Client program, via Maintain > Special Utilities > Clear Local Cache.

 

Change the cache folder for cognos controller client:

Launch Windows Explorer
4. Open the folder where Controller is installed.

  • Default this is here: C:\Program Files\ibm\IBM Cognos Controller Local Client

5. Open the file “CCR.exe.config” inside Notepad++
6. Search for the entry similar to the following:
<add key=”CacheDir” value=””/>
7. Modify this entry appropriately, for example similar to the following:
<add key=”CacheDir” value=”D:\Controller\Cache” />
8. Save and test

Start Cognos Controller client, and try to open the same account list.

 

There are 3 types of cache folder used by Cognos Controller Client:
(1) Microsoft Internet Explorer cache at C:\Documents and Settings\%USERNAME%\Local Settings\Temporary Internet Files
(2) Microsoft .NET components at C:\Documents and Settings\%username%\Local Settings\Application Data\assembly
(3) Controller user cache files: at C:\Documents and Settings\%appdata%\Application Data\Cognos\ccr\

If there is damage files in this folders, can give strange errors inside Cognos Controller Client program.

 

More information:

Optimize the Database – IBM Documentation

FAQ – What does an ‘internal’ database optimise do, and how often should I run a database optimize?

https://www.ibm.com/support/pages/how-modify-controller-use-different-location-its-temporary-cache-files 

https://www.ibm.com/support/pages/error-systemxml-root-element-missing-inside-multiple-menus-controller-caused-appdata-redirected-unc-path 

https://www.ibm.com/support/pages/explanation-why-cognos-controller-client-side-cache-file-folders-can-be-very-large-and-how-housekeep-them

https://www.ibm.com/support/pages/how-automatically-clear-local-cache-when-closing-controller

https://www.ibm.com/support/pages/controller-microsoft-sql-database-optimisation-and-maintenance-proven-practice-best-practice 

https://www.ibm.com/support/pages/massive-sql-transaction-logs-caused-rebuilding-sql-indexes

https://www.ibm.com/support/pages/reducing-size-controller-database-deleting-period-locking-company-records-table-xacclock

 

You can use Cognos Controller database optimize function to make the following database changes:

  • Remove Period Zero Values – clears the value 0 from selected period tables.
  • Rebuild Structure Tables – which gives quicker access to data.
  • Rebuild Indexes (advanced option) – Rebuilds all indexes in the database to enhance performance and give quicker access to data.
  • Analyze Schema (advanced option) – Reanalyzes the schema for Oracle database.

by ·Comments Off on How backup PAW

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/

by ·Comments Off on PAW Synchronization

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

by ·Comments Off on The user must enroll their device with an approved MDM provider like Intune

Product:

Microsoft Power BI portal

Issue:

When refresh a powerbi report ( e.g. Semantic model ) we got a error – this report worked before in the Power BI portal workspace. Error is like:

Data source error{“error”:{“code”:”DMTS_OAuthTokenRefreshFailedError”,”pbi.error”:{“code”:”DMTS_OAuthTokenRefreshFailedError”,”details”:[{“code”:”DM_ErrorDetailNameCode_UnderlyingErrorMessage”,”detail”:{“type”:1,”value”:” Device is not in required device state: compliant. Conditional Access policy requires a compliant device, and the device is not compliant. The user must enroll their device with an approved MDM provider like Intune…

 

Solution:

You must be in Microsoft Edge web browser to do this change, if you do the change in Chrome, it will not work.

On your reports Semantic model – click on the 3 dots and select “settings”.

Go to data source credentials and click on edit credntials that is marked as not working.

And enter your windows account credentials again.

Now it should be green, and this login is affecting all your reports – if the access point is the same.

Now click the “refresh” icon to update you PowerBi report semantic model in the portal.

 

 

If above does not work, try below.

Download the Semantic model to you computer.

Restart your computer.

Login to your company windows account.

Open the PowerBI pbix file, and refresh the report.

You will be prompted to login to the datasource.

Enter your credintials for windows account.

When the report is refreshed and working in PowerBI on your computer.

Save the report.

Publish the report to the powerbi portal – overwrite the previus report.

 

More Information:

https://learn.microsoft.com/en-us/entra/identity/conditional-access/concept-conditional-access-conditions

Blood, Sweat, and built-in compliance policy

There is a known issue with the Chrome browser that can cause this error to occur. If there is no device information sent in the sign-in logs, this might be the problem. Device information is sent when there is a PRT and the user is logged onto the browser. If the user is using Chrome, the Windows 10 accounts extension is needed.

If this is the case, you can test by asking the user to logon to the Edge browser or install the Windows 10 accounts extension to see if the issue is resolved.

https://learn.microsoft.com/en-us/azure/active-directory/conditional-access/concept-conditional-access-conditions

If they are signing in using Edge, they cannot use an incognito window because it will not pass the device state.

Is Microsoft Authenticator App is installed on the device? As Microsoft Authenticator is an broker app for iOS and would be needed to pass MFA and Device claims to Azure AD.

Sign-ins from legacy authentication clients also do not pass device state information to Azure AD.

 

Some years ago Microsoft stated that the in-app browser must be using a supported browser such as Edge, however, the Windows store uses Edge, and it also does not pass the device ID, so the conditional access policy can’t be compliant.

Many 3rd party applications use in-app browsers that are “not supported,” and it appears that Microsoft doesn’t offer the appropriate developer documentation that would allow 3rd parties to include this conditional access device information in their in-app browsers, even if they used Edge.

These are the links that were provided as dev resources:

https://www.graber.cloud/en/aadsts50131-device-not-required-state/ 

https://cloudbrothers.info/entra-id-azure-ad-signin-errors/ 

You may need to do below in chrome to get it to work…

this is the exact requirements (Chome-side, your Azure AD setup has its own stuff) you needed:

Latest “Chrome Enterprise Policy List”: https://support.google.com/chrome/a/answer/187202?hl=en

GPO Settings
User Configuration\Policies\Administrative Templates\Google\Google Chrome\HTTP Authentication
-Kerberos delegation server whitelist
autologon.microsoftazuread-sso.com,aadg.windows.net.nsatc.net
-Authentication server Whitelist
autologon.microsoftazuread-sso.com,aadg.windows.net.nsatc.net
# Needed if you’re blocking extensions from being installed to whitelist this one
User Configuration\Policies\Administrative Templates\Google\Google Chrome\Extensions
-Configure the list of force-installed apps and extensions (Enabled)
ppnbnpeolgkicgegkbkbjmhlideopiji
-Configure extension installation allow list (Enabled)
ppnbnpeolgkicgegkbkbjmhlideopiji

Note: That extension ID I pulled from https://chrome.google.com/webstore/detail/windows-accounts/ppnbnpeolgkicgegkbkbjmhlideopiji (Windows Accounts)