HierarchySubsetElementDelete odd behavior

Post Reply
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

HierarchySubsetElementDelete odd behavior

Post by PavoGa »

Have the following subset with hundreds of elements:
All Accounts
100-100-100
200-100-101
...

This code fails with "Unable to delete element at position "0" from dimension:subset":

Code: Select all

    sMDX = EXPAND('UNION({[%dimAccountsMaster%].[%hierAccountsForecast%].currentmember}, TM1FILTERBYLEVEL(TM1SUBSETALL([%dimAccountsMaster%].[%hierAccountsForecast%]), 0), ALL)');
    HierarchySubsetCreate(dimAccountsMaster, hierAccountsForecast, subAccountsForecast);
    HierarchySubsetMDXSet(dimAccountsMaster, hierAccountsForecast, subAccountsForecast, sMDX);
    HierarchySubsetElementDelete(dimAccountsMaster, hierAccountsForecast, subAccountsForecast, 1);
However do this (note the two extra lines after HierarchySubsetMDXSet) and it works like a charm:

Code: Select all

    sMDX = EXPAND('UNION({[%dimAccountsMaster%].[%hierAccountsForecast%].currentmember}, TM1FILTERBYLEVEL(TM1SUBSETALL([%dimAccountsMaster%].[%hierAccountsForecast%]), 0), ALL)');
    HierarchySubsetCreate(dimAccountsMaster, hierAccountsForecast, subAccountsForecast);
    HierarchySubsetMDXSet(dimAccountsMaster, hierAccountsForecast, subAccountsForecast, sMDX);
    sFirstElement = HierarchySubsetGetElementName(dimAccountsMaster, hierAccountsForecast, subAccountsForecast, 1);
    nFirstElementIndex = HierarchySubsetElementGetIndex(dimAccountsMaster, hierAccountsForecast, subAccountsForecast, sFirstElement, 1);
    HierarchySubsetElementDelete(dimAccountsMaster, hierAccountsForecast, subAccountsForecast, 1);
Is it just me or does it seem there is a lot of quirkiness with the Hierarchy functions? I'not saying I'd be fine with it, but if the documentation said "HEY! Run these two seemingly unrelated functions before executing HierarchySubsetElementDelete!" I would at least think it was planned that way, kind of like certain datasource functions go together. But without that, this is just nuts and time consuming the figure out the right combination of functions to run to get some functions to work. See my post on MDX subsets and assigning a caption to a hierarchy for another example.
Ty
Cleveland, TN
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: HierarchySubsetElementDelete odd behavior

Post by PavoGa »

By the way, HierarchySubsetElementDelete does work with this:

Code: Select all

    sMDX = EXPAND('UNION({[%dimAccountsMaster%].[%hierAccountsForecast%].currentmember}, TM1FILTERBYLEVEL(TM1SUBSETALL([%dimAccountsMaster%].[%hierAccountsForecast%]), 0), ALL)');
    HierarchySubsetCreate(dimAccountsMaster, hierAccountsForecast, subAccountsForecast);
    HierarchySubsetMDXSet(dimAccountsMaster, hierAccountsForecast, subAccountsForecast, sMDX);
    nFirstElementIndex = HierarchySubsetElementGetIndex(dimAccountsMaster, hierAccountsForecast, subAccountsForecast, 'Georgia Bulldogs', 1);
    HierarchySubsetElementDelete(dimAccountsMaster, hierAccountsForecast, subAccountsForecast, 1);
 
Not tested, but I have serious doubts about this code:

Code: Select all

    nFirstElementIndex = HierarchySubsetElementGetIndex(dimAccountsMaster, hierAccountsForecast, subAccountsForecast, 'Florida Gators', 1);
Go Dawgs.
Ty
Cleveland, TN
User avatar
Michel Zijlema
Site Admin
Posts: 712
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: HierarchySubsetElementDelete odd behavior

Post by Michel Zijlema »

