TM1 Question of the day

Post Reply
Wim Gielis
MVP
Posts: 1785
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

TM1 Question of the day

Post by Wim Gielis » Sun Sep 16, 2018 10:41 am

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

User avatar
gtonkin
MVP
Posts: 616
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: TM1 Question of the day

Post by gtonkin » Sun Sep 16, 2018 11:36 am

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: 1785
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: TM1 Question of the day

Post by Wim Gielis » Sun Sep 16, 2018 5:38 pm

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

User avatar
macsir
Community Contributor
Posts: 534
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: TM1 Question of the day

Post by macsir » Sun Sep 16, 2018 9:13 pm

thanks, good to know.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

babytiger
Posts: 67
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 » Sun Sep 16, 2018 11:40 pm

That's really interesting. I wondered if this is a version specific functionality.
MK

Wim Gielis
MVP
Posts: 1785
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: TM1 Question of the day

Post by Wim Gielis » Mon Sep 17, 2018 6:45 am

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Wim Gielis
MVP
Posts: 1785
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: TM1 Question of the day

Post by Wim Gielis » Mon Sep 17, 2018 7:15 am

Wim Gielis wrote:
Mon Sep 17, 2018 6:45 am
Someone 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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

babytiger
Posts: 67
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 » Mon Sep 17, 2018 7:49 am

Wim Gielis wrote:
Mon Sep 17, 2018 7:15 am
Wim Gielis wrote:
Mon Sep 17, 2018 6:45 am
Someone 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: 1785
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: TM1 Question of the day

Post by Wim Gielis » Mon Sep 17, 2018 8:12 am

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

User avatar
Elessar
Posts: 143
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010
Location: Russia

Re: TM1 Question of the day

Post by Elessar » Mon Sep 17, 2018 8:26 am

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..
Sorry for my English ;)

Post Reply