Customize Grid Spread in Hyperion Planning 2


In this blog, I’ll try to explain how the option “grid spread” works, and the customization of it.
I always thought about Hyperion from the perspective of an Administrator, but this time its different 🙂
As a user sometimes it is difficult to enter the same value to all the children of an account. Some people use a work around where they’ll create a replica of the Webform in EXCEL and then do a copy & paste. Planning comes with some in-built options to perform such tasks so why do the double work!!!!!
Let’s have a look at the “Fill” option in Grid Spread.
I had to input 10 -> Jan for all the accounts coming under Salary and Wages, so the steps that I performed to get 10 in all the cells. 
  • Select the column Salary and Wages -> Jan
  • Go to Grid spread
  • Enter the Spread Value (i.e. 10 in this case)
  • Select the Spread Type as “Fill”
  • Click Spread. (You can see that now all the cells are filled with 10)
You have to keep in mind that “Fill” will replace all the values in the existing cells. Also “spread” ignores read only cells, cells which you don’t have access.
Now I’ve to increase the values of one particular account, but it should be proportionate to the existing values.
I can make use of “Proportional Spread” option. In the example I’ve to double the existing value (55 to 110)
                                                                                      before spread
                                                                               after spread
The values are proportionately distributed after the spread, you don’t need to do any additional math to achieve this.
Now let’s say that I’ve to evenly split 60 across the children of a parent, you can use “Evenly Split” option of grid spread.
Customization of Grid Spread
No let’s discuss about the customization of grid spread pattern. Grid spread patterns are stored in a repository table called HSP_SPREAD_PATTERN. You can use 
  • Numbers from 0 to 9 
  • P denotes parent value
  • ! denotes lock (the existing value will not be effected)
  • * denotes repeat option 
445 distribution
Use the below SQL statement to create 445 distribution.
Insert into HSP_SPREAD_PATTERN (ID,NAME,LABEL,PATTERN) values (‘3′,’445′,445 Distribution’,’4 4 5′);
Working principle is similar to “Proportional spread” only difference is of the spread ratio (if there are 5 members under a parent then 1st 3 members will follow 445, where 4 and 5th member will receive 4 4) 
Parent to Last THIRD member
Use the below SQL statement to create this pattern
Insert into HSP_SPREAD_PATTERN (ID,NAME,LABEL,PATTERN) values (‘4′,’Balance’,’Parent to Last THIRD member’,’! ! P’);
We won’t be using these types of spread pattern everyday, but I’ll cover these so that the working method of !, *, P can be explained.
In this pattern the value you are spreading, will appear at every third member in the hier.
Parent to First THIRD member
Use the below SQL statement to create this pattern
Insert into HSP_SPREAD_PATTERN (ID,NAME,LABEL,PATTERN) values (‘5′,’First’,’Parent to First THIRD member’,’P ! !’);
In this pattern the value you are spreading, will appear at every first third member (Arithmetic progression of 3) in the hier. (if there are 5 members parent value will be copied to 1st and 4th member)
Bell Curve Distribution
Use the below SQL statement to create bell curve distribution
Insert into HSP_SPREAD_PATTERN (ID,NAME,LABEL,PATTERN) values (‘6′,’BellCurve’,’Bell Curve Distribution’,’1 2 1′);
Working principle is similar to “Proportional spread” only difference is of the spread ratio (if there are 5 members under a parent then 1st 3 members will follow 121, where 4 and 5th member will receive 12)
Parent to 2nd Child
Use the below SQL statement to create this spread pattern.
Insert into HSP_SPREAD_PATTERN (ID,NAME,LABEL,PATTERN) values (‘7′,’Parent_2_Child’,’Parent to 2nd child’,’0 1 0′);
2nd child (Arithmetic progression of 2) will receive parent value and other members will get 0.
You can create your own patterns according to your business needs by following the same steps 🙂
Advertisements

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase.

Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I’m an Independent Consultant with “Intekgrate Corporation” based out of Aurora office.

I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader.

I was awarded Oracle ACE Director for my contributions towards EPM community.


Leave a Reply

2 thoughts on “Customize Grid Spread in Hyperion Planning