Adding a subset to a dbrw

Post Reply
Analytics123
Posts: 75
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Adding a subset to a dbrw

Post by Analytics123 » 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,

lotsaram
MVP
Posts: 3006
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

Re: Adding a subset to a dbrw

Post by lotsaram » Thu Oct 19, 2017 5:32 pm

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?
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Wim Gielis
MVP
Posts: 1562
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Adding a subset to a dbrw

Post by Wim Gielis » Thu Oct 19, 2017 9:59 pm

Often, trying something takes far less time than posting a question online...
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 104 TM1 articles and a lot of custom code
Newest blog article: Delete a cube and its dimensions

Wim Gielis
MVP
Posts: 1562
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Adding a subset to a dbrw

Post by Wim Gielis » Fri Oct 20, 2017 3:21 am

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.
Last edited by Wim Gielis on Sat Oct 21, 2017 10:37 pm, edited 1 time in total.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 104 TM1 articles and a lot of custom code
Newest blog article: Delete a cube and its dimensions

Analytics123
Posts: 75
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Adding a subset to a dbrw

Post by Analytics123 » Fri Oct 20, 2017 8:45 pm

Yeah i have done it , but not sure it will cause performance issues .

User avatar
vovanenok
Posts: 60
Joined: Mon Jun 23, 2014 4:54 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2003-2007-2010
Location: Toronto, Canada
Contact:

Re: Adding a subset to a dbrw

Post by vovanenok » Fri Oct 27, 2017 3:03 pm

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".

tm123
Posts: 55
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Adding a subset to a dbrw

Post by tm123 » Mon Oct 30, 2017 12:20 pm

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

User avatar
Steve Rowe
Site Admin
Posts: 1679
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Adding a subset to a dbrw

Post by Steve Rowe » Mon Oct 30, 2017 1:47 pm

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.

Post Reply