Duplicate Elements in Rollups

Large nested rollups can lead to elements being counted twice or more within a rollup.
The following process helps you to find, across all dimensions of your system, all rollups that contain elements consolidated more than once under the rollup.

The code isn't the cleanest and there are probably better methods to achieve the same result so don't hesitate to edit the page or comment.

It proceeds like this:

loop through all dimensions
  loop through all rollups of a dimension
    if(number of distinct elements in rollup <> number of elements in rollup)
      then loop through all alphanumerically sorted elements of the rollup
        duplicates are found next to each other

The process takes less than 30 sec to run on a 64bit 9.1.4 system with 212 dimensions, one of the dimensions holds more than a 1000 consolidations and some "decent" nesting (9 levels).

/!\ This process will not work with some TM1 versions because of the MDX function DISTINCT, it has been successfully tested on v. 9.1.4.
DISTINCT does not work properly when applied to expanded rollups on v. 9.0.3

You can just copy/paste the following code in the Prolog window of a process.
Just edit the first line to dump the results where you would like to see them.

/!\ The weight of the duplicated elements is also shown, just in case identical elements cancel each other out with opposite weights.

report = 'D:\duplicate.elements.csv'; 
#config the line above. 
#caveat: empty or consolidations-only dimensions will make this process fail, because empty subsets cannot be created 
allrollups = '}All rollups';
subset = '}compare'; 
maxDim = DimSiz('}Dimensions'); 
i = 1; 
#for each dimension 
while (i <= maxDim); 
vDim = DIMNM('}Dimensions',i); 
#test if there are any rollups 
#asciioutput(report, vDim); 
SubsetDestroy(vDim, allrollups); 
SubsetCreatebyMDX(allrollups,'{TM1FILTERBYLEVEL( {TM1SUBSETALL([' | vDim | '])},0)}'); 
If(SubsetGetSize(vDim,allrollups) <> DimSiz(vDim)); 
#get a list of all rollups SubsetDestroy(vDim, allrollups); 
SubsetCreatebyMDX(allrollups,'{EXCEPT(TM1SUBSETALL([' | vDim | ']), TM1FILTERBYLEVEL( {TM1SUBSETALL([' | vDim | '])},0))}'); 
maxRolls = SubsetGetSize(vDim,allrollups); 
rollup = 1; 
#for each rollup in that dimension while (rollup <= maxRolls);
Consolidation = SubsetGetElementName(vDim, allrollups, rollup); 
If(SubsetExists(vDim, subset) = 1); 
SubsetDestroy(vDim, subset); 
Endif; 
SubsetCreatebyMDX(subset, '{DISTINCT(TM1DRILLDOWNMEMBER( {[' | vDim | '].[' | Consolidation | ']}, ALL, RECURSIVE))}' ); 
Distinct = SubsetGetSize(vDim, subset); 
SubsetDestroy(vDim, subset); 
SubsetCreatebyMDX(subset, '{TM1DRILLDOWNMEMBER( {[' | vDim | '].[' | Consolidation | ']}, ALL, RECURSIVE)}' ); 
All = SubsetGetSize(vDim, subset); 
if(All <> Distinct); 
SubsetDestroy(vDim, subset); 
#alphasort consolidation elements so duplicates are next to eachother and a 1-pass is enough to find them all 
SubsetCreatebyMDX(subset, '{TM1SORT( {TM1DRILLDOWNMEMBER( {[' | vDim | '].[' | Consolidation | ']}, ALL, RECURSIVE)},ASC) }' ); 
maxElem = SubsetGetSize(vDim, subset); 
prevE = ''; 
nelem = 1; 
while(nelem <= maxElem); 
element = SubsetGetElementName(vDim, subset, nelem); 
#if 2+ identical elements next to each other then these are the duplicates If(prevE @= element); AsciiOutput(report,'',vDim,Consolidation,element,NumberToString(ELWEIGHT(vDim,Consolidation,element))); 
Endif; 
prevE = element; 
nelem = nelem + 1; 
end; 
Endif; 
rollup = rollup + 1; 
end; 
SubsetDestroy(vDim, allrollups); 
If(SubsetExists(vDim, subset) = 1); 
SubsetDestroy(vDim, subset);
Endif; 
Endif; 
i = i + 1; 
end;