Retrieving Data using Alternate Hierarchy

Post Reply
gkoscs
Posts: 3
Joined: Fri Jan 02, 2009 9:12 pm
OLAP Product: TM1
Version: Planning Analytics 2.0
Excel Version: 2013

Retrieving Data using Alternate Hierarchy

Post by gkoscs » Tue Aug 22, 2017 7:54 pm

I am using Planning Analytics 2.0 (on premises). The alternative hierarchy feature can be very powerful and it appears to be easy to retrieve data using either PAW or PAX. However, does anyone know if it is possible to retrieve data which references an alternate hierarchy by using either a DBRW formula in a slice worksheet (now called Custom Report) or by using a CellGetN in a TI Process? If it's not possible now, is it contemplated by IBM for the future?

Many thanks.

st2000
Posts: 61
Joined: Mon Aug 15, 2016 8:48 am
OLAP Product: TM1 (Windows) & SSAS 2014 Ent.
Version: 10.2.0 FP3
Excel Version: Excel 2013
Location: Hamburg, DE, EU
Contact:

Re: Retrieving Data using Alternate Hierarchy

Post by st2000 » Wed Aug 23, 2017 4:47 pm

You could retrieve data in PA2 directly from a MDX view. And MDX supports qualifying particular hierarchies per dimension like [Time].[Calendar].[Year] where Calendar would be one hierarchy beyond others into the Time dimension.
MDX views as a TI data source
Previously, a view could be created and saved by using MDX expressions. Beginning in this release, the MDX-based view
can now be used as a TurboIntegrator data source.
http://public.dhe.ibm.com/software/data ... m1_nfg.pdf
-----------------------------------
Best regards,
Stefan

gkoscs
Posts: 3
Joined: Fri Jan 02, 2009 9:12 pm
OLAP Product: TM1
Version: Planning Analytics 2.0
Excel Version: 2013

Re: Retrieving Data using Alternate Hierarchy

Post by gkoscs » Fri Aug 25, 2017 7:04 pm

Thank you very much Stefan for this answer.

I am able to create a view using MDX, which I can see in PA workspace is correct. However, my ultimate goal is to read this view into a process and create a special summarized cube by using one of the hierarchies. When trying to read the MDX view, I get the following error message:

Cannot assign column 1 to variable v1: Unsupported column type

Column 1 is the position of the hierarchy value. Variable v1 is a simple text variable.

Any ideas on what the problem is?

Paul Segal
Community Contributor
Posts: 242
Joined: Mon May 12, 2008 8:11 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Retrieving Data using Alternate Hierarchy

Post by Paul Segal » Wed Aug 30, 2017 4:00 pm

A slight hijack, but my concern is that DBR/DBRW/Custom Reports won't ever support alternate hierarchies. I appreciate the need to keep these functions backward compatible, but if we ever want to use alternate hierarchies in PAX in Custom Reports then we would need a new function (DBRH anyone?) for this, as I understand it. We have a lot of history with the way DBRW formulas work, it's easy to understand, and although I'm ready to be convinced otherwise I'd like to see them in some way support alternate hierarchies.
Paul

User avatar
stephen waters
MVP
Posts: 320
Joined: Mon Jun 30, 2008 12:59 pm
OLAP Product: TM1
Version: 10_2_2
Excel Version: Excel 2010

Re: Retrieving Data using Alternate Hierarchy

Post by stephen waters » Thu Aug 31, 2017 12:06 pm

Paul,
Lack of DBRW support for new "Hierarchies" was mentioned at one of the Road Map sessions at IBM Vision in May. IBM said they plan to introduce a new formula that does support Hierarchies but I don't think any timescale was mentioned.

Post Reply