Ultimate Long TM1 quiz

lotsaram
MVP
Posts: 3687
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Ultimate Long TM1 quiz

Post by lotsaram »

MarenC wrote: Mon Nov 04, 2024 2:52 pm I would like to bet there isn't a model out there that doesn't use an if somewhere, be it a TI or a rule.
There will always be IF statements. The point is that a Boolean test is one of the most, if not the most expensive functions you can use in a rule. If you can write a rule with a pure mathematical approach and avoid using an IF then you should.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
MarenC
Regular Participant
Posts: 405
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Ultimate Long TM1 quiz

Post by MarenC »

There will always be IF statements. The point is that a Boolean test is one of the most, if not the most expensive functions you can use in a rule. If you can write a rule with a pure mathematical approach and avoid using an IF then you should.
Of course, but saying that is a bit different to saying this:
Anyway, you should avoid IFs in TM1 in any case.
User avatar
gtonkin
MVP
Posts: 1235
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Ultimate Long TM1 quiz

Post by gtonkin »

...you should avoid IFs in TM1...
Sentiment is valid. Avoid IFs where possible.

Here is a "nice" feeder example I saw in a previous model:

Code: Select all

#--Feed XYZ Measure
['XYZ Amount','Actual'] =>
	DB(
		IF(
			ATTRS('Period',!Period,'Planning')@='Y' & 
			DB('Cube', !Dim1, ..., !DimN-1, 'XYZ Amount')<>0,
            'Cube',
			''), 
		!Dim1, ..., !DimN-1, 'XYZ %');
How do we avoid IFs?
- In the first instance we are doing an "AND" on two criteria - still not 100% sure if TM1 evaluates both or stops if the first is false.
- If we split into multiple IFs, we are looking up on the Period using an attribute, besides the change in value not triggering feeders, we should rather be using a consolidation of Periods that are flagged as Planning = Y and use the consolidation on the Left. One IF gone.
- The other IF is something I have seen over the years where people try to optimise by feeding non-zero values. TM1 does this by default. We are already specifying the measure on the Left and feeding would only be triggered where it has values, albeit a zero rather than UNDEF. This statement is superfluous imo and could be removed too leaving us with no IFs.

So back to the sentiment: "Avoid IFs in TM1" sounds like solid advice.
BR, George.

Learn something new: MDX Views
MarenC
Regular Participant
Posts: 405
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Ultimate Long TM1 quiz

Post by MarenC »

Avoid IFs where possible.
Seems more reasonable than,
you should avoid IFs in TM1 in any case.
But this seems reasonable too:
As with virtually any computing based system, it is impossible to avoid ifs in TM1.
User avatar
Elessar
Community Contributor
Posts: 401
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: Ultimate Long TM1 quiz

Post by Elessar »

Ok guys, let's close #44. I repeat question #45:

What are the differences between this methods of data clear?
ViewZeroOut (view covering the whole cube)
CubeClearData
CubeDestroy+CubeCreate

2 days left
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
MarenC
Regular Participant
Posts: 405
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Ultimate Long TM1 quiz

Post by MarenC »

Hi,

They will all clear the data from the cube.
CubeDestroy and Create will delete all sandbox data whereas the other methods will not clear or delete those.
CubeClearData is faster than ViewZeroOut of the whole cube (unless maybe you wrap an if around it :D )

Maren
lotsaram
MVP
Posts: 3687
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Ultimate Long TM1 quiz

Post by lotsaram »

I don't much participate here but as I finish my lunch I might have a quick stab at this one.

A : ViewZeroout (whole cube) will:
- clear all data from the cube
- take a variable amount of time depending on the volume of data in the cube (could take quite a while of cube is large)
- assuming logging is on for the cube and not disabled will write the clear as individual cell transactions to the tm1s.log

B : CubeClearData will:
- clear all data from the cube
- complete almost instantly regardless of the cube size and volume of data
- not leave any trace i the tm1s.log regardless of the status of transaction logging for the cube

C : CubeDestroy followed by CubeCreate will:
- result in a new empty cube (i.e. same result as ViewZeroOut and CubeClearData)
- complete almost instantly regardless of the cube size and volume of data
- not leave any trace i the tm1s.log regardless of the status of transaction logging for the cube
- also destroy any rule file the cube may have had
- also destroy any public views the cube may have had
- also destroy any private views of the cube from all currently logged in users (for users not logged in with private views I'm honestly not sure. There's an outside chance they may persist)
- also destroy any sandbox data of the cube from all currently logged in users (for users not logged in I'm fairly certain their sandbox data would also be wiped)
- also destroy any cube dependencies which the server may have established in the case that there are "downstream" cubes which reference the recreated cube in their rules (reestablishing the dependencies during future queries could cause locks in the model)

If you have this requirement then take option B. Do not take option C.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
User avatar
Elessar
Community Contributor
Posts: 401
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: Ultimate Long TM1 quiz

Post by Elessar »

Correct answer:
Is in great stab by lotsaram: viewtopic.php?p=84096#p84096
But I need to add my 50 cents: CubeDestroy will also delete cube's control cubes (}Picklist, }CellSecurity)

But you will not get a point, being MVP ;)
So, winner of this round is MarenC!

Question #45:
I've created a subset in Architect using "All" button. After I had saved it with "All elements" name, it is shown with static subset icon. Will new elements appear in "All elements" dimension after I add them to the dimension?

Image 71.png
Image 71.png (13 KiB) Viewed 2459 times
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
ascheevel
Community Contributor
Posts: 304
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Ultimate Long TM1 quiz

Post by ascheevel »

