Subset Reference

Post Reply
mail_kupi
Posts: 22
Joined: Wed Jan 26, 2011 3:06 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2003

Subset Reference

Post by mail_kupi » Tue Apr 26, 2011 8:10 am

Hello all,

i want to get value from another cube but only certain element.

['Sales1'] = DB['Production','Book'] + DB['Production','Pencil'] +DB['Production','Bag']

my intention is want to have sales1 value base on cube production which is from book, pencil and bag element only is there more simple rule too wrap this rule
like using subset?

['Sales1'] = DB['Production',{'Book','Pencil','Bag'}];

Regards

User avatar
Alan Kirk
Site Admin
Posts: 6118
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: Subset Reference

Post by Alan Kirk » Tue Apr 26, 2011 8:15 am

mail_kupi wrote:Hello all,

i want to get value from another cube but only certain element.

['Sales1'] = DB['Production','Book'] + DB['Production','Pencil'] +DB['Production','Bag']

my intention is want to have sales1 value base on cube production which is from book, pencil and bag element only is there more simple rule too wrap this rule
like using subset?

['Sales1'] = DB['Production',{'Book','Pencil','Bag'}];
I don't think that you're thinking quite laterally enough.

What you want isn't a subset. It's a consolidation.

['Sales1'] = N:DB('Production', 'ConsolidationWhichAddsBookPencilAndBagTogether','OtherElements');
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

mail_kupi
Posts: 22
Joined: Wed Jan 26, 2011 3:06 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2003

Re: Subset Reference

Post by mail_kupi » Tue Apr 26, 2011 8:23 am

Thanks Alan,

but actually the element has a consolidation but i just want to take some element child from that consolidation.

Regards
Novianto

kpk
MVP
Posts: 214
Joined: Tue Nov 11, 2008 11:57 pm
OLAP Product: TM1, CX
Version: TM1 7x 8x 9x 10x CX 9.5 10.1
Excel Version: XP 2003 2007 2010
Location: Hungary

Re: Subset Reference

Post by kpk » Tue Apr 26, 2011 8:37 am

mail_kupi wrote:Thanks Alan,

but actually the element has a consolidation but i just want to take some element child from that consolidation.

Regards
Novianto
You can have (at least virtually) unlimited number of consolidations in a dimension.
Using a kind of technical consolidation for this purpose is more efficient from performance point of view.
Best Regards,
Peter

mail_kupi
Posts: 22
Joined: Wed Jan 26, 2011 3:06 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2003

Re: Subset Reference

Post by mail_kupi » Thu Apr 28, 2011 3:22 am

So its mean i cannot using subset to fullfil what i want, but instead using multi consolidation to achieve that, is it right?

ok then,

Thanx a lot kpk... :D

lotsaram
MVP
Posts: 3386
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Subset Reference

Post by lotsaram » Thu Apr 28, 2011 7:24 am

mail_kupi wrote:So its mean i cannot using subset to fullfil what i want, but instead using multi consolidation to achieve that, is it right?

ok then,

Thanx a lot kpk... :D
As far as TM1 rules are concerned subsets do not exist. You can only refer to dimension elements in rules not subsets.

User avatar
Alan Kirk
Site Admin
Posts: 6118
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: Subset Reference

Post by Alan Kirk » Thu Apr 28, 2011 9:27 am

lotsaram wrote:
mail_kupi wrote:So its mean i cannot using subset to fullfil what i want, but instead using multi consolidation to achieve that, is it right?

ok then,

Thanx a lot kpk... :D
As far as TM1 rules are concerned subsets do not exist. You can only refer to dimension elements in rules not subsets.
Just for clarity (in case any new users are searching this thread in the future), Lotsaram is of course referring to named subsets. It's possible to hard code a subset (in the context of "a collection of elements") into a rule definition as is mentioned in the Rules guide:
Using Subsets in an Area Definition

You can use a subset in place of a single element in an area definition by enclosing all subset members in curly braces.

