Ultimate Long TM1 quiz

User avatar
Elessar
Community Contributor
Posts: 363
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:
}Picklist cube has priority over "picklist" attribute (if cell is not empty).

Winner of this round is MarenC!

Question #25:
(One of my favorite ones)
Write a rule to make not SUM, but PRODUCT of descendants in consolidated elements:
Image 55.png
Image 55.png (15.96 KiB) Viewed 704 times
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
Mark RMBC
Community Contributor
Posts: 293
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Ultimate Long TM1 quiz

Post by Mark RMBC »

Hi,

Don't think anything new in planning Analytics solves this, so the old method should still work.

Create a new measure called MinusValue

Rule for MinusValue is:

Code: Select all

['MinusValue']=N: IF(['B']<0,1,0);
Create a new measure called NatLog

Rule for NatLog is:

Code: Select all

['NatLog']=N:IF(['B']<>0, LN(ABS(['B'])),0);
Rule for C Level is:

Code: Select all

['B']=C:EXP(['NatLog']) * -1^['MinusValue'];
Old method is outlined here: viewtopic.php?p=26007#p26007

regards,

Mark
EP_explorer
Regular Participant
Posts: 213
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 »

As I Completely forget features on exp and logarithm and don't want to refresh my memory
so I have another solution
Question25_1.JPG
Question25_1.JPG (56.4 KiB) Viewed 631 times
The most part of IFs has aim to avoid multiplying for zero (as a I understand we don't want to receive zero as answer although it seems it is correct answer for input data)
User avatar
Elessar
Community Contributor
Posts: 363
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 described here: viewtopic.php?p=83664#p83664
Beautiful solution with a little Math and without recursion and next/previous elements.

Winner of this round is Mark RMBC! Welcome on board!

Question #26:
There are 4 rules to transfer data from ABC cube to ABC1 cube. Which of them will work correctly? Which is the best?
Image 59.png
Image 59.png (71.44 KiB) Viewed 563 times
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
MarenC
Regular Participant
Posts: 364
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 believe the first 2 rules in your screenshot will work and the other 2 will error, as ABC1 is the not the primary name of the element in ABC.

Funnily enough I don't know which of the first 2 is best, be interested to see the thinking behind the answer to that one. But given you are feeding from a text attribute I will guess that the first rule is the best one to use!

Maren
Adam
Posts: 107
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 »

I like the first choice the most but would opt for DB against }ElementAttributes vs ATTRS as wiser people than me have said it’s faster.

I don’t like the Alias approaches as that doesn’t scale beyond 1 to 1 mapping. Same for DIMIX, that doesn’t scale when element order is different between ABC and ABC1.
I've started blogging about TM1, check it out: www.havaslabs.com

Take care,
Adam
User avatar
Elessar
Community Contributor
Posts: 363
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:
1st and 2nd will work. For "What is best" - it's a discussion question, firstly to discuss what mapping is better to use - text attribute-based, or alias-based. I prefer text attribute: this allows you to make not only 1*1 mappings, and also allows crazy mappings like "Map ABC1's B1 to ABC's C1".

And also, thanks Adam for note: Cubewise has proved that DB(}ElementAttributes...) works faster than ATTRS: https://code.cubewise.com/blog/7-tips-t ... ics-rules/

Winner of this round is MarenC!

Question #27:
A user has suppressed zero rows and wants to save list of non-zero accounts to a private subset. How can he perform this?

Image 60.png
Image 60.png (100.48 KiB) Viewed 431 times
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
michalb
Posts: 18
Joined: Thu Dec 03, 2020 3:45 pm
OLAP Product: TM1
Version: 2.1
Excel Version: 2016

Re: Ultimate Long TM1 quiz

Post by michalb »

What I always do is apply "suppress zeros" in the rows. Then, I select all the elements in the rows of the suppressed view by clicking on the row header. Next, I right-click on any element and choose "keep". In the subset editor for the dimension in the rows, the subset I want to save will be on the right side. Finally, I choose "save as" and select "private".
MarenC
Regular Participant
Posts: 364
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 usually ensure access to the set editor is switched off, then I don't have to worry about this!

Maren
User avatar
Elessar
Community Contributor
Posts: 363
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:
  1. michalb's one: "I select all the elements in the rows of the suppressed view by clicking on the row header. Next, I right-click on any element and choose "keep". In the subset editor for the dimension in the rows, the subset I want to save will be on the right side. Finally, I choose "save as" and select "private"."
  2. Export to excel, copy-paste accounts to subset editor
  3. Write an MDX pointing to cube (need to know MDX)
  4. (for mammoths): In Architect, save view and use filter by view extract
Winner of this round is michalb!

I'll be on a vacation next 2 weeks, so here are 2 questions in a row:

Question #28:

User has READ access to the process, which writes data to a cube. User does not have access to the cube (NONE in }CubeSecurity). What will happen when user launches the process?

Question #29:

How can you transfer data between two different servers (let's say we are on on_prem PA)? What are PROs and CONs? Which is the best?
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
Post Reply