TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

HierarchySubsetElementDelete odd behavior

https://www.tm1forum.com/viewtopic.php?t=14340

Page 1 of 1

HierarchySubsetElementDelete odd behavior

Posted: Fri Oct 26, 2018 6:45 pm
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.

Re: HierarchySubsetElementDelete odd behavior

Posted: Fri Oct 26, 2018 8:56 pm
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.

Re: HierarchySubsetElementDelete odd behavior

Posted: Fri Nov 02, 2018 4:40 pm
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

Re: HierarchySubsetElementDelete odd behavior

Posted: Fri Nov 02, 2018 10:56 pm
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

Re: HierarchySubsetElementDelete odd behavior

Posted: Sun Nov 04, 2018 11:25 am
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.
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited