Opening and Saving a .Xdi within a TI Process

[declan]

In the past subsets were created manually where i work but then we moved on to using process' which use the SubsetElementInsert function in their epilog. We did this however by having an extra line for each element we wanted in the subset e.g
SubsetDeleteAllElements('Dim','Subset');

SubsetElementInsert('Dim','Subset',A,1);
SubsetElementInsert('Dim','Subset',B,2);
SubsetElementInsert('Dim','Subset',C,3);

Obviously for larger subsets this is rediculous and due to the fact that what goes in the subset was automatically updated in the metadata and then we wrote it into the epilog when we noticed a change it could be easy to miss something out.

So i have now written a small piece of code that looks at the new elements created in the metadata and puts them in order within the subset - to do this however i am required to first open the .xdi and save the dim from it so that i have a blank canvas and don't lose elements from the subset that were updated in the past.

This is also quite time consuming and a bit of an annoyance really, so is there a way anyone knows of with which i can put in the process a bit of code that causes the Dimension to update from the .xdi?

Or failing that if anyone is handy with batch files could i create a batch file to open the .xdi and save it or does that require even more steps that me doing it manually?

[Steve Rowe]

Hi declan,

If it was me I'd try and avoid using the xdi at all.
If you want to use an xdi then there are excel VBA ways of maintaining a subset (SUBDEFINE) which you might want to consider. If I was using TI to maintain subsets then I would be looking to maintain the dimension with TI too. Mixing the two different approaches may lead to more issues than you already have.

(EDIT - Subdefine only creates a private subset so may not be much use).

I have a similar issue where I want to maintain a report structure in a subset. This is how I manage it.

I have a spreadsheet with each element appearing in column A in the same order as I want them to appear in the subset.
Column B has the index number of the row (the elements order in the dimension).
Colmn C has a DBSA forumula to send the attribute value in TM1.
A numeric attribute in TM1 that holds the value in column B.
A TI process that process the dimension and adds the elements in order of the index attribute.

Code in the TI process, all in the prolog, no datasource. Note that this invovles several while loops and will take a while to run on a large dimension and to protect yourself you should run it in a dev environment until you are confident you know how it works.

Code: Select all

#Change these 3 values, no other changes to the script needed.
Dim='Name of the dimension you want the process to act on';
Sub='Name of the subset you want to create';
AttrName='Name of the attribute that holds the row index';

ixCycle=1;
ixDim=1;
mxDim= Dimsiz (Dim);
ixSub=1;
mxSub=0;
booElementFound=1;


#Create subset 
#Could use subset delete all elements here if you prefer,
#don't think it exists in the version I wrote this in.  Note that 
#this won't work if your subset is already in a view.

If (SubsetExists (Dim, Sub)=1);
    SubsetDestroy (Dim, Sub);
EndIf;
SubsetCreate( Dim, Sub);


#Loop through the dim once to find the max sub index
While ( ixDim <mxDim);
  Ele= Dimnm (Dim , ixDim);
  EleixSub=AttrN ( Dim, Ele, AttrName);
  If (EleixSub>mxSub);
       mxSub=EleixSub;
  EndIf;
  ixDim=ixDim+1;
End;

#Reset Counter
ixDim=1;

While (booElementFound=1);
#Start of loop searching for elements to populate the subset

#Start of loop thorugh the dimension looking for element with the atrribute matching the subset index.
While ( ixDim < mxDim );
  Ele= Dimnm (Dim , ixDim);
#  ASCIIOutput ('temp.cma' ,'ixDim ' | Str ( ixDim ,5 ,0) , Str ( mxDim ,5 ,0));

  EleixSub=AttrN ( Dim, Ele, AttrName);
#Compare the elements subset index value to the ixSub we are looking for
  If (EleixSub=ixSub);
#We have found the element we are looking for so add it to the subset and advance the  counter
     SubsetElementInsert(Dim, Sub, Ele, ixSub);
     ixSub=ixSub+1;
     booElementFound=1;
     ixDim=mxDim;
#     ASCIIOutput ('temp.cma' ,'ixDim ' | Str ( ixDim ,5 ,0) , Ele);
Else;
#We did not find the element so set the flag
     booElementFound=0;
EndIf;

#Reset and advance counters
ixDim=ixDim+1;


End;

#Reset and advance counters
ixDim=1;
ixCycle=1+1;

End;

[ScottW]

I have a similar issue where I want to maintain a report structure in a subset. This is how I manage it.
I have a spreadsheet with each element appearing in column A in the same order as I want them to appear in the subset.
Column B has the index number of the row (the elements order in the dimension).
Colmn C has a DBSA forumula to send the attribute value in TM1.
A numeric attribute in TM1 that holds the value in column B.
A TI process that process the dimension and adds the elements in order of the index attribute.
Code in the TI process, all in the prolog, no datasource. Note that this invovles several while loops and will take a while to run on a large dimension

