Adding a subset to a dbrw

Post Reply
Analytics123
Posts: 128
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 »

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: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Adding a subset to a dbrw

Post 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?
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Adding a subset to a dbrw

Post by Wim Gielis »

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

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Adding a subset to a dbrw

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

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Analytics123
Posts: 128
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 »

Yeah i have done it , but not sure it will cause performance issues .
User avatar
vovanenok
Posts: 88
Joined: Mon Jun 23, 2014 4:54 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: Office 365
Location: Toronto, Canada
Contact:

Re: Adding a subset to a dbrw

Post 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".
----------
TeamOne Google Sheets add-on for IBM Planning Analytics
Let's connect on LinkedIn
tm123
Posts: 132
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 »

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: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Adding a subset to a dbrw

Post 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.
Technical Director
www.infocat.co.uk
Post Reply