Ultimate Long TM1 quiz

[lotsaram]

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.

[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.

[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.

[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.

[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

[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 )

Maren

[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.

[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 (13 KiB) Viewed 1970 times

[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.

[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?

[ascheevel]

Yes

[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?

[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]

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.

[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 (4.45 KiB) Viewed 411 times

Write an MDX to show the last month with values (08) and all months before that (01-07).

Image 75.png (2.78 KiB) Viewed 411 times

[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]

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 + ']')
}