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 »

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: 62
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 »

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 »

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: 306
Joined: Mon May 12, 2008 8:11 am
OLAP Product: TM1
Version: TM1 11 and up
Excel Version: Too many to count

Re: Retrieving Data using Alternate Hierarchy

Post by Paul Segal »

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: 324
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 »

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.
mvaspal
Community Contributor
Posts: 341
Joined: Wed Nov 03, 2010 9:16 pm
OLAP Product: tm1
Version: 10 2 2 - 2.0.5
Excel Version: From 2007 to 2013
Location: Earth

Re: Retrieving Data using Alternate Hierarchy

Post by mvaspal »

Hi,
This is now more than 2 years old, anybody maybe know more about the "roadmap"?
If we need dynamic (on rows) but formatted reports in Excel with Hierarchies, I am not sure what is the best way of doing it - custom reports are not dynamic, quick reports do support Hierarchies, but they do not use sets on the rows.
thank you!
Bakkone
Posts: 119
Joined: Mon Oct 27, 2014 10:50 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: Retrieving Data using Alternate Hierarchy

Post by Bakkone »

Hiearchies in Websheets has been in a few presentations lately. Don't know if it means its close... but heres hoping for H1 2020.
dmillerksu
Posts: 10
Joined: Sun Apr 17, 2011 2:01 pm
OLAP Product: Planning Analytics
Version: 2.0.7 PAW v46
Excel Version: 19

Re: Retrieving Data using Alternate Hierarchy

Post by dmillerksu »

Has anyone heard of any update to adding alternate hierarchy capabilities in DBRW's yet?
Post Reply