Subset Element Insert

[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

[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.

[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.

[Wim Gielis]

Share the actual code of the process, if not we must be guessing as to what the cause is.

[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,

[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.

[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

[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.

[lotsaram]

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.)

[Wim Gielis]

My bad, I was too quick in using the Metadata tab to insert elements in the subset. Apologies.

[ashokjb]

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.