ViewExtractSkipRuleValuesSet

User avatar
PavoGa
Community Contributor
Posts: 381
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

ViewExtractSkipRuleValuesSet

Post by PavoGa » Fri Mar 25, 2016 3:33 pm

I am needing some clarification on how this actually works because I am seeing a behavior that, while I did not expect it, actually makes sense in a way.

Here is the scenario: We have a process that copies data from one cube to a blank copy of that cube, the copy having no rules. We only want non-rule driven, non-consolidation data, so we set:

Code: Select all

ViewExtractSkipCalcsSet(cubDLBRInputTBD, vwSource, 1);
ViewExtractSkipRuleValuesSet(cubDLBRInputTBD, vwSource, 1);
ViewExtractSkipZeroesSet(cubDLBRInputTBD, vwSource, 1);
Here is the problem. We saw some values show up in the target cube after the copy that had zeros same cells in the source cube. When we turned off the rule for the source cube, lo and behold, there were the values. Now, we know that if a value was written to a cell in a cube and a rule later comes into play for that cell, that the underlying value is retained. So here is the question: does ViewExtractSkipRuleValuesSet do nothing more than turn off the rules while the process is running? What I expected was a skipping of any rule-derived cell, not just a turning off of the rules.

If it is doing nothing more than turning off the rules, then I should expect any underlying value to copy. Seems to be that is what we are seeing.
Ty
Cleveland, TN

User avatar
PavoGa
Community Contributor
Posts: 381
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: ViewExtractSkipRuleValuesSet

Post by PavoGa » Tue Apr 05, 2016 1:36 pm

Surprised this did not engender some comment. Did I miss something?
Ty
Cleveland, TN

BrianL
MVP
Posts: 264
Joined: Mon Nov 03, 2014 8:23 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2 PA2
Excel Version: 2016

Re: ViewExtractSkipRuleValuesSet

Post by BrianL » Tue Apr 05, 2016 3:26 pm

I think there's no comment because you answered your own question. You did the test to see what the actual product behavior is and you even came up with an explanation for it.

What else are you looking for? Confirmation that TM1 is supposed to behave that way? You would have to go to IBM for that confirmation not this forum.

User avatar
PavoGa
Community Contributor
Posts: 381
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: ViewExtractSkipRuleValuesSet

Post by PavoGa » Tue Apr 05, 2016 4:57 pm

Actually, yes, I was looking for confirmation this behavior was expected and/or observed by others. I did not find any other references to it in my searches on the subject and, with all due respect, there are plenty of threads on this forum dealing with observed TM1 behavior that is not documented.

If I phrased my original request and questions in an incorrect manner, please forgive me.
Ty
Cleveland, TN

User avatar
tiagoblauth
Posts: 20
Joined: Wed Jul 08, 2015 11:50 am
OLAP Product: TM1, Cognos BI
Version: 10.2.1
Excel Version: 2010

Re: ViewExtractSkipRuleValuesSet

Post by tiagoblauth » Tue Apr 05, 2016 5:33 pm

Hi, let's see if we are in the same page. When you create a Turbointegrator to copy values from one cube to another, TI reads cell by cell from the source view and follow you commands to copy the values to the target cube. This ViewExtractSkipRuleValuesSet function filters the rule cells in the source so TI will not go through it. It's a simple way to filter the source in the cube.
One easy way to test these functions is to right click a cube view and select "Export as test data...". There are some skip options that works in the same way as these options here.
If you want to create a backup cube and it doesn't have any rule, you should use:
ViewExtractSkipCalcsSet(cubDLBRInputTBD, vwSource, 1);
ViewExtractSkipRuleValuesSet(cubDLBRInputTBD, vwSource, 0);
ViewExtractSkipZeroesSet(cubDLBRInputTBD, vwSource, 1);

Good luck and please let us know if you need something else.

tomok
MVP
Posts: 2630
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: ViewExtractSkipRuleValuesSet

Post by tomok » Tue Apr 05, 2016 9:41 pm

