I have a subset that refreshes nightly .
Is there a way i can refer that subset in a dbrw to retrieve cube value .
Thanks,
Adding a subset to a dbrw
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
-
- MVP
- Posts: 3661
- 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
Is this even a serious question?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,
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.
-
- MVP
- Posts: 3123
- 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
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
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
-
- MVP
- Posts: 3123
- 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
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.Wim Gielis wrote: ↑Thu Oct 19, 2017 9:59 pm Often, trying something takes far less time than posting a question online...
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
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
-
- 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
Yeah i have done it , but not sure it will cause performance issues .
- 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
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.Analytics123 wrote: ↑Fri Oct 20, 2017 8:45 pm Yeah i have done it , but not sure it will cause performance issues .
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".
-
- 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
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
- Steve Rowe
- Site Admin
- Posts: 2417
- 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
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.
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.
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.
Why not re-build the consolidation at the same time as you build the subset?I have a subset that refreshes nightly .
Is there a way i can refer that subset in a dbrw to retrieve cube value .
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
www.infocat.co.uk