HELP -Create/update Dimension by TI process-how to maintain Structure?? ??

[donap]

*** Referring to BASIC DIMENSION Structure -- NOT A SUBSET ******

Please see attachments for Image 1 and Image 2.( I couldn't figure out how to insert the image )

My business requirements :
   *** The 3 highlighted in YELLOW in Image 1 are high-level Consolidation elements.
   ***   These elements MUST ALWAYS APPEAR IN THIS SAME ORDER. 
   *** All child levels, whether "C" or "N" level elements sorted ALPHABETICALLY within the Parent.
      AS illustrated in Image 1 :
a>   All the elements indicated by the FUSHIA lines should be sorted ByName WITHIN their YELLOW parent group (one of the 3 above).

b>   All the elements indicated by the LIME GREEN brackets should be sorted ByName within their FUSHIA parent group.

c>   All the elements indicated by the AQUA BLUE brackets should be sorted ByName within their LIME GREEN parent group

d>   All the elements indicated by the RED brackets are LEAF Level Elements and should be sorted ByName within their corresponding parent group  - regardless of if the parent is FUSHIA, LIIME GREEN or AQUA Blue.

**** This Dimension load process will be run AT LEAST ONCE PER MONTH – to capture changes in the business structure – which could be a RE-ORGANIZATION – a LEAF LEVEL moving to a DIFFERENT PARENT,  or a category being deleted, or a new one being added. 

The TI process is Executed – it contains the following Code:
The Data Source is a Business table through ODBC connection...

PROLOG tab:
          DimensionSortOrder(sDim,'ByInput','ASCENDING','ByName','ASCENDING');

Metadata
DimensionElementInsert(sdim,'','GRP','c');
DimensionElementInsert(sdim,'','TOP','c');
DimensionElementInsert(sdim,'','USA','c');

Along with other logic to insert the Leaf Level Elements in the correct parent based on other fields in the input data source. 

THE RESULT of this "subsequent" run is shown in Image 2 – The left column is the initial structure when I open the dimension, the 2nd and 3rd columns are just where I had to scroll down through the dimension to find the 3 Consolidated Levels I was looking for.

How can I achieve the desired results ?

[AJAYC]

Donap

Unless you try to control every element in this dimension when added, to be the next sequential index number, then you'll not achieve this easily.

I have similar needs however, I have resolved the so called "business need" slightly differently.


I normally create a main hierarchy under which all others will sit, so in your case I will call this "Total". Now in all cases I want this to be the very first element in my dimension, so I have added to the start of my PROLOG TI the following to test if it is, and if not, make it so.



sTopNode = 'Total' ;

IF ( DIMIX ( sDim, sTopNode ) = 0 ) ;
  sFirstElement ( DIMNM ( sDim, 1 ) ;
  DimensionElementInsert ( sDim, sFirstElement, sTopNode, 'C' ) ;
ENDIF ;


Since I uncouple the dimension hierarchy during rebuilds, never deleting nodes or elements, whenever this runs, it makes sure that the top node remains the with index of "1" in the dimension.

I can then directly add the three main nodes "GRP", "TOP" and "USA" to this :


DimensionElementInsert(sDim, sTopNode, 'GRP', 'C') ;
DimensionElementInsert(sDim, sTopNode, 'TOP', 'C') ;
DimensionElementInsert(sDim, sTopNode, 'USA', 'C') ;


Make sure all of the above in on the PROLOG tab.

On the METADATA tab you can then create the hierarchy as intended by adding the parents and then their children

I add in the EPILOG some code to create a 'Default' subset, which points the user to the sTopNode above, so that to the user the dimension appears in order:


sSubset = 'Default' ;

IF ( SubsetExists ( sDim, sSubset ) = 0 ) ;
   SubsetCreate ( sDim, sSubset ) ;
   SubsetElementInsert ( sDim, sSubset, sTopNode ) ;
ELSE ;
   SubsetDeleteAllElements ( sDim, sSubset )
   SubsetElementInsert ( sDim, sSubset, sTopNode ) ;
ENDIF ;

Not quite what you wanted but hopefully an alternative way to resolve your issue.

HTH
Ajay

[donap]

Thank you AJAYC - I was thinking the same thing about using a Default Subset for presentation to the Users.

I have the code as you suggested, with the exception of the TOTAL level forcing to Index 1. Neat trick - Thank you for that.

How do you UNCOUPLE the relationships??
I need to do that also !!

[AJAYC]

Hi Donap

By uncoupling, I mean I am unwinding the hierarchy by using the following function through the dimension before updating it:

DimensionElementComponentDelete( YourDimension, TheParent, TheElement);

This ensures you rebuild a new hierarchy each time.

Ajay

[donap]

Awesome !
So - in the PROLOG Tab of the TI process, you loop through the Dimension applying the
     DimensionElementComponentDelete( YourDimension, TheParent, TheElement);

Command to every element ?   

Thank you

[AJAYC]

Not quite....almost though  ;)

I have a TI process which is designed to simply unwind hierarchies, all I do I pass the name of the dimension to it, and it unwinds it. This process needs a datasource, which is simply the 'All' subset of the dimension passed. Since it has a datasource, I then unwind in the METADATA of that process.


You'll want to call this process that unwinds dimensions in the PROLOG of your dimension update process.

HTH
Ajay

[donap]

Thanks !