Page 1 of 1

Adding a subset to a dbrw

Posted: Thu Oct 19, 2017 5:02 pm
by Analytics123
I have a subset that refreshes nightly .

Is there a way i can refer that subset in a dbrw to retrieve cube value .

Thanks,

Re: Adding a subset to a dbrw

Posted: Thu Oct 19, 2017 5:32 pm
by lotsaram
Analytics123 wrote: Thu Oct 19, 2017 5:02 pm I have a subset that refreshes nightly .

Is there a way i can refer that subset in a dbrw to retrieve cube value .

Thanks,
Is this even a serious question?
What is stopping you doing this?
Have you tried it?

Re: Adding a subset to a dbrw

Posted: Thu Oct 19, 2017 9:59 pm
by Wim Gielis
Often, trying something takes far less time than posting a question online...

Re: Adding a subset to a dbrw

Posted: Fri Oct 20, 2017 3:21 am
by Wim Gielis
Wim Gielis wrote: Thu Oct 19, 2017 9:59 pm Often, trying something takes far less time than posting a question online...
If, however, you think that using a subset name inside a DBRW formula will blow up your TM1 server and turn it into little more than dust: read about this topic here.

Re: Adding a subset to a dbrw

Posted: Fri Oct 20, 2017 8:45 pm
by Analytics123
Yeah i have done it , but not sure it will cause performance issues .

Re: Adding a subset to a dbrw

Posted: Fri Oct 27, 2017 3:03 pm
by vovanenok
Analytics123 wrote: Fri Oct 20, 2017 8:45 pm Yeah i have done it , but not sure it will cause performance issues .
I personally use this approach quite often. Especially if you need to see the total of some dynamically selected elements. For instance. You have "cube A" where you can set "exclusion flag" for elements from Dim1.
Now you need to show the total amount from "cube B" for all the excluded Dim1 elements. So you create a dynamic subset in Dim1 selecting all the elements with "exclusion flag" = 1 and use that subset name in DBRW function.

Performance issues come from badly designed architecture. TM1 is very efficient/fast if you design your model "right".

Re: Adding a subset to a dbrw

Posted: Mon Oct 30, 2017 12:20 pm
by tm123
This is one of the ‘hidden’ features I love the most. I use thus all the time, in both, DBRW formulas as well as in TI Processes in CELLGETN, so this way you dont have to write loops to get subtotals

Re: Adding a subset to a dbrw

Posted: Mon Oct 30, 2017 1:47 pm
by Steve Rowe
Isn't this functionality just a hack?

There should only be one way to return a number from TM1 and that is via a reference to a dimension member.
Otherwise you lose control of what results the system is returning. This is one of the main reasons that companies move from spread sheets to systems, one version of the truth and all that good stuff.

I once worked somewhere where a set of users were all setting up the same named private subsets on their user accounts so that they could generate their own reports, no thanks!

Another occasion where this logic caused a big issue. A year consolidation called '2017' and a subset called 'Yr 2017' containing the consolidation and all of its children. End-user writes a report and un-intentionally references 'Yr 2017' and doubles all the results.

How many reports do you have in the wild that could be referencing a subset name in error or by design? Unknowable...
Do you have anyway to know which subsets are being used in reports and so need to be looked after or updated? Unknowable...
Does this concern you? It should...

The only way I can guarantee that my reports return the correct results is to have them reference dimension elements only.

I'd love to have a cfg that turns this functionality off, for me it is just wrong and represents a backdoor that just should not be available. Maybe you could make it available on a dimension by dimension basis.
I have a subset that refreshes nightly .

Is there a way i can refer that subset in a dbrw to retrieve cube value .
Why not re-build the consolidation at the same time as you build the subset?

That said vovanenok's use case of a dynamic MDX consolidation could be useful as you can change the result of a subset driven consolidation without running code. I'm not sure that I have ever felt the need for this, I expect if I had the thing that was changing that would have driven the MDX was being changed in TI anyway and so I would have rebuilt the consolidation at the same time.