PavoGa wrote: Fri Oct 26, 2018 6:45 pm Have the following subset with hundreds of elements:
All Accounts
100-100-100
200-100-101
...

This code fails with "Unable to delete element at position "0" from dimension:subset":

Code: Select all

    sMDX = EXPAND('UNION({[%dimAccountsMaster%].[%hierAccountsForecast%].currentmember}, TM1FILTERBYLEVEL(TM1SUBSETALL([%dimAccountsMaster%].[%hierAccountsForecast%]), 0), ALL)');
    HierarchySubsetCreate(dimAccountsMaster, hierAccountsForecast, subAccountsForecast);
    HierarchySubsetMDXSet(dimAccountsMaster, hierAccountsForecast, subAccountsForecast, sMDX);
    HierarchySubsetElementDelete(dimAccountsMaster, hierAccountsForecast, subAccountsForecast, 1);
However do this (note the two extra lines after HierarchySubsetMDXSet) and it works like a charm:

Code: Select all

    sMDX = EXPAND('UNION({[%dimAccountsMaster%].[%hierAccountsForecast%].currentmember}, TM1FILTERBYLEVEL(TM1SUBSETALL([%dimAccountsMaster%].[%hierAccountsForecast%]), 0), ALL)');
    HierarchySubsetCreate(dimAccountsMaster, hierAccountsForecast, subAccountsForecast);
    HierarchySubsetMDXSet(dimAccountsMaster, hierAccountsForecast, subAccountsForecast, sMDX);
    sFirstElement = HierarchySubsetGetElementName(dimAccountsMaster, hierAccountsForecast, subAccountsForecast, 1);
    nFirstElementIndex = HierarchySubsetElementGetIndex(dimAccountsMaster, hierAccountsForecast, subAccountsForecast, sFirstElement, 1);
    HierarchySubsetElementDelete(dimAccountsMaster, hierAccountsForecast, subAccountsForecast, 1);
Is it just me or does it seem there is a lot of quirkiness with the Hierarchy functions? I'not saying I'd be fine with it, but if the documentation said "HEY! Run these two seemingly unrelated functions before executing HierarchySubsetElementDelete!" I would at least think it was planned that way, kind of like certain datasource functions go together. But without that, this is just nuts and time consuming the figure out the right combination of functions to run to get some functions to work. See my post on MDX subsets and assigning a caption to a hierarchy for another example.
Hi PavoGa,

My guess would be that the HierarchySubsetCreate and HierarchySubsetMDXSet functions will define the subset (as an MDX expression), but do not yet execute the MDX expression (so do not yet generate the list of elements). Retrieving an element through f.i. HierarchySubsetGetElementName actually executes the MDX expression and generates the list of elements. After that the element with index 1 is known...

Michel
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: HierarchySubsetElementDelete odd behavior

Post by paulsimon »

Hi

I haven't tried it but RefreshMdxHierarchy(dimensionName, hierarchy) might be an alternative. I suspect that what Michel says is right. The two statements that you have added force the MDX to be evaluated to populate the subset. We used to have to put in an RefreshMDXHierarchy(Dim) to get BI Reports to work properly. I note that they have now added an optional hierarchy parameter to this function so you can limit the scope of this to a single Hierarchy within the dimension.

It does seem a little odd that there is no Hierarchy equivalent of the SubsetCreateByMDX function. I suspect that for a normal dimension this both creates the subset and evaluates the MDX to populate it.

I can see where Steve is coming from when he says that it is best to just think of hierarchies and not to distinguish between the main dimension hierarchy and other hierarchies. However, it does appear that in reality that both exist, eg there are physically separate file objects, and separate TI functions.

Regards

Paul Simon
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: HierarchySubsetElementDelete odd behavior

Post by Steve Rowe »

Don't forget that with the old style functions dimname:hiername is supported in the dimension name position, though I've not checked with this specific function.
Technical Director
www.infocat.co.uk
Post Reply