Product:
Planning Analytics 2.0.9.19
Microsoft Windows 2019 server
Google Chrome

Issue:
The background image in TM1WEB is changed on the server – but the change is not shown on end users web browsers.

Solution:

On many browsers, pressing Control-F5 will reload the page and bypass the cache. The Wikipedia “Bypass your cache” page has details for all modern web browsers.

Or you can change the name of the file PA_header.svg in folder D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\themes\carbon\standalone\images\login to something else. Then you need to update all code that calls that file – can be this css-html files for tm1web:

D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\themes\carbon\carbon.css

D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\themes\carbon\standalone\all.css

D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\themes\carbon\standalone\LoginDialog.css

 

Or clean the Web Browser cache at the end users laptop.

More Information:

https://medium.com/@gulshan_n/overcoming-persistent-image-caching-how-to-ensure-visitors-see-your-latest-website-changes-cd89a1434e1a 

https://www.avast.com/c-how-to-clear-cache

Resurrecting images from a web browser cache can be a useful way to recover lost or deleted images that you previously viewed online. Here’s a step-by-step guide on how to do this for various web browsers:

Google Chrome

  1. Access Cache:
    – Type chrome://cache in the address bar and press Enter. This will take you to a list of cached files.
  2. Find Images:
    – You can’t view the images directly, but you can see the URLs. To find images, you can also use a tool like ChromeCacheView (a third-party utility) that allows you to view and extract images from the cache more easily.
  3. Extract Images:
    – If using ChromeCacheView, download and run it. It will display cached files, including images. You can select the images you want to save and extract them to your computer.

Mozilla Firefox

  1. Access Cache:
    – Type about:cache in the address bar and press Enter. This will show you the disk and memory cache information.
  2. Find Images:
    – Look for the section that lists the cache entries. You can find images by checking the file type and URL.
  3. Extract Images:
    – You can use Mozilla’s cache viewer or a third-party tool like Firefox Cache Viewer to extract images more conveniently.

Microsoft Edge

  1. Access Cache:
    – Type edge://cache in the address bar and press Enter. This will show you cached items.
  2. Find Images:
    – Similar to Chrome, you might need a third-party tool like EdgeCacheView to find and extract images.

Safari

  1. Access Cache:
    – For Safari, the cache is not easily accessible like in other browsers. You can use Terminal commands or third-party tools.
  2. Find Images:
    – You can also look in the ~/Library/Caches/com.apple.Safari/ directory to find cached files.
  3. Extract Images:
    – Use a third-party application like SafariCache to help retrieve images from the cache.

General Tips

  • Look for File Types: When browsing the cache, look specifically for file types like .jpg.png, or .gif.
  • Use Third-Party Tools: Tools like WebCacheImage, ChromeCacheView, and others can simplify the process.
  • Browser Extensions: Some extensions can help manage and view cached files directly from the browser.

https://www.linkedin.com/pulse/11-ways-fix-google-chrome-loading-images-benjamin-ladegbaye 

How to refresh cached images and files in Chrome

 

Product:
Planning Analytics 2.0.9.19
Microsoft Windows 2019 server

Issue:
I would like to turn of the DEV Tm1 servers during the weekend, can i do it with a chore?

Solution:

Create a parameter cube, something like below, add the tm1 instance name in the application column. Set a Y in Stop column if you would like the chore to stop the service. In the last two columns, we update the time when it was stopped or started.

# -- setup the parameter cube --

Cube = 'ScheduleCube' ;
DimName1 = 'sApplication' ;
DimName2 = 'ScheduleMeasures' ;
sLoadtestdata = 'NO' ;
# sLoadtestdata = 'YES' ;

DimensionDestroy (DimName1 ) ;
DimensionCreate (DimName1 ) ;

# -- add elements
nNum = 1;
While( nNum <= 15 );
sNum = numbertostring (nNum) ;
DimensionElementInsertDirect(DimName1 ,'', sNum , 'N' ) ;
nNum = nNum + 1;
End;



DimensionDestroy (DimName2);
DimensionCreate (DimName2 );

# -- add elements
DimensionElementInsertDirect (DimName2 ,'', 'Application' , 'S' ) ;
DimensionElementInsertDirect (DimName2 ,'', 'Text' , 'S' ) ;
DimensionElementInsertDirect (DimName2 ,'', 'StartRun' , 'S' ) ;
DimensionElementInsertDirect (DimName2 ,'', 'StopRun' , 'S' ) ;
DimensionElementInsertDirect (DimName2 ,'', 'Stop' , 'S' ) ;
DimensionElementInsertDirect (DimName2 ,'', 'Start' , 'S' ) ;
DimensionElementInsertDirect (DimName2 ,'', 'Last Stopped' , 'S' ) ;
DimensionElementInsertDirect (DimName2 ,'', 'Last Started' , 'S' ) ;


