How to restrict unwanted combination of multidimension?

[renken158]

Hi expert,
This is Harvey, a new guy in TM1 world, I have a question need your help now.
I have one cube contains two dimensions, one is cost center, the other is internal order.
Cost center:
1000;
2000;
3000;
Internal order:
A(has an attribute with value is 2000)
These two dimensions will have three combinations, which are
1000, A;
2000, A;
3000, A;
but what I really want is
2000, A;
could you please tell me how to do this, thanks a lot.

[renken158]

Hi, Expert,
please let me make my question more understood, please see the below picture.

Capture.PNG (23.31 KiB) Viewed 6529 times

what I really want the row format should be:
1020000,55014001;
1030000,55014002;
1030020,550137;
Thanks!

[tomok]

Depends on what you mean by "restrict", but I am assuming you mean to make those combinations unusable. You have several options. 1) Using cell security make those intersetions NONE. Then no one will be able to enter anything in to the cell. Unfortunately, they will still be able to see them when zero-suppression is turned off. 2) Create a rule(s) that makes the cells you don't want equal to zero, like ['1000', 'A'] = 0. Once again, this will still allow the intersections to be seen when zero suppression is turned off. The bottom line is that TM1 is an OLAp tool and this is how cubes work. A relational tool would be more suited to what you want.

[lotsaram]

Usually for OLAP reporting zero suppression will take care of this for you. Once all null records are suppressed then the user will only see the "valid" intersections, that is the combinations containing data. If rows need to be expanded to allow data entry in a planning model but only the correct combinations then you would achieve this with an extra measure "Valid/Used" and simply populate with a 1 then use zero suppression on rows. The 1s can be populated with TI or rules, if you use rules then it will need to be fed. The basis for this in your example would be an attribute of internal order indicating the cost centre to which the order is assigned. Your system *should* have this, if it doesn't then you need to create it.

[renken158]

Dear tomok and lotsaram,
thanks a lot for your kind and quick reply.
I still have two doubt.
1.if I choose to edit cellsecurity, do I have to edit all the cell as below? If so, I think it's not a good choice.

cellsecurity.PNG (36.16 KiB) Viewed 6502 times

2.if I choose to suppress zero rows, and one line with all the cells I need to edit are zero, I found that line will be disappear after suppressing, that's not what I want...

zero_suppressing.PNG (15.32 KiB) Viewed 6502 times

thanks again!

[renken158]

Dear lotsaram,
I read your reply carefully, I wrote the rule like this, please correct me if possible, thanks in advance.
If I populate the input field as '1' with rule, then this field will be uneditable, that's not what I want...
I am not familiar with feeders, please tell me if I need to use feeders here, thanks again.

#dummy value for reference field in unvalid row
['Reference']=N:if(!Costcenter@<>ATTRS('InternalOrder',!InternalOrder,'Column 5') %
!CostElement@<>ATTRS('InternalOrder',!InternalOrder,'CostElement'),
0,continue);

#dummy value for reference field in valid row
['Reference']=N:if(!Costcenter@=ATTRS('InternalOrder',!InternalOrder,'Column 5') &
!CostElement@=ATTRS('InternalOrder',!InternalOrder,'CostElement'),
100,continue);

#dummy value for input field in invalid row
['CCInput']=N:if(!Costcenter@<>ATTRS('InternalOrder',!InternalOrder,'Column 5') %
!CostElement@<>ATTRS('InternalOrder',!InternalOrder,'CostElement'),
0,continue);

#consolidate leaf
[]=ConsolidateChildren('Budget_Month','Budget_KeyFigures','Costcenter','CostElement','InternalOrder');

#for consolidate node, * 1, in order to display as 'grey' instead of 'white'
[]=C:DB('BU1',!Costcenter,!InternalOrder,!CostElement,!Budget_Month,!Budget_KeyFigures)*1;

[lotsaram]

I'm only going to say this once. You really, really need to go and get some training and read the reference material cover to cover, particularly the rules guide and developers guide AND you need to seriously consider bringing in a EXPERIENCED TM1 consultant to assist you with your first 1 or 2 implementations otherwise you are on a hiding to nothing.

Cell Security Rules: No you would NEVER edit or update a cell security cube manually. Almost always if cell security is used then it will be done using rules. Many models do not use cell security at all as most of the time cube and element security is sufficient. Also whether a cell is grey/white and writeable has no impact on whether is will display with zero suppression which is just about whether cells contain data.

Reference value rule: Rule itself seems OK but there's no need for Continue, if the test it false then set to 0. You would need to feed this, I would suggest to feed only one month, no need to feed all. Also it seems there are a few other dimensions that were't apparent in your original screenshot. Usually the best way to solve how to feed something like this is to populate string measures (in a separate "setup" or "config" cube) which have the values of the element combinations to feed in the target cube. This can be initially populated via TI looking up various attributes. The benefits of using string data for this is that when strings change value the feeder is re-fired which isn't the case for numeric data. However this is a bit too advance for your current level.

Feeders: It is VERY IMPORTANT to feed only what is necessary as you do not want to over feed as this will have a big negative impact on performance.

Open C rule * 1:No, no, no. You don't need to do this. A non-leaf cell will already be grey anyway as direct data entry is not possible. If you want to prohibit spreading to consolidations then you do this with element security.

ConsolidateChildren rule:Also no, no, no. TM1 naturally consolidates numeric data, you don't need to program anything to make this happen. ConsolidateChildren should only ever be used if there is a specific need to override the natural consolidation algorithm (which is rare). Also ConsolidateChildren performs very poorly as it does not use the sparse consolidation algorithm, try to avoid using it.

And good luck, you're going to need it.

[renken158]

Thanks a lot for your help, althought your comments is still a little difficult for me.
I am a SAP ABAP developer with many years expirence, TM1 is rather new for me, I will keep studying~
it would be highly appreciated if you could provide some URL links related to TM1 beginner,thanks again!

[renken158]

Dear lotsaram,
sorry to trouble you again, still some questions, hope you can provide some tips.
1.ConsolidateChildren rule:I agree with you regarding the poor performance regarding this statement, but if I don't insert this rule, I found that the consolidated node could not be populated value automatically. I am sure the dimension type I used is numeric, is there something wrong with my operations?
[]=ConsolidateChildren('Budget_Month','Budget_KeyFigures','Costcenter','CostElement','InternalOrder');
2.I wrote the cellsecutiry rule like this, which leads to the below result, but I don't want the 'blank' rows shown, I prefer to hide them. is there any way to do this?
[]=S:IF(DB('}tp_intermediate_RDCLS}BU1',!Costcenter,!}Groups,'all_applications','StaticRights')@<>'' &
(!Costcenter@=ATTRS('InternalOrder',!InternalOrder,'Column 5') &
!CostElement@=ATTRS('InternalOrder',!InternalOrder,'CostElement')),
'WRITE',
CONTINUE);

cellsecurity2.PNG (31.05 KiB) Viewed 6445 times

3.Regarding the zero suppress,suppose that when users open an application, and before input budget, they surely don't want to see redundant rows. After pressing 'zero suppress' button, all the rows with zero cells disappears, including the rows with all editable cells are zero, which is not what I want. because before editing, the values of these cells are surely zero...
before pressing 'zero suppress' button.

zero_suppressing2.PNG (22.68 KiB) Viewed 6445 times

after pressing 'zero suppress' button.

zero_suppressing3.PNG (16.91 KiB) Viewed 6445 times

[lotsaram]

The questions you are asking have already been answered. I see no point in repeating what's been said.

[renken158]

Dear lotsaram,
thanks you very much!