Ultimate Long TM1 quiz

User avatar
Elessar
Community Contributor
Posts: 394
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: 31 0 0. A is not feeding anything, being consolidated. It's children B and C both feed A1.

Winner of this week is ascheevel!

Question #6:
We are done with feeders, well done! Now new set of questions.

There is "Employee" cube with employees list, their job types and salaries (from 24Retail sample). Picklist for Job Type uses subset with alias enabled (element names are different). I've created a new "Employee analysis" cube with extra "JobType" dimension.
Write a rule (without feeder. Spoiler: this will be the next question) to transfer data from "Employee" to "Employee analysis" cube.
Dimension order in "Employee" cube:
  • Organization
  • EmployeeList
  • Year
  • Version
  • Employee
Image 62.png
Image 62.png (44.75 KiB) Viewed 8856 times
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
MarenC
Regular Participant
Posts: 394
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 assuming the Employee analysis dimension order is the same as the Employee Cube order.

Code: Select all

['Current Salary']=N:

If( DB( 'Employee', !Organization, !EmployeeList, !Year, !Version, 'Job Type' ) @= ATTRS( 'JobType', !JobType, 'Alias' ),
	DB('Employee', !Organization, !EmployeeList, !Year, !Version, !Employee ),
	STET
  );
Maren
User avatar
gtonkin
MVP
Posts: 1234
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 »

Do readers also assume that the <StoreAlias=F> modifier was not used on the picklist for JobType?
BR, George.

Learn something new: MDX Views
User avatar
Elessar
Community Contributor
Posts: 394
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 »

Yes :geek: Ordinary subset-based picklist "subset:JobType:All leaves', where "All leaves" has alias enabled
Last edited by Elessar on Thu Oct 03, 2024 12:33 pm, edited 1 time in total.
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
User avatar
Elessar
Community Contributor
Posts: 394
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:

Code: Select all

SKIPCHECK;

['Current Salary'] = N: IF(DIMIX('JobType', !JobType) = DIMIX('JobType', DB('Employee', !organization, !EmployeeList, !Year, !Version, 'Job Type')),
	DB('Employee', !organization, !EmployeeList, !Year, !Version, !Employee),
	0);
Using DIMIXes is better than comparing alias, because you do not need to know alias name here.

Winner of this week is MarenC!

Question #7:
I've written a feeder from source "Employee" cube:

Code: Select all

['Current Salary'] => DB('Employee analysis', !organization, !EmployeeList, !Year, !Version, 
	DB('Employee', !organization, !EmployeeList, !Year, !Version, 'Job Type'), 
	!Employee);

It works fine until user changes Job Type. The cell does not sum up to Total. But "Trace cell" states that source cell does feed target cell. What's going on here? How should I write a correct feeder?
Image 63.png
Image 63.png (50.72 KiB) Viewed 8675 times
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
Adam
Posts: 117
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 »

Also feed from Job Type - it’s a string cell so any change to it will cause FEEDER to trigger and re-trigger:

Code: Select all

['Job Type'] => DB('Employee analysis', !organization, !EmployeeList, !Year, !Version, 
	DB('Employee', !organization, !EmployeeList, !Year, !Version, 'Job Type'), 
	!Employee);
I've started blogging about TM1, check it out: www.havaslabs.com

Take care,
Adam
User avatar
Elessar
Community Contributor
Posts: 394
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! But why does "Trace cell" show "fed" with my feeder?
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
User avatar
Elessar
Community Contributor
Posts: 394
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 »

Anyone? 1 question is still without answer
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
MarenC
Regular Participant
Posts: 394
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,

Not from me but I am eagerly awaiting the answer!

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

Elessar wrote: Sun Feb 18, 2024 12:20 pm OK! But why does "Trace cell" show "fed" with my feeder?
Source is feeding, but not to the target cell shown in the function, which is seemingly a live functional statement.
I've started blogging about TM1, check it out: www.havaslabs.com

Take care,
Adam
User avatar
Elessar
Community Contributor
Posts: 394
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:
Firstly, feeder from numeric "Salary" do not know that Job Type is changed, and feeder will not re-fire after Job type is changed.
Secondly, "Trace feeders" do not check if target cell is fed. It checks if current feeder written in rule, with current data (like Job type), should fire a feeder or not.

The correct feeder will be:

Code: Select all

FEEDERS;

['Job Type'] => DB('Employee analysis', !organization, !EmployeeList, !Year, !Version, 
	DB('Employee', !organization, !EmployeeList, !Year, !Version, 'Job Type'), 
	'Current Salary'); 
Winner of this week is Adam! (I believe "!Employee" is a typo in your answer?)