# -- create a cube
CubeCreate(Cube, DimName1 , DimName2 );

# -- add data to cube --

IF ( sLoadtestdata @= 'YES' ) ;
cellputs ( 'planning sample' , Cube , '1' , 'application' ) ;
cellputs ( 'Y' , Cube , '1' , 'Start' ) ;
cellputs ( 'Y' , Cube , '1' , 'Stop' ) ;
cellputs ( 'proven_techniques' , Cube , '2' , 'application' ) ;

# -- add your default test data here -- 
ENDIF;

Create then a TM1 TI process to stop services:

# --- stop a service --

Cube = 'ScheduleCube' ;
DimName1 = 'sApplication' ;
DimName2 = 'ScheduleMeasures' ;

# -- check number of elements --
nLong = DIMSIZ (DimName1) ;
nNum = 1 ;
WHILE ( nNum <= nLong ) ;
sNum = numbertostring (nNum) ;
# -- get the application name --
sApp = CELLGETS ( cube , sNum, 'Application' ) ;
# -- check that it is not empty --
IF (LONG (sApp) <> 0 );
# -- check that it is suppose to be stopped --
IF ('Y' @= CELLGETS ( cube, sNum, 'Stop' ) ) ;

# -- get the time and put in the cube --
sTime = TIMST(now, '\Y-\m-\d \h:\i:\s');
CELLPUTS ( sTime, cube, sNum, 'Last Stopped' ) ;
# -- make the call to stop --

sBatchFile = 'NET STOP "' | sApp |'"' ;
ExecuteCommand('cmd /c ' | sBatchFile, 0);

ENDIF;
ENDIF;
nNum = nNum +1 ;
END;


Create a TM1 TI process to start services:

# -- start a service --

Cube = 'ScheduleCube' ;
DimName1 = 'sApplication' ;
DimName2 = 'ScheduleMeasures' ;

# -- check number of elements --
nLong = DIMSIZ (DimName1) ;
nNum = 1 ;
WHILE ( nNum <= nLong ) ;
sNum = numbertostring (nNum) ;
# -- get the application name --
sApp = CELLGETS ( cube , sNum, 'Application' ) ;
# -- check that it is not empty
IF (LONG (sApp) <> 0 );
# check that it is suppose to be started --
IF ('Y' @= CELLGETS ( cube, sNum, 'Start' ) ) ;

# -- get the time and put in the cube --
sTime = TIMST(now, '\Y-\m-\d \h:\i:\s');
CELLPUTS ( sTime, cube, sNum, 'Last Started' ) ;
# -- make the call to start --

sBatchFile = 'NET START "' | sApp |'"' ;

ExecuteCommand('cmd /c ' | sBatchFile, 0);

ENDIF;
ENDIF;
nNum = nNum +1 ;
END;

 

Schedule them in cores, to run on Friday at 2300 and on Monday at 0300 – to get it to stop and start the service.

I recommend that you have a “savedataall” in the TM1 applications that run every Friday before above.

Set the chore to start on the weekday it should stop the service, and set it to run the chore every 7 days.

More Information about other things:

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

https://www.mci.co.za/business-performance-management/learning-mdx-views-in-ibm-planning-analytics/

Primary sales forecasting using PA, SPSS Modeler and python

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

Product:

Planning Analytics 2.0.9.19
Microsoft Windows 2019 server

Issue:

TI Process take longer to run – and TM1 Architect is non responsive. If you check the tm1server.log file, you find text like this:

TM1.Server sf_Rename: Failed to rename (d:\tm1data\products}subs\nameoftheprocesshaveingissus.sub) to (d:\tm1data\products}subs\nameoftheprocesshaveingissus.sub$$$). Error: error code:5 reason:”Access is denied.” file..
….could not rename file to intermediate file, “nameoftheprocesshaveingissus.sub” to “nameoftheprocesshaveingissus.sub$$$” after 10 retrys in 20 seconds…

 

Solution:

Go into Windows service and change the user that run the Cognos TM1 server instance to be Local System instead of a specific user or service account.

Maybe security demands on the server NTFS have change by use of any new Windows Policy.

Make certain you are logged into Windows with a user account that has permission to access the location (the folder where the tm1 application is). In the case of running any process that create subset or views, you will need to make sure your user account (that run the TM1 service) has permission to save files to the software’s data and subfolders.

 

Product:

