Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/
True story.deepakjain2020 wrote:Hi All,
When we extract a data from a cube into a file, TI ignores consolidated elements and Zero values by default?
Regards,
Deepak Jain
Hi Jim,TI like all other software, does what you tell it do. If you're unsure about this you need to become more familiar with TI.
The following commands are set to 1 by default whenever a ViewCreate statement is executed.deepakjain2020 wrote:The file which we are going to have will exclude Consolidated values and as well as ZERO values by default, is that true?
Code: Select all
ViewExtractSkipZeroesSet ( sCubeName, sViewName, 0);Code: Select all
ViewCreate ( sCubeName, sViewName );
ViewExtractSkipCalcsSet ( sCubeName, sViewName, 1);
ViewExtractSkipRuleValuesSet ( sCubeName, sViewName, 1);
ViewExtractSkipZeroesSet ( sCubeName, sViewName, 1);Bumping an old thread here ... Are these defaults mentioned anywhere in IBM's documentation? I've been searching and haven't found anything.rmackenzie wrote:The following commands are set to 1 by default whenever a ViewCreate statement is executed.deepakjain2020 wrote:The file which we are going to have will exclude Consolidated values and as well as ZERO values by default, is that true?
- ViewExtractSkipCalcsSet
- ViewExtractSkipRuleValuesSet
- ViewExtractSkipZeroesSet
Thanks Deepak. Yeah, I saw the commands in the reference guide but I was looking for where in the documentation it states these are defaulted to on after a view create statement (I don't think it's documented). I believe everyone on the forum that says this is true and it's easily testable, as a newbie it's just aggravating IBM didn't include this in their literature.deepakjain2020 wrote:Hi Gibson,
You can find those in reference guide, which give info on how set value for functions.
Regards,
Deepak Jain
jim wood wrote:If memory serves they all default to false, so unless you specify otherwise it will include everything. Which makes sense, why would it exclude anything without you telling it to do so,
Jim.
Jim, rmachenzie is saying the opposite. Can you confirm which is true?rmackenzie wrote:The following commands are set to 1 by default whenever a ViewCreate statement is executed.deepakjain2020 wrote:The file which we are going to have will exclude Consolidated values and as well as ZERO values by default, is that true?If you want to include consolidated elements you would specifically set them to 0. E.g. to include zero values, you would code:
- ViewExtractSkipCalcsSet
- ViewExtractSkipRuleValuesSet
- ViewExtractSkipZeroesSet
For cubes that contain data for reporting, planning, analytics etc; they are usually sparse. As most of the time, no-one is interested in the zero values, the views created default to '1' for this setting. If you are concerned that it is too ambiguous, then you simply code all the settings when creating a view:Code: Select all
ViewExtractSkipZeroesSet ( sCubeName, sViewName, 0);Because they are default, you don't have to do this. But it does make the code more transparent and easier for others to follow the process.Code: Select all
ViewCreate ( sCubeName, sViewName ); ViewExtractSkipCalcsSet ( sCubeName, sViewName, 1); ViewExtractSkipRuleValuesSet ( sCubeName, sViewName, 1); ViewExtractSkipZeroesSet ( sCubeName, sViewName, 1);
Code: Select all
ViewCreate('RevenueAndStats','zTestView');So what do we see in the .vue file? (Extracts, not in this order:)I wrote:381,x
When exporting as text, whether or not to skip rule calculated values. (0=No, 1 = Yes.)
361,x
When the view is exported as text, this determines whether consolidations are skipped in the export. (0 = no, 1 = yes.)
362,x
This is zero suppression for views created through the export text dialog or the TI ViewCreate function. 0 = off, 1 = on. See also 372.
So in a raw view created with the ViewCreate function, rules values are not skipped, consolidations are skipped, zero suppression is on.TI wrote:381,0
361,1
362,1
Quite so. Here's another one that I didn't see coming either. It's been a while since I wrote the documentation that I referred to above and I thought that it might be timely to just do some double checks to make sure that it was still current.jim wood wrote:Interesting. I didn't seem that coming. So the morale of the story is, always specify them and you'll be ok.
Code: Select all
SC_CUBE = 'RevenueAndStats';
SC_VIEW1 = 'zTestViewWithoutZeores';
SC_VIEW2 = 'zTestViewWithZeores';
If ( ViewExists ( SC_CUBE, SC_VIEW1) = 1 );
ViewDestroy ( SC_CUBE, SC_VIEW1);
EndIf;
If ( ViewExists ( SC_CUBE, SC_VIEW2) = 1 );
ViewDestroy ( SC_CUBE, SC_VIEW2);
EndIf;
ViewCreate(SC_CUBE,SC_VIEW1);
ViewCreate(SC_CUBE,SC_VIEW2);
ViewExtractSkipZeroesSet ( SC_CUBE, SC_VIEW2, 0);
It would have to have been a very, very long time ago. Those notes of mine date back to 8.2.12 (updated for 9.0, 9.5 and now 10.2.2) and it was the same pattern back then. For as long as I can remember the Export as Text dialog (which uses the same .vue parameters as the ViewCreate function) skipped consolidations and zeroes, since both were most commonly used as a data source and neither of those would be needed (or wanted) in most data sources.paulsimon wrote:From memory I think Jim is right - the default used to be no skips unless you specified them. It seems that in more recent versions IBM changed the defaults say skip.
Agreed.paulsimon wrote:Anyway the best practice is to always specify all three and that way you won't be caught out by any further changes.
I'm pretty sure you're right. Back on version 6 we were still using map sheets. I recall that TI became available after we (a different "we" to now) did an upgrade to the legendary 7.1.4 version, but I can't recall if it was around before that. Back in those days TI was licenced separately (or at least down here it was) so not all v7 users would have had it, even if it was available then.paulsimon wrote:Yes it probably was a very long time ago. When Jim and I first worked together it was on version 6. Version 7 was just coming in. I don't think TI even arrived until 7.1 or something like that.
It was, hence the code number in the .vue file being somewhat removed from the other two. And of course it also explains why the TI function for skipping consolidations has a slightly misleading name; ViewExtractSkipCalcsSet. There wasn't the ability to skip rule based calculations at the time so most likely the writer of that function didn't consider that the name could be ambiguous.paulsimon wrote:Having said that I have always specified all the skip parameters so I wouldn't have really noticed if anything changed. The anomaly around Skip Rule Calcs being false while the other two are true, might be because there was originally only Skip Zeroes and Consols, and Skip Rule Calc Values was added later.
I think so; to me the defaults have always made sense. As I said either export as text or ViewCreate will typically be used as data sources, whether to feed another TM1 cube (or to copy a version) or to feed an external database. You won't typically want zeroes for that (if you're copying to another cube, ViewZeroOut is far more efficient), you won't typically want all of the consolidations (since those will reject if you try to load them anyway)... but assuming that you're making an archive copy (say) you will want the rules calculated amounts since there's no point in keeping live calculations for static data. Or, alternatively, if you're feeding to an external database there's no point in having the ETL or target database recalculate those values.paulsimon wrote:On the other hand may be someone decided that the default should be false.
Definitely the safest way; never assume.paulsimon wrote:Anyway I will continue to always specify all three.