Ultimate Long TM1 quiz

[ascheevel]

Ceiling or Ceiling.Math in excel?

If you simply want similar as RoundUP which would be similar as Ceiling.Math the below would give you similar results in tm1.

Code: Select all

## result of ceiling will be same as Ceiling.Math(3.7) in excel: 4
['value'] = N: 3.7;
['ceiling'] = N: ROUND(['value'] + 0.499999, 0);

## result of ceiling of 0.326 will be 1

If we want to round up to the nearest multiple of significance, we can subtract the result of MOD(value, significance) from the original value and then add back the significance.

Code: Select all

## result of ceiling will be same as Ceiling(4.5, 2) in excel: 6
['value'] = N: 4.5;
['significance'] = N: 2;
['ceiling'] = N: ['value'] - MOD(['value'], ['significance']) + ['significance'];

# result of ceiling 0.326 with significance 0.01 will be 0.33

[Elessar]

Correct answer:
Is here: viewtopic.php?p=84204#p84204

Question idea is taken from here: http://www.tm1forum.com/viewtopic.php?p=43103
And i like Duncan's answer also:

Code: Select all

['roundup'] = - INT( - ['value'] ); 

winner of this round is ascheevel!

Question #51:

I have an Exploration view. I Need to display attribute for each element in row ("employee name" in this example). How can I do this?

Image 269.png (63.08 KiB) Viewed 36268 times

[MarenC]

Hi,

I would add a With Member expression to the MDX to retrieve the attribute.

WITH MEMBER [Employee].[Employee].[Emp Name] AS [Employee List].[Employee List].CURRENTMEMBER.PROPERTIES("Employee Name")

Then add the above to the Select Statement in the appropriate place.

Maren

[Elessar]

Correct answer:

Well, it was more a PAfE user's question, and I've expected answer with "Insert user column" + DBRW(}ElementAttributes).

Image 1.png (24.54 KiB) Viewed 35990 times

But, Maren has proved his MDX knowledge again, so:
Winner of this round is MarenC!

!LAST! Question #52:

I need to copy data from "Working" version to "Submitted" STET calculation-free version inside a cube. If I create 1 process for this, where data source is view with Working version, and simple CellPutN/S to Submitted version in DATA tab, it takes 5 minutes to execute. If do it with 2 processes: 1st to export Working version to file, 2nd to load to Submitted version from file, it takes 3 seconds to execute both. Why?

[ascheevel]

I doubt I have the technicalities right, but I've always explained it as the calc cache being invalidated from the CellPut and thus forcing the source view to be re-evaluated/recalculated when you try to read from and write to the same cube in a TI. I believe it's also particularly sensitive to MDX subsets based on cube value. For inexplicable reasons, I usually do a bad surfer voice when I describe it too, "dude, the TI looks at the value in the source view and says, 'i'm just gonna copy you over here' and then !PA-POW! the cache gets blown out and it needs to figure out where the heck it is in the source view again before moving onto the next record. It's gnarly, don't do it; just export/import and be done with it."

Because the export/import works faster in your example and I'm assuming you're describing two independent executions and not one where it runs slow the very first time and then fast thereafter, I think we can rule out feeders firing for the first time when the values in the target version change from a zero to a non-zero on the first run.

[Elessar]

Correct answer:
As we've discussed before, stargates are very fragile. Each time process makes CellPutN, stargate view is deleted. When we use an export process, stargate view is calculated once for the whole view.

Winner of this round is ascheevel!

[Elessar]

Wellllll, Finally, at long last, the Year-Long-Quiz is over!
it was very interesting experience. It looks like this topic has the most answers on TM1forum.com. Thanks to all participants!

I have re-checked the leaderboard and fixed some points, but the leader was already known a month ago. Super-puper winner of the Ultimate one-year long TM1 quiz is MarenC! Congratulations! You can add it to your CV and show it to your boss and ask him to increase salary.
I'll put your nickname (and/or your real name, photo, any picture) to the first post of this topic.

@Admins, I think, Maren has proved his TM1 knowledge and he now he deserves "Community Contributor" rank. Along with Adam as "Community Contributor" and Ascheevel as MVP.

One year has passed, but the quiz will continue: I still have some questions, and a bag of questions from George Tonkin. Questions will not be as regular. There will be 2 separate leaderboards" one-year and infinite one. Stay tuned.

[MarenC]

Hi,

I have to say that I thought the long quiz was a great idea for a forum.

Thank you for your efforts Elessar, much appreciated by me.

Maren

[Elessar]

Well, it was a good rest, let us continue this super-long quiz.

Question #53:
Gurus say that you need to use ConsolidateChildren very carefully. Why?

[Emixam]

I remember reading somewhere it can impact performance but I don't know why. I always avoid this function because c-level rule does not impact higher-lever consolidations and it messes up the natural consolidation of elements. TM1 always calculate consolidations first THEN override consolidation value with C: formula.

[MarenC]

Like Emixan, I have read that it is inefficient but never exactly why it is inefficient.

I imagine it is because it adds extra calculation to the cube, whereas the natural consolidation is a much quicker calculation.

I have had cause to use it in the past, so I don't avoid it as such, but I don't think the use case for it arises that much.

[lotsaram]

People c'mon! I expect a bit better!

Q: What makes rule calculations in TM1 efficient?
A: The sparse consolidation algorithm. (Or colloquially "feeders".)

Sparse consolidation is not applied to ConsolidateChildren. There is no jump straight from populated leaf cells to the calculation being requested. Every single intersection in the dimensional cross-product below the point being evaluated must be visited and its value collected in order to calculate the result. Hence if more than 1 or 2 dimensions are included in the cross-product to evaluate, and especially if dimensions are larger, then the number of cells to evaluate explodes and performance suffers as a result. Think of it as being like skipcheck and feeders are turned off for any calculation using ConsolidateChildren. (Because effectively that is exactly what is happening.)

[Elessar]

Correct answer:
Is here: viewtopic.php?p=84295#p84295

Thanks Lotsa for detailed answer. I think we can now remove "MVPs will not receive points" rule, as 1-year-quiz is over

So, winner of this round is lotsaram!

Question #54:

Gurus advise to avoid using ConsolidatedAVG function. Why? (spoiler: Sparse consolidation is fine here)

[MarenC]

Hi,

I am not aware of any gurus giving advice about not using consolidatedavg, so I will say this is a trick question.

I would only say that it is probably not that widely used, so tread carefully!

BTW - great explanation from lotsaram on the previous answer!

Maren

[Elessar]

Ok, too much time has passed.

Correct answer:
Stargate views do not work with ConsolidatedXXX functions (they will just not created when a cell with ConsolidatedXXX is used).
BTW, I could not find a proof, but I've learned this from Andreas Kugelmeier on one of his best practices webinars. I am not sure if it is still relevant on latest PA versions.

Well, winner of this round is Andreas Kugelmeier!

Question #55:

We have 2 equal cubes: Cube1 and Cube2.
There is a rule in Cube2:

Code: Select all

[] = N: DB('Cube1', !Account, !Measure); 

User does not have access to Cube1 (NONE in cubesecurity) and has access to Cube2 (READ). Will he see the numbers in Cube2, which came from inaccessible Cube1?

[MarenC]

I could not find a proof, but I've learned this from Andreas Kugelmeier on one of his best practices webinars. I am not sure if it is still relevant on latest PA versions.

Hmmmm, sounds like it was a trick question!

For Question 55, yes they will see the values in Cube 2 which came from cube 1.