DBRW with alternative hierarchies

Post Reply
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:

DBRW with alternative hierarchies

Post by Wim Gielis »

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 ?
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
MarenC
Regular Participant
Posts: 346
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

Post by MarenC »

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
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: DBRW with alternative hierarchies

Post by Wim Gielis »

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.
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
MarenC
Regular Participant
Posts: 346
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

Post by MarenC »

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
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: DBRW with alternative hierarchies

Post by Wim Gielis »

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
declanr
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

Post by declanr »

MarenC wrote: Tue Feb 01, 2022 4:33 pm What is the use case for involving more than 1 alternative hierarchy from the same dimension in the same formula?
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
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: DBRW with alternative hierarchies

Post by Wim Gielis »

declanr wrote: Tue Feb 01, 2022 5:35 pmAs 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.
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
burnstripe
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

Post by burnstripe »

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
MarenC
Regular Participant
Posts: 346
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

Post by MarenC »

Hi,
Did you ever drag a hierarchy into a view in Workspace for instance ?
Actually I never have, which explains my queries. Thanks to Declan for making it clear!

Maren
User avatar
macsir
MVP
Posts: 782
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

Post by macsir »

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? :D
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
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: DBRW with alternative hierarchies

Post by Wim Gielis »

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
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
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: DBRW with alternative hierarchies

Post by Steve Rowe »

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...?
Technical Director
www.infocat.co.uk
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: DBRW with alternative hierarchies

Post by Wim Gielis »

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.
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
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: DBRW with alternative hierarchies

Post by Steve Rowe »

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
Technical Director
www.infocat.co.uk
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: DBRW with alternative hierarchies

Post by Wim Gielis »

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
User avatar
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

Post by Mike Cowie »

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:

Code: Select all

=DBRW("Planning Sample:plan_BudgetPlan",(D2,D3,D4,D5,D6,D7),D8)
Which looks to Excel like a function with 3 arguments, but is ultimately treated in the same way as this by Perspectives:

Code: Select all

=DBRW("Planning Sample:plan_BudgetPlan",D2,D3,D4,D5,D6,D7,D8)
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
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!
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: DBRW with alternative hierarchies

Post by Wim Gielis »

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
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: DBRW with alternative hierarchies

Post by Steve Rowe »

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
User avatar
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

Post by Mike Cowie »

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!
Post Reply