DBRW with alternative hierarchies
-
- MVP
- Posts: 3120
- 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:
DBRW with alternative hierarchies
Hello,
I was wondering. Can we already have a report in PAfE, just a slice with DBRW formulas.
There, 1 argument in a DBRW uses an element in an alternative (PA) hierarchy.
Does the software already support this ?
I would image that having more than 1 hierarchy from the same dimension would be difficult in the DBRW, but 1 such selection should be possible, no ?
I was wondering. Can we already have a report in PAfE, just a slice with DBRW formulas.
There, 1 argument in a DBRW uses an element in an alternative (PA) hierarchy.
Does the software already support this ?
I would image that having more than 1 hierarchy from the same dimension would be difficult in the DBRW, but 1 such selection should be possible, no ?
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
-
- Regular Participant
- Posts: 350
- Joined: Sat Jun 08, 2019 9:55 am
- OLAP Product: Planning Analytics
- Version: Planning Analytics 2.0
- Excel Version: Excel 2016
Re: DBRW with alternative hierarchies
Hi,
I am struggling to see the difference between "1 argument in a DBRW uses an element in an alternative (PA) hierarchy" and "having more than 1 hierarchy from the same dimension"
I would say no this isn't possible but don't take my word for it as I have not tested and don't understand what you are trying to do!
Maren
I am struggling to see the difference between "1 argument in a DBRW uses an element in an alternative (PA) hierarchy" and "having more than 1 hierarchy from the same dimension"
I would say no this isn't possible but don't take my word for it as I have not tested and don't understand what you are trying to do!
Maren
-
- MVP
- Posts: 3120
- 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: DBRW with alternative hierarchies
Hi
For a 5D cube, the DBRW has 6 arguments.
Even if we source from an alternative PA hierarchy it’s still 6.
However, if we involve more than 1 alternative hierarchy from the same dimension then the number of arguments increases.
I had hoped that as long as we don’t use more arguments than expected in the DBRW for that cube, PAfE would be able to do it.
For a 5D cube, the DBRW has 6 arguments.
Even if we source from an alternative PA hierarchy it’s still 6.
However, if we involve more than 1 alternative hierarchy from the same dimension then the number of arguments increases.
I had hoped that as long as we don’t use more arguments than expected in the DBRW for that cube, PAfE would be able to do it.
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
-
- Regular Participant
- Posts: 350
- Joined: Sat Jun 08, 2019 9:55 am
- OLAP Product: Planning Analytics
- Version: Planning Analytics 2.0
- Excel Version: Excel 2016
Re: DBRW with alternative hierarchies
Hi Wim,
I assume that when people say PAX does not support hierarchy aware formulas, they are talking about retrieving a value from only a single hierarchy at a time.
I can't even imagine what involving more than 1 alternative hierarchy from the same dimension in one formula even means.
What is the use case for involving more than 1 alternative hierarchy from the same dimension in the same formula?
Maren
I assume that when people say PAX does not support hierarchy aware formulas, they are talking about retrieving a value from only a single hierarchy at a time.
I can't even imagine what involving more than 1 alternative hierarchy from the same dimension in one formula even means.
What is the use case for involving more than 1 alternative hierarchy from the same dimension in the same formula?
Maren
-
- MVP
- Posts: 3120
- 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: DBRW with alternative hierarchies
Did you ever drag a hierarchy into a view in Workspace for instance ? That’s the use case and being able to update and retrieve cube values in that manner.
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: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: DBRW with alternative hierarchies
Consider a sku dimension where n-level elements are car models.
You want all automatic models from hierarchy 1, all SUV models from hierarchy 2 - this result returns only the sum of n-level elements that exist under both hierarchy consolidations (essentially an inner join.)
As to your question Wim, I assume DBRW is unaware of hierarchies altogether and even just trying to reference 1 wouldn’t work… that being said, I haven’t had the need to try or test it out.
Declan Rodger
-
- MVP
- Posts: 3120
- 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: DBRW with alternative hierarchies
It confirms my thoughts but pretty bad situation after 6+ years in the land of PAW with alternative hierarchies...
PAW is too rigid in terms of formatting or extra logic (what we typically do in IF cells surrounding an Excel Perspectives / PAfE report).
So then we move to Excel but if we cannot use the alternative hierarchies, even not 1 per DBRW, that's nothing less then a shame.
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
-
- Regular Participant
- Posts: 197
- Joined: Wed May 06, 2020 2:58 pm
- OLAP Product: Planning Analytics
- Version: 2.0.9
- Excel Version: 2016
Re: DBRW with alternative hierarchies
Hi Wim,
The Dbrw aren't hierarchy aware. So if the consolidated element doesn't exist in the default hierarchy it won't recognise it and likewise any consolidation names that do exist in multiple hierarchies, then only the rollup value from the default hierarchy will be picked up.
This topic was re-raised on the IBM forum recently, see link below. Although the response wasn't great and I think the dqr (dynamic quick report) won't be until the next big release. What level of flexibility and level of interaction we'll get from this though, is anyone's guess
https://community.ibm.com/community/use ... 4eda43146b
The Dbrw aren't hierarchy aware. So if the consolidated element doesn't exist in the default hierarchy it won't recognise it and likewise any consolidation names that do exist in multiple hierarchies, then only the rollup value from the default hierarchy will be picked up.
This topic was re-raised on the IBM forum recently, see link below. Although the response wasn't great and I think the dqr (dynamic quick report) won't be until the next big release. What level of flexibility and level of interaction we'll get from this though, is anyone's guess
https://community.ibm.com/community/use ... 4eda43146b
-
- Regular Participant
- Posts: 350
- Joined: Sat Jun 08, 2019 9:55 am
- OLAP Product: Planning Analytics
- Version: Planning Analytics 2.0
- Excel Version: Excel 2016
Re: DBRW with alternative hierarchies
Hi,
Maren
Actually I never have, which explains my queries. Thanks to Declan for making it clear!Did you ever drag a hierarchy into a view in Workspace for instance ?
Maren
- macsir
- MVP
- Posts: 785
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: DBRW with alternative hierarchies
I can understand performance is a big concern from IBM if they develop this formula. That's why they want to replace it with a new report type. If that solves the problem, then everyone is happy.
Out of curiosity, how is DBRH performed in cubewise's slice? Has anyone use it to develop some monster report yet?
Out of curiosity, how is DBRH performed in cubewise's slice? Has anyone use it to develop some monster report yet?
-
- MVP
- Posts: 3120
- 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: DBRW with alternative hierarchies
I would hope that IBM tackle the relevant and much needed things FIRST AND FOREMOST. Rather than what I read today in changes in the upcoming version 73.
https://www.ibm.com/docs/en/planning-an ... res_2_0_73
https://www.ibm.com/docs/en/planning-an ... res_2_0_73
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
- 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: DBRW with alternative hierarchies
Trying to think of a use case for what those new formula might be?
They don't seem particularly relevant to TM1 and more generally in Excel, what would you use them for...?
They don't seem particularly relevant to TM1 and more generally in Excel, what would you use them for...?
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- MVP
- Posts: 3120
- 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: DBRW with alternative hierarchies
Hi Steve,
Not sure I understood your comment. Anything that is done with PA alternative hierarchies would be used in all TM1 clients, including Excel. Meaning that, what would otherwise be SUMIF’s in Excel would be the result of a DBRW that calls 1 or more alternate hierarchies.
Not sure I understood your comment. Anything that is done with PA alternative hierarchies would be used in all TM1 clients, including Excel. Meaning that, what would otherwise be SUMIF’s in Excel would be the result of a DBRW that calls 1 or more alternate hierarchies.
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
- 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: DBRW with alternative hierarchies
Sorry..
I meant these formula in the link you provided. Absolutely a DBRH would be the number one item on my wish list!
https://www.ibm.com/docs/en/planning-an ... ebook.html
TM1RESOLVEBOOK( ) - removes nested parentheses from each cell within a workbook
TM1RESOLVESHEET( ) - removes nested parentheses from each cell in the active worksheet
TM1RESOLVEFORMULAS( ) - removes nested parentheses within a cell range
TM1RESOLVENESTEDPARENS( ) - removes the nested parentheses and returns a string
I meant these formula in the link you provided. Absolutely a DBRH would be the number one item on my wish list!
https://www.ibm.com/docs/en/planning-an ... ebook.html
TM1RESOLVEBOOK( ) - removes nested parentheses from each cell within a workbook
TM1RESOLVESHEET( ) - removes nested parentheses from each cell in the active worksheet
TM1RESOLVEFORMULAS( ) - removes nested parentheses within a cell range
TM1RESOLVENESTEDPARENS( ) - removes the nested parentheses and returns a string
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- MVP
- Posts: 3120
- 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: DBRW with alternative hierarchies
I have no clue what these functions are about
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
- Mike Cowie
- Site Admin
- Posts: 482
- Joined: Sun May 11, 2008 7:07 pm
- OLAP Product: IBM TM1/PA, SSAS, and more
- Version: Anything thru 11.x
- Excel Version: 2003 - Office 365
- Location: Alabama, USA
- Contact:
Re: DBRW with alternative hierarchies
Hi Wim,
This had been bugging me, too, and I now think I understand what these are meant do do after some help from Matt Hopkins @ QueBIT tracking down someone at IBM to get further clarification.
The short answer is that these are functions designed to help clean up existing TM1 Perspectives Excel reports that use a specific DBR/DBS nested formula argument syntax that most people probably have never seen and which IBM (I'd expect) wants to ultimately avoid supporting in PAfE because it's both confusing and unnecessary.
In the older days of Excel (2003 and earlier) we were, apparently, limited to 30 arguments to a function, so what TM1 Perspectives supported once we could have more than 16 dimensions was to allow nesting multiple elements within a single DBR/DBS argument to work around this 30 argument limitation. That means you could use this:
Which looks to Excel like a function with 3 arguments, but is ultimately treated in the same way as this by Perspectives:
So, I'd agree these should have been documented in much more detail.I also suspect that these should be classified as functions designed for VBA usage-- once you've called them to clean up this older, nested formula syntax you shouldn't need them anymore. I'm guessing those details didn't get through to the people that write the documentation the first time around (and I'll try to send some of that feedback along to support unless the IBM'ers here beat me to it!)
I also agree that DBRH + subset/user-defined-consolidation equivalent references in DBRx formulas would easily be top priority for PAfE.
Regards,
Mike
This had been bugging me, too, and I now think I understand what these are meant do do after some help from Matt Hopkins @ QueBIT tracking down someone at IBM to get further clarification.
The short answer is that these are functions designed to help clean up existing TM1 Perspectives Excel reports that use a specific DBR/DBS nested formula argument syntax that most people probably have never seen and which IBM (I'd expect) wants to ultimately avoid supporting in PAfE because it's both confusing and unnecessary.
In the older days of Excel (2003 and earlier) we were, apparently, limited to 30 arguments to a function, so what TM1 Perspectives supported once we could have more than 16 dimensions was to allow nesting multiple elements within a single DBR/DBS argument to work around this 30 argument limitation. That means you could use this:
Code: Select all
=DBRW("Planning Sample:plan_BudgetPlan",(D2,D3,D4,D5,D6,D7),D8)
Code: Select all
=DBRW("Planning Sample:plan_BudgetPlan",D2,D3,D4,D5,D6,D7,D8)
I also agree that DBRH + subset/user-defined-consolidation equivalent references in DBRx formulas would easily be top priority for PAfE.
Regards,
Mike
Mike Cowie
QueBIT Consulting, LLC
Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
QueBIT Consulting, LLC
Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
-
- MVP
- Posts: 3120
- 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: DBRW with alternative hierarchies
Thank you Mike. That seems very plausible but it is not something I’ve ever used before.
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
- 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: DBRW with alternative hierarchies
Thanks Mike.....you learn something new everyday! This is almost triggering me into rant but I've better things to do!
Technical Director
www.infocat.co.uk
www.infocat.co.uk
- Mike Cowie
- Site Admin
- Posts: 482
- Joined: Sun May 11, 2008 7:07 pm
- OLAP Product: IBM TM1/PA, SSAS, and more
- Version: Anything thru 11.x
- Excel Version: 2003 - Office 365
- Location: Alabama, USA
- Contact:
Re: DBRW with alternative hierarchies
I've only seen DBR's with nested parentheses once "in the wild" and I remember wondering: what is this madness? I expect it's very, very uncommon out there.
Mike Cowie
QueBIT Consulting, LLC
Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
QueBIT Consulting, LLC
Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!