ViewZeroOut with Hierarchies in PA2.0

Post Reply
User avatar
PavoGa
Community Contributor
Posts: 261
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

ViewZeroOut with Hierarchies in PA2.0

Post by PavoGa » Thu Oct 25, 2018 8:24 pm

Cannot assign a subset created using HiearchySubsetCreate to a view using ViewSubsetAssign. Have tried:

Code: Select all

HierarchySubsetCreate(dimName, hierName, subName);
HierarchySubsetMDXSet(dimName, hierName, subName, sMDX);

ViewCreate(cubName, vwName);
ViewSubsetAssign(cubName, vwName, dimName, subName);

# Process aborts with the message "Subset "subName" not found in dimension "dimName"

Code: Select all

# if this is used:
HierarchySubsetCreate(dimName, hierName, subName);
HierarchySubsetMDXSet(dimName, hierName, subName, sMDX);
sDimHierCombo = EXPAND('%dimName%:%hierName%');

ViewCreate(cubName, vwName);
ViewSubsetAssign(cubName, vwName, sDimHierCombo, subName);

# it aborts with: Cannot use an alternate hierarchy with this funtion, referencing the line with the ViewSubsetAssign
We can build a view with MDX and use that for a data source. However, we cannot zero out an MDX view and the documentation seems to imply MDX views are only for use as a data source.

So the question is, what is the best practice to build a view to zero out IF one is using hierarchies? One suggestion was to add the elements in the hierarchy to the main dimension and building the subset there, but that creates a whole host of other maintenance issues. It seems odd that alternate hierarchies preclude being able to zero out a slice based on a hierarchy.

Have I missed something? Thoughts?
Ty
Cleveland, TN

User avatar
macsir
Community Contributor
Posts: 551
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: ViewZeroOut with Hierarchies in PA2.0

Post by macsir » Thu Oct 25, 2018 9:03 pm

I think MDX view is just not mature yet at this stage.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

User avatar
PavoGa
Community Contributor
Posts: 261
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

Re: ViewZeroOut with Hierarchies in PA2.0

Post by PavoGa » Thu Oct 25, 2018 9:09 pm

Agreed.

But that still begs the question: how does one use hierarchies and be able to clear out a target view before loading/reloading data?
Ty
Cleveland, TN

lotsaram
MVP
Posts: 3138
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: ViewZeroOut with Hierarchies in PA2.0

Post by lotsaram » Thu Oct 25, 2018 10:22 pm

To answer your original question, pretty much the same answer as in this post. Basically you can't mix trad views with hierarchies. Never the twain shall meet.

