Ultimate Long TM1 quiz

[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 (44.75 KiB) Viewed 2013 times

[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

[gtonkin]

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

[Elessar]

Yes Ordinary subset-based picklist "subset:JobType:All leaves', where "All leaves" has alias enabled

[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 (50.72 KiB) Viewed 1855 times

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

[Elessar]

OK! But why does "Trace cell" show "fed" with my feeder?

[Elessar]

Anyone? 1 question is still without answer

[MarenC]

Hi,

Not from me but I am eagerly awaiting the answer!

Maren

[Adam]

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.

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

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

(I believe "!Employee" is a typo in your answer?)

Sorry, I copied your feeder statement and only adjusted left of the =>.

[Elessar]

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

[MarenC]

Hi,

Who said anything about poor admins wading through the data

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

[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>';

[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

[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

[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 (16.29 KiB) Viewed 1395 times

[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