TM1 TI Process

[HarshaPatil]

Hi Team,

We have one process to load data from cube to table.

In that we have Dimensions- Year, Day, Budget_KPI, Branch & Measure. These dimensions have static subset only Day is dynamic as we have to run this process daily morning via chore. So we are selecting day(current date-1) everyday and changing subset manually and scheduling this chore at morning. Is there any other method in TM1 that day will change automatically everyday.

[Wim Gielis]

The easiest method is to use, in the zero out, a subset containing 1 Day element. You would use SubsetCreate to create an empty subset. To populate it, derive the day of yesterday, for instance Timst( Now - 1, '\Y\m\d' ) or variations thereof. Or use an attribute “Previous Date”. As long as you end with the correct element it’s fine.

Code: Select all

# Returns today as in 20230601
vToday =  Timst( Now, '\Y\m\d' );

# Returns yesterday in 20230531
vYesterday = Date( Now - 1, 1);

Looking at the questions you ask that last weeks; I do think that a course on TM1 would be a good idea. It will definitely help you advance in the software.

[Wim Gielis]

Here is another approach I used in the past, in a TM1 model with very frequent dates input.
I needed to run/validate many TI processes with a start date and end date:
- start date = 20230531
- end date = 20230601

Which means, run a TI process whereby "yesterday as the first day" and "today as the last day".
Dates in the very near past and future were needed, like today, yesterday, day before yesterday, tomorrow, day after tomorrow, date offset in numbers, ...

Instead of always calculating and entering the exact dates, following the conventions of date syntax (yyyy-mm-dd or similar), I wrote a rule in a 3D configuration cube:

Code: Select all

# empty input means empty output
# an input with 1 / and length 10 means convert as a date
# an input with 1 - and length 10 means convert as a date
# eg - eergisteren - dby - the day before yesterday
# g - gisteren - y - yesterday
# v - vandaag - t - today
# m - morgen - tw - tomorrow
# om - overmorgen - dat - the day after tomorrow
# all other cases, treat as an offset from today (positive or negative number)
['Date relative to today', 'Overwrite'] = S: 
If( DB('Sys_CFG', !Sys_CFG_Environment, 'Input', !Sys_CFG_Msr ) @= '', 
	'', Timst( 
	If( Long( DB('Sys_CFG', !Sys_CFG_Environment, 'Input', !Sys_CFG_Msr ) ) = 10 & Scan( '/', DB('Sys_CFG', !Sys_CFG_Environment, 'Input', !Sys_CFG_Msr ) ) > 0, 
		DayNo( Subst( DB('Sys_CFG', !Sys_CFG_Environment, 'Input', !Sys_CFG_Msr ), 7, 4 ) | '-' | Subst( DB('Sys_CFG', !Sys_CFG_Environment, 'Input', !Sys_CFG_Msr ), 4, 2 ) | '-' | Subst( DB('Sys_CFG', !Sys_CFG_Environment, 'Input', !Sys_CFG_Msr ), 1, 2 )), 
	If( Long( DB('Sys_CFG', !Sys_CFG_Environment, 'Input', !Sys_CFG_Msr ) ) = 10 & Scan( '-', DB('Sys_CFG', !Sys_CFG_Environment, 'Input', !Sys_CFG_Msr ) ) > 0, 
		DB('Sys_CFG', !Sys_CFG_Environment, 'Input', !Sys_CFG_Msr ), 
		DayNo( Today(1)) + 
			If( Scan( '_' | Lower( DB('Sys_CFG', !Sys_CFG_Environment, 'Input', !Sys_CFG_Msr )) | '_', '_eg_eergisteren_dby_day before yesterday_' ) > 0, 
				-2, 
			If( Scan( '_' | Lower( DB('Sys_CFG', !Sys_CFG_Environment, 'Input', !Sys_CFG_Msr )) | '_', '_g_gisteren_y_yesterday_' ) > 0, 
				-1, 
			If( Scan( '_' | Lower( DB('Sys_CFG', !Sys_CFG_Environment, 'Input', !Sys_CFG_Msr )) | '_', '_v_vandaag_t_today_' ) > 0, 
				0, 
			If( Scan( '_' | Lower( DB('Sys_CFG', !Sys_CFG_Environment, 'Input', !Sys_CFG_Msr )) | '_', '_m_morgen_tw_tomorrow_' ) > 0, 
				1, 
			If( Scan( '_' | Lower( DB('Sys_CFG', !Sys_CFG_Environment, 'Input', !Sys_CFG_Msr )) | '_', '_om_overmorgen_dat_day after tomorrow' ) > 0, 
				2, 
				Numbr( DB('Sys_CFG', !Sys_CFG_Environment, 'Input', !Sys_CFG_Msr ))))))))), 
	'\Y-\m-\d' ));

Near the top, you can see what is allowed for input in a String cell:
- no input means no output
- or, different ways of providing the date string
- or, an offset based on today's date (calculated in the rule) with either words / abbreviations / a day offset.

-3 means: 3 days ago
4 means: 4 days in the future
0 means: today
y means: yesterday
dat means: day after tomorrow
20-11-2023: means a valid date of Nov 20 this year
...
etc. The formatted date string is returned.

In case you could not read all words, it's Dutch English and Dutch input are allowed.

The Prolog tab of the TI process just does twice a CellPutS and twice a CellGetS. Very convenient.
The conversion logic is not copy/pasted in 1, 2, ..., 10, ... TI processes but stored in a cube.

Hence, run the TI process with:
- pStartDate: dby
- pEndDate: t

I find it much easier than entering:

- pStartDate: 2023-05-29
- pEndDate: 2023-06-01

YMMV though