Page 1 of 1

Double counting elements in a dimension

Posted: Sat May 20, 2017 2:59 pm
by Wim Gielis
Hello all,

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

Re: Double counting elements in a dimension

Posted: Sat May 20, 2017 8:46 pm
by Duncan P
Wim, you've made my day. There's someone else out there that puts the comma at the beginning of each parameter line instead of the end.

Re: Double counting elements in a dimension

Posted: Sat May 20, 2017 9:59 pm
by Wim Gielis
That's only very recently, in the past I never did that.
Double clicking the parameter value to copy or change it, is possible without a comma. That's my only reason.

Re: Double counting elements in a dimension

Posted: Sat May 20, 2017 10:28 pm
by Alan Kirk
Duncan P wrote:Wim, you've made my day. There's someone else out there that puts the comma at the beginning of each parameter line instead of the end.
Heretics.

Both of you.

Just.... heretics.

Edit: By the way Wim, as an MVP you should have posting privileges straight into Useful Code. I'll double check that when I have a moment.

Re: Double counting elements in a dimension

Posted: Sat May 20, 2017 11:28 pm
by Wim Gielis
Alan Kirk wrote:Edit: By the way Wim, as an MVP you should have posting privileges straight into Useful Code. I'll double check that when I have a moment.
You are right Alan, I can post there.
I did not know that.

Re: Double counting elements in a dimension

Posted: Thu May 25, 2017 2:49 pm
by Wim Gielis
Hello,

In the meantime, I reworked / extended the processes. They run much faster now on large dimensions with a big DNLEV.
Updated version of the 2 processes can be found in the first post of this topic.