This is the last in a series of posts related to deriving metadata from HFM’s backed tables. The earlier posts can be found here and here. It will be a short post and I’ve put it together mainly for the sake of completeness. Today, we will look at the Entity dimension.
As usual, the _ITEM table provides most of the information we will need.
The only additional joins we will need (that we haven’t seen from the earlier posts) relate to the Holding Company property, for which we will need to join back to the ENTITY_ITEM table. Taking everything we’ve learned from the last 2 posts, our query might look like the following:
select par.label FPARENT, -- Parent
ch.label FCHILD, -- Child
de.description FDESCRIPTION, -- Description
decode(ch.allowadjustments, 0, 'False', 'True') FALLADJ, -- Allow adjustments
decode(ch.isicp, 0, 'N', 'Y') FISICP, -- IsICP
decode(ch.allowchildrenadjs, 0, 'False', 'True') FALLCHADJ, -- Allow children adjustments
sec.label FSECCL, -- SecurityClass
ch.userdefined1 FUD1, -- User Defined1
ch.userdefined2 FUD2, -- User Defined2
ch.userdefined3 FUD3, -- User Defined3
hold.label FHOLDC, -- Holding company
secp.label FSECPA -- SecurityClass Partner
from hfm_entity_layout ly, -- Layout [stores hierarchy relationships]
hfm_entity_item ch, -- Get Child values
hfm_entity_item par, -- Get Parent values
hfm_entity_desc de, -- Description table
hsv_hfm_secclasses sec, -- Security Classes table
where ly.itemid = ch.itemid -- Join for hierarchy relationships
and ly.parentid = par.itemid
and ch.itemid = de.itemid (+) -- Outer join for descriptions
and ch.securityclassid = sec.itemid (+) -- Outer join for sec classes
and ch.holdingcompany = hold.itemid (+) -- Outer join for holding company entity
and ch.eapsecurityclassid = secp.itemid (+) -- Outer join to get partner security class
order by par.itemid, ly.prevsiblingid, ch.itemid, ly.nextsiblingid -- Ordering
Note, the only thing that is missing is the currency association, which I am still researching. I will update the post once I have an answer.
Our query above gives us the results we need (bar currency, damn that’s annoying…):
This concludes our series of posts on HFM dimension tables. The point here is that just like we can use the planning repository to do a number of cool things (see great posts from Brian Marshall and the folks at DevEPM here and here respectively); we can do similar things with the HFM backend tables as well. Where there is a table, there is a way.