Page 1 of 1
Subset Element Insert
Posted: Sun Jan 27, 2019 7:35 am
by ashokjb
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
Re: Subset Element Insert
Posted: Sun Jan 27, 2019 8:32 am
by David Usherwood
You are recreating your subset in the Data tab every time you read a record. Move the destroy/create code to the Prolog.
Re: Subset Element Insert
Posted: Sun Jan 27, 2019 9:19 am
by ashokjb
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.
Re: Subset Element Insert
Posted: Sun Jan 27, 2019 1:13 pm
by Wim Gielis
Share the actual code of the process, if not we must be guessing as to what the cause is.
Re: Subset Element Insert
Posted: Sun Jan 27, 2019 1:44 pm
by ashokjb
Please find the code below;
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;
Attaching the file used during the import too.
Thanks,
Re: Subset Element Insert
Posted: Sun Jan 27, 2019 1:48 pm
by Wim Gielis
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.
Re: Subset Element Insert
Posted: Sun Jan 27, 2019 1:49 pm
by Wim Gielis
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
Re: Subset Element Insert
Posted: Sun Jan 27, 2019 2:55 pm
by ashokjb
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.
Re: Subset Element Insert
Posted: Sun Jan 27, 2019 7:38 pm
by lotsaram
ashokjb wrote: ↑Sun Jan 27, 2019 2:55 pm
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.
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.)
Re: Subset Element Insert
Posted: Sun Jan 27, 2019 9:56 pm
by Wim Gielis
My bad, I was too quick in using the Metadata tab to insert elements in the subset. Apologies.
Re: Subset Element Insert
Posted: Mon Jan 28, 2019 6:06 am
by ashokjb
lotsaram wrote: ↑Sun Jan 27, 2019 7:38 pm
ashokjb wrote: ↑Sun Jan 27, 2019 2:55 pm
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.
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.)
Hi lotsaram,
Thanks for your reply and I understand your point. I used the DimensionElementInsertDirect and it works fine now.
Thanks Wim for your assistance.