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.
A quick look at EPMA shows us the Entity dimension properties we will need to look for, in the tables.
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.
Hi Vijay –
The join for currencies is against the CURRENCIES table with a bit of math:
SELECT e.label, e.defaultvalueid, c.label
FROM hfm_entity_item e LEFT JOIN hfm_currencies c
ON e.defaultvalueid = 17 + (c.itemid * 3)
The 17 roughly represents default members of the Value dimension (in the VALUE_ITEM table). After that, there are three virtual members for each currency (USD, USD Adjs, USD Total), so the currency ID has to be incremented by three. The sole exception to this is the [None] member which has a currency of [None]. If you don’t care about the [None] member, you don’t need an outer join.
Great post. Hope this helps.
Thanks Keith. Appreciate it. I’ll update this post, at some point.