Keep Dimension Elements as part of TI Process

[tstagliano]

WE have a client dimension with 2 hierarchies. One hierarchy is called Total Clients and the level below that would be each of the parent codes for our client listing. Under each parent could be 1 or a handful of local client codes. Also under this Total Clients level, we would have an element called Unassigned Clients which would store client codes that are not available as part of a data load process.

What we are trying to create is an update process to the client dimension so that when we move clients around to different parents in a master CSV file, we will run the Update process to recreate the dimension, we want to keep the Unassigned Clients level in place. Right now when we run the process that will recreate the dimension, that level disappears as it is not included in the CSV master file.

Any thoughts

Thanks

Tom

[TrevorGoss]

Hello,

Right now when we run the process that will recreate the dimension, that level disappears as it is not included in the CSV master file.

How, exactly is the update process recreating the dimension? Is it possible for you to post some code?

It should be fairly straight forward, getting the level of the dimension before any changes. It all depends on how you have written your update process.

If you are concerned with the level of an element within a dimension, you can use the ELLEV function.

[tstagliano]

Trevor, We did not specifuically write any code for this process.. I just used the tabs within the TI Process Creator. Below is the some code on the various tabs under Advanced that was auto generated.

ProLog
#****Begin: Generated Statements***
DIMENSIONDELETEALLELEMENTS('TestClient');
DIMENSIONSORTORDER('TestClient','BYNAME','ASCENDING','BYHIERARCHY','ASCENDING');
#****End: Generated Statements****

Metadata

#****Begin: Generated Statements***
DIMENSIONELEMENTINSERT('TestClient','',ClientCode,'n');
DIMENSIONELEMENTINSERT('TestClient','',HGParentCode,'c');
DIMENSIONELEMENTINSERT('TestClient','',Unit2,'c');
DIMENSIONELEMENTINSERT('TestClient','',Level1,'c');
DIMENSIONELEMENTINSERT('TestClient','',Level2,'c');
DIMENSIONELEMENTCOMPONENTADD('TestClient',HGParentCode,ClientCode,2.000000);
DIMENSIONELEMENTCOMPONENTADD('TestClient',Unit2,ClientCode,2.000000);
DIMENSIONELEMENTCOMPONENTADD('TestClient',Level1,HGParentCode,1.000000);
DIMENSIONELEMENTCOMPONENTADD('TestClient',Level2,Unit2,1.000000);
#****End: Generated Statements****

Data

#****Begin: Generated Statements***
ATTRPUTS(ClientDesc,'TestClient',ClientCode,'Client Name');
ATTRPUTN(IndustryCode,'TestClient',ClientCode,'Industry');
ATTRPUTN(Unit,'TestClient',ClientCode,'Unit');
ATTRPUTS(Currency,'TestClient',ClientCode,'Currency');
ATTRPUTN(KFParent,'TestClient',ClientCode,'KF Ultimate Parent');
ATTRPUTS(UltimateParent,'TestClient',HGParentCode,'Ultimate Parent');
#****End: Generated Statements****

[TrevorGoss]

Ahhhh fair enough, you used the wizard to create your code.

You could still hardcode the Unassigned Clients element into the dimension, just write code to move it out of its current place and into the place you want it to go to.

[tstagliano]

would you happen to have some guidance on how to structure that code to keep the Unassigned Clients element in the dimension.

[sachin]

What we are trying to create is an update process to the client dimension so that when we move clients around to different parents in a master CSV file, we will run the Update process to recreate the dimension, we want to keep the Unassigned Clients level in place. Right now when we run the process that will recreate the dimension, that level disappears as it is not included in the CSV master file.

Tom

You are performing DimensionDeleteAllElements in prolog of your code, this will get rid of ALL the elements including the Unassigned Clients.

I am assuming you want to retain "Unassigned Clients" and its children as well. If they are already part of the master csv then ignore rest of my comment. Take a look at the Dimension Hierarchy Unwind process in bed rock site. You will need to perform few steps in the TI:

1. Unwind the hierarchy of "Total Clients" in prolog
2. Do your regular dim insert and comp add in meta, populate your attriibutes in data tab
3. At the end you will need to call a separate process to loop through all the leaf elements in dimension and check if they have a parent. If there is no parent, then add them to Unassigned Client hierarchy. This is required, since there is a miniscule possibility in one of those bad hair days (depending on your data in the file), when a client's parent is omitted in the master csv.

[Wim Gielis]

Regarding DIMENSIONDELETEALLELEMENTS, please avoid that function...

[TrevorGoss]

Regarding DIMENSIONDELETEALLELEMENTS, please avoid that function...

If you do not mind, can you explain why you wish people to avoid this function?

As it is in the auto-generated code, the wizard provides that function, so am I reasonable in thinking that the IBM developer of TIs thinks its ok to use?

Thanks.

[Edward Stuart]

There is a risk to data using this function, should the TI not complete or be interrupted then there is the potential that some/ all of your elements are deleted and therefore your data with it.

There are plenty of posts within the forum around this

[Wim Gielis]

Indeed. If that function is in the Prolog of a TI process and for one reason or another, the TI process gets interrupted before it reaches the Metadata tab:
you delete all elements, hence all data in all cubes that use this dimension.
Restoring a backup is your way out then, with most probably data loss as a consequence.

There could be cases when you use the function but then for instance, a temporary dimension that is populated during a process and needs to be cleared when the next run of the process takes off.

The TI wizard is pretty simplistic and not to be used whenever your TM1 model is more than a POC let's say.