Subset Element Insert
-
- Posts: 8
- Joined: Tue May 26, 2015 11:14 am
- OLAP Product: TM1, PAL
- Version: 9.5, 10.2.2, 2.0.1
- Excel Version: 2010 2013
Subset Element Insert
Hi All,
I am trying to update a dimension from txt file and create a subset which will have only the newly added elements of the dimension. I have used the following code;
Metadata tab;
sDimName='Rev_Cos_GL';
vGL_Prod=GL;
IF(DIMIX(sDimName,vGL_Prod)=0);
DimensionElementInsert(SDimName,'',vGL_Prod,'n');
DimensionElementComponentAdd(SDimName,'Total',vGL_Prod,1);
DimensionSortOrder(SDimName,'ByName','Ascending','ByHierarchy','Ascending');
Endif;
------------------------------
Data Tab;
vSubsetName='Temp';
If(SubsetExists(sDimName,vSubsetName)=1);
SubsetDestroy(sDimName,vSubsetName);
Endif;
SubsetCreate(sDimName,vSubsetName);
SubsetElementInsert(sDimName,vSubsetName,vGL_Prod,1);
The issue I am facing here is, new elements are added to the dimension but the subset is showing only one element which is not a new element but an existing element in the dimension.
Am I missing anything in the code to pick only the newly added elements?
Thanks,
AJ
I am trying to update a dimension from txt file and create a subset which will have only the newly added elements of the dimension. I have used the following code;
Metadata tab;
sDimName='Rev_Cos_GL';
vGL_Prod=GL;
IF(DIMIX(sDimName,vGL_Prod)=0);
DimensionElementInsert(SDimName,'',vGL_Prod,'n');
DimensionElementComponentAdd(SDimName,'Total',vGL_Prod,1);
DimensionSortOrder(SDimName,'ByName','Ascending','ByHierarchy','Ascending');
Endif;
------------------------------
Data Tab;
vSubsetName='Temp';
If(SubsetExists(sDimName,vSubsetName)=1);
SubsetDestroy(sDimName,vSubsetName);
Endif;
SubsetCreate(sDimName,vSubsetName);
SubsetElementInsert(sDimName,vSubsetName,vGL_Prod,1);
The issue I am facing here is, new elements are added to the dimension but the subset is showing only one element which is not a new element but an existing element in the dimension.
Am I missing anything in the code to pick only the newly added elements?
Thanks,
AJ
-
- Site Admin
- Posts: 1454
- Joined: Wed May 28, 2008 9:09 am
Re: Subset Element Insert
You are recreating your subset in the Data tab every time you read a record. Move the destroy/create code to the Prolog.
-
- Posts: 8
- Joined: Tue May 26, 2015 11:14 am
- OLAP Product: TM1, PAL
- Version: 9.5, 10.2.2, 2.0.1
- Excel Version: 2010 2013
Re: Subset Element Insert
Hi David,
I tried that as well but still the same issue. I see that the ti process is only inserting the last record on the text file into the subset.
I tried that as well but still the same issue. I see that the ti process is only inserting the last record on the text file into the subset.
-
- MVP
- Posts: 3118
- 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: Subset Element Insert
Share the actual code of the process, if not we must be guessing as to what the cause is.
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: 8
- Joined: Tue May 26, 2015 11:14 am
- OLAP Product: TM1, PAL
- Version: 9.5, 10.2.2, 2.0.1
- Excel Version: 2010 2013
Re: Subset Element Insert
Please find the code below;
Attaching the file used during the import too.
Thanks,
Code: Select all
Prolog Tab;
vDimName='RevGL';
vSubsetName='Temp';
If (DimensionExists(vDimName)=0);
DimensionCreate(vDimName);
Endif;
If(SubsetExists(vDimName,vSubsetName)=1);
SubsetDestroy(vDimName,vSubsetName);
Endif;
Metadata tab;
vGL_Prod=GL;
vDimName='RevGL';
IF(DIMIX(vDimName,vGL_Prod)=0);
DimensionElementInsert(vDimName,'',vGL_Prod,'n');
DimensionElementInsert(vDimName,'','Total','c');
DimensionElementComponentAdd(vDimName,'Total',vGL_Prod,1);
DimensionSortOrder(vDimName,'ByName','Ascending','ByHierarchy','Ascending');
Endif;
Data Tab;
If(SubsetExists(vDimName,vSubsetName)=0);
SubsetCreate(vDimName,vSubsetName);
SubsetElementInsert(vDimName,vSubsetName,vGL_Prod,1);
Endif;
Thanks,
- Attachments
-
- ERP_GL.csv
- (270.73 KiB) Downloaded 205 times
-
- MVP
- Posts: 3118
- 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: Subset Element Insert
You write your SubsetElementInsert within the test whether the subset exists or not.
The element is only added to the subset if the subset does not exist yet.
But you create the subset (as an empty subset) in the Prolog tab.
The element is only added to the subset if the subset does not exist yet.
But you create the subset (as an empty subset) in the Prolog 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
-
- MVP
- Posts: 3118
- 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: Subset Element Insert
Here is the full code, plese inspect it and learn (read) about what each of the tabs of a TI process does:
Code: Select all
# Prolog Tab;
vDimName='RevGL';
vSubsetName='Temp';
If (DimensionExists(vDimName)=0);
DimensionCreate(vDimName);
DimensionSortOrder(vDimName,'ByName','Ascending','ByHierarchy','Ascending');
Endif;
SubsetDestroy(vDimName,vSubsetName);
# Metadata tab;
vGL_Prod=GL;
IF(DIMIX(vDimName,vGL_Prod)=0);
DimensionElementInsert(vDimName,'','Total','C');
DimensionElementInsert(vDimName,'',vGL_Prod,'N');
DimensionElementComponentAdd(vDimName,'Total',vGL_Prod,1);
SubsetElementInsert(vDimName,vSubsetName,vGL_Prod,1);
Endif;
# Data Tab;
# No code
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: 8
- Joined: Tue May 26, 2015 11:14 am
- OLAP Product: TM1, PAL
- Version: 9.5, 10.2.2, 2.0.1
- Excel Version: 2010 2013
Re: Subset Element Insert
Hi wim,
thanks for the code,
I have tried this as well before but got an error saying "Dimension element not found in dimension". I had to move the subsetElementInsert to the data tab coz of the error and it works only in the data tab. I also created a empty subset in the prolog tab but when I do this, the subset is created with the same element duplicated for each record processed (Attached screenshot). Once I move the subset create to the data tab, I am getting only one record and that too the last record of the file. I am unable to understand this behavior, I understand the subset creation should be in the prolog tab but why does it insert the same element for each record processed.
thanks for the code,
I have tried this as well before but got an error saying "Dimension element not found in dimension". I had to move the subsetElementInsert to the data tab coz of the error and it works only in the data tab. I also created a empty subset in the prolog tab but when I do this, the subset is created with the same element duplicated for each record processed (Attached screenshot). Once I move the subset create to the data tab, I am getting only one record and that too the last record of the file. I am unable to understand this behavior, I understand the subset creation should be in the prolog tab but why does it insert the same element for each record processed.
- Attachments
-
- Scenario1.JPG (41 KiB) Viewed 5827 times
-
- MVP
- Posts: 3654
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Subset Element Insert
This is expected. You need to lean what happens on each tab of a TI process. In particular all metadata changes are done to a "shadow copy" of the dimension and the dimension is compiled only at the end of metadata. Therefore if an element is new to the dimension then you can't add it to a subset until the Data tab as new elements don't yet exist in the dimension until AFTER the metadata commit has happened. (The DimensionElementInsert & DimensionElementComponentAdd functions act on the shadow copy, the SubsetElementInsert acts on the original copy of the dimension.)
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- MVP
- Posts: 3118
- 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: Subset Element Insert
My bad, I was too quick in using the Metadata tab to insert elements in the subset. Apologies.
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: 8
- Joined: Tue May 26, 2015 11:14 am
- OLAP Product: TM1, PAL
- Version: 9.5, 10.2.2, 2.0.1
- Excel Version: 2010 2013
Re: Subset Element Insert
Hi lotsaram,lotsaram wrote: ↑Sun Jan 27, 2019 7:38 pmThis is expected. You need to lean what happens on each tab of a TI process. In particular all metadata changes are done to a "shadow copy" of the dimension and the dimension is compiled only at the end of metadata. Therefore if an element is new to the dimension then you can't add it to a subset until the Data tab as new elements don't yet exist in the dimension until AFTER the metadata commit has happened. (The DimensionElementInsert & DimensionElementComponentAdd functions act on the shadow copy, the SubsetElementInsert acts on the original copy of the dimension.)
Thanks for your reply and I understand your point. I used the DimensionElementInsertDirect and it works fine now.
Thanks Wim for your assistance.