unwinding certain elements issue

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

unwinding certain elements issue

Post by Analytics123 » Fri Dec 01, 2017 2:04 am

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: 532
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: unwinding certain elements issue

Post by gtonkin » Fri Dec 01, 2017 5:18 am

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: 407
Joined: Sat Mar 10, 2012 1:03 pm
OLAP Product: TM1,Cognos
Version: 9.5.2
Excel Version: 2007

Re: unwinding certain elements issue

Post by BariAbdul » Fri Dec 01, 2017 12:29 pm

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: 86
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 » Fri Dec 01, 2017 3:03 pm

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: 86
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 » Fri Dec 01, 2017 6:56 pm

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

Thanks,

Moh
Posts: 39
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 » Sat Dec 02, 2017 6:03 am

Sir Pls share your code,it going to help me and others

Moh
Posts: 39
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 » Wed Dec 06, 2017 2:51 am

Analytics123 please share code

orlando
Posts: 6
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: unwinding certain elements issue

Post by orlando » Wed Dec 06, 2017 8:55 am

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: 39
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 » Wed Dec 06, 2017 3:06 pm

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: 86
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 » Wed Dec 06, 2017 6:20 pm

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

Post Reply