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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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 hfm_entity_item hold, hsv_hfm_secclasses secp 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.
Regards,
Keith
Thanks Keith. Appreciate it. I’ll update this post, at some point.