But I think your last question is the far more interesting and pertinent one.
PavoGa wrote:
Thu Oct 25, 2018 9:09 pm
But that still begs the question: how does one use hierarchies and be able to clear out a target view before loading/reloading data?
Answer; for now you can't as ViewZeroOut doesn't recognize MDX views. I do have a defect raised for this APAR PH03765 (and I sure hope I'm not the only one who has raised this).
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
PavoGa
Community Contributor
Posts: 261
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

Re: ViewZeroOut with Hierarchies in PA2.0

Post by PavoGa » Fri Oct 26, 2018 12:01 pm

Thanks, Lotsa.

Well, for now...ugh...this is going to ugly.

Got a couple of idea, but if anyone has a recommended methodology for achieving this, it would be nice to know. Got a couple of ideas, none of which seem all that appetizing.

Thanks again!
Ty
Cleveland, TN

lotsaram
MVP
Posts: 3138
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: ViewZeroOut with Hierarchies in PA2.0

Post by lotsaram » Fri Oct 26, 2018 5:58 pm

PavoGa wrote:
Fri Oct 26, 2018 12:01 pm
Got a couple of idea, but if anyone has a recommended methodology for achieving this, it would be nice to know. Got a couple of ideas, none of which seem all that appetizing.
Basically the only option is to build a leaf level filtered subset on the hierarchized dimension(s) to mimic the intersections of the hierarchies if they were separate analysis dimensions. For some use cases simple enough. But if there are several alternate hierarchies from multiple dimensions then the emulating the slice with leaf subsets could get quite complicated.

Basically the sooner we have this functionality the better.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Wim Gielis
MVP
Posts: 1826
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: ViewZeroOut with Hierarchies in PA2.0

Post by Wim Gielis » Fri Oct 26, 2018 6:07 pm

Lotsaram wrote:
Fri Oct 26, 2018 12:01 pm
Basically the sooner we have this functionality the better.
It won't be in version 2.0.6 as this version is out.
Last edited by Wim Gielis on Fri Oct 26, 2018 7:11 pm, edited 3 times in total.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

User avatar
PavoGa
Community Contributor
Posts: 261
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

Re: ViewZeroOut with Hierarchies in PA2.0

Post by PavoGa » Fri Oct 26, 2018 6:51 pm

I've got a workaround. Not pretty, but it is promising.

Got a process that "syncs' the Leaves hierarchy to the master dimension by putting any leaf elements that exist in just the alternate hierarchies in a consolidation "Other Elements" in the dimension.

Then, we build the subset we want off of an alternate hierarchy, call another process that just duplicates the hierarchy subset as a dimension subset and use the dimension subset in a traditional view to zero out.

Yep. Butt Ugly was what we called that in south Georgia.
Ty
Cleveland, TN

User avatar
paulsimon
MVP
Posts: 647
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: ViewZeroOut with Hierarchies in PA2.0

Post by paulsimon » Sun Oct 28, 2018 8:05 pm

Hi

I am not sure why you need to build an Other Elements consolidation. If you can identify the base level elements of the hierarchy, then you can just use those elements to directly build a subset on the main dimension and use that in ViewZeroOut.

Having said that, the approach that I currently intend to follow is to build alternate hierarchies in the main dimension as well as Hierarchies for each of the alternate hierarchies. The issue with ViewZeroOut is one reason. The other is that we have a lot of legacy Perspectives sheets and views and lots of use of TM1 Web that all need to have the alternate hierarchies in the main dimension, not in the new Hierarchies. The new Hierarchies are only available in PAX or PAW and we don't currently have a license for the latter. There is also the issue of user retraining. Our users are used to Perspectives and TM1 Web. Retraining users to use PAX will not be a quick process.

Regards

Paul Simon

User avatar
Steve Rowe
Site Admin
Posts: 1818
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: ViewZeroOut with Hierarchies in PA2.0

Post by Steve Rowe » Mon Oct 29, 2018 10:28 am

The complexity that requires the "Other Elements" in the dimname.dimname hierarchy that the VZO can see is because dimname.dimname does not have to have all the N levels that are in dimname.HierA that you may be trying to execute the VZO against.

Therefore your dimname.dimname hierarchy has to contain all the N levels of dimname.H* for the purpose of being able to VZO, hence putting those N levels in Other Elements to keep them separate for "tidiness".

(I think this is what PavoGa was talking about)

This doesn't arise in your use case because you are supporting both Alternative Rollups in dimname.dimname and Hierarchies in dimname.H*

User avatar
PavoGa
Community Contributor
Posts: 261
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

Re: ViewZeroOut with Hierarchies in PA2.0

Post by PavoGa » Mon Oct 29, 2018 1:59 pm

paulsimon wrote:
Sun Oct 28, 2018 8:05 pm
Hi

I am not sure why you need to build an Other Elements consolidation. If you can identify the base level elements of the hierarchy, then you can just use those elements to directly build a subset on the main dimension and use that in ViewZeroOut.
Exactly what Steve said. Due to some of the business requirements, we cannot duplicate the alternate hierarchies within the main dimname.dimname. The Other Elements rollup (at the root level) is strictly to not mix up those alternate N levels with the regular base dimname.dimname hierarchy.

It is not pretty, I do not like it, but at this point do not see more attractive alternatives. I doubly hate it because we are building in overhead that IF this VZO problem is fixed, we are stuck with the overhead without some rework.
Ty
Cleveland, TN

lotsaram
MVP
Posts: 3138
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: ViewZeroOut with Hierarchies in PA2.0

Post by lotsaram » Mon Oct 29, 2018 5:03 pm

PavoGa wrote:
Fri Oct 26, 2018 6:51 pm
I've got a workaround. Not pretty, but it is promising.

Got a process that "syncs' the Leaves hierarchy to the master dimension by putting any leaf elements that exist in just the alternate hierarchies in a consolidation "Other Elements" in the dimension.

Then, we build the subset we want off of an alternate hierarchy, call another process that just duplicates the hierarchy subset as a dimension subset and use the dimension subset in a traditional view to zero out.

Yep. Butt Ugly was what we called that in south Georgia.
What I don't understand is why you have leaf elements that don't exist in the "main" (same named) hierarchy. Even if they roll up to "Other" or "Unallocated" my 2c it's always best to have all leaves present in the main hierarchy.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
PavoGa
Community Contributor
Posts: 261
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

Re: ViewZeroOut with Hierarchies in PA2.0

Post by PavoGa » Mon Oct 29, 2018 7:12 pm

lotsaram wrote:
Mon Oct 29, 2018 5:03 pm

What I don't understand is why you have leaf elements that don't exist in the "main" (same named) hierarchy. Even if they roll up to "Other" or "Unallocated" my 2c it's always best to have all leaves present in the main hierarchy.
Long story as to why this is designed this way and too long to explain the whys here. However, by using this Other Elements consolidation, we can do exactly as suggested, meet our requirements and maintain the base hierarchy as desired. If I had known this VZO thing was an issue, would have done it differently but c'est la vie.
Ty
Cleveland, TN

User avatar
paulsimon
MVP
Posts: 647
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: ViewZeroOut with Hierarchies in PA2.0

Post by paulsimon » Mon Oct 29, 2018 10:51 pm

Hi

From my thinking, I tend to agree with lotsaram. I cannot see why you would want to have leaf level elements that only exist in a Hierarchy. I am genuinely interested to know what advantage that gives you?

I think that Hierarchies are still quite new to most of us and aren't necessarily delivering everything that we might want. For example, the automatic Attribute Hierarchies are not much use and you are better building a proper Hierarchy.

At present I am still planning that the main dimension will have all the base level elements. Data will be loaded to elements in the main dimensions and any ViewZeroOuts will be on the main dimension.

I primarily see Hierarchies as a useful aid to reporting. In our plans, they will contain different consolidations of the elements in the main dimension. They will be built at the same time as alternate hierarchies in the main dimension. Their consolidations will mirror the names used for the consolidations in the alternate hierarchies of the main dimension. Therefore all consolidations will still be unique across the dimension regardless of which Hierarchy they are in. I know that you can potentially call the top level parent of each Hierarchy 'Total' but I can see a lot of downsides to that. I prefer to call it something that describes what makes this Hierarchy different to any other.

The key benefit of Hierarchies as I see it, is for reporting in that Hierarchies allow us to use the same dimension in rows and columns or title area. For example, we have alternate hierarchies in our Programme dimension for budget categories and for ringfences. Hierarchies will allow us to show an exploration view with budget categories on columns and ringfence consolidations on the rows. At present, in order to do that we need to build a separate cube, with an extra budget category dimension that is populated in a potting exercise by pulling data from the central cube and writing to the programme dimension and the budget category dimension according to the budget category attribute of the programme. The use of hierarchies should allow us to remove the need for a reporting cube. We probably won't remove it now since we have a lot of reports that use it, but if starting again, then we would do things differently.

If it was a greenfield site, things might be different, but we have a lot of users who are familiar with Perspectives, TM1 Web, and the existing cubes. Teaching them to use new cubes, or a different approach to an existing cube using Hierarchies, and teaching them to use new tools such as PAX or PAW, is not going to happen overnight. That is why we intend to continue to provide the fall back position of alternate hierarchies within the main dimension.

One potential issue with having Hierarchies that contain elements that are not in the main dimension is on how you reconcile the numbers since this implies that you will get a different total depending on which Hierarchy you choose. Part of our Daily Tests are to check that we have the same total on all alternate hierarchies as the main hierarchy.

Regards

Paul Simon

User avatar
Steve Rowe
Site Admin
Posts: 1818
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: ViewZeroOut with Hierarchies in PA2.0

Post by Steve Rowe » Tue Oct 30, 2018 9:57 am

Its pretty hard when we are in this (hopefully) transistionary state with some objects and functions working on the old object model and some using the new.
I cannot see why you would want to have leaf level elements that only exist in a Hierarchy. I am genuinely interested to know what advantage that gives you?
A notional use case could be a slowly changing dimension and structure with elements dropping in and out in the real world, dimname.dimname could be your current reporting structure. dimname.Mar2018 could be the reporting structure as it stood in Mar 2018, these two structures are independent, you could have elements in play in Mar2018 that are no longer relevant to the current structure.

I think its easier to refer to dimname.dimname as the default hierarchy rather than the main hierarchy. There is nothing special about dimname.dimname versus any other hierarchy, I found that as soon as I flipped over to thinking "there's just hierarchies and dimension is just a container" from "I have my main dimension that I'm used to and then hierarchies that are sort of subsets of the main dimension" it was all a lot easier to work with.

re your reconciliation point there is a system generated "All Leaves" hierarchy that contains all the N levels which is a step in that direction. I think that "Hierarchies replacing Alternate Roll-ups" is probably just one use case for hierarchies, there are going to be others as we start using it, these may not require identical N level members.

We might even find that a 2d cube with many different mutually exclusive hierarchies is the "best" way of building certain types of application.

i.e.
Master dimension has the code structure acc-dept-CC-Month-Year-Vers and we process all the data onto this long concatenated element.
One other dimension with the element "Data" gives us our cube.
We then have Hierarchies that we would recognise as "dimensions". Account, Department , Cost Centre, Month, Year, Version, all with mutually exclusive members.

User avatar
PavoGa
Community Contributor
Posts: 261
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

Re: ViewZeroOut with Hierarchies in PA2.0

Post by PavoGa » Tue Oct 30, 2018 12:25 pm

paulsimon wrote:
Mon Oct 29, 2018 10:51 pm
Hi

From my thinking, I tend to agree with lotsaram. I cannot see why you would want to have leaf level elements that only exist in a Hierarchy. I am genuinely interested to know what advantage that gives you?
Long story short: the users want/need the ability to have a Wild, Wild West scenario with their planning and re-forecasts. What that means is creating ad hoc planning elements that represent any combination of cost centers/accounts/etc at which they can plan at that elevated level and then distribute to the constituents based on history. To support that method, consolidations are required as well made of of the elements represented by the planning element. These may change for each re-forecast. So we have to maintain the the hierarchy as it was in Period Two, while Period Six's hierarchy is different. The planning elements may increase/decrease at the whim of the planners. There are reporting hierarchies as well that have to be managed. The users want to be able flip back an forth and see numbers as they were as well as how they are.

After reviewing the hierarchy functionality, we felt it provided the least complicated method of achieving this goal. And so far, in spite of some of the quirks we have to work around, we are accomplishing this while providing a fairly simple interface for the users to achieve their requirements.

Trying to do what we are doing in 10.2 would have been a more complicated proposition. I know I've been pounding the hierarchy issues we run into on the Forum, but it is because there is a dearth of documentation and experience with PA versus, of course, previous versions. So we're either providing insight into some problems or hoping someone who has already worked through a similar issue may have an answer.

We are now, after recognizing the limitation (depending on one's perspective), incorporating all leaf elements into the dimname:dimname hierarchy. Just segregating them off because the original set of elements has its own unique usage.
Ty
Cleveland, TN

User avatar
paulsimon
MVP
Posts: 647
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: ViewZeroOut with Hierarchies in PA2.0

Post by paulsimon » Tue Oct 30, 2018 11:15 pm

Hi

So you have mentioned two reasons
a) historic hierarchies
b) scenario planning

