TI cutting off dimension elements

Post Reply
RonLat
Posts: 7
Joined: Tue May 02, 2017 7:49 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

TI cutting off dimension elements

Post by RonLat » Fri Oct 06, 2017 10:18 am

Hello,

I've got a dimension that needs to be cloned and the VPPT_* and PT_* elements need to be deleted. The remaining structure above should stay the same and the consolidated MPPT_* elements should be changed to leaf elements (see attachment Dim_Tarif_Plan.PNG). Is there a possibility to get it done in TI?
Attachments
Dim_Tarif_Plan.PNG
Dim_Tarif_Plan.PNG (31.89 KiB) Viewed 127 times

User avatar
Alan Kirk
Site Admin
Posts: 5719
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: TI cutting off dimension elements

Post by Alan Kirk » Fri Oct 06, 2017 10:54 am

RonLat wrote:
Fri Oct 06, 2017 10:18 am
Hello,

I've got a dimension that needs to be cloned and the VPPT_* and PT_* elements need to be deleted. The remaining structure above should stay the same and the consolidated MPPT_* elements should be changed to leaf elements (see attachment Dim_Tarif_Plan.PNG). Is there a possibility to get it done in TI?
Assuming that that is the entire pattern, this can be easily done:
- Use the All subset of the original dimension as your data source.
- Look in The Reference Guide for the If() block statement.
- Look in The Reference Guide for the SubSt function, which allows you to extract a certain number of characters from a variable. If the left 3 characters are 'PT_" then just itemskip() that row; don't add it to the dimension clone.
- Same if the left 5 characters are VPPT_
- However if the left 5 are MPPT_, then add it as an N element.
- If it's anything else, add it as a consolidation.

You can get the parent of each MPPT_ element by looking up its parent in the source dimension using the ElPar... functions.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

RonLat
Posts: 7
Joined: Tue May 02, 2017 7:49 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: TI cutting off dimension elements

Post by RonLat » Fri Oct 06, 2017 1:55 pm

Thank you for the hint. The current code in the metadata tab:

Code: Select all

 
vDimName = 'Tarif_Test';
vElem = Tarif;
vIndex = ELPARN(vDimName, vElem);
vParent = ELPAR(vDimName, vElem, vIndex);		  
vWeight = ELWEIGHT(vDimName, vParent, vElem);

IF (SUBST(vElem, 1, 5) @= 'VPPT_' % SUBST(vElem, 1, 3) @= 'PT_');
	itemskip;	
ELSEIF (SUBST(vElem, 1, 5) @= 'MPPT_');
	DimensionElementInsert(vDimName, '', vElem, 'N');
ELSE;
   DimensionElementInsert(vDimName, '', vElem, 'C');
   DimensionElementComponentAdd(vDimName, vParent, vElem, vWeight);
ENDIF;
 
TI creates the leaf and consolidated elements correctly. However the process ends with an error message. DimensionElementComponentAdd doesn't do the right thing. It doesn't add the leaf elements to the consolidated elements. 'Tarif' is the highest element in the structure: (see attachment Dim_Tarif_Plan_2.PNG):

Code: Select all

"Tarif",Data Source line (2) Error: MetaData procedure line (20): Consolidated Element "" not found.
"Plantarife",Data Source line (3) Error: MetaData procedure line (20): Consolidated Element "" not found.
"Plantarife_PK",Data Source line (4) Error: MetaData procedure line (20): Consolidated Element "" not found.
…
Attachments
Dim_Tarif_Plan_2.PNG
Dim_Tarif_Plan_2.PNG (6.04 KiB) Viewed 95 times

RonLat
Posts: 7
Joined: Tue May 02, 2017 7:49 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: TI cutting off dimension elements

Post by RonLat » Fri Oct 06, 2017 2:59 pm

A code modification created the additional element _EmptyParent_, but DimensionElementComponentAdd(vDimName, vParent, vElem, vWeight) still doesn't work (see Dim_Tarif_Plan_3.PNG).

Code: Select all

vDimName = 'Tarif_Test';
vElem = Tarif;
vIndex = ELPARN(vDimName, vElem);
vParent = ELPAR(vDimName, vElem, vIndex);		  
vWeight = ELWEIGHT(vDimName, vParent, vElem);


IF (SUBST(vElem, 1, 5) @= 'VPPT_' % SUBST(vElem, 1, 3) @= 'PT_');
	itemskip;	
ELSEIF (SUBST(vElem, 1, 5) @= 'MPPT_');  
	DimensionElementInsert(vDimName, '', vElem, 'N');
ELSE; 
	DimensionElementInsert(vDimName, '', vElem, 'C');
ENDIF;

if (vParent @= '');
	DimensionElementInsert(vDimName, '', '_EmptyParent_', 'N');
else;
	DimensionElementComponentAdd(vDimName, vParent, vElem, vWeight);
endif;


Another code modification shows that vParent = ELPAR(vDimName, vElem, vIndex); ist probably the problem. It is allways empty. This code doesn't even create the consolidated elements, because it never jumps into the else - part (see Dim_Tarif_Plan_4.PNG). So how do I get the parents?

Code: Select all

vDimName = 'Tarif_Test';
vElem = Tarif;
vIndex = ELPARN(vDimName, vElem);
vParent = ELPAR(vDimName, vElem, vIndex);		  
vWeight = ELWEIGHT(vDimName, vParent, vElem);


IF (SUBST(vElem, 1, 5) @= 'VPPT_' % SUBST(vElem, 1, 3) @= 'PT_');
	itemskip;	
ELSEIF (SUBST(vElem, 1, 5) @= 'MPPT_');  
	DimensionElementInsert(vDimName, '', vElem, 'N');
ENDIF;
   
if (vParent @= '');
	itemskip;

else;
	DimensionElementInsert(vDimName, '', vElem, 'C');
	DimensionElementComponentAdd(vDimName, vParent, vElem, vWeight);
endif;
Attachments
Dim_Tarif_Plan_4.PNG
Dim_Tarif_Plan_4.PNG (1.67 KiB) Viewed 88 times
Dim_Tarif_Plan_3.PNG
Dim_Tarif_Plan_3.PNG (3.86 KiB) Viewed 88 times

RonLat
Posts: 7
Joined: Tue May 02, 2017 7:49 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: TI cutting off dimension elements

Post by RonLat » Fri Oct 06, 2017 4:03 pm

Well, finaly I found the problem. I was looking for ELPARN, ELPAR, ELWEIGHT in the target dimension instead of the source dimension. Thanks a lot for the idea. It's time for the weekend :-)

The working code:

Code: Select all

vDimName = 'Tarif_Test';
vElem = Tarif;
vIndex = ELPARN('Tarif', vElem);
vParent = ELPAR('Tarif', vElem, vIndex);		  
vWeight = ELWEIGHT('Tarif', vParent, vElem);

IF (SUBST(vElem, 1, 5) @= 'VPPT_' % SUBST(vElem, 1, 3) @= 'PT_');
	itemskip;
ELSEIF (SUBST(vElem, 1, 5) @= 'MPPT_');  
	DimensionElementInsert(vDimName, '', vElem, 'N');
ELSE; 
	DimensionElementInsert(vDimName, '', vElem, 'C');
ENDIF;
   

IF (vParent @= '');
	itemskip;
ELSE;
	DimensionElementComponentAdd(vDimName, vParent, vElem, vWeight);
ENDIF;

Post Reply