Hierarchy sort and Hierarchy MDX

[Wim Gielis]

Sorry, Wim, I see now what you're wanting. Say, given "Coy", you want to see the hierarchies Coy is a member of represented fully from the top to Coy. Hmmmm, tricky...

Yes, the final end result being that one must be able to recreate any given element and all ancestors / unique paths after a dimension unwind. Yes it's tricky but I think I'm there. More testing needed though.

[PavoGa]

Wim,

I think I've got it. Here is the MDX code that returns a subset wherever the members appear. MDX:

Code: Select all

INTERSECT(	descendants( [tmp_ty].[total customers] )
	, [tmp_ty].[SubCoy]
	, all
	)

[Wim Gielis]

Thank you Ty, I will have a look at it tomorrow.
In fact I found a solution which is pretty easy. Loop over the dimension and use Elisanc to see if we have an ancestor. Collect all these in a subset. Then do a double loop over the subset elements - see if each pair of elements has a parent-child relation, or child-parent (the other sense). Store the results in a temporary dimension such that unique combinations remain. If wanted add the weight of the child too.
Last step is a simple TI process to do the import of the different combinations in the temporary dimension.
I will add my code soon.

[Wim Gielis]

Here is the full coding:

- export process (Prolog tab code only, no Data tab)
- import process (Prolog tab and Data tab, data source is a subset)

Code: Select all

vDim = 'Fct_Customer';

and

Code: Select all

SubsetCreateByMDX( 'ElementsForHierarchy', '{Filter( {TM1FilterByLevel( {TM1SubsetAll( [' | vDim | '] )}, 0 )}, [' | vDim | '].[New Market] = "Y" )}', 1 );

are the only references to my customer case, please change them accordingly.

The processes will work in TM1 10.2.2 FP4 or higher because of the use of temporary subsets. Change this if needed.
But the processes will also need TM1 10.2.2 FP1 or higher because of the use of the function SubsetMDXSet. Again, change this if needed.

Enjoy and thank you to those who responded !

Wim

Code: Select all

#####
# Wim Gielis
# http://www.wimgielis.be
#####

vDim = 'Fct_Customer';
vDim_HierarchyConcat = vDim | '_hierarchy_rebuilt';


DataSourceAsciiQuoteCharacter = '';


# Temporary subsets to loop through
SubsetCreateByMDX( 'ElementsForHierarchy', '{Filter( {TM1FilterByLevel( {TM1SubsetAll( [' | vDim | '] )}, 0 )}, [' | vDim | '].[New Market] = "Y" )}', 1 );
If( SubsetMDXSet( vDim, 'ElementsForHierarchy', '' ) = 0 );
ProcessQuit;
EndIf;

SubsetCreateByMDX( 'Consolidated_elements', '{ Except( {TM1SubsetAll( [' | vDim | '] )}, { TM1FilterByLevel( {TM1SubsetAll( [' | vDim | '] )}, 0)} )}', 1 );
If( SubsetMDXSet( vDim, 'Consolidated_elements', '' ) = 0 );
ProcessQuit;
EndIf;


SubsetCreate( vDim, 'Ancestor_elements', 1 );

DimensionDestroy( vDim_HierarchyConcat );
DimensionCreate( vDim_HierarchyConcat );

AttrInsert( vDim_HierarchyConcat , '', 'Parent', 'S' );
AttrInsert( vDim_HierarchyConcat , '', 'Child', 'S' );
AttrInsert( vDim_HierarchyConcat , '', 'Child element type', 'S' );
AttrInsert( vDim_HierarchyConcat , '', 'Weight', 'N' );


