Planning Analytics Local 2.0: Multiple Hierarchies

Post Reply
harrytm1
Regular Participant
Posts: 226
Joined: Thu Apr 02, 2009 2:51 pm
OLAP Product: IBM Planning Analytics
Version: Latest version
Excel Version: 2003 to 2019

Planning Analytics Local 2.0: Multiple Hierarchies

Post by harrytm1 »

Hi all,

We are currently testing the recently released PAL 2.0. One of the features we are very interested in is using hierarchies as virtual dimensions in PAx (fka Cafe) and PAW (I don't think it works in TM1 Web, but will certainly test it).

One thing we know is that multiple hierarchies can be created in PAW as seen in the IBM video. But it cannot be done through the old school XDI files or Dim Editor. During the launch event, we asked if PAW is in fact calling some TI in the background and the reply was "yes". And of course we all know that PAW is an add-on, which means customer needs to pay for it. So it does not make sense to not have the ability to create multiple hierarchies if customer chooses not to purchase PAW, especially when PAx uses it.

So now we are exploring ways to create multiple hierarchies using TI such that these hierarchies can then be recognised by PAx and be used as virtual dimensions in views.

We refer to the TI reference guide and found a lot of new TI functions that starts with "Hierarchy" e.g. HierarchySubsetATTRN, HierarchyContainsAllLeaves, HierarchySubsetCreate, as well as functions such as HierarchyElementComponentAdd, SubsetAttrPutS and HierarchyATTRS. These should be the TI functions that help to create the new hierarchies.

The questions we have are:
- We cannot find any IBM documentation to describe in details a TI that uses such functions to do what PAW does. We now have to comb through the new functions to figure this out.
- If a dimension is maintained using XDI file, what will happen if hierarchies are further created using PAW or TI? Will these hierarchies be wiped out the next time a save action is done in XDI which we suspect will happen? We will be testing it out.
- We already have a generic TI that builds alternate hierarchies using attribute values. Such hierarchies are 3 levels deep. How does that differ from hierarchies created using the new functions? Will the hierarchies created by existing generic TI, or even through XDI or dim editor be recognised by PAx and PAW so that they can be used as virtual dims?
- If they cannot be used as virtual dims, is there a control cube or attribute in a control dim somewhere that can register old hierarchies so that they can eventually be used as virtual dimensions?
- Given the introduction of new TI functions, some of them seem to be repeated such as HierarchyElementComponentAdd (new) versus DimensionElementComponentAdd. Also, it seems that hierarchies and subsets can now have their own attributes (not surprising since subsets are already being treated as c-element in certain way e.g. custom consolidation) through functions such as HierarchySubsetATTRS and HierarchyAttrPutS. Has anyone come across any IBM documentation that details on the relationship between a Dimension and its Hierarchies and the differences between such "new" hierarchies and the "old" ones (i.e. created using XDI, pre-PAL TI or dim editor)?

Oh, by the way, spotted this function SubsetElementGetIndex. Is this available in previous version, or new to PAL 2.0? Seems like the function that we have all been waiting for!

We will update any findings in this thread. Look forward to your contributions!
Planning Analytics latest version, including Cloud
Marcus Dietrich IBM
Posts: 7
Joined: Mon Dec 19, 2016 11:56 pm
OLAP Product: PA TM1
Version: 2.0
Excel Version: 2013

Re: Planning Analytics Local 2.0: Multiple Hierarchies

Post by Marcus Dietrich IBM »

Hi
Here is a video from Guy on the new hierarchies in TM1 PA 2.0 to get you started, it should already answer some of your queries. Let me see what else I can dig out.

Hierarchies part 1 https://youtu.be/jxdMSAAHvh8
Hierarchies part 2 https://youtu.be/yQgcutv65eQ
Marcus Dietrich IBM
Posts: 7
Joined: Mon Dec 19, 2016 11:56 pm
OLAP Product: PA TM1
Version: 2.0
Excel Version: 2013

Re: Planning Analytics Local 2.0: Multiple Hierarchies

Post by Marcus Dietrich IBM »

I'd recommend using these TI functions to create additional hierarchies for your dimensions

http://www.ibm.com/support/knowledgecen ... tions.html

Those give you full control over your hierarchies design, eg number of levels per hierarchy.

PAW calls a rather undocumented function

CreateHierarchyByAttribute(DimensionName, AttributeName,NameOfTotalForOrphanedMembers,TopLevelMemberName );

to create hierarchies from attributes; easy but having the downside that your hierarchy can only be 2 levels deep.
BrianL
MVP
Posts: 264
Joined: Mon Nov 03, 2014 8:23 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2 PA2
Excel Version: 2016

Re: Planning Analytics Local 2.0: Multiple Hierarchies

Post by BrianL »

harrytm1 wrote:Oh, by the way, spotted this function SubsetElementGetIndex. Is this available in previous version, or new to PAL 2.0? Seems like the function that we have all been waiting for!
It's in 10.2.2 FP6 too
harrytm1
Regular Participant
Posts: 226
Joined: Thu Apr 02, 2009 2:51 pm
OLAP Product: IBM Planning Analytics
Version: Latest version
Excel Version: 2003 to 2019

Re: Planning Analytics Local 2.0: Multiple Hierarchies

Post by harrytm1 »

BrianL wrote:
harrytm1 wrote:Oh, by the way, spotted this function SubsetElementGetIndex. Is this available in previous version, or new to PAL 2.0? Seems like the function that we have all been waiting for!
It's in 10.2.2 FP6 too
Thanks for pointing this out, Brian. We skipped FP6 due to certain defects so we miss this new TI function.
Planning Analytics latest version, including Cloud
harrytm1
Regular Participant
Posts: 226
Joined: Thu Apr 02, 2009 2:51 pm
OLAP Product: IBM Planning Analytics
Version: Latest version
Excel Version: 2003 to 2019

Re: Planning Analytics Local 2.0: Multiple Hierarchies

Post by harrytm1 »

Marcus Dietrich IBM wrote:I'd recommend using these TI functions to create additional hierarchies for your dimensions

http://www.ibm.com/support/knowledgecen ... tions.html

Those give you full control over your hierarchies design, eg number of levels per hierarchy.

PAW calls a rather undocumented function

CreateHierarchyByAttribute(DimensionName, AttributeName,NameOfTotalForOrphanedMembers,TopLevelMemberName );

to create hierarchies from attributes; easy but having the downside that your hierarchy can only be 2 levels deep.
Hi Marcus, thanks for the video links and hierarchy manipulation TI functions. We had watched the video which gave us some ideas.

The undocumented CreateHierarchyByAttribute function is likely to be similar to our existing generic TI which builds hierarchies using attribute values. But we suspect the main difference is the new TI function will additionally register the hierarchy in a control cube so that it can be used as a virtual dim in PAW and PAx for analysis, or add the hierarchy in the new control dim Hierarchy_<dim name> which we suspect is acting as a lookup dim so that PA knows what hierarchies are available in the dim as virtual dims.

Furthermore, if PAW front-end only can create hierarchies using attributes, such hierarchies will only be limited to 3 levels i.e. top node > attribute values as c-elem > underlying n-elem. We don't think use of virtual dims are solely limited to such attribute hierarchies. We think there should be a new TI function (or even using old TI function like DimensionElementInsert) that adds hierarchies that were created in earlier versions of TM1 through XDI, TI or dim editor into the control dim Hierarchy_<dim name>. This will allow them to act as virtual dim. We are testing this out in Cloud Concierge now.

Lastly, it appears that you are from IBM. I asked this question in another thread and hope that you can shed some light. It appears that we need to install PAW (which we are still having problem with; probably because we tried to install it in the same VM as PAL. Saw your earlier post that recommend to install PAW in a separate VM. This will lead to VM within a VM situation because of docker.) in order for PAx to work.

The question is on licensing. If a company wants to use multiple hierarchies feature but does not want to purchase PAW add-on licenses, can they still install PAW and let the appointed Modeler creates hierarchies using PAW so that end users can use virtual dimensions in PAx? Even if we take multiple hierarchies out of the equation and the company simply wants to use PAx without purchasing PAW (e.g. existing TM1 customers), can they install PAW without infringing licensing agreement?

We are still scratching our heads as to why PAW is needed to run PAx when it is not required in TM1 10.2.2.

Thanks in advance!
Planning Analytics latest version, including Cloud
harrytm1
Regular Participant
Posts: 226
Joined: Thu Apr 02, 2009 2:51 pm
OLAP Product: IBM Planning Analytics
Version: Latest version
Excel Version: 2003 to 2019

Re: Planning Analytics Local 2.0: Multiple Hierarchies

Post by harrytm1 »

Just a quick update. Hierarchies created in PAW are actually created as dim files in the same Data folder of the model. Just that you won't find them under Data folder. Instead, a sub-folder e.g. Product}hiers is created and each hierarchy, which is now a dim file, is stored there. That makes sense because a hierarchy is treated as a dimension now.

Another thing to note is that we will not see such hierarchies in Architect's sub editor, probably because Architect requires uniqueness to be enforced through the dimension. And also because such hierarchies are not considered part of the dim in the traditional sense. There is a control dim e.g. }Hierarchies_Product that will list the hierarchies created by the undocumented TI function CreateHierarchyByAttribute or through PAW. The hierarchies will be shown as such Product:Customer Target in the subset editor in Architect.
Planning Analytics latest version, including Cloud
Marcus Dietrich IBM
Posts: 7
Joined: Mon Dec 19, 2016 11:56 pm
OLAP Product: PA TM1
Version: 2.0
Excel Version: 2013

Re: Planning Analytics Local 2.0: Multiple Hierarchies

Post by Marcus Dietrich IBM »

Hi harrytm1
harrytm1 wrote:
Furthermore, if PAW front-end only can create hierarchies using attributes, such hierarchies will only be limited to 3 levels i.e. top node > attribute values as c-elem > underlying n-elem. We don't think use of virtual dims are solely limited to such attribute hierarchies. We think there should be a new TI function (or even using old TI function like DimensionElementInsert) that adds hierarchies that were created in earlier versions of TM1 through XDI, TI or dim editor into the control dim Hierarchy_<dim name>. This will allow them to act as virtual dim. We are testing this out in Cloud Concierge now.
I agree that an attribute-driven hierarchy is rather limited; I believe the TI functions here should allow you to do what you want to do, i.e. create your own custom dimension hierarchy:

http://www.ibm.com/support/knowledgecen ... tions.html

for example HierarchyElementInsert, HierarchyElementComponentAdd.

Another approch is: if you do a right mouse click on a dimension in PA Workspace then you get a dimension editor with which you can add new hierarchies and manually design your hierarchy members, like in this screen shot: https://ibm.box.com/s/lrw4j2sdeu1d7uf20k5kx95jvpcww8ct

the underlying principle is quite simple, you can add base members from the default hierarchy and new consolidations into your hierarchy.
harrytm1 wrote:
If a company wants to use multiple hierarchies feature but does not want to purchase PAW add-on licenses, can they still install PAW and let the appointed Modeler creates hierarchies using PAW so that end users can use virtual dimensions in PAx? Even if we take multiple hierarchies out of the equation and the company simply wants to use PAx without purchasing PAW (e.g. existing TM1 customers), can they install PAW without infringing licensing agreement?
the answer is yes; the direction we (technical) people in IBM have been given is that existing clients who are entitled to PA Local 2.0 with their maintenance can install and use PAW with the Modeler license to create hierarchies, which can then be consumed by all users using PAx (PA for Excel); you may want to get your IBM Partner Rep's confirmation, if you contact me via the email feature then I can get you in contact with the right person in your region.
harrytm1 wrote:
We are still scratching our heads as to why PAW is needed to run PAx when it is not required in TM1 10.2.2.
I'm also scratching my head over this one, I also would have preferred clients being able to use PAx without needing the PAW overhead because PAx is pretty amazing! I'll see if I can get some additional info on this, it won't change the fact though. It should also be possible to run the docker in a VM because that's exactly what the Cloud Concierge images do.
harrytm1
Regular Participant
Posts: 226
Joined: Thu Apr 02, 2009 2:51 pm
OLAP Product: IBM Planning Analytics
Version: Latest version
Excel Version: 2003 to 2019

Re: Planning Analytics Local 2.0: Multiple Hierarchies

Post by harrytm1 »

Marcus Dietrich IBM wrote:Hi harrytm1
harrytm1 wrote:
Furthermore, if PAW front-end only can create hierarchies using attributes, such hierarchies will only be limited to 3 levels i.e. top node > attribute values as c-elem > underlying n-elem. We don't think use of virtual dims are solely limited to such attribute hierarchies. We think there should be a new TI function (or even using old TI function like DimensionElementInsert) that adds hierarchies that were created in earlier versions of TM1 through XDI, TI or dim editor into the control dim Hierarchy_<dim name>. This will allow them to act as virtual dim. We are testing this out in Cloud Concierge now.
I agree that an attribute-driven hierarchy is rather limited; I believe the TI functions here should allow you to do what you want to do, i.e. create your own custom dimension hierarchy:

http://www.ibm.com/support/knowledgecen ... tions.html

for example HierarchyElementInsert, HierarchyElementComponentAdd.

Another approch is: if you do a right mouse click on a dimension in PA Workspace then you get a dimension editor with which you can add new hierarchies and manually design your hierarchy members, like in this screen shot: https://ibm.box.com/s/lrw4j2sdeu1d7uf20k5kx95jvpcww8ct

the underlying principle is quite simple, you can add base members from the default hierarchy and new consolidations into your hierarchy.
harrytm1 wrote:
If a company wants to use multiple hierarchies feature but does not want to purchase PAW add-on licenses, can they still install PAW and let the appointed Modeler creates hierarchies using PAW so that end users can use virtual dimensions in PAx? Even if we take multiple hierarchies out of the equation and the company simply wants to use PAx without purchasing PAW (e.g. existing TM1 customers), can they install PAW without infringing licensing agreement?
the answer is yes; the direction we (technical) people in IBM have been given is that existing clients who are entitled to PA Local 2.0 with their maintenance can install and use PAW with the Modeler license to create hierarchies, which can then be consumed by all users using PAx (PA for Excel); you may want to get your IBM Partner Rep's confirmation, if you contact me via the email feature then I can get you in contact with the right person in your region.
harrytm1 wrote:
We are still scratching our heads as to why PAW is needed to run PAx when it is not required in TM1 10.2.2.
I'm also scratching my head over this one, I also would have preferred clients being able to use PAx without needing the PAW overhead because PAx is pretty amazing! I'll see if I can get some additional info on this, it won't change the fact though. It should also be possible to run the docker in a VM because that's exactly what the Cloud Concierge images do.
Thanks, Marcus. I think PAW is the engine behind multi hierarchy feature and since sub editor in PAx needs to now also display hierarchies as virtual dim, it makes sense that PAW is needed.

Yes, I tested a manually created hierarchy using PAW and it can be used as virtual dim. So we are certainly not limited to attribute hierarchy only.

Having said that, we now need to develop a set of TI that use new TI functions to replicate existing hierarchies created the old school ways into "new" hierarchies which can be used as virtual dim. I don't see any TI in Cloud Concierge that does this.

Lastly, the concept of hierarchy is really just the same as dimensions. The difference is that they must be created using the new hierarchy Ti functions.
Planning Analytics latest version, including Cloud
Marcus Dietrich IBM
Posts: 7
Joined: Mon Dec 19, 2016 11:56 pm
OLAP Product: PA TM1
Version: 2.0
Excel Version: 2013

Re: Planning Analytics Local 2.0: Multiple Hierarchies

Post by Marcus Dietrich IBM »

harrytm1 wrote:
Lastly, the concept of hierarchy is really just the same as dimensions. The difference is that they must be created using the new hierarchy Ti functions.
Agree just with one benefit for hierarchies: users can "ignore" them when they don't need them while "real" dimensions always require a selection. I like how PAW hides the hierarchies by default in the hidden area, PAx also hides them by default when not in use.

I believe they have done a really good job with how they implemented using the hierarchies, the fact that designing/tweaking them requires PAW is admittedly not so great.

Development also says replacing dimensions with hierarchies will speed up the cube's query performance (not: replacing parallel hierarchies in one dimension with the new hierarchies).
Marcus Dietrich IBM
Posts: 7
Joined: Mon Dec 19, 2016 11:56 pm
OLAP Product: PA TM1
Version: 2.0
Excel Version: 2013

Re: Planning Analytics Local 2.0: Multiple Hierarchies

Post by Marcus Dietrich IBM »

btw I think one more thing worth mentioning for the new hierarchies feature is what I feel is a prime use case - slowly changing dimensions; your default hierarchy for a dimension - let's say organisational structure - could be the current structure while hierarchies could show you your past org structure or the new org structure. By combining hierarchies in rows and columns you can see how your data moves through your different organisational structures.

example screen shot with PAx here:
https://ibm.box.com/s/m2ydh0pkdy9uguwg0ar351b85723e0lj

in the rows I have my current sales structure (hierarchy Region), in the column a market-oriented new structure (hierarchy Markets); I can see how my Central Europe Region sales moves into the A Market.
BrianL
MVP
Posts: 264
Joined: Mon Nov 03, 2014 8:23 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2 PA2
Excel Version: 2016

Re: Planning Analytics Local 2.0: Multiple Hierarchies

Post by BrianL »

harrytm1 wrote: We are still scratching our heads as to why PAW is needed to run PAx when it is not required in TM1 10.2.2.
PAx is utilizing the same exact set editor and cube viewer as PAW. Unfortunately, the code only exists in the PAW install and that's why PAW is required to access those parts of the PAx UI. You can clearly see the same UI in both clients and it's also clearly a break from prior releases of CAFE.
BigG
Community Contributor
Posts: 211
Joined: Tue Sep 15, 2009 11:13 pm
OLAP Product: IBMPA
Version: PA 2.0 Cloud
Excel Version: 2010

Re: Planning Analytics Local 2.0: Multiple Hierarchies

Post by BigG »

This is good info from All. I am in the process of building a new cube in PA and looking at leveraging all of this in the design. Cube has less dims - multiple hierarchies coming off the attributes for the main dimension.

Anyway,thought I would add this security piece to the conversation

Code: Select all

 HierarchyElementSecurityPut(Level, DimName, HierName, ElName, Group);
Which (if it works - still to build in) will get rid of noise for the end users.

https://www.ibm.com/support/knowledgece ... typut.html
GG
BigG
Community Contributor
Posts: 211
Joined: Tue Sep 15, 2009 11:13 pm
OLAP Product: IBMPA
Version: PA 2.0 Cloud
Excel Version: 2010

Re: Planning Analytics Local 2.0: Multiple Hierarchies

Post by BigG »

Just a point to note, by design it looks like, you cannot save a view back to TM1 server that uses an alternative hierarchy. Meaning you need to save a workbook in PAX, or save a 'Book' with the 'View' in PAW. Cant see how to manage otherwise, unless anyone else has found a solution?

I think for existing models, keep the multiple hierarchies as is, and just add the alternatives as needed.
GG
kangkc
Community Contributor
Posts: 206
Joined: Fri Oct 17, 2008 2:40 am
OLAP Product: TM1, PA , TMVGate
Version: 2.x
Excel Version: 36x
Location: Singapore
Contact:

Re: Planning Analytics Local 2.0: Multiple Hierarchies

Post by kangkc »

BigG wrote:Just a point to note, by design it looks like, you cannot save a view back to TM1 server that uses an alternative hierarchy. Meaning you need to save a workbook in PAX, or save a 'Book' with the 'View' in PAW. Cant see how to manage otherwise, unless anyone else has found a solution?

I think for existing models, keep the multiple hierarchies as is, and just add the alternatives as needed.
You can save as MDX. This is how we use it.
BigG
Community Contributor
Posts: 211
Joined: Tue Sep 15, 2009 11:13 pm
OLAP Product: IBMPA
Version: PA 2.0 Cloud
Excel Version: 2010

Re: Planning Analytics Local 2.0: Multiple Hierarchies

Post by BigG »

thanks kankc, how do I do that in PAX, the save button in ribbon is greyed out and there is no 'save' in MDX, also where does it save too?

Also found this https://community.watsonanalytics.com/d ... pecti.html
GG
kangkc
Community Contributor
Posts: 206
Joined: Fri Oct 17, 2008 2:40 am
OLAP Product: TM1, PA , TMVGate
Version: 2.x
Excel Version: 36x
Location: Singapore
Contact:

Re: Planning Analytics Local 2.0: Multiple Hierarchies

Post by kangkc »

BigG wrote:thanks kankc, how do I do that in PAX, the save button in ribbon is greyed out and there is no 'save' in MDX, also where does it save too?

Also found this https://community.watsonanalytics.com/d ... pecti.html
My apology as I should have been more specific. Under PAx -> Exploration, you will see a MDX button. What we do is to copy the MDX statement and save it for other use.
BigG
Community Contributor
Posts: 211
Joined: Tue Sep 15, 2009 11:13 pm
OLAP Product: IBMPA
Version: PA 2.0 Cloud
Excel Version: 2010

Re: Planning Analytics Local 2.0: Multiple Hierarchies

Post by BigG »

Has anyone tried HierarchyTopElementInsert as it does not seem to insert the element into PAW. Tried in Prolog and Metadata. eg.

TotalHierarchy2 with two children added in Metadata is the HierarchyTopElementInsert but displayed in PAW:
-child1
-child2

I would expect the root to be displayed
TotalHierarchy2
-child1
-child2

Also, does anyone have issues with alternative hierarchy aliases in PA, I get
Attribute is Not Unique
message on leaf level items, possibly because they are used in all hierarchies?
GG
BigG
Community Contributor
Posts: 211
Joined: Tue Sep 15, 2009 11:13 pm
OLAP Product: IBMPA
Version: PA 2.0 Cloud
Excel Version: 2010

Re: Planning Analytics Local 2.0: Multiple Hierarchies

Post by BigG »

So far with alternative Hierarchies in PA cloud managed service - therefore latest version:
• Rules break if you specifically reference multiple elements in a target for a dimension that has alternative hierarchies initiated – fix by creating individual lines for each element
• Adding hierarchy by attribute manually in PAW works – but not if you delete one you didn’t like and try to create another with a different name
• Adding hierarchy by attribute through TI works, but if you hit issue above it will not
• Adding a new hierarchy uses same leaf elements – but alias creation breaks because it thinks you are duplicating
• Adding a top level to the hierarchy using HierarchyTopElementInsert does not add a top level to the hierarchy
• Cannot use alternative hierarchies in DBR related reports (Dynamic and Custom)
• If no longer wanted, you cannot delete alternative hierarchies completely from the system - always have 'Leaves'


IBM are aware of above
GG
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: Planning Analytics Local 2.0: Multiple Hierarchies

Post by Steve Rowe »

I'll add one more that I have not seen documented (though not looked...!)

Picklist subset syntax.

The subset reference syntax for subsets changes for dimensions with hierachies.

instead of SUBSET:dimname:subname

we have SUBSET:dimname\:hierarchy:subname

i.e. replace
subset:Entity:FE - Division
with
subset:Entity\:Entity:FE - Division

In this case I am referencing the same named default hierarchy.

If you get it wrong interestingly a message shows in log telling you what the syntax is.
Symptom is that the pick list drop down doesn't show up, then you spend ages trying to figure what you have wrong, realise it is a dimension with hierarchies, decide picklists don't work with hierarchies and then some hours later see the message in the message log...

Not withstanding some wrinkles, am currently working a system with a Day dimension and hierarchies for Year / Month / Week / Day of Week. It's really incredible functionality, so easy to use both as a dev and as a user. <3

Going to take a while for out minds to expand to the new paradigm.
Technical Director
www.infocat.co.uk
Post Reply