Another case might be

c) Like for like sales in retail. However, that would typically be a subset of all stores, eg only those stores that were open this year and last year.

For maintaining past hierarchies at one client we had a main hierarchy and then an historic hierarchies consolidation where all the consolidations needed to be prefixed with the date at which the hierarchy was valid. This worked but looked pretty ugly, particularly as you needed to prefix the entire hierarchy not just the parts that were different to the current hierarchy. I can certainly see how hierarchies will help with this. Fortunately my current client only wants to look at the current hierarchy.

The relational approach to this is to use start and end dates on each link in the hierarchy. The benefit of that is that you only need to add records where the hierarchy has changed and then you do a query like get me all links where start date < required date and end date > required date or is null (or you just stored the end date as 9999-12-31 on the current record to avoid the need to check for null

The downside of the relational approach is that it is rather slow. In Kimball Dimensional modelling you would typically speed it up a little by adding a current record flag to get the current hierarchy more quickly, but it is still not going to match the speed of any multi-dimensional database. However, it might be possible to use the relational approach to store the different hierarchies and then only build them in TM1 when required.

I can see why Hierarchies would help with Scenario Planning, although there are a number of limitations, such as ViewZeroOut, Attributes, the difficulty of writing Hierarchy specific Rules, etc.

For planning at different levels, we typically used an automated process that created _Input elements below all consolidations in a range of levels, so that people could decide to either input at the detailed level or a more consolidated level by entering into the _Input element below the consolidation. That in some ways gives more flexibility than creating Hierarchies with different elements.

I think at present, I still prefer to think of a main hierarchy that has all elements and then those are just consolidated different ways in different Hierarchies.

It might be the case that you could, as Steve suggests, instead of having a dimension per account segment, just have one dimension with the full chart of accounts string, and then have alternate hierarchies for account, cost centre, etc. However, one thing I am still not clear on is what the performance would be like compared to the more traditional separate dimensional approach.

Where it might be useful is :

d) Chart of Accounts mapping where eg a subsidiary's Local Account elements are consolidated into the standard account and you have one Hierarchy subsidiary. This works fine if the Local Account Segment can be mapped to the Standard Account. However, often it is necessary to say that eg Local Account A + Local Cost Centre X identifies Standard Account B. That cannot be done with a simple hierarchy. However, if you were to take the full account string approach then you could potentially do the mapping since you are effectively collapsing all Local Segments into a single dimension. Of course this might bring a lot of redundancy, eg where they have 5 segments in the Local Chart of Accounts but only two are needed to identify a Standard Account. In the past I have used a rule based approach to this sort of mapping with surprisingly good performance.

Regards

Paul SImon

User avatar
PavoGa
Community Contributor
Posts: 261
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

Re: ViewZeroOut with Hierarchies in PA2.0

Post by PavoGa » Wed Oct 31, 2018 12:08 pm

Agree with everything you said Paul. I've already dealt with multiple hierarchies in the manner you discussed with prior TM1 versions on a limited basis. In this model, the business wanted to, as I indicated, maximize their flexibility. While some of the dims/hierarchies will change very little over time, if at all, they do have some legitimate basis for maximizing flexibility. As we looked into the hierarchy capability, we saw the answer. And frankly, it would work very well except for the glitches we have run into, things we did not foresee and were undocumented like VZO and updating a Hierarchy attribute before creating an MDX subset. :shock:

We picked this methodology and have, so far, been able to work around the issues in relatively minor ways. The VZO problem is resolved easily enough with a simple TI to sync Leaves hierarchy to the dimname:dimname and, for our purposes, segregating those elements into under a consolidation element. It is just taking time to figure these things out.

Great discussion. Thanks to everyone for weighing in.
Ty
Cleveland, TN

Post Reply