# loop over all subset elements
e = 1;
While( e <= SubsetGetSize( vDim, 'ElementsForHierarchy' ));

   vElement = SubsetGetElementName( vDim, 'ElementsForHierarchy', e );

   If( Dtype( vDim, vElement ) @= 'S' );

      DimensionElementInsertDirect( vDim_HierarchyConcat, '', vElement, 'N' );

      AttrPutS( vElement, vDim_HierarchyConcat, vElement, 'Child' );
      AttrPutS( 'S', vDim_HierarchyConcat, vElement, 'Child element type' );

   Else;

      SubsetDeleteAllElements( vDim, 'Ancestor_elements' );

      # loop over all consolidated elements
      # we will fill a new subset containing all consolidated elements that the given element has as ancestors
      con = 1;
      While( con <= SubsetGetSize( vDim, 'Consolidated_elements' ));

         vConso_Element = SubsetGetElementName( vDim, 'Consolidated_elements', con );
         If( ElisAnc( vDim, vConso_Element, vElement ) > 0 );
            SubsetElementInsert( vDim, 'Ancestor_elements', vConso_Element, 0 );
         EndIf;

         con = con + 1;

      End;

      # do we have ancestors of the given element ?
      If( SubsetGetSize( vDim, 'Ancestor_elements' ) > 0 );

         SubsetElementInsert( vDim, 'Ancestor_elements', vElement, 0 );

            # store the results of the subsets in a dimension

            # loop over the ancestors
            anc_cus = 1;
            While( anc_cus <= SubsetGetSize( vDim, 'Ancestor_elements' ));

               vAncestor_Element = SubsetGetElementName( vDim, 'Ancestor_elements', anc_cus );

               # loop over the hierarchized parents and if needed, insert the ancestor at the correct spot
               anc_cus_2 = 1;
               While( anc_cus_2 <= SubsetGetSize( vDim, 'Ancestor_elements' ));

                  vAncestor_Element_2 = SubsetGetElementName( vDim, 'Ancestor_elements', anc_cus_2 );
                  vParentChildRelationExists = 0;

                  If( ElisComp( vDim, vAncestor_Element_2, vAncestor_Element ) > 0 );

                     vParent = vAncestor_Element;
                     vChild = vAncestor_Element_2;
                     vParentChildRelationExists = 1;

                  ElseIf( ElisComp( vDim, vAncestor_Element, vAncestor_Element_2 ) > 0 );

                     vParent = vAncestor_Element_2;
                     vChild = vAncestor_Element;
                     vParentChildRelationExists = 1;

                  EndIf;

                  If( vParentChildRelationExists = 1 );

                     vElement_Concat = '_PARENT_' | vParent | '_CHILD_' | vChild;
                     DimensionElementInsertDirect( vDim_HierarchyConcat, '', vElement_Concat, 'N' );

                     AttrPutS( vParent, vDim_HierarchyConcat, vElement_Concat, 'Parent' );
                     AttrPutS( vChild, vDim_HierarchyConcat, vElement_Concat, 'Child' );
                     AttrPutS( Dtype( vDim, vChild ), vDim_HierarchyConcat, vElement_Concat, 'Child element type' );
                     AttrPutN( Elweight( vDim, vParent, vChild ), vDim_HierarchyConcat, vElement_Concat, 'Weight' );

                  EndIf;

                  anc_cus_2 = anc_cus_2 + 1;

               End;

               anc_cus = anc_cus + 1;

            End;

      EndIf;

   EndIf;

   e = e + 1;

End;

DimensionUpdateDirect( vDim_HierarchyConcat );

Code: Select all

#####
# Wim Gielis
# http://www.wimgielis.be
#####

vDim = 'Fct_Customer';
vDim_HierarchyConcat = vDim | '_hierarchy_rebuilt';

If( DimensionExists( vDim ) = 0 );
ProcessError;
EndIf;

If( DimensionExists( vDim_HierarchyConcat ) = 0 );
ProcessError;
EndIf;

# A dynamic data source
vSubset = 'tmp_datasource';

SubsetCreateByMDX( vSubset, '{Hierarchize( {TM1SubsetAll( [ ' | vDim_HierarchyConcat | '] )} )}' );

If( SubsetExists( vDim_HierarchyConcat, vSubset ) > 0 );
   SubsetMDXSet( vDim_HierarchyConcat, vSubset, '' );
   DataSourceType = 'SUBSET';
   DatasourceNameForServer = vDim_HierarchyConcat;
   DatasourceDimensionSubset = vSubset;
Else;
   DataSourceType = 'NULL';
   ProcessError;
EndIf;

Code: Select all

vParent = AttrS( vDim_HierarchyConcat, vElement, 'Parent' );
vChild = AttrS( vDim_HierarchyConcat, vElement, 'Child' );
vChildElementType = AttrS( vDim_HierarchyConcat, vElement, 'Child element type' );
vWeight = AttrN( vDim_HierarchyConcat, vElement, 'Weight' );

If( vChildElementType @= 'S' );

   If( Dimix( vDim, vChild ) = 0 );
      DimensionElementInsert( vDim, '', vChild, 'S' );
   EndIf;

Else;

   If( Dimix( vDim, vParent ) = 0 );
      DimensionElementInsert( vDim, '', vParent, 'N' );
   EndIf;

   If( Dimix( vDim, vChild ) = 0 );
      DimensionElementInsert( vDim, '', vChild, vChildElementType );
   EndIf;

   DimensionElementComponentAdd( vDim, vParent, vChild, vWeight );

EndIf;

[BariAbdul]

Thanks Wim ,Very useful.