Overwriting values that have actually been entered into a cube, either manually or TI, by writing a rule that supercedes them does not get rid of the values stored in the cube. You just never see them because the rules engine overrides them. However, if you remove the rule they will lo and behold reappear. Is this normal or expected? I have no clue. In my opinion it should not work this way but it does. It's a shame they have never documented it.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Wim Gielis
MVP
Posts: 2256
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: ViewExtractSkipRuleValuesSet

Post by Wim Gielis » Tue Apr 05, 2016 9:51 pm

Indeed, a simple and quick zero out method by writing a rule that we used to do in earlier versions of TM1, has vanished since some time.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

babytiger
Posts: 77
Joined: Wed Jul 31, 2013 4:32 am
OLAP Product: Cognos TM1, EP, Analyst
Version: 10.2.2
Excel Version: 2013
Location: Sydney AU

Re: ViewExtractSkipRuleValuesSet

Post by babytiger » Tue Apr 05, 2016 10:37 pm

I believe once you have the rules in place, ie. On top of stored values. Then perform a cubesavedata or a savedataall function, it will remove the stored values that are superseded by the rules.
MK

Andy Key
MVP
Posts: 341
Joined: Wed May 14, 2008 1:37 pm
OLAP Product: TM1 CX
Version: 2-5 to 10-2-x
Excel Version: 2003 2007 2010 2013
Location: Sydney
Contact:

Re: ViewExtractSkipRuleValuesSet

Post by Andy Key » Wed Apr 06, 2016 3:02 am

How these values will get exported will depend on your setting for RulesOverwriteCellsOnLoad in the config file.
Andy Key

User avatar
qml
MVP
Posts: 1074
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.6 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: ViewExtractSkipRuleValuesSet

Post by qml » Wed Apr 06, 2016 9:54 am

Well, as Andy says, there is a config parameter RulesOverwriteCellsOnLoad whose documentation explains the intended default behaviour and how to change it. It seems that by default, values in cells that have rules applied are not wiped out until the next server (re)start. This can be changed in such a way that the values are never wiped out, but it cannot be changed in such a way that the values are always wiped out right away after applying a rule.

I would personally welcome a new switch to make TM1 remove data for rule-derived cells on rule save, but it should definitely not be the default. Being able to remove all data in a cube with a rule mishap is too risky to make it the default behaviour.
Kamil Arendt

Bakkone
Posts: 102
Joined: Mon Oct 27, 2014 10:50 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: ViewExtractSkipRuleValuesSet

Post by Bakkone » Wed Apr 06, 2016 11:28 am

Personally I wouldn't mind.

I would love it if removal of a rule meant the last calculated value was saved as a static value. But this would then mean that any previous written value would of course have to get overwritten.

User avatar
PavoGa
Community Contributor
Posts: 381
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: ViewExtractSkipRuleValuesSet

Post by PavoGa » Wed Apr 06, 2016 3:17 pm

babytiger wrote:I believe once you have the rules in place, ie. On top of stored values. Then perform a cubesavedata or a savedataall function, it will remove the stored values that are superseded by the rules.
This is not consistent with what we have seen on this. The values never go away once they are there unless actions are taken to clear them out.
Ty
Cleveland, TN

User avatar
qml
MVP
Posts: 1074
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.6 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: ViewExtractSkipRuleValuesSet

Post by qml » Wed Apr 06, 2016 3:25 pm

PavoGa wrote:This is not consistent with what we have seen on this. The values never go away once they are there unless actions are taken to clear them out.
Does a server restart change anything?
Kamil Arendt

User avatar
PavoGa
Community Contributor
Posts: 381
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: ViewExtractSkipRuleValuesSet

Post by PavoGa » Wed Apr 06, 2016 3:38 pm

qml wrote:
PavoGa wrote:This is not consistent with what we have seen on this. The values never go away once they are there unless actions are taken to clear them out.
Does a server restart change anything?
After reading the comments and the documentation on RulesOverwriteCellsOnLoad (Thank you, Andy and qml), I discussed with the other developers, one of whom worked with IBM and Applix, and we believe we are seeing this holding on to underlying cell values on rule-driven cells even through server restarts. However, the discussion has raised a couple of questions in my mind, so we'll test a couple of scenarios and see what we get.

Thanks for all the comments. This is apparently not cut and dried as it seemed.
Ty
Cleveland, TN

