unwinding certain elements issue

Post Reply
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

unwinding certain elements issue

Post by Analytics123 »

Hi ,

I have a dimension with hierarchy like below .

All customers
America
EMEA
ASIA

Under each country i have 5 levels.
unwinding is done because customers might swap between one parent to other at same level.

I want to do unwinding only for customer under America and one region under asia where swapping occurs.

My unwinding code is below

vMax = DIMSIZ(vDim);
vCount = 1;

WHILE (vCount <= vMax);
vElem = DIMNM(vDim, vCount);
vMaxParents = ELPARN(vDim, vElem);
if((ELISANC(vDim,'AMERICAS',vElem)=1)%(ELISANC(vDim,'Region - A00',vElem)=1));
WHILE (vMaxParents>0);

vParent = ELPAR(vDim, vElem, vMaxParents);
DimensionElementComponentDelete(vDim, vParent, vElem);
vMaxParents = vMaxParents - 1;
END;
ENDIF;
vCount = vCount + 1;
END;

this unwinding only takes my america's first child and puts is outside , then the first childs children are not unwinded.

Meaning america has region 1 to region7 and region 1 has 4 subregions.

the region 1 to region 7 are unwinded , but its children are still maintained.

How do I unwind all elements under Americas

Any help is appreciated.
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: unwinding certain elements issue

Post by gtonkin »

Will refer you to the search function as this has been covered many times over the years - your basic problem is that you are creating fragments by unwinding from the top down-you need to work backwards through the dimension/hierarchy in most cases.
Edit: Could you not use MDX to create a subset with the full hierarchies you need and then unwind them?
BariAbdul
Regular Participant
Posts: 424
Joined: Sat Mar 10, 2012 1:03 pm
OLAP Product: IBM TM1, Planning Analytics, P
Version: PAW 2.0.8
Excel Version: 2019

Re: unwinding certain elements issue

Post by BariAbdul »

Might be not exactly what you looking for ,but along the similar lines,might help:
http://www.tm1forum.com/viewtopic.php?f=3&t=13716 Thanks
"You Never Fail Until You Stop Trying......"
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: unwinding certain elements issue

Post by Analytics123 »

can anyone share a code snippet for unwinding using mdx .
Say assume I have a dimension where i unwind only below code

Americas->V00,V01
All elements under this 2 regions V00 and V01 .

I have a mdx like below

{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER({[Customers SoldTo].[Region - V00],[Customers SoldTo].[Region - V01]}, ALL, RECURSIVE )}, 0)}

Now how to unwind the elements meaning seperating parent from child .

say I use this subset as a source for TI which will have all elements under V00 and V01.
How would i associate the parent and child for sepearting them from the consolidation ?

Thanks,
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: unwinding certain elements issue

Post by Analytics123 »

I got to work used subsetsize and get element name and used the same process on it .

Thanks,
Moh
Posts: 43
Joined: Fri Aug 01, 2014 5:17 pm
OLAP Product: Cognos
Version: 10.1.1
Excel Version: 2010

Re: unwinding certain elements issue

Post by Moh »

Sir Pls share your code,it going to help me and others
Moh
Posts: 43
Joined: Fri Aug 01, 2014 5:17 pm
OLAP Product: Cognos
Version: 10.1.1
Excel Version: 2010

Re: unwinding certain elements issue

Post by Moh »

Analytics123 please share code
User avatar
orlando
Community Contributor
Posts: 167
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: unwinding certain elements issue

Post by orlando »

Hi,

i'm using this code to unwind a hierarchy (or parts from that). I always move the unwinded elmenets to a new parent called "trash".
Then i'm always sure, that i don't lose any elements.

#############################

sDim = 'MyDim';
sTopNode='MyTopNode';


sMDX ='{HIERARCHIZE({TM1DRILLDOWNMEMBER( {['|sDim|']['|sTopNode|']}, ALL, RECURSIVE )})}';

sTrash = 'Trash';

sSub = GetProcessName;

if(SubsetExists(sDim, sSub)=1);
SubsetDestroy(sDim, sSub);
endif;

SubSetCreateByMDX(sSub, sMDX);

SubSetElementInsert(sDim, sSub, sTopNode, 1);

SubSetElementDelete(sDim, sSub, 1);

DimensionElementInsert(sDim, '', sTrash, 'N');

iCounter = SubSetGetSize(sDim, sSub);

i=1;
while(i<=SubSetGetSize(sDim, sSub));
sElem = SubsetGetElementName(sDim, sSub, iCounter);
iPar = ElParN(sDim, sElem);

p=1;
if(iPar >=1);
while(p<=iPar);
sParent = ELPAR(sDim, sElem, p);
if(ELISANC(sDim, sTopNode,sParent)=1);
DimensionElementComponentAddDirect(sDim, sTrash, sElem,1);
DimensionElementComponentDeleteDirect(sDim, sParent, sElem);
p=9999999999;
endif;
p=p+1;
end;
endif;


i=i+1;
iCounter = iCounter-1;
end;

#########################
Moh
Posts: 43
Joined: Fri Aug 01, 2014 5:17 pm
OLAP Product: Cognos
Version: 10.1.1
Excel Version: 2010

Re: unwinding certain elements issue

Post by Moh »

thank you Mr. orlando.i am disappointed with Analytics123 despite my several request he did not share the code,i suprised how can he expect help from others.
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: unwinding certain elements issue

Post by Analytics123 »

Moh,

I was out of town , here you go .

DIMENSIONSORTORDER(vDim,'','','ByHierarchy','ASCENDING');

vMax = SubsetGetSize(vDim,vSubsetName);
vCount = 1;
WHILE (vCount <= vMax);
vElem = SubsetGetElementName(vDim,vSubsetName,vCount);
vMaxParents = ELPARN(vDim, vElem);
WHILE (vMaxParents>0);
vParent = ELPAR(vDim, vElem, vMaxParents);
DimensionElementComponentDelete(vDim, vParent, vElem);
vMaxParents = vMaxParents - 1;
END;
vCount = vCount + 1;
END;



Thanks
Moh
Posts: 43
Joined: Fri Aug 01, 2014 5:17 pm
OLAP Product: Cognos
Version: 10.1.1
Excel Version: 2010

Re: unwinding certain elements issue

Post by Moh »

Thank you sir
Post Reply