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,
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
a.dim1=c.object_id (+) and
How it looks
Generation will tell you about Supporting detail hierarchy.
Note: You can add more joins if you’ve more than 14 dimensions.