User avatar
PavoGa
Community Contributor
Posts: 381
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: ViewExtractSkipRuleValuesSet

Post by PavoGa » Wed Apr 06, 2016 3:41 pm

tiagoblauth wrote:Hi, let's see if we are in the same page. When you create a Turbointegrator to copy values from one cube to another, TI reads cell by cell from the source view and follow you commands to copy the values to the target cube. This ViewExtractSkipRuleValuesSet function filters the rule cells in the source so TI will not go through it. It's a simple way to filter the source in the cube.
One easy way to test these functions is to right click a cube view and select "Export as test data...". There are some skip options that works in the same way as these options here.
If you want to create a backup cube and it doesn't have any rule, you should use:
ViewExtractSkipCalcsSet(cubDLBRInputTBD, vwSource, 1);
ViewExtractSkipRuleValuesSet(cubDLBRInputTBD, vwSource, 0);
ViewExtractSkipZeroesSet(cubDLBRInputTBD, vwSource, 1);

Good luck and please let us know if you need something else.
But we are specifically, as stated, looking for non-rule driven cells only.
Ty
Cleveland, TN

BrianL
MVP
Posts: 264
Joined: Mon Nov 03, 2014 8:23 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2 PA2
Excel Version: 2016

Re: ViewExtractSkipRuleValuesSet

Post by BrianL » Wed Apr 06, 2016 3:57 pm

I would not expect previous cell data to ever get removed if TM1 thinks there is a rule statement that applies and returns 'continue' even conditionally. I'd need to test to be certain, but it should be a pretty quick and simple test to run.

User avatar
PavoGa
Community Contributor
Posts: 381
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: ViewExtractSkipRuleValuesSet

Post by PavoGa » Wed Apr 06, 2016 9:39 pm

BrianL wrote:I would not expect previous cell data to ever get removed if TM1 thinks there is a rule statement that applies and returns 'continue' even conditionally. I'd need to test to be certain, but it should be a pretty quick and simple test to run.
Yes, but we are getting it even on non-conditional rules. For whatever the reason, sometimes a decision was made to change a cell to being rule-driven. If there was a value, the value is sticking. I did do some testing today including server shutdowns and restarts to confirm.

So it seems to be confirmed that ViewExtractSkipRuleValueSet simply ignores the rules for a given view, it does not actually skip over rule driven cells. I agree with tomok, my opinion is that it should not work that way, but it does and the documentation is not really clear on the point.

Thanks again.
Ty
Cleveland, TN

tomok
MVP
Posts: 2630
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: ViewExtractSkipRuleValuesSet

Post by tomok » Wed Apr 06, 2016 10:01 pm

This really shouldn't be an issue. As a general rule you should never put rules on a previously input cell in a production environment. This should be done in development and when you promote to production the cube should be empty with the data loading in production. This propblem would have never reared its ugly head had you done it that way.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Karthik1710
Posts: 21
Joined: Fri Oct 31, 2014 3:25 am
OLAP Product: Cognos TM1
Version: 10.2
Excel Version: 2007

Re: ViewExtractSkipRuleValuesSet

Post by Karthik1710 » Fri Apr 08, 2016 5:46 pm

Curious. I just tested it out.

Had a test cube created with 2 dimensions (Dummy_Acct, Dummy_Amount).
First entered an amount of 25000 for all accounts.
Through a rule, assigned 500 to one specific account.

Using a TI, I took the cube as source and used below:
ViewExtractSkipCalcsSet
ViewExtractSkipRuleValuesSet
ViewExtractSkipZeroesSet

Put in an ASCIIOUTPUT in Data.

Result: I did not see the rule over-ridden Account in the output file.

So I think the ViewExtractSkipRuleValuesSet works the way I thought it works. It simply skips reading the cells that have rules set. Is that what you are looking for?

P.S.: I am yet to check what are the config parameters set.

User avatar
PavoGa
Community Contributor
Posts: 381
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: ViewExtractSkipRuleValuesSet

Post by PavoGa » Thu Aug 25, 2016 8:47 pm

Result: I did not see the rule over-ridden Account in the output file.
That is inconsistent with all of our testing and experience.
Ty
Cleveland, TN

Post Reply