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
from
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
where
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.
HTH
HI, do you know if it is possible to extract the same but in PBCS
I wrote a post earlier about using LCM https://orahyplabs.com/2012/02/copy-supporting-detail-within-same.html
You can leverage a similar process to do the same.