Extract Supporting detail information from Planning

You  might have wondered, tried and some might have succeeded in extracting this information, however I (consider myself lazy and forgot about picking this from my drafts) couldn’t answer this question when someone (Ramesh Paul) asked me this question.
I planned to write about it and then forgot (I created a draft that time) about it. All on a sudden I had to face that question again and I thought of answering it right away.
You can read more about supporting detail here.
Supporting detail is stored in Planning relational tables (not in Essbase). HSP_COLUMN_DETAIL and HSP_COLUMN_DETAIL_ITEM holds the required information.
Supporting Detail in Planning
SQL for extracting Supporting Detail
select b.label, b.generation,
(CASE b.operator
WHEN 0 THEN ‘Ignore’
when 1 THEN ‘Add’
when 2 THEN ‘Subtract’
when 3 THEN ‘Multiply’
when 4 THEN ‘Divide’
END) Operator, b.value, c.object_name, d.object_name, e.object_name, f.object_name, g.object_name, h.object_name, i.object_name, j.object_name, k.object_name, l.object_name, m.object_name, n.object_name, o.object_name, p.object_name
hsp_column_detail a,
hsp_column_detail_item b,
hsp_object c,
hsp_object d,
hsp_object e,
hsp_object f,
hsp_object g,
hsp_object h,
hsp_object i,
hsp_object j,
hsp_object k,
hsp_object l,
hsp_object m,
hsp_object n,
hsp_object o,
hsp_object p
a.detail_id=b.detail_id and
a.dim1=c.object_id (+) and
a.dim2=d.object_id (+)and
a.dim3=e.object_id (+)and
a.dim4=f.object_id (+)and
a.dim5=g.object_id (+)and
a.dim6=h.object_id (+)and
a.dim7=i.object_id (+)and
a.dim8=j.object_id (+)and
a.dim9=k.object_id (+)and
a.dim10=l.object_id (+)and
a.dim11=m.object_id (+)and
a.dim12=n.object_id (+)and
a.dim13=o.object_id (+)and
a.dim14=p.object_id (+);
How it looks
Generation will tell you about Supporting detail hierarchy.

Note: You can add more joins if you’ve more than 14 dimensions.



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