Deleting elements without any data

[Crayola]

Hi all!

I am fairly new to TM1 so please bear with my very newbie scripting and question.

We have a cube which has not been maintained for a long time. It has accumulated a lot of of dimension elements which contains no values in any of the cube measures (i'm talking millions).

I have been researching online and it seems there are many different ways to tackle this. The approach I am attempting at the moment is to assign an attribute of "delete" to the elements I want removed and then using the element delete function. I have created a subset to assign as source and have run an ASCII output and can confirm ASCIIoutput contains items i want to delete.

I ran my TI on one of the smaller consolidations as a tester which has 10 elements (5 bad ones).However the issue is when I execute the query - it just keeps running. I can't quite figure out where I am going wrong

Source data: Dimension Subset

Prolog:

Code: Select all

vDim= cDimSKU;
SbDim=cSubSource;
If(SubsetExists(vDim,SbDim)=1);
SubsetDestroy(vDim,SbDim);
EndIf;
SubsetCreate(vDim,SbDim);
#
i=1; k=1; Nb=DimSiz(vDim);
While(i<=Nb);
Member=DimNm(vDim,i);
 If( Ellev(vDim,Member)=0 &  Elisanc(vDim,Parent',Member)=1);
SubsetElementInsert(vDim,SbDim,Member,k);
    k=k+1;
  EndIf;
i=i+1;
End;

#=== Set Data Source
 DatasourceType = 'SUBSET';
 DatasourceNameForServer = cDimSKU;
 DatasourceDimensionSubset = SbDim ;

Metadata:

Code: Select all

if(sSubstring > 0 & sTotalRev = 0 );
AttrPutS('Delete', cDimSKU, vVariable, vAttribute1, '', '');
else;
itemskip;
endif;
#
delete = AttrS(cDimSKU, vVariable, vAttribute1);
if(delete @= 'Delete' & sTotalRev =0);
AsciiOutput ( FilePath,vVariable, delete, NumberToString(sTotalRev));
DimensionElementDelete(cDimSKU, vVariable);
else;
itemskip;
endif;

Any help on this or suggestion for a better approach would be really appreciated

[ascheevel]

You don't need to use a subset as a datasource for the TI and code in metadata. Your metadata also appears to be updating an attribute and then querying the just updated attribute. Why not skip the attribute update/query entirely and simply delete the element if sTotalRev = 0 and sSubstring > 0?

I think you can accomplish what you're seeking to do with a loop in the Prolog. Your posted code looks like it might have a syntax error and some referenced variables are not defined in what you posted, but I've sketched up an example for you to use as a starting point. You'll want to review and update to fit your specific needs. My code below most likely has syntax errors, I did not compile it in a TI to test.

Code: Select all

## PROLOG

vDim= cDimSKU;

# did not see in your posted code where you defined vAttribute1
vAttribute1 = '';

# did not see in your posted code where you defined Parent
Parent = '';

Nb=DimSiz(vDim);
While(Nb > 0);
	Member=DimNm(vDim,Nb);
	IF(ELLEV(vDim, Member) = 0 & ELISANC(vDim, Parent, Member) = 1);

		# sSubstring variable in your metadata is not defined in your posted code
		sSubstring = ??
		
		# update sTotalRev with CellGetN
		sTotalRev = CellGetN(....)
		
		if(sTotalRev =0 & sSubstring > 0);
			DimensionElementDelete(vDim, Member);
		endif;
	ENDIF;

	Nb = Nb - 1;
END;

If you desire to review elements before deletion, you could write a separate process to populate a subset of delete candidates and then have another process you would run after reviewing the subset that will loop through the subset and delete the elements. Again, all can be done in Prolog of TI, no need to set Subset as TI datasource.

[Crayola]

Hi ascheevel - Thank you so much for replying. I have input the following into the prolog and didnt use any data sources. I had put in between 454 and 456 as a tester as one of the elements I want removed is index = 455 (assuming my understanding of the loop is correct).

However the code runs for about 10+ minutes just for deleting that one line. It has successfully deleted . But as there are so many more to Delete - any tips on what I can do to speed up the process?

Code: Select all

cCube = 'Sales'
sWarehouse = 'All Warehouses';
sDepartment = 'All Departments';
sYear = 'All Years';
sMeasure = 'Sales Revenue';
cDimSku = 'SKU';
vDim= cDimSKU;
parent = 'AGH';

sMKLFlag = 'LHC';

nb=456;
#Nb=DimSiz(vDim);
While(Nb > 454);
	Member=DimNm(vDim,Nb);
	IF(ELLEV(vDim, Member) = 0 & ELISANC(vDim, parent, Member) = 1);

##2 criteria for deleting. Has LHC & does not have revenue at all
		# scanning for SKUs which contain LHC another criteria for deletion
		
sSubstring = SCAN (sMKLFlag, Member);
		
		# update sTotalRev with CellGetN
		
		
sTotalRev = CellGetN(cCube, sWarehouse, sDepartment  sYear, Member, sMeasure);
#
		
		if(sTotalRev =0 & sSubstring > 0);
			DimensionElementDelete(vDim, Member);
		endif;
	ENDIF;

	Nb = Nb - 1;
END;

[Crayola]

You don't need to use a subset as a datasource for the TI and code in metadata. Your metadata also appears to be updating an attribute and then querying the just updated attribute. Why not skip the attribute update/query entirely and simply delete the element if sTotalRev = 0 and sSubstring > 0?

I think you can accomplish what you're seeking to do with a loop in the Prolog. Your posted code looks like it might have a syntax error and some referenced variables are not defined in what you posted, but I've sketched up an example for you to use as a starting point. You'll want to review and update to fit your specific needs. My code below most likely has syntax errors, I did not compile it in a TI to test.

Code: Select all

## PROLOG

vDim= cDimSKU;

# did not see in your posted code where you defined vAttribute1
vAttribute1 = '';

# did not see in your posted code where you defined Parent
Parent = '';

Nb=DimSiz(vDim);
While(Nb > 0);
	Member=DimNm(vDim,Nb);
	IF(ELLEV(vDim, Member) = 0 & ELISANC(vDim, Parent, Member) = 1);

		# sSubstring variable in your metadata is not defined in your posted code
		sSubstring = ??
		
		# update sTotalRev with CellGetN
		sTotalRev = CellGetN(....)
		
		if(sTotalRev =0 & sSubstring > 0);
			DimensionElementDelete(vDim, Member);
		endif;
	ENDIF;

	Nb = Nb - 1;
END;

If you desire to review elements before deletion, you could write a separate process to populate a subset of delete candidates and then have another process you would run after reviewing the subset that will loop through the subset and delete the elements. Again, all can be done in Prolog of TI, no need to set Subset as TI datasource.

Hi ascheevel - Thank you so much for replying. I have input the following into the prolog and didnt use any data sources. I had put in between 454 and 456 as a tester as one of the elements I want removed is index = 455 (assuming my understanding of the loop is correct).

However the code runs for about 10+ minutes just for deleting that one line. It has successfully deleted . But as there are so many more to Delete - any tips on what I can do to speed up the process?

Code: Select all

cCube = 'Sales'
sWarehouse = 'All Warehouses';
sDepartment = 'All Departments';
sYear = 'All Years';
sMeasure = 'Sales Revenue';
cDimSku = 'SKU';
vDim= cDimSKU;
parent = 'AGH';

sMKLFlag = 'LHC';

nb=456;
#Nb=DimSiz(vDim);
While(Nb > 454);
	Member=DimNm(vDim,Nb);
	IF(ELLEV(vDim, Member) = 0 & ELISANC(vDim, parent, Member) = 1);

##2 criteria for deleting. Has LHC & does not have revenue at all
		# scanning for SKUs which contain LHC another criteria for deletion
		
sSubstring = SCAN (sMKLFlag, Member);
		
		# update sTotalRev with CellGetN
		
		
sTotalRev = CellGetN(cCube, sWarehouse, sDepartment  sYear, Member, sMeasure);
#
		
		if(sTotalRev =0 & sSubstring > 0);
			DimensionElementDelete(vDim, Member);
		endif;
	ENDIF;

	Nb = Nb - 1;
END;

[ascheevel]

Nothing in metadata, data, or epilog tabs and the datasource is set to none? How long would it take to return a value in cubeview that your CellGetN for sTotalRev is querying? I've modified your code by splitting the IF statements and putting LogOutput statements at each step so you can see the timestamps, you would definitely NOT want to keep the LogOutputs in there if running against the whole dimension. Only keep for a few cycles of the loop like you have it set up now for your test.

Code: Select all

cCube = 'Sales'
sWarehouse = 'All Warehouses';
sDepartment = 'All Departments';
sYear = 'All Years';
sMeasure = 'Sales Revenue';
cDimSku = 'SKU';
vDim= cDimSKU;
parent = 'AGH';

sMKLFlag = 'LHC';

nb=456;
#Nb=DimSiz(vDim);
While(Nb > 454);
	Member=DimNm(vDim,Nb);
	LogOutput('WARN', EXPAND('evaluating %Member%'));
	IF(ELLEV(vDim, Member) = 0);
		LogOutput('WARN', EXPAND('%Member% is Level0, checking if %parent% is ancestor'));
		IF(ELISANC(vDim, parent, Member) = 1);
			LogOutput('WARN', EXPAND('%parent% is ancestor of %Member%, checking if %sMKLFlag% in name'));
			##2 criteria for deleting. Has LHC & does not have revenue at all
					# scanning for SKUs which contain LHC another criteria for deletion
					
			sSubstring = SCAN (sMKLFlag, Member);

			IF(sSubstring > 0);
				LogOutput('WARN', EXPAND('%sMKLFlag% found in %Member%, checking total revenue'));
				# update sTotalRev with CellGetN
				
				sTotalRev = CellGetN(cCube, sWarehouse, sDepartment  sYear, Member, sMeasure);
#
				LogOutput('WARN', EXPAND('done checking total revenue for %Member%'));
				if(sTotalRev =0);
					LogOutput('WARN', EXPAND('deleting %Member%'));
					DimensionElementDelete(vDim, Member);
				ENDIF;
			ELSE;
				LogOutput('WARN', EXPAND('%sMKLFLAG% not found in %Member%, skipping'));
			ENDIF;
		ELSE;
			LogOutput('WARN', EXPAND('%parent% is not ancestor of %Member%, skipping'));
		ENDIF;
	ELSE;
		LogOutput('WARN', EXPAND('%Member% is not level 0, skipping'));
	ENDIF;
	LogOutput('WARN', EXPAND('done evaluating %Member%'));
	Nb = Nb - 1;
END;

[Crayola]

Nothing in metadata, data, or epilog tabs and the datasource is set to none? How long would it take to return a value in cubeview that your CellGetN for sTotalRev is querying? I've modified your code by splitting the IF statements and putting LogOutput statements at each step so you can see the timestamps, you would definitely NOT want to keep the LogOutputs in there if running against the whole dimension. Only keep for a few cycles of the loop like you have it set up now for your test.

Yep nothing in any of the other TI tabs and datasource set to none.

Thank you for the updated code. I've input it and it takes exactly about one minute to retrieve the total revenue. Thoughts? Don't really have a baseline for deleting elements but would this be considered standard or slower than normal?

Code: Select all

48584   [4a]   WARN   2020-04-10 15:19:46.852   TM1.TILogOutput   evaluating LHC_2081357_2259
48584   [4a]   WARN   2020-04-10 15:19:46.853   TM1.TILogOutput   LHC_2081357_2259 is Level0, checking if AGH is ancestor
48584   [4a]   WARN   2020-04-10 15:19:46.853   TM1.TILogOutput   AGH is ancestor of LHC_2081357_2259, checking if LHC in name
48584   [4a]   WARN   2020-04-10 15:19:46.853   TM1.TILogOutput   LHC found in LHC_2081357_2259, checking total revenue
48584   [4a]   WARN   2020-04-10 15:20:44.394   TM1.TILogOutput   done checking total revenue for LHC_2081357_2259
48584   [4a]   WARN   2020-04-10 15:20:44.394   TM1.TILogOutput   deleting LHC_2081357_2259
48584   [4a]   WARN   2020-04-10 15:20:44.395   TM1.TILogOutput   done evaluating LHC_2081357_2259

[Wim Gielis]

Thank you for the updated code. I've input it and it takes exactly about one minute to retrieve the total revenue. Thoughts? Don't really have a baseline for deleting elements but would this be considered standard or slower than normal?

If it's a one-off exercise, why would you bother about 1 minute ? Or is 1 minute only for a small part of your dimension ?

Regarding deleting elements, you could identify them with an attribute value (AttrPutS), then create an MDX to grab the elements based on the attribute (Filter). Next steps is to make the subsets static (SubsetMDXSet) and last step is to use SubsetDeleteAllElements.

Regarding populating the attribute in the first place, there's indeed multiple ways. I would populate the attribute for all elements, then process the data and whenever there's data, take out the attribute and try to stop processing data as soon as possible - for that element.

[Crayola]

If it's a one-off exercise, why would you bother about 1 minute ? Or is 1 minute only for a small part of your dimension ?

Regarding deleting elements, you could identify them with an attribute value (AttrPutS), then create an MDX to grab the elements based on the attribute (Filter). Next steps is to make the subsets static (SubsetMDXSet) and last step is to use SubsetDeleteAllElements.

Regarding populating the attribute in the first place, there's indeed multiple ways. I would populate the attribute for all elements, then process the data and whenever there's data, take out the attribute and try to stop processing data as soon as possible - for that element.

Hi Wim - thanks for your response. unfortunately it was only for a very small part of the dimension. Will have millions of these to remove.

Thank you for the tip! I've just created a separate process to flag the items i want to remove with an attribute and then using the other query kindly provided by ascheevel I replaced the cellgetn with attrs and it is running much faster- deletion takes about one second per element rather than a minute. Will also attempt the subsetDeleteAllelements approach alongside this too. Will it remove the element from the Dimension too?

[Wim Gielis]

Will also attempt the subsetDeleteAllelements approach alongside this too. Will it remove the element from the Dimension too?

My bad. It deletes the elements from the subset, not the dimension.
A looping approach (backwards from the last subset element to the first) will be needed.
DimensionElementDeleteDirect might be more handy than DimensionElementDelete, you should test both.

[David Usherwood]

What about HierarchySubsetDeleteAllElements?
https://www.ibm.com/support/knowledgece ... ments.html
Although it is clearly related to the new (unfinished ) hierarchy functions (you can also tell because the new functions have long meaningful names compared to their predecessors) it would be worth testing whether it works on the 'real' default hierarchy.

[Wim Gielis]

What about HierarchySubsetDeleteAllElements?
https://www.ibm.com/support/knowledgece ... ments.html
Although it is clearly related to the new (unfinished ) hierarchy functions (you can also tell because the new functions have long meaningful names compared to their predecessors) it would be worth testing whether it works on the 'real' default hierarchy.

Hi David,

That will be the hierarchy counterpart of the function I proposed.
Both will be able to delete elements from a subset, rather than deleting from the underlying hierarchy or dimension.

[Wim Gielis]

I also mention my recent attempt for cooperation, which wasn't really a success:
https://www.tm1forum.com/viewtopic.php?f=3&t=15195

[David Usherwood]

What about HierarchySubsetDeleteAllElements?
https://www.ibm.com/support/knowledgece ... ments.html
Although it is clearly related to the new (unfinished ) hierarchy functions (you can also tell because the new functions have long meaningful names compared to their predecessors) it would be worth testing whether it works on the 'real' default hierarchy.

Hi David,

That will be the hierarchy counterpart of the function I proposed.
Both will be able to delete elements from a subset, rather than deleting from the underlying hierarchy or dimension.

Indeed. However I looked back through the thread and none of us actually quoted the correct TI command, which is
DimensionDeleteElements (DimensionName, Subset )
DimensionName
The name of the dimension from which you want to delete the subset of elements.
Subset
The list of elements to delete from the indicated dimension. The subset is usually temporary.

[Wim Gielis]

Indeed. However I looked back through the thread and none of us actually quoted the correct TI command, which is
DimensionDeleteElements (DimensionName, Subset )
DimensionName
The name of the dimension from which you want to delete the subset of elements.
Subset
The list of elements to delete from the indicated dimension. The subset is usually temporary.

That's it ! I knew it exists but I missed the name. Thanks.

[lotsaram]

Indeed. However I looked back through the thread and none of us actually quoted the correct TI command, which is
DimensionDeleteElements (DimensionName, Subset )
DimensionName
The name of the dimension from which you want to delete the subset of elements.
Subset
The list of elements to delete from the indicated dimension. The subset is usually temporary.

What wizardry is this? I never heard of this DimensionDeleteElements function! Anyone know since which version this one has been with us?

[Wim Gielis]

What wizardry is this? I never heard of this DimensionDeleteElements function! Anyone know since which version this one has been with us?

October 2018, PA 2.0.6

https://www.ibm.com/support/knowledgece ... ocess.html

It's the version where ViewCreateByMDX was introduced too.

[Crayola]

DimensionDeleteElements (DimensionName, Subset )

That's it ! I knew it exists but I missed the name. Thanks.

Really appreciate all the responses I am getting for this
Forgive me if I am being slow here but does this mean I can create my temporary subset in prolog with all the items with the criteria I want to delete and then execute this function as a one liner without looping through (n-1)?

[Wim Gielis]

Exactly, delete elements if they are part of the subset.

[vaonlydrb]

Hi,

Has anyone been able to get this to work successfully? I'm having issues with using this function DimensionDeleteElements (DimensionName, Subset )

I am unable to save the TI when using DimensionDeleteElements (vDim, vSubset); function. I get the below error message

================================================
Error During Process Compilation
Prcedure: Prolog
Line Number: 10
Error: Syntax error on or before:
(vDim, vSubset);
equal sign not found
================================================
I have the below code in the prolog tab

PROLOG:
------------------------------------------
vDim = 'LCE-Empl';
vSubset = 'DELETE';

SubsetCreatebyMDX (vSubset, '{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [LCE-Empl] )}, 0)}, [LCE-Empl].[KeepElements] = 0.000000)} ' );

DimensionDeleteElements (vDim, vSubset);

SubsetDestroy(vDim, vSubset);
-----------------------------------------------

I know the TI works (& tested) when I comment DimensionDeleteElements (vDim, vSubset); line.

Any idea how to fix this issue?

Thanks,
DB

[Wim Gielis]

What version do you use ? Did the version already know that newer function ?