TM1 Forum

Code: Select all

I created Cube MaxText to give a simplied example		
and entered some test data		

CUBE:	hqserv:MaxTest	
hqserv:MaxTestMeas	Value	


	Product A	Product B
All Locations	13	52
Germany	1	15
Berlin	0	4
Munich	1	11
UK	12	37
Midlands	3	5
Birmingham	2	3
Coventry	1	2
North	2	2
Edinburgh	2	0
Newcastle	0	2
South East	3	2
London	1	1
Reading	2	1
West and SW	4	28
Bristol	2	3
Cardiff	1	16
Devon	1	9
		
Note that this example has 3 levels of hierarchy, and that it is a ragged hierarchy		
with Germany have no Region level. Despite this the routine still copes.		
		
I then created a dimension called HierChild as follows:		
			
X	HierChild		

N	CH_1		1
N	CH_2		2
N	CH_3		3
N	CH_4		4
N	CH_5		5

C	CH_1_Cumu		1
	CH_1		


C	CH_2_Cumu		2
	CH_2		
	CH_1_Cumu		

C	CH_3_Cumu		3
	CH_3		
	CH_2_Cumu		

C	CH_4_Cumu		4
	CH_4		
	CH_3_Cumu		

C	CH_5_Cumu		5
	CH_5		
	CH_4_Cumu		

The Cumu elements are just there to help prevent over-feeding, so that			
eg if a Location only has two children then I will feed CH_2_Cumu, as we shall see later.			

It is important the CH_1 thru CH_5 appear in index positions 1-5 in the dimension.
If any of your elements had more than 5 children, it is easy to extend the
example to cope with that simply by adding more elements to 
the HierChild dimension, and modifying the rules slightly.

I then created another cube called MaxTestChild that is similar to MaxTest
but it has the extra HierChild dimension.

The MaxTestChild cube pulls in data from the MaxTest Cube via Rules
and uses the extra HierChild dimension to help calculate
the MaxVal (Maximum Value) of the children below each level
The rules in MaxTestChild are:

skipcheck ;

# Find Maximum Value at all levels.

# Limitations:
# Assumes that no Location has more than 5 elements
# Assumes no negative numbers.

# Prevent calculations at consol levels of the HierChild dimension
# ie the Cumus, since these are only there for feeding purposes.

[ 'Value']=C:
  IF( ELLEV( 'HierChild', !HierChild ) = 0 
        ,
        CONTINUE
        ,
        STET
   ) ;

# We want to pull in values against the appropriate HierChild
# of the Location.

['Value']=C:
#  If the location is at the base level (level 0) there are no children
#  so set the Value of each HierChild to 0
  IF( ELLEV('Location',!Location) =0
        ,
0
        ,
#     If the number of the HierChild being calculated (which is
#     the same as its dimix), is more than the number of 
#     children of the Location then set the Value
#     on that HierChild to 0 since there is no child of the Location
#     to supply a value.
        IF( dimix('HierChild',!HierChild) > ELCOMPN('Location',!Location) 
              ,
0
              ,
#           If the Location is at level 1, then this is the first level
#           that will have children.
#           Pull in the value from the MaxTest cube for the Location
#           that is the nth Child of the level 1 location, where n
#           is the dimix of the HierChild element in the HierChild dimension.
              IF( ELLEV('Location',!Location) = 1
                    ,
                    DB('MaxTest',
                          ELCOMP('Location',!Location,dimix('HierChild',!HierChild) ),
                          !Product,'Value'
                    )
                    ,
#                 For higher levels, in the location dimension, 
#                 we can reference this cube, and we take in
#                 the value from the nth child of the consolidated
#                 location, where n is the dimix of the HierChild element 
#                 in the HierChild dimension.
#                 We pull in the MaxVal from HierChild CH_1 since this
#                 is where the final max val is held (see below).
                    DB('MaxTestChild',
                         ELCOMP('Location',!Location,dimix('HierChild',!HierChild) ),
                         !Product,
                         'CH_1',
                         'MaxVal')
              )
        )
  ) ;
  
# Now we use recursion to loop through the values held against
#  HierChild 5 down to 1

# Since HierChild 5, CH_5, is the highest numbered HierChild, 
# the value on CH_5 must be the MaxVal so far.

# Start the Recursion

['CH_5','MaxVal']=C:
        ['CH_5','Value']
  ;

