Checking for Underfed cells
-
- Posts: 10
- Joined: Wed Nov 02, 2016 8:17 am
- OLAP Product: TM1 / PA
- Version: 10.2.2 / PA Cloud
- Excel Version: 2013
Checking for Underfed cells
Hi,
Bit of a long shot this, does anyone know of a clever way to analyse a whole cube for Underfeeding?
I have used the overfeeding trick involving the cloned cube and that works really well, but I understand Underfeeding will be much harder, as the cells in question wouldn't be readable for comparison. I realise I can just reconcile to base data or use "Check Feeders" but this is painstaking and I was looking for a more rounded approach.
I thought of the following :-
Clone the cube with one extra Dim with two elements (maybe "skip" and "non skip") and no rules, write a quick TI to export all N-level rule values to the cloned cube on the "skip" layer. Then turn off skipcheck, do the same and put it on "non skip", compare the two layers. I realise this may take FOREVER on a large cube though.
Now I write this, I am considering maybe its not worth all the effort vs the risk of something materially being missed!
Bit of a long shot this, does anyone know of a clever way to analyse a whole cube for Underfeeding?
I have used the overfeeding trick involving the cloned cube and that works really well, but I understand Underfeeding will be much harder, as the cells in question wouldn't be readable for comparison. I realise I can just reconcile to base data or use "Check Feeders" but this is painstaking and I was looking for a more rounded approach.
I thought of the following :-
Clone the cube with one extra Dim with two elements (maybe "skip" and "non skip") and no rules, write a quick TI to export all N-level rule values to the cloned cube on the "skip" layer. Then turn off skipcheck, do the same and put it on "non skip", compare the two layers. I realise this may take FOREVER on a large cube though.
Now I write this, I am considering maybe its not worth all the effort vs the risk of something materially being missed!
-
- Site Admin
- Posts: 1454
- Joined: Wed May 28, 2008 9:09 am
Re: Checking for Underfed cells
You could try this - quite similar to yours but doesn't involve changing the rules.
- Create a clone cube with an extra measure dimension
Difference = Values - Fed Values - Write a TI to export N level values from your real cube to the clone cube
- Run it with zero suppress on, writing to Fed Values
- Run it with zero suppress off, writing to Values
- Review the differences
-
- Posts: 10
- Joined: Wed Nov 02, 2016 8:17 am
- OLAP Product: TM1 / PA
- Version: 10.2.2 / PA Cloud
- Excel Version: 2013
Re: Checking for Underfed cells
Thanks - that's simpler. I will give it a go when I get chance and report back on the outcome!
-
- MVP
- Posts: 3117
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Checking for Underfed cells
LiamLawrence wrote: ↑Thu Jan 24, 2019 7:39 amBit of a long shot this, does anyone know of a clever way to analyse a whole cube for Underfeeding?
Yes. Run this TI command where you enter the cubename and inspect the generated text file:
Code: Select all
DebugUtility( 113, 0, 0, 'cubename', 'underfed cells.txt', '' );
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- Ajay
- Regular Participant
- Posts: 183
- Joined: Wed May 14, 2008 8:27 am
- OLAP Product: TM1
- Version: 10.2.0, PA 2.0.9
- Excel Version: 2016
- Location: London
Re: Checking for Underfed cells
Hi Wim
Do you have, or could you point me in the direction of, the full list of codes for DebugUtility() function ?
I've searched for these for a while and can't locate them.
Cheers
Ajay
Do you have, or could you point me in the direction of, the full list of codes for DebugUtility() function ?
I've searched for these for a while and can't locate them.
Cheers
Ajay
-
- MVP
- Posts: 3117
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Checking for Underfed cells
Hi,
No I don't have such a list. Basically it's trial and error from my part.
For example, a loop from 1 to 200 or even 1000 is what I did, for various combinations of input values for the other arguments.
I saw that number 108 can export the data of a cube to a text file that you provide:
Code: Select all
DebugUtility( 108, 0, 0, 'cubename', 'test.txt', '' );
There is another one that can delete all application cubes and (respective) dimensions, except the chosen cube (and hence, its dimensions).
Number 116 seems to be not used and throws an error when used. Other numbers do not throw an error, as per my testing (numbers 1 to 1000 were tested).
It would be good if IBM staff could provide a list of at least the ones that are useful to TM1 developers / admin persons.
I have the impression that only numbers 100-135 have been used, but I can be wrong on that one.
If anyone can help discovering the numbers, please join and post your findings. It could be similar to the list of constants that are used in *.pro files.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- Ajay
- Regular Participant
- Posts: 183
- Joined: Wed May 14, 2008 8:27 am
- OLAP Product: TM1
- Version: 10.2.0, PA 2.0.9
- Excel Version: 2016
- Location: London
Re: Checking for Underfed cells
Thanks Wim
I've raised a ticket with IBM this morning for a full list.
"If" I get one back from them, I will post it on the forum.
Cheers
Ajay
I've raised a ticket with IBM this morning for a full list.
"If" I get one back from them, I will post it on the forum.
Cheers
Ajay
- Elessar
- Community Contributor
- Posts: 340
- Joined: Mon Nov 21, 2011 12:33 pm
- OLAP Product: PA 2
- Version: 2.0.9
- Excel Version: 2016
- Contact:
Re: Checking for Underfed cells
Hi,
I think DebugUtility is for IBM's internal use, and I doubt it's documented well. So they unlikely will share it with the TM1 Society.
There is a topic for collecting known codes: https://www.tm1forum.com/viewtopic.php?f=3&t=10475
@Wim, kindly ask you to post you investigation there
I think DebugUtility is for IBM's internal use, and I doubt it's documented well. So they unlikely will share it with the TM1 Society.
There is a topic for collecting known codes: https://www.tm1forum.com/viewtopic.php?f=3&t=10475
@Wim, kindly ask you to post you investigation there
Best regards, Alexander Dvoynev
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
-
- MVP
- Posts: 3117
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Checking for Underfed cells
Hello,
Sure, here's my test code for investigations:
Code: Select all
i = 0;
While( i <= 1000 );
If( i <> 116 );
AsciiOutput( 'test.txt', Expand( 'Start of %i%' ));
DebugUtility( i, 0, 0, 'TM1_' | NumberToString( i ) | '.txt', '', '' );
AsciiOutput( 'test.txt', Expand( 'End of %i%' ));
EndIf;
i = i + 1;
End;
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- Elessar
- Community Contributor
- Posts: 340
- Joined: Mon Nov 21, 2011 12:33 pm
- OLAP Product: PA 2
- Version: 2.0.9
- Excel Version: 2016
- Contact:
Re: Checking for Underfed cells
Thanks Wim,
I meant "Please post your investigation's results there: https://www.tm1forum.com/viewtopic.php?f=3&t=10475", so that all the known DebugUtility commands would be in one topic
Sorry for misleading you
I meant "Please post your investigation's results there: https://www.tm1forum.com/viewtopic.php?f=3&t=10475", so that all the known DebugUtility commands would be in one topic
Sorry for misleading you
Best regards, Alexander Dvoynev
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
-
- MVP
- Posts: 3117
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Checking for Underfed cells
Hello,
I would like to refrain from doing that, since it's all trial and error and guessing from my part. The chances of being wrong are rather high.
Let's wait for IBM to come up with a list, instead of this speculation.
Certainly when I noticed that there are commands that DELETE cubes and dimensions, it's dangerous and I don't want to ruin TM1 models because of some late-night speculation and guesswork. By the way, the SetMTQ command that was proposed in the other topic, does not work for me.
I would like to refrain from doing that, since it's all trial and error and guessing from my part. The chances of being wrong are rather high.
Let's wait for IBM to come up with a list, instead of this speculation.
Certainly when I noticed that there are commands that DELETE cubes and dimensions, it's dangerous and I don't want to ruin TM1 models because of some late-night speculation and guesswork. By the way, the SetMTQ command that was proposed in the other topic, does not work for me.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- macsir
- MVP
- Posts: 785
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: Checking for Underfed cells
Interesting to know DebugUtility and tried to run the following code
Looks like it only produce a feeder file for the cube in PAL 2.0.9.4 after the first run.
The second run works with obfuscated data.
Code: Select all
DebugUtility( 114, 0, 0, 'myCube' , 'targetFolder' , '' );
The second run works with obfuscated data.
-
- MVP
- Posts: 3117
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Checking for Underfed cells
For me it seems to work every time, generating a *.cub file.
Version 2.0.9.3
Maybe you are mixing modes 113 and 114 for DebugUtility ?
Version 2.0.9.3
Maybe you are mixing modes 113 and 114 for DebugUtility ?
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- gtonkin
- MVP
- Posts: 1199
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Checking for Underfed cells
Think Wim has called it - 114 is to output to an obfuscated .cub file, 113 should give you information on underfed cells.
Try something like:
e.g. DebugUtility(pFunction, 0, 0, pCube, sFilename, 'Budget,Luxemburg,T Series 4.0 L Sedan');
You could also play with:
# List all calculated feeders in a text file, including the total number, for the given cube
# Create a file with the list of overfed Cells (Cells Fed but with 0 value)
Also, try DebugUtilityEx:
Gives a lot of information on feeders and dependencies.
As with all undocumented functions, use at your own peril.
Try something like:
Code: Select all
DebugUtility( 113, 0, 0, pCube, 'pPath\cubename_underfed_cells.txt' , '[element][, element][,…]' );
You could also play with:
Code: Select all
DebugUtility( 100, 0, 0, pCube, 'pPath\cubename_feeders.txt', '' );
Code: Select all
DebugUtility( 105, 0, 1, pCube, 'pPath\cubename_overfed_cells.txt' , '' );
Also, try DebugUtilityEx:
Code: Select all
DebugUtilityEx('FeedersFromCell','SalesCube','Budget','United States','L Series 1.6 L Convertible','Units','Jan','FeedersFromCell.txt');
As with all undocumented functions, use at your own peril.