Hmmm, if the subset is maintained in an excel file with one column having the element and another the index value of the element in the subset, why not save yourself the bother of sending in the attribute, the complexity of multiple while loops and long run times on large dims? Just save the Excel sheet as a CSV and use it as a data source. You could add any new elements (should there be any) on the meta data tab and build the subset on the data tab. The process would run in nanoseconds and the code would be a lot simpler.

Just my 2c

[Steve Rowe]

Also a valid approach Scott.

I was wondering as I wrote my reply why I bothered writing a TI to do this in this way....This is my justification for it but it might be that I just fancied writing a TI

This approach was intended for end-users to look after and when I set it up I did not have the time to write any VBA. With the flat file approach you have to rely on the end user producing the file in the correct format and saving it in the correct location with the right name. My feeling is this approach whilst slower to run and more complex to develop has a simplier "business process" around it, for me a reasonable trade off.

Cheers,

[ScottW]

I get your point about user maintainability.

Here's what I typically do:
- Files have naming convention SubDef_<whatever>.csv
- "Master process" uses a while loop with WildCardFileSearch on subdef*.csv and returns the file names of all csv files matching the search.
- File name passed to ExecuteProcess('SYS_CreateSubset', ...) as a parameter
- SYS_CreateSubset TI assigns the file name and path parameters as its data source
- Datasource: Cell A1 has dimension name, cell B1 has subset name. Rest of col A are element names all other cols blank
- In the TI initialise a counter, if row 1 then assign dim and subset, If subset exists delete all elements else create it
- subsequent rows add elements in the order they appear in the list

All the users need to know is
- file name starting with "SubDef"
- Dim name in A1, subset name in A2
- Elements in the order they should appear in the subset

It's pretty simple and easy to maintain. Of course it's not foolproof (nothing is), but it is close.

[declan]

Thanks for the replies but i went with a different method;
rather than reseting the dim from the .xdi sheet i added a line into the process which cause all the newly created elements from attributes to be rolled up under a new element called 'Delete'. I then have a process which will run before the initial TI and is designed solely to find all elements that have 'Delete' as a parent and then delete them - therefore refreshing the Dimension for me to start again.

[paulsimon]

Declan

Why would you want to add elements and then delete them? I think I must be missing something.

Integrating TI and an XDI is risky. If the XDI gets out of step, you could end up losing data.

If you do want to have subsets that dynamically update as new elements are added, could you use an MDX subset?

Another approach to keeping your subsets valid is to avoid deleting elements. I am guessing that you have a DimensionDeleteAllElements at the start of your dimension update? Instead of doing that consider using a process to delete all consolidation links, which then leaves both the base level and consolidated elements in place. It therefore does not remove those elements from any subsets. It also does not cause errors if the elements are referenced in rules. The dimension update then becomes a matter of adding in the latest links from your source, and adding any new elements (I link this to a zNew Elements consolidation). The only downside to this approach is that you can end up with elements that appear to be consolidations but actually have no children. However, this is easy to clean up if wanted.

Regards


Paul Simon

[ScottW]

To get back to Declan's post I agree totally with Paul.

Declan, I am quite mystified as to exactly what and WHY you are trying to do. If TI is being used to add elements to the dimension then why would you use an XDI to remove them? For that matter why would you use an XDI at all? For changing dimensions it is very risk to mix TI and XDI as the possibility of deleting elements that hold data is very real.

As I understand it you have
- the "master" for the dimension maintained in an XDI worksheet
- TI that adds new elements to the dimension and makes them children of a parent called "Delete" (why not call the consolidation "New Elements"?)
- You then open the XDI and delete the new elements?????

1/ When you say "delete" elements do you mean MOVE them to the hierarchies to which they belong? (Otherwise why create the elements if only to delete them at the first opportunity, this doesn't make sense?)
2/ This would be possible in an XDI but would require a defined region of the spreadsheet using ELCOMP formulas to fill in the new elements beneath the "Delete" consolidation and then below that presumably some formulas with conditional logic to repeat the children of "Delete" and define them as N elements. This all seems complex and convoluted.

Is there a mixup in terminology, do you mean XDI or do you mean dimension editor? Either way I would recommend either maintain the dimension with TI or maintain it manually, trying o do both creates lots of complications.

[declan]

Hi,
The reason for deleting the elements is that they are created from attributes elements have and then the elements are placed under them. Which is all well and good at first but in the event an element's attribute changes; it ends up being put under 2 contradicting parents and as such values against it will double up. So therefore before the TI is ran again to update the new elements i must delete the ones that were previously created by attributes.
And this approach was implemented partly to reduce our reliance on the .xdi but at the same time it offers a safety net so that if someone does accidently save the .xdi, the process will refresh the data to a current point in time.