Yes, parameter 278 in the sub file will have a 1 which dictates that the subset, despite being static, will be populated with all elements in that dimension anytime it is referenced or opened. As such, there also will be no element count parameter 270 in the .sub file or the list of static elements following it.
User avatar
Elessar
Community Contributor
Posts: 401
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: Ultimate Long TM1 quiz

Post by Elessar »

Correct answer:
Yes, they will.

Winner of this round is ascheevel!

Question #46:
ADMIN, SecurityAdmin, DataAdmin have NONE access for all applications in }ApplicationSecurity cube. Will a user from ADMIN role see applications?
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
ascheevel
Community Contributor
Posts: 304
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Ultimate Long TM1 quiz

Post by ascheevel »

Yes
User avatar
Elessar
Community Contributor
Posts: 401
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: Ultimate Long TM1 quiz

Post by Elessar »

Correct answer:
Yes

Winner of this round is ascheevel! (Well, it was again 50% to guess, and both were correct! You are lucky!)

Question #47:
Which TI function will terminate process and cancel all changes it has done?
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
MarenC
Regular Participant
Posts: 405
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Ultimate Long TM1 quiz

Post by MarenC »

Hi,

I am going to have a wild stab in the dark and say "ProcessExitByProcessRollback" will terminate a process and cancel all changes it has done.

Maren
EP_explorer
Regular Participant
Posts: 221
Joined: Sat Dec 04, 2010 2:35 pm
OLAP Product: PAL
Version: 2.0.9
Excel Version: 2016

Re: Ultimate Long TM1 quiz

Post by EP_explorer »

In Versions since 2020 year (may be 2019 - I don't remember exactly) ProcessError() canceles all changes that has done during the process.

In early versions it didn't cancel changes.
User avatar
Elessar
Community Contributor
Posts: 401
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: Ultimate Long TM1 quiz

Post by Elessar »

Correct answer:
ProcessError or ProcessRollback.

@Maren: I have not used "ProcessExitByProcessRollback" before and checked it on different environments: it does not terminate process and does not cancel changes. It looks like either the function itself, or it's description in TM1 Reference is wrong. Thus, if you can prove that it does terminate process and cancel all changes, I'll share this point between you and EP_Explorer.

Meanwhile, winner of this round is EP_explorer!

Question #48:

There is a cube with months as columns. Month 04 and 08 have values, all other months are 0.
Image 74.png
Image 74.png (4.45 KiB) Viewed 900 times

Write an MDX to show the last month with values (08) and all months before that (01-07).
Image 75.png
Image 75.png (2.78 KiB) Viewed 900 times
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
MarenC
Regular Participant
Posts: 405
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Ultimate Long TM1 quiz

Post by MarenC »

This seems a tricky one and I have used my own current test set up to apply this to:

Code: Select all

{EXCEPT(
FILTER( TM1FILTERBYLEVEL( {TM1SUBSETALL( [Period] )}, 0), 
  [Period].currentmember.properties('Period Number') <= TM1Member(TAIL(FILTER(TM1FILTERBYLEVEL( {TM1SUBSETALL( [Period] )}, 0), [CubeName].([Period].currentmember,[Measure].[Value])<>0),1).item(0),0).properties('Period Number') 
), 
       FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Period] )}, 0)}, [Period].currentmember.properties('PeriodNumber') = '')
)}
Be interested if there is something easier than this!

Maren
Adam
Posts: 118
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.x
Excel Version: Microsoft 365 x64

Re: Ultimate Long TM1 quiz

Post by Adam »

How about this:

Code: Select all

{
   [Month].[Month].[01] : 
   StrToMember('[Month].[Month].[' + (Tail(NonEmpty([Month].[Month].[01] : [Month].[Month].[12]), 1)).Item(0).Item(0).Name + ']')
}
I've started blogging about TM1, check it out: www.havaslabs.com

Take care,
Adam
User avatar
Elessar
Community Contributor
Posts: 401
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: Ultimate Long TM1 quiz

Post by Elessar »

Correct answer:
The question is from this thread, thanks Marioler for question idea: viewtopic.php?f=3&t=15786

Ty and Wim have provided a good solution there:

Code: Select all

PERIODSTODATE( [Manad].[Total Year].Level, TM1MEMBER( TAIL( 
FILTER( [Manad].[Total Year].children, [cubename].([z.TestRowEntryList].[R01]) > 0), 1).item(0), 0))
@Adam: your MDX has a good idea, but it does not check the cube if value is zero or not

winner of this round is MarenC!

Question #49:

Is there a rule function - analog to Excel's FLOOR? If not, how can we calculate it?
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
ascheevel
Community Contributor
Posts: 304
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Ultimate Long TM1 quiz

Post by ascheevel »

Which definition of Floor?

If simply rounding down to nearest integer, INT will achieve this.

Code: Select all

## result of floor will be same as RoundDown(3.7, 0) in excel
['value'] = N: 3.7;
['floor'] = N: INT(['value']);
## expect floor to be 3


If needing to round to the nearest multiple of a significance value, you can subtract the result of MOD(value, significance) from the original value

Code: Select all

## result of floor will be same as FLOOR(21.5, 2) in excel: 
['value'] = N: 21.5;
['significance'] = N: 2;
['floor'] = ['value'] - MOD(['value'], ['significance']);
## expect floor to be 20 but you need to add an if statement when there's a sign mismatch to get same behavior as excel

## result of floor will be similar as FLOOR(21.5, -2) in excel:
['value'] = N: 21.5;
['significance'] = N: -2;
['floor'] = IF(SIGN(['value']) <> SIGN(['significance'], 0, ['value'] - MOD(['value'], ['significance']));
## expect floor to be 0, excel would return #num!



Post Reply