Double counting elements in a dimension

Ideas and tips for enhancing your TM1 application
Post Reply
Wim Gielis
MVP
Posts: 1569
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Double counting elements in a dimension

Post by Wim Gielis » Sat May 20, 2017 2:59 pm

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
Attachments
TECH_double counting I.pro
(38.68 KiB) Downloaded 49 times
TECH_double counting II.pro
(5.96 KiB) Downloaded 38 times
Last edited by Wim Gielis on Sun May 28, 2017 7:31 pm, edited 13 times in total.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 104 TM1 articles and a lot of custom code
Newest blog article: Delete a cube and its dimensions

Duncan P
MVP
Posts: 584
Joined: Wed Aug 17, 2011 1:19 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2
Excel Version: 2003 2007
Location: York, UK

Re: Double counting elements in a dimension

Post by Duncan P » Sat May 20, 2017 8:46 pm

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.

Wim Gielis
MVP
Posts: 1569
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Double counting elements in a dimension

Post by Wim Gielis » Sat May 20, 2017 9:59 pm

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 104 TM1 articles and a lot of custom code
Newest blog article: Delete a cube and its dimensions

User avatar
Alan Kirk
Site Admin
Posts: 5731
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: Double counting elements in a dimension

Post by Alan Kirk » Sat May 20, 2017 10:28 pm

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.
"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.

Wim Gielis
MVP
Posts: 1569
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Double counting elements in a dimension

Post by Wim Gielis » Sat May 20, 2017 11:28 pm

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.
Last edited by Wim Gielis on Thu Jun 08, 2017 10:55 pm, edited 1 time in total.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 104 TM1 articles and a lot of custom code
Newest blog article: Delete a cube and its dimensions

Wim Gielis
MVP
Posts: 1569
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Double counting elements in a dimension

Post by Wim Gielis » Thu May 25, 2017 2:49 pm

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 104 TM1 articles and a lot of custom code
Newest blog article: Delete a cube and its dimensions

Post Reply