TM1 Question of the day
-
- MVP
- Posts: 3128
- 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
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.
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
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
- gtonkin
- MVP
- Posts: 1211
- 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
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?
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?
-
- MVP
- Posts: 3128
- 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
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
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
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
- macsir
- MVP
- Posts: 785
- 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
thanks, good to know.
-
- 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
That's really interesting. I wondered if this is a version specific functionality.
MK
-
- MVP
- Posts: 3128
- 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
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 ?
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 ?
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
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
-
- MVP
- Posts: 3128
- 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
5 minutes can always be donated
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
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
-
- 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
Env: 10.2.2 FP7Wim Gielis wrote: ↑Mon Sep 17, 2018 7:15 am5 minutes can always be donated
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 ?
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
-
- MVP
- Posts: 3128
- 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
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
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
- Elessar
- Community Contributor
- Posts: 355
- 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
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..
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..