# We use recursion to compare the Value on a HierChild
# against the MaxVal recorded against the next HierChild
# so we compare the MaxVal of CH_5 against the
# Value of CH_4, and hold the result as the
# MaxVal of CH_4.
# Then we look at CH_3 and compare the Value 
# of CH_3 aganst the MaxVal of CH_4, and
# store the result as the MaxVal of CH_3, and so on
# until we come to CH_1 where the MaxVal against
# CH_1 must be the MaxVal across all 5 children.
# (If the location has less than 5 children it doesn't
# matter since the value there will be 0 so it won't
# be the max.

# Recurse bubbling max value down from CH_5 down to CH_1

['MaxVal']= 
  IF( ELLEV('Location',!Location) = 0 
        ,
0
        ,
        Max(
                ['Value']
                ,
                DB('MaxTestChild',
                     !Location,
                     !Product,
                      DNEXT('HierChild',!HierChild),
                       'MaxVal'
                )
        )
   ) ;

feeders ;

# To avoid over-feeding we feed to the cumu of the 
# HierChild number that is the
# max number of children of the Location	
# which automatically feeds all children before	
# this but not after this.	

['Value']=>	
   DB('MaxTestChild',	
        !Location,	
        !Product,	
        !HierChild | '_Cumu',	
         'MaxVal'	
   ) ;	

The Rules above mean that, within the 	
MaxTestChild cube the MaxVal is bubbled down to CH_1	

CUBE:	hqserv:MaxTestChild


		Product A	Product A	Product A	Product A	Product A	Product B	Product B	Product B	Product B	Product B
		CH_1	CH_2	CH_3	CH_4	CH_5	CH_1	CH_2	CH_3	CH_4	CH_5
Value	All Locations	1	2	0	0	0	11	16	0	0	0
Value	Germany	0	1	0	0	0	4	11	0	0	0
Value	UK	2	2	2	2	0	3	2	1	16	0
Value	Midlands	2	1	0	0	0	3	2	0	0	0
Value	North	2	0	0	0	0	0	2	0	0	0
Value	South East	1	2	0	0	0	1	1	0	0	0
Value	West and SW	2	1	1	0	0	3	16	9	0	0
MaxVal	All Locations	2	2	0	0	0	16	16	0	0	0
MaxVal	Germany	1	1	0	0	0	11	11	0	0	0
MaxVal	UK	2	2	2	2	0	16	16	16	16	0
MaxVal	Midlands	2	1	0	0	0	3	2	0	0	0
MaxVal	North	2	0	0	0	0	2	2	0	0	0
MaxVal	South East	2	2	0	0	0	1	1	0	0	0
MaxVal	West and SW	2	1	1	0	0	16	16	9	0	0

The MaxTest Cube has the following rules to											
feed values across to the MaxTestChild Cube and to											
pull back the result. Therefore the MaxTestChild cube can be relegated to being a calculation											
cube that no one needs to look at since the results can be obtained just by looking at the MaxTest Cube.											

skipcheck ;											

['MaxVal']= N: ['Value'] ;											

['MaxVal']=C:											
  IF( ELLEV( 'Location' , !Location ) = 0 											
        ,											
        ['Value'] 											
        ,
        DB('MaxTestChild',!Location,!Product,'CH_1','MaxVal') 
  ) ;

feeders ;

['Value']=>['MaxVal'];

# Feed to the Cumulative consolidation of the max number of
# children of the parent location.
# This will ensure that all children will be fed.

[]=>DB('MaxTextChild',
             !Location,
             !Product,
             'CH_' |
             str(				
                   elcompn('Location',				
                                 elpar('Location',!Location,1) 				
                   ) ,				
                   1,1				
             ) | '_Cumu',				
            !MaxTestMeas) ;				

The final result then looks like this				

CUBE:	hqserv:MaxTest			


	Product A	Product A	Product B	Product B
	Value	MaxVal	Value	MaxVal
All Locations	13	2	52	16
Germany	1	1	15	11
Berlin	0	0	4	4
Munich	1	1	11	11
UK	12	2	37	16
Midlands	3	2	5	3
Birmingham	2	2	3	3
Coventry	1	1	2	2
North	2	2	2	2
Edinburgh	2	2	0	0
Newcastle	0	0	2	2
South East	3	2	2	1
London	1	1	1	1
Reading	2	2	1	1
West and SW	4	2	28	16
Bristol	2	2	3	3
Cardiff	1	1	16	16
Devon	1	1	9	9