For example, the following area definition applies a calculation formula to all cube cells identified by the element Trout and any of the elements Karachi, Boston, or Helsinki:

['Trout', {'Karachi', 'Boston', 'Helsinki'}] =
but that's not at all the same thing as using a defined subset, in addition to which it's on the area side, not the equation side. In the context of this question, Lotsaram is completely correct.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

lotsaram
MVP
Posts: 3386
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Subset Reference

Post by lotsaram » Thu Apr 28, 2011 12:46 pm

Alan Kirk wrote:
lotsaram wrote:
mail_kupi wrote:So its mean i cannot using subset to fullfil what i want, but instead using multi consolidation to achieve that, is it right?

ok then,

Thanx a lot kpk... :D
As far as TM1 rules are concerned subsets do not exist. You can only refer to dimension elements in rules not subsets.
Just for clarity (in case any new users are searching this thread in the future), Lotsaram is of course referring to named subsets. It's possible to hard code a subset (in the context of "a collection of elements") into a rule definition as is mentioned in the Rules guide:
Using Subsets in an Area Definition

You can use a subset in place of a single element in an area definition by enclosing all subset members in curly braces.

For example, the following area definition applies a calculation formula to all cube cells identified by the element Trout and any of the elements Karachi, Boston, or Helsinki:

['Trout', {'Karachi', 'Boston', 'Helsinki'}] =
but that's not at all the same thing as using a defined subset, in addition to which it's on the area side, not the equation side. In the context of this question, Lotsaram is completely correct.
Thanks Alan,

I would regard that as an error (one among a great many) in the documentation even if it is only semantics. Given that TM1 has a defined concept of what a subset is this use of the term is incorrect, it would be much better in this case to refer to a list of elements specified in this way as an array since that is what it is.
You can use an array in place of a single element in an area definition by enclosing a list of dimension members separated by commas within curly braces.
For anyone else who may reference this in the future it is worth noting that even though subsets are not part of dimension structure and can't be referenced in rules, they are "kinda sorta" defacto consolidated elements as you can reference a subset name in a DBRW and it will pull out the consolidated value of all members (as long as the name doesn't conflict with a real element in which case the real element wins (thankfully). Also you can pull the members of a subset using ELCOMP and not just SUBNM (which isn't surprising given the ability of a subset to act as an ah hoc consolidation). However these features of subsets are undocumented and undocumented fetures are subject to change without notice as I found out only yesterday when I discovered to my dismay that in 9.5.1 applying a rule area definition to a region that previously contained data will immediatelly clear the data which never used to be the case in old versions (the data just sat in the background being "masked" by the rule and could miraculously resurface upon removal of the rule.)

lotsaram
MVP
Posts: 3386
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Subset Reference

Post by lotsaram » Tue May 03, 2011 4:50 pm

Interestingly in reference to the above changed behaviour. IBM have just released a config setting to enable the former behaviour. One wonders when the default behaviour changed ...

https://www-304.ibm.com/support/docview ... s=swgimgmt
During the processing of feeders for a cube, a cube's value can be wiped out if there is a rule for that cell. Once the cube which had a cell wiped out is saved, the value is gone so the action has no effect on the cube. However, if the rule is edited but the cube is not subsequently modified, the cube is not saved to disk. In that case, real cell values may be wiped out when the rules run.


The RulesOverwriteCellsOnLoad parameter can be used to prevent the zeroing out action after a rule is edited.

If you are changing rules and the rules may, due to various edits, cause some cells which have data to become rule-derived, add RulesOverwriteCellsOnLoad=F to the configuration file.

If this parameter is set to True or is not present, whenever the server loads, rule-derived cells are wiped to zero. The data value in those cells is lost even if the rule is subsequently changed so that the cell is no longer rule-derived.

Use this parameter to prevent the data wipe of rules-derived cells on server load. By default this parameter is not present in the configuration file or is set to True.

Post Reply