Checking for Underfed cells

Post Reply
LiamLawrence
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

Post by LiamLawrence »

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!
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: Checking for Underfed cells

Post by David Usherwood »

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
You can almost certainly test on a subset of the content eg one year and version.
LiamLawrence
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

Post by LiamLawrence »

Thanks - that's simpler. I will give it a go when I get chance and report back on the outcome!
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

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', '' );
Wim
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
User avatar
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

Post by Ajay »

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
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

Ajay wrote: Sat Feb 02, 2019 8:38 pm 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
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', '' );
Number 100 can export 'calculated feeders to a text file and show the count of fed cells', loosely speaking (no big investigation from my side).
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
User avatar
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

Post by Ajay »

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
User avatar
Elessar
Community Contributor
Posts: 331
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

Post by Elessar »

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
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

Elessar wrote: Mon Feb 04, 2019 10:26 am@Wim, kindly ask you to post you investigation there
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;
and several variations, like using cubename only, or cubename and text file, and so on.
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
User avatar
Elessar
Community Contributor
Posts: 331
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

Post by Elessar »

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 :)
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

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.
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
User avatar
macsir
MVP
Posts: 782
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

Post by macsir »

Interesting to know DebugUtility and tried to run the following code

Code: Select all

DebugUtility( 114, 0, 0, 'myCube' , 'targetFolder' , '' );
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.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

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 ?
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
User avatar
gtonkin
MVP
Posts: 1192
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

Post by gtonkin »

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:

Code: Select all

DebugUtility( 113, 0, 0, pCube, 'pPath\cubename_underfed_cells.txt' , '[element][, element][,…]' );
e.g. DebugUtility(pFunction, 0, 0, pCube, sFilename, 'Budget,Luxemburg,T Series 4.0 L Sedan');

You could also play with:

Code: Select all

DebugUtility( 100, 0, 0, pCube, 'pPath\cubename_feeders.txt', '' );
# List all calculated feeders in a text file, including the total number, for the given cube

Code: Select all

DebugUtility( 105, 0, 1, pCube, 'pPath\cubename_overfed_cells.txt' , '' );
# Create a file with the list of overfed Cells (Cells Fed but with 0 value)

Also, try DebugUtilityEx:

Code: Select all

DebugUtilityEx('FeedersFromCell','SalesCube','Budget','United States','L Series 1.6 L Convertible','Units','Jan','FeedersFromCell.txt');
Gives a lot of information on feeders and dependencies.

As with all undocumented functions, use at your own peril.
Post Reply