Using subset with DBRW function

[vovanenok]

Hi all

I know that you cannot use subset with DBRW function. But I don't want to create a consolidate element each time I need to see a total.
In a cube view I can simply use a subset name and get a subset elements total. But how to workaround this in Perspectives?

I need to use that " subset total" with zero-suppressed active form.

[vovanenok]

sorry, my fault
I forgot to change TM1RPTTITLE function inside TM1RPTVIEW to point to the same subset name

So we can use subset name inside DBRW which is an amazing feature!

[Wim Gielis]

See http://users.skynet.be/fa436118/wim/tm1 ... dup_EN.htm

[vovanenok]

However while creating a static subset with SubsetElementInsert function you cannot use another subset as an element, it will throw an error that element not found
I'm trying to workaround it by using SubsetCreateByMDX and MDX like:
{[dim_name].[subset_name]}
but that returns all the subset elements instead of just a "subset" element

In subset editor you can simply paste a subset name and it will insert it as an element. How to achieve the same in TI?

[Steve Rowe]

IMO "Subset as totals" is a bit of functionality that originated from a desire to give end-users the ability to create ad-hoc consolidations, it has / is gradually creeping into being a real object to be used to deliver totals but bare in mind it was never intended to be used in this way.

Again IMO, If you are in TI and trying to create a consolidation you should be creating a consolidated element not a subset. A subset is the object that contains the list of elements that you want to put in the consolidation, it isn't the consolidation in it's own right.

Cheers,

[vovanenok]

I think that "bit of functionality that originated from a desire to give end-users the ability to create ad-hoc consolidations" is already implemented in the most places in TM1. I also tested it's supported in CellGetN / CellGetS functions. So why not to add this feature to SubsetElementInsert ?

IMO TI functionality should support all the possible manual operations with subsets

[lotsaram]

I think that "bit of functionality that originated from a desire to give end-users the ability to create ad-hoc consolidations" is already implemented in the most places in TM1. I also tested it's supported in CellGetN / CellGetS functions. So why not to add this feature to SubsetElementInsert ?

IMO TI functionality should support all the possible manual operations with subsets

Don't forget that MDX comes from Analysis Services which has no concept of subsets. A subset is technically not an element, it so happens that a subset is treated as a pseudo-consolidation in the background but as it isn't an element then no reason actually why a function like SubsetElementInsert should work for a subset name passed as the element.

The very valid point raised by Steve is if you are in TI then why not just create a consolidation as a consolidation. Surely this makes the most sense? TM1 supports an unlimited number of alternate hierarchies and rollups so provided this is properly organized there is no reason not to provide a controlled interface to allow users to create consolidations.

[vovanenok]

Hi lotsaram

Sometimes you don't want to build a consolidation for each single user request.
For example, let's say there is a location dimension.
Each user needs to check a couple cities total. Creating a private subset would make a model nice and clean. In case with consolidations, I would need to create and support each consolidation myself.

In my current case this is even a bit different. I have a process which can create a subset by providing either an element list, MDX or my custom keywords (I belive almost each TM1 developer has some kind of such process). I want that process to be able to insert another "subset total" in subset.
Since that's been already implemented in other places and you can do it manually in a subset editor, I would expect the same thing in TI for SubsetElementInsert function (or using a new function)

[tomok]

Each user needs to check a couple cities total. Creating a private subset would make a model nice and clean. In case with consolidations, I would need to create and support each consolidation myself.

Since TI cannot create/modify/delete private subsets then it's kind of a moot point isn't it?

[vovanenok]

Each user needs to check a couple cities total. Creating a private subset would make a model nice and clean. In case with consolidations, I would need to create and support each consolidation myself.

Since TI cannot create/modify/delete private subsets then it's kind of a moot point isn't it?

Yes, that's not a case for private subsets, but as I mentioned above:

I have a process which can create a subset by providing either an element list, MDX or my custom keywords (I belive almost each TM1 developer has some kind of such process). I want that process to be able to insert another "subset total" in subset.

And I use that process to update/recreate all the public subsets

[lotsaram]

I understand where you're coming from but I think being able to insert a subset as an element with TI would be asking for trouble and it wouldn't end up being "clean" at all. Although it's a best practice to ensure that subset names are not the same as element names there is no enforcement of uniqueness of subset vs. element identities by TM1 but an element will always take precedence to a subset of the same name. So allowing subset into subset insertion via SubsetElementInsert could have unpredictable results.

As mentioned TI can only deal with public objects (with the one exception of ViewPublish and I guess the Sandbox functions) and can't create private subsets anyway. Which brings us back to the original point, you're in TI so you have a choice to create a public subset for the purposes of a custom rollup or to just create a rollup. Creating a subset creates an object. Ultimately which is "cleaner" a rollup within the dimension (which could be cataloged by user) or a public subset? I would tend to favor the creation of rollups as opposed to a rampant proliferation of subsets.

But ultimately we can also only work within the bounds of what the tool can actually do. There are many nice to haves which we can wish for.

[babytiger]

I don't see the point of having a "subset total" that aggregate on the fly. One would question the usefulness and purpose vs the confusion it causes.

If the aim is allow user to sum numbers up for a subset on the fly, that functionality already exists. In cube view there the roll op button, while on perspectives, you can just use it with DBR or DBRW formula.

If such total requires to persist, then it would make sense to create a consolidation. As part of the model design, one should understand what and how users see/use the data, rather randomly summing numbers on the fly.

[vovanenok]

I don't see the point of having a "subset total" that aggregate on the fly. One would question the usefulness and purpose vs the confusion it causes.

If the aim is allow user to sum numbers up for a subset on the fly, that functionality already exists. In cube view there the roll op button, while on perspectives, you can just use it with DBR or DBRW formula.

If such total requires to persist, then it would make sense to create a consolidation. As part of the model design, one should understand what and how users see/use the data, rather randomly summing numbers on the fly.

What about private views and subsets, maybe that's also redundant and sometimes confusing as we have public ones? Sandboxes ...

[babytiger]

On a cube view, you can apply ad-hoc "roll ups" on private/public subsets.

But I would definitely not want private subset to be applied on DBRW/DBR functions. Imagine the amount of confusion it would cause. Potentially, every user will see a different value in the same Excel cell formula. That would be a troubleshooting nightmare vs the very small gain (if any).

[declanr]

As has now been established - what you would like in terms of functionality isn't currently available. Although a few forum members work for IBM it is extremely unlikely that a topic here will lead them to consider it as a new development; however if you would like them to (and it sounds like you would) you can try requesting it as per the instructions in the topic below:

http://www.tm1forum.com/viewtopic.php?f=19&t=11198

I must admit I wouldn't personally vote for it but should you put across a good enough argument to get a significant number of votes it is possible IBM would consider it.