Getting Member formulas from a Planning Application


Sometimes you get a request from Client saying we want to see all the formulas that are used in Planning application.

I once got the same reuqest…..I was thinking how I do this…. First idea was to use Outline Extractor and then filter out the formulas used (which was a good one at that point of time). But when I extracted it I got all the members (the ones which doesn’t have formulas also).

But then I thought, instead of using EXCEL and filtering why not look for something else. So i looked in the Relational repository and found that Planning stores all the formulas under a table called HSP_MEMBER_FORMULA…..and member names are stored in another table called HSP_OBJECTS.

So write a SQL for extracting all Planning formulas…..It is most effective because it’ll only give you the member which has formulas…(not an entire list where you have to filter for those who have formula)

SELECT     a.object_name, b.formula
FROM       HSP_OBJECT a, HSP_MEMBER_FORMULA b
 where a.object_id = b.member_id and b.formula is NOT NULL

Hope it helps…..
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

0 thoughts on “Getting Member formulas from a Planning Application