Page 1 of 1
[SOLVED] fill cube with values filtered by attributes
Posted: Mon Sep 23, 2013 1:20 pm
by moritz
Hello everyone,
im new to TM1, but i have experience in web-development coding.
I've got a short question, which bothers me for quiet a few hours.
I want to get the values cumulated for all elements with the Attribute "Laufend".
Code: Select all
['Output lfd. Geschäft'] = N:DB('TX_TF_RSM_OI','Output',!TX_TF_RSM_Datum,'Gesamt','Gesamt',
[[All elements with the attribute "Laufend".]]
,'Gesamt','Gesamt');
I'm not sure if its the right use for attributes.
Another thing is my Rule-Editor looks like this:
Any instructions how i get the normal one with code higlighting?
I'm looking forward for your answers and feedback.
Greetings from Cologne, Germany!
Moritz
PS Can you recommend any other TM1 Rule Tutorials, besides the IBM Guides?
Re: fill cube with values filtered by attributes
Posted: Mon Sep 23, 2013 1:40 pm
by tomok
Something like this:
Code: Select all
['Output lfd. Geschäft'] = N:IF(ATTRS('TX_TF_RSM_Standort',TX_TF_RSM_Standort,'Art')@='Laufend',DB('TX_TF_RSM_OI','Output',!TX_TF_RSM_Datum,'Gesamt','Gesamt','Gesamt','Gesamt'),0);
Also, don't forget you'll need to feed Output lfd. Geschäft.
What's up with four dimensions all having the same element called "Gesamt'? There's nothing to stop you from doing this but I would argue it's going to be confusing to end users and it's going to require the Dimension:Element syntax when writing shorthand rules. Not best practice in my book, YMMV.
Re: fill cube with values filtered by attributes
Posted: Mon Sep 23, 2013 1:53 pm
by moritz
Hi Tomok,
thanks for your reply.
TX_TF_RSM_Standort is not a Dimension of the cube i want to fill with data.
(I think that is the reason your snippet triggers an error. Syntax-Error)
What do you mean with "don't forget you'll need to feed Output lfd. Geschäft."?
This works fine:
Code: Select all
['Input'] = N:DB('TX_TF_RSM_OI','Input',!TX_TF_RSM_Datum,'Gesamt','Gesamt','Gesamt','Gesamt','Gesamt');
I'm not able to change the structure of the other cubes in any way.
"Gesamt" means all elements of the dimension consolidated.
But thanks for your info.
Re: fill cube with values filtered by attributes
Posted: Mon Sep 23, 2013 2:16 pm
by tomok
moritz wrote:TX_TF_RSM_Standort is not a Dimension of the cube i want to fill with data.
Then you are not going to be able to do what you want. The only way the target cube is going to be able reference the attribute for an element is if those elements actually exist in the cube, otherwise you would have to hard-code each element individually in a rule. I don't know anything about the structure of your model or the business purpose behind what you are doing so I cannot tell if this is an option or not.
moritz wrote:What do you mean with "don't forget you'll need to feed Output lfd. Geschäft."?
Read the TM1 Rules documentation, especially the section covering Feeders.
Re: fill cube with values filtered by attributes
Posted: Mon Sep 23, 2013 9:11 pm
by lotsaram
Gruss Moritz,
TM1 OLAP is not like an RDBMS, you cannot do conditional sums based on an attribute value (conditional filtered lists driven by MDX yes but not sums unless you use additional measures.) The way this is achieved in TM1 is to use the attribute values as a reference point to build a hierarchy in the target dimension and then use the database's natural consolidation to return the sum. In this case you want to build a consolidation point called "Laufend" in the Standort dimension. This is the simplest way. Then in the cube that you wish to populate you just use DB reference to cube with RSM Standort dimension and pull value for "Laufend".
And Tomok is right, it is a bad design that is very confusing for users (even non-DAU) where top node of every dimension is named the same. Same criticism applies to a model with English nomenclature with "Total" at the top of each dimension, this is just bad practice. When looking at a crosstab or other report the user then needs to click and expand on each dimension button to see what dimension they are dealing with. This hinders users and is annoying. Much better to include a meaningful name in the top of the hierarchy that signals to the user what dimension they are looking at, z.B. "Gesamt Standorte", "Gesamt Geschäftsbereiche", "Gesamt Sparten" oder "Standorte - Gesamt", "Geschäftsbereich - Gesamt", "Sparte - Gesamt" oder "Alle Standorten", "Alle ......
you get the picture.
Re: fill cube with values filtered by attributes
Posted: Tue Sep 24, 2013 5:09 pm
by moritz
Thanks again for your answers.
I will definitely have a deeper look in the Guides provided by IBM.
I got it by editing the elements of the Dimension "Standort".
Some Elements are consolidated under "Gesamt>Berlin" and again under "Laufend"
I'm not sure, if its good to use it like this.
