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.
Ultimate Long TM1 quiz
-
- 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
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- 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
Of course, but saying that is a bit different to saying this: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.
Anyway, you should avoid IFs in TM1 in any case.
- 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
Sentiment is valid. Avoid IFs where possible....you should avoid IFs in TM1...
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 %');
- 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.
-
- 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
Seems more reasonable than,Avoid IFs where possible.
But this seems reasonable too:you should avoid IFs in TM1 in any case.
As with virtually any computing based system, it is impossible to avoid ifs in TM1.
- 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
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
What are the differences between this methods of data clear?
ViewZeroOut (view covering the whole cube)
CubeClearData
CubeDestroy+CubeCreate
2 days left
-
- 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
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
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
-
- 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
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.
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.
- 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
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?
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?
-
- 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
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
- 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
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?
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?
- 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
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?
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?
-
- 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
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
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
-
- 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
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.
In early versions it didn't cancel changes.
- 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
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.
Write an MDX to show the last month with values (08) and all months before that (01-07).
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.
Write an MDX to show the last month with values (08) and all months before that (01-07).
-
- 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
This seems a tricky one and I have used my own current test set up to apply this to:
Be interested if there is something easier than this!
Maren
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') = '')
)}
Maren
-
- 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
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 + ']')
}
- 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
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:
@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?
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))
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?
-
- 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
Which definition of Floor?
If simply rounding down to nearest integer, INT will achieve this.
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
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!