Question #8:
In the same example: As I've said before, picklist uses an alias-enabled subset. The real element names are "A-Z". After "TM1 consultant" job type (Element "F") is changed to "PA Consultant", we will lose its data in analysis cube. What can we do with this?
Attachments
Image 73.png
Image 73.png (69.89 KiB) Viewed 8276 times
Last edited by Elessar on Sat Mar 02, 2024 12:45 pm, edited 3 times in total.
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
MarenC
Regular Participant
Posts: 394
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,

It is Friday so bear with me but I would have said either change the Job Type in the Employee cube to be the updated alias or use one of the aliases to store the old job type value.

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

Elessar wrote: Fri Feb 23, 2024 4:17 pm(I believe "!Employee" is a typo in your answer?)
Sorry, I copied your feeder statement and only adjusted left of the =>. :-)
I've started blogging about TM1, check it out: www.havaslabs.com

Take care,
Adam
User avatar
Elessar
Community Contributor
Posts: 394
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 »

MarenC wrote: Fri Feb 23, 2024 4:32 pm Hi,

It is Friday so bear with me but I would have said either change the Job Type in the Employee cube to be the updated alias or use one of the aliases to store the old job type value.

Maren
Good, but we need something automated and admin-friendly. TM1 administrator can forget to change old alias to new (and there can be million of places).
Old alias can store 1 historical value, but what if the changes are coming monthly? We cannot store all the aliases
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
MarenC
Regular Participant
Posts: 394
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,

Who said anything about poor admins wading through the data :D

There are a few ways to automate using TI etc but I was thinking to myself that I would never rely on aliases if I were designing this and would have a code id, and then I noticed in your last screenshot that there is a code id of sorts, job type a,b,c,d etc. So I would use that, and while I was at it I would make the alias include the code just to avoid the chances of any duplicate aliases.

Thinking about this a bit more, I still guess we have the issue of TM1 Consultant no longer being the alias but being the value in the cube. My reasoning is that we need to use the Job Type element Name in the original picklist and have a description field so the user can be assured they have picked the correct Job Type. Then everything can hang off this.

The objection to this could be that the user might not know the element name but only the description, in that case its back onto the Admins to maintain this via TI or something else more elaborate, like a whole new cube which tracks descriptions.

I know you have something really simple in mind, but it isn't jumping out at me yet!

Maren
User avatar
gtonkin
MVP
Posts: 1234
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 »

Hi Maren,
Not sure if you are familiar with this undocumented feature but a picklist can show a description/alias from a set but store the code/principal name.

Use the following syntax and set the StoreAlias=F to store the principal name otherwise T for the alias.

Code: Select all

Subset:Dimension:Subsetname<StoreAlias=F>
e.g.

Code: Select all

['SAP Cost Centre','Value']=S:'Subset:Cost Centre:_S-All N-Description<StoreAlias=F>';
BR, George.

Learn something new: MDX Views
MarenC
Regular Participant
Posts: 394
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 George,

I was definitely not aware of that feature, many thanks! You obviously keep your hear well to the ground to dig up all this stuff.

I doubt that is what the Quiz Master had in mind though as way of a solution...

Maren
User avatar
Elessar
Community Contributor
Posts: 394
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 »

"Quiz Master" has only questions in mind, not solutions :) So the more (good, working) solutions you provide the better

But yes, <StoreAlias=F> is also in my solutions list, and George could receive half a point if he was not MVP
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
User avatar
Elessar
Community Contributor
Posts: 394
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:
There are many ways to do this. Including:
  1. Store element name, not its alias. There are several ways:
    1. Websheet or book with button. After user fills picklists, he needs to press button. The button launches process which stores element names in a different cell used in rules.
    2. Websheet with DBSS. DBSS sends element names to a different cell used in rules. I've described this approach in this tread: viewtopic.php?f=3&t=15737
  2. Create a process, which will search for an old alias in all cubes and replace it with new one after alias is changed. This process should be triggered by a dimension update process when alias changes
  3. Use StoreAlias (thanks ppniederbracht for bringing this up here: viewtopic.php?t=11372)
Winner of this week is MarenC!

Now easy questions are coming!

Question #9:
What do Slice and Snapshot to excel do? What is the difference? What software is needed to use files generated by each button?
Image 47.png
Image 47.png (16.29 KiB) Viewed 8271 times
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
MarenC
Regular Participant
Posts: 394
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,
  • Slice exports the data in the view to excel and allows for write back to the view/cube (via DBRW formulas). Note that the values will be the current values and not the values at the time of the slice.
  • Snapshot exports the data in the view to excel as static values.
Need excel for snapshots.
Need client excel add in for slices, ie Perspectives or PAX (not tried with PAX so assuming this!).
Spreadsheet services running for slices I assume.

Maren
Post Reply