Cognos Controller 11.0.1200
Microsoft Windows 2019 server

Issue:

Cognos Controller use can not open excel from data entry – the open button is grey, or the when they click on the excel icon, the excel crash with error like this:

Unable to cast COM object of type ‘Microsoft.Office.Interop.Excel.ApplicationClass’ to interface type ‘Microsoft.Office.Interop.Excel._Application’. This operation failed because the QueryInterface call on the COM component for the interface with IID ‘{000208D5-0000-0000-C000-000000000046}’ failed due to the following error: Interface not registered (Exception from HRESULT: 0x80040155).

Solution:

Restart the laptop and reinstall Microsoft Office is the solution.

 

 

More Information:

https://www.ibm.com/support/pages/user-not-automatically-logged-excel-link-when-using-controller-client-excel-based-functionality-example-data-entry-leading-security-error-1-errors 

https://www.ibm.com/support/pages/troubleshooting-user-not-automatically-logged-excel-link-when-launching-functionality-main-controller-program-ccrexe

https://www.ibm.com/support/pages/user-not-automatically-logged-excel-link-when-launching-functionality-main-controller-program-caused-corrupt-xuser-table 

https://www.ibm.com/support/pages/open-button-greyed-out-intermittently-cannot-open-any-data-entry-forms 

https://www.ibm.com/support/pages/troubleshooting-interface-not-registered-exception-hresult-0x80040155-when-trying-use-data-entry-reported-values 

https://www.ibm.com/support/pages/unable-cast-com-object-type-queryinterface-call-com-component-interface-not-registered-0x80040155-error-launching-excel-link-controller-caused-orphan-registry-key

Product:

Planning Analytics 2.0.9.19
Microsoft Windows 2019 Server

Issue:
How start or stop a TM1 instance from command line?

Solution:

There is a bat file in the TM1 installation you can take help from – check the folder : C:\Program Files\ibm\cognos\tm1_64\bin64

startup_tm1s.bat <path to tm1s.cfg file> { <TM1 Server Name> }

Above will start the TM1 if it is installed, otherwise it will install the TM1 instance and start it. Service_tm1s.bat file will both start and stop a TM1 instance.

Write a CMD file that have command of this character to use it;

Here we start TM1 instance FAP, in folder c:\tm1data\FAP\config.

>service_tm1s.bat stop TM1ServerConfigFolder TM1Servername

>service_tm1s.bat start TM1ServerConfigFolder TM1Servername

The downsize is that you need to inform of the path to the TM1 config file.

A bat file with the NET START command is better.

REM Stop the services
NET STOP "FAP" /Y

REM Start the services again
NET START "FAP"

More Information:

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

https://exploringtm1.com/how-to-do-a-daily-or-weekly-restart-a-tm1-model/

https://ecapitaladvisors.com/blog/how-to-set-up-turbo-integrator-chores-to-run-advanced-scheduling/ 

https://www.coretechnologies.com/products/ServiceScheduler/ 

https://support.pelco.com/s/article/Configure-a-Windows-service-to-restart-on-a-schedule-using-Task-Scheduler-1538586706677?language=en_US 

Product:
Microsoft SQL server

Issue:

In a view of dates, we only want the 5 last years and the 5 future years, from today date. How do it?

Solution:

Create a view of the table listed above, where you define it like below:

CREATE VIEW [DM].[DMDateView]
AS
SELECT [key_dimDate]
,[DateIndex]
,[Date]
,[Day]
,[DaySuffix]
,[Weekday]
,[WeekDayName]
,[DOWInMonth]
,[DayOfYear]
,[WeekOfMonth]
,[WeekOfYear]
,[ISOWeekOfYear]
,[Month]
,[MonthName]
,[Quarter]
,[QuarterName]
,[Year]
,[MMYYYY]
,[MonthYear]
,[FirstDayOfMonth]
,[LastDayOfMonth]
,[FirstDayOfQuarter]
,[LastDayOfQuarter]
,[FirstDayOfYear]
,[LastDayOfYear]
,[FirstDayOfNextMonth]
,[FirstDayOfNextYear]
FROM [DM].[dimDate]
where 1=1
and [Date] BETWEEN DateAdd(yy, -5, GetDate()) AND DateAdd(yy, +5,GetDate())

 

 

More Information:

https://www.mickpatterson.com.au/blog/add-auto-increment-to-a-column-in-sql-server

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

https://www.dbvis.com/thetable/sql-server-dateadd-the-complete-guide/

Product:

Planning Analytics 2.0.9.19

Issue:

Want the user name, running the TM1 TI process to be in the filename – how?

Solution:

There are many ways to do this, check internet and tm1py.

But this is a suggestion, when you use CAMID with Cognos Analytics:

# -- get the user
sUserRAW = TM1User() ;

IF ( SCAN ('R*S' ,sUserRAW ) <> 0 ) ;
sUser = 'Scheduled' ;
ELSE;
# -- get the alias 
sUserALIAS = ATTRS( '}Clients' , sUserRaw , '}TM1_DefaultDisplayValue') ;
# -- find the name part of the domain/name 
sUserBREAK = SCAN ( '/' , sUserALIAS ) ;
# -- SUBST(string, beginning, length) 
sUser = SUBST( sUserALIAS, sUserBREAK +1, LONG (sUserALIAS) - ( sUserBREAK ) ) ;
ENDIF;

sFileName = 'Data.' | sUser | '.' | TIMST( NOW(), '\Y\m\d.\h\i', 1) | '.zip';

 

 

More Information:

https://everanalytics.wordpress.com/2017/08/10/tm1_alias_attributes/ 

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

Product:

Planning Analytics 2.0.9.19

Issue:

When we copy the production TM1 instance to the development server, we have to turn off all the chores – is there a simpler way?

Solution:

Create a TM1 TI process that check a environment cube value, that is stored in a separate data folder, that tell if it is TEST or PROD server you run in.

Then add this TI process first in all the chores, so when a chore is run, it first checks if it is in TEST, and then quits the chore.

In the log file you will have a text like this: TM1.Process Process “SYS. Turn off this chore if in TEST”: : Execution was aborted by ChoreQuit() Function. : ChoreQuit() function called

To be able to temporary run the chore, we have a prompt in the TI process, that you can set to YES in the schedule, to make it run in your development environment.

Prompt can look like this:

Code should be like this in PROLOG:

sEnvironment = CellGetS( 'systemparameters', 'Environment' , 'Text');
IF ( sEnvironment @= 'Test') ;
IF ( pRun @<> 'YES' );
ChoreQuit ;
ENDIF; 
ENDIF;

 

More Information:

How to set up Turbo Integrator Chores to run Advanced Scheduling

https://bihints.com/closer-look-chores

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

Product:

Planning Analytics 2.0.9.19

Planning Analytics Workspace version 96

Microsoft Windows 2019

Issue:

The csv files amount have changed from period to comma for decimals when we run the TM1 TI process?

Possible Solution:

Depending from where you start your TM1 TI process, the local region settings will affect the decimal separator in the file.

If you run a TM1 TI process from PAW in EDGE set to Swedish for language and spelling, then the value will have a comma (,) as a decimal separator.

If you run a TM1 TI process from PAW in EDGE or CHROME set to English for language and spelling, then the value will have a period (.) as a decimal separator.

Check the web browser options – language and ensure that you have English as first choice to get a period as decimal separator like 100.99

TM1 Architect will use the region settings of the users value in Windows control panel.

 

You can set values in the TM1 TI process, to make conversion of value work, but the ASCIIOUTPUT may not be affected.

DatasourceASCIIDecimalSeparator TurboIntegrator local variable sets the decimal separator to be used in any conversion of a string to a number or a number to a string. If you set this variable you must also set the DatasourceASCIIThousandSeparator variable. This will affect NumberToString (value) but not ASCIIOUTPUT (value).

The character specified must be a standard ASCII printable character, with a decimal value between 33 and 127 inclusive.

Either of the following examples sets the comma character (,) as the separator.

DatasourceASCIIDecimalSeparator=',';
DatasourceASCIIDecimalSeparator=Char(44);

More Information:

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

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

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

What is the syntax for NumberToStringEx?

NumberToStringEx(Value, NumericFormat, DecimalSep, ThousandsSep)

  • Value = any real number​
  • NumericFormat = TM1 numeric format string e.g. #,0.## ​
  • DecimalSep = Decimal separator to be used in the output​
  • ThousandsSep = Thousands separator to be used in the output

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

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

Product:

Microsoft SQL server

Issue:

How check if a column can be converted to numeric from string?

Solution:

Start SSMS and enter below SQL code to list rows that is not convertable to numbers:

 select TOP (1000) * FROM [schemaname].[tablename]
where ISNUMERIC (columname) = 0

 

Below code to make a full copy of a table:

SELECT *
INTO newtable 
FROM oldtable
WHERE condition;

 

Below code to only list rows where the column content is above 15 charcters:

 select TOP (1000) * FROM [schemaname].[tablename]
where LEN (columnname) >= 15

 

More Information:

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

https://www.airops.com/sql-guide/how-to-check-length-string-sql

https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver16