Another Newbie Question - DimensionSortOrder -By Hierarchy

[donap]

Thanks in advance. I appreciate any help, hints, even guesses that give me something to try and think on.
My dilemma today is we have some Dimensions that require Monthly re-run of the Create Dimension TI process.  Occasionally a the dimension will be re-organized slightly- One (or more) Leaf Level may have been moved to a different Parent. For examples - Maybe Regions - Northeast, Midwest, South, New England. The Leaf Level elements are cities.
Occasionally, for business purposes, a CITY may be from one to another region... ie, Albany may be under NORTHEAST, but then something changes and they want ALBANY to report up under NEW ENGLAND. The data in the source is all updated. When we run the Create dimension TI process -- it creates the new occurrence of Albany under New England, but it does NOT REMOVE the entry under NorthEast -- So we have the same Leaf level DUPLICATED in 2 places.  I have researched, and applying a  DimensionSortOrder AFTER.  I have found VERY LITTLE documentation that explains what the "ByHierarchy" option actually does.
ByInput - Retains the order in which elements were originally inserted into the dimension.
ByName - Sorts dimension elements by name.
ByLevel - Sorts dimension elements by level.
ByHierarchy - Sorts dimension elements by hierarchy.  -- WHAT DOES THAT MEAN ?
Thank you
Any other suggestions to prevent duplication when the SOURCE data reflects a re-org would be helpful.
According to this - there is no way to prevent it
http://www-01.ibm.com/support/docview.wss?uid=swg21440169

[AJAYC]

Hi Donap

If I am understanding your dilemma, I am not sure your issue is with the sort order of the dimension but more with the fact that the Create Dimension TI may not be unwinding the current dimension before rebuilding it. By this I mean, is it possible that the TI process is not detaching the leaf level elements from their parent nodes, prior to reconnecting them under the new relationship defined ?

So in your case, you'd want the "Albany" to be detached from "Northwest", first, before it is added to "New England". This would ensure that "Albany" exists once, and appears correctly as a child of "New England", and no longer "Northwest".

I am assuming that TI process is using a datasource to rebuild the dimension and not manually maintained ?

Regards
Ajay

[donap]

AJAYC - you are correct in this example.  Our TI process is HUGE as there are MANY Alternate Hierarchies defined to group Accounts for users based on Business needs.   The TI process (created by a consultant who is no longer here) Deletes all the non-leaf levels, then allows the rebuild to happen based on the data source. Although, he had this delete commented out for a time, then uncommented it. I am unsure if it is needed or not. Do you know a better way to detach the child, systematically ? So that, from Month to Month we wouldn't be discovering new issues?   I read somewhere that applying  DimensionSortOrder cleans up this kind of duplication, but was confused by what the parameter ByHierarchy actually does. 

At at more general level - since I am new to TM1 in general, I was also looking for a better explanation of what the ByHierarchy  actually does. IBM documentation only says :  ByHierarchy - Sorts dimension elements by hierarchy.  -- 
what behavior is actually the result of using this sort method ?? I could not find a better explanation anywhere when I tried to Google the term.

[AJAYC]

Hi Donap

Personally, I don't delete the parents in any such dimension builds. It's possible that you may have loaded string based data, such as commentary, to the parent and you'd lose that the moment the parent is deleted. Or you might have a report which uses the parent which may stop working if you deleted it eg. if you had a Children ( [YourParent]) in report studio, or similarly in a Perspectives report.

If you want to detach leaf elements from a parent you'll want to use the following function within the TI process:

DimensionElementComponentDelete( YourDimension, TheParent, TheElement);


This will not delete the child or the parent, but simply break the link between them, thus preserving the underlying data.

In terms of your "ByHierarchy" question, this has baffled me at times too, however rather simplistically, I take it to mean that it is the order of all elements and nodes from the very top hierarchy(ies) within your dimension. I don't use it myself so I don't have an example to share with you, I'm afraid.

HTH
Ajay

[donap]

AJAYC - Thank you. That Function seems very useful ! I will experiment !! And we do have Cafe and BI Report Studio reports using this dimension