This is one for the "Useful code, tips & tricks" subforum.
I created a generic TI process that investigates a dimension for double countings.
You can also investigate all dimensions of a given cube, or all dimensions in the TM1 model.
This process can provide output / change existing TM1 objects in the following way:
- by creating a text file in the TM1 logging directory, containing the double countings per dimension ( pOutput_CreateTextFile )
- by adding a subset to the dimension being investigated, containing the double countings ( pOutput_AddSubsetToOriginalDim )
- by adding a dimension property to the cube }DimensionProperties, containing the number of double countings per dimension ( pOutput_AddDimensionProperty )
- by creating and not deleting a new cube called '}TECH_Double_Countings' ( pOutput_KeepTempCube )
Element weightings can be shown in a cube
They show the effect of 1 element on another, indicating the effect of the double counting.Execute process I (only), II is automatically executed by process I.
The parameter pMode determines to what extent dimensions will be inspected:
- If pMode = 1, you inspect 1 dimension. Specify the dimension name in pName.
- If pMode = 2, you inspect all dimensions of a cube. Specify the cube name in pName.
- If pMode = 3, inspect all application dimensions and create a 'light' output. pName is ignored.
- If pMode = 4, inspect all application dimensions and create a 'full' output. pName is ignored.
- If pMode = 5, you can insert a measure in the cube }DimensionProperties where you can skip dimensions from the treatment. pName is ignored.
You need TM1 10.2.2 FP6 or higher to execute the processes.
But it's easy to change the processes and have TM1 10.2.2 FP4 as the oldest version. See my comments in the code.
Going back to older versions of TM1 will require to change the temporary subsets and views into permanent subsets and views, as well as change LogOutput to AsciiOutput (or comment out the LogOutput statements)
After having applied the code to a number of TM1 models, these are my results:
- usually the Period / Month dimension has double countings. For example, a 'YTD' consolidation groups 12 consolidated YTD elements for the periods (no double counting) but underneath the 12 YTD elements you have the level 0 periods. These roll up several times in the "YTD" element. This is okay and not a problem.
- I have seen dimensions for Cost centers, Accounts, Products, Employees and many more suffering from double countings. This can be problematic and the exact reason why I wrote these processes.
- my best advice to you use the tool is:
1. Run the tool once with "pMode = 5". The other parameters do not matter here. You create a }DimensionProperties element
2. Single out the large dimensions with a high DNLEV and run the tool with "pMode = 1" for these dimensions. Inspect the results in attributes, subsets and a text file.
3. When you are done with these big dimensions, put a 1 for these dimensions in the }DimensionProperties cube on the measure "Skip dimension for double counting"
4. Run the tool with "pMode = 3" or "pMode = 4". 4 gives a richer output than 3 but it could take somewhat longer.
Please say "Thank you" or leave a comment if you use these processes with their 600 lines of decent TM1 programming
In case of errors or issues or extensions of remarks: leave a comment as well.
Good luck !
Wim