TM1 Question of the day

Post Reply
Wim Gielis
MVP
Posts: 3103
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:

TM1 Question of the day

Post by Wim Gielis »

Hi all,

Here's a TM1 question. I took the experiment and the answer is below, after a couple of Enter's :-)

You have a TI process with a view as the data source. You have code in both Metadata and Data tab.
In the Metadata tab you CellPutN a bunch of cells in the data source view to 0.
Will these cells be part of the cells that are treated in the Data tab ?
Or does TM1 assess the source data when going from the Prolog tab to the Metadata tab and does not do any subsequent reassessment ?

Answer: scroll down

















































































Here is the answer: TM1 does reassess the data source and it could be that less (or more) cells come by in the Data tab.
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: TM1 Question of the day

Post by gtonkin »

Thanks Wim. Have never had a need to do this but good to know.
So basically a view configured in Prolog and updated in Meta Data will be refreshed before Data. Zeroing cells in Meta Data will thus cause a further reduced view, if zero suppression is applied.

Do I understand you correctly?
Wim Gielis
MVP
Posts: 3103
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: TM1 Question of the day

Post by Wim Gielis »

Absolutely correct George.

Background: my view consists of string cells (at N and C level) with numeric input done on string elements.
If the syntax for the 'string numbers' is not valid, or for other reasons, I clear cells in the Metadata tab.
Other reasons could be: data that is to be moved to different intersections.
Then the Data tab has less / different cells to treat.

I could also Itemskip these combinations in the Data tab and not use the Metadata tab at all, that would be a little bit quicker.
But the setup is such that a limited number of string cells contains data, which will be 'exploded' to N-level numeric cells.
This is done with While...End loops in 2 dimensions over many elements, so in the Metadata tab I limit those loops using a helper dimension (while I also clear some cells with a bad syntax). Therefore, I need the Metadata tab. Otherwise I would only use the Data tab and Itemskip these cells.

I could also mirror the 2 dimensions in which numbers to be input at C level, and make all elements N-level. Let's see what works best :-)
Last edited by Wim Gielis on Mon Sep 17, 2018 12:17 pm, edited 1 time in total.
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: TM1 Question of the day

Post by macsir »

thanks, good to know.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
babytiger
Posts: 78
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: TM1 Question of the day

Post by babytiger »

That's really interesting. I wondered if this is a version specific functionality.
MK
Wim Gielis
MVP
Posts: 3103
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: TM1 Question of the day

Post by Wim Gielis »

I use Planning Analytics 2.0.5.

In my case I have a string measure (both at N and C level), not a numeric measure. That is not tested.
Also, what would happen if there are rules-calculated cells in the view, a CellPutN in the Metadata tab in a certain cell would mean that more cells should belong to the data source - going from Metadata to Data tab ? Will feeders get fired ?

Someone who has the time to play around with it ? :D
Last edited by Wim Gielis on Mon Sep 17, 2018 12:16 pm, edited 1 time in total.
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
MVP
Posts: 3103
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: TM1 Question of the day

Post by Wim Gielis »

Wim Gielis wrote: Mon Sep 17, 2018 6:45 amSomeone who has the time to play around with it ? :D
5 minutes can always be donated :lol:

A TI process with a dynamic level 0 subset as the data source, and the Metadata inserting an N-level element in the dimension,
will have the Data tab including this new element.

Anyone who can test in a different version ?
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
babytiger
Posts: 78
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: TM1 Question of the day

Post by babytiger »

Wim Gielis wrote: Mon Sep 17, 2018 7:15 am
Wim Gielis wrote: Mon Sep 17, 2018 6:45 amSomeone who has the time to play around with it ? :D
5 minutes can always be donated :lol:

A TI process with a dynamic level 0 subset as the data source, and the Metadata inserting an N-level element in the dimension,
will have the Data tab including this new element.

Anyone who can test in a different version ?
Env: 10.2.2 FP7

Test:
1. Created a new dimension (called test)with the following:
Total
...1
...2
...3
...4
...5
...6
...7
...8
...9
...10

2. created a dynamic mdx subset, with express: {TM1FilterByLevel({TM1SubsetAll([Test])},0)}
which shows elements 1 to 10.

3. created TI with the following:
Prolog:
DatasourceType = 'SUBSET';
DatasourceNameForServer = 'Test';
DatasourceSubset = 'mdx_level0';

Metadata
ASCIIOUTPUT( 'metadata_tab.txt', v1 );
sNewElem = NUMBERTOSTRING(NUMBR(v1)+100);
DimensionElementInsert( 'test', '', sNewElem , 'N' );
DimensionElementComponentAdd( 'test', 'Total', sNewElem , 1);

Data
ASCIIOUTPUT( 'data_tab.txt', v1 );

Compared output files:
metadata_tab.txt ... showing elements 1 to 10.
data_tab.txt ... showing 1 to 10, plus newly added elements.

I would say it is a confirmed scenario where datasource gets reassessed before processing data tab.

I guess this scenario (where target is also source) doesn't get used often enough.

Good to know. ;)
MK
Wim Gielis
MVP
Posts: 3103
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: TM1 Question of the day

Post by Wim Gielis »

Thank you for confirming babytiger
Last edited by Wim Gielis on Mon Sep 17, 2018 12:16 pm, edited 2 times in total.
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: TM1 Question of the day

Post by Elessar »

Thanks, Wim!

For me, the fact that it is not described in documentation and we need to observe this through experiments like physicists or chemists, sounds really weird..
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
Post Reply