I need to preface today’s post with the following disclaimer, “I am not an HFM guy”. I understand it and respect it, but I am not an HFM developer. What I wanted to do today though, was look at how we can extract dimensions from HFM directly. A lot of times, we have to depend on text files coming out of EPMA to provide metadata definitions for HFM dimensions. To me, this is not ideal, as I personally would prefer to consume data (or master data) from tables as it allows you more leeway to perform further manipulations.
Disclaimer
Before I get too far into this, just another disclaimer that this worked for me on a test environment. This could be considered a warranty breaker. If you are attempting to do this, make sure you TEST, TEST, TEST.
And now that that’s out of the way…
To start off, let’s look at how these dimension tables are set up in HFM. The diagram below shows a high level representation of the Entity and Account dimension tables.
In reality, the backend tables are prefixed with the application name. So, entity dimension tables may be set up as follows, for an application called “HFM”.
For our purposes, we only need to use the *DESC, *ITEM and *LAYOUT tables (except for the account dimension, where will also need the *ACCOUNT_CUSTATTR table).
- ITEM: the ITEM table is the primary table that contains all the member names and the key to link to the other tables. This “key” is the ITEMID column. This table also gives us information like IsICP, AllowAdjustments, UserDefined1 etc.
DESC: This table provides descriptions for the member names contained in any of the other tables. So, Account descriptions will come from the ACCOUNT_DESCR table; Entity descriptions, from the ENTITY_DESC table.
- LAYOUT: This table gives us the hierarchical relationship between members. ITEMID would be the “child” member and PARENTID, the “parent” member. NEXTSIBLINGID and PREVSIBLINGID may be used to determine which member appears next within HFM.
Custom Dimensions
All custom dimension information is stored inside one set of tables, CUSTOM_ITEM, CUSTOM_DESC and CUSTOMLAYOUT. The dimensions are identified by the LDIMID column. For instance, a quick look at the CUSTOM_HEADER (I have not had to use this table for any other reason) table tells us that there are 5 custom dimensions in this application.
And, if we look at CUSTOM_ITEM, we can see this additional column. This also means that we will have to filter out custom dimensions one by one.
Now, let’s look at how we can put this information together. If I wanted to extract metadata for the Custom2 dimension, I would use the following query:
|
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 28 29 30 31 32 33 34 |
select par.label FPARENT, -- Parent ch.label FCHILD, -- Child de.description FDESCRIPTION, -- Description decode(ch.iscalculated, 0, 'False', 'True') FISCALCULATED, -- HFM IsCalculated decode(ch.switchsign, 0, 'False', 'True') FSWITHSIGN, -- SwitchSign decode(ch.switchtype, 0, 'False', 'True') FSWITCHTYPE, -- SwitchType decode(ch.securityclass, -1, '', sec.label) FSECCL, -- SecurityClass ch.userdefined1 FUD1, -- User Defined1 ch.userdefined2 FUD2, -- User Defined2 ch.userdefined3 FUD3, -- User Defined3 ch.submissiongroup FSUBG, -- Submission Group ly.aggrweight FAGGW -- Aggregation weight from hfm_custom_layout ly, -- Layout [stores hierarchy relationships] hfm_custom_item ch, -- Get Child values hfm_custom_item par, -- Get Parent values hfm_custom_desc de, -- Description table hsv_hfm_secclasses sec -- Security Classes table where ly.ldimid = 2 -- Only Custom2 dimension and ch.ldimid = 2 and par.ldimid = 2 and de.ldimid = 2 and ly.itemid = ch.itemid -- Join for hierarchy relationships and ly.parentid = par.itemid and ch.itemid = de.itemid (+) -- Outer join for descriptions and ch.securityclass = sec.itemid (+) -- Outer join for sec classes order by par.itemid, -- Ordering ly.prevsiblingid, ch.itemid, ly.nextsiblingid; |
The above query gives us the following results:
The SQL above may not be the best, but it does the trick and gives us what we need. Note: you might have to change the query to suit your needs.
Conclusion
As you can see, we are able to easily put together a dimension extract which can be sourced by other systems. Imagine if you needed to set up an Essbase or Planning application and needed to source metadata from HFM? This would also work in cases where you might need to import data into DRM.
I will put together another post to go over metadata extracts for the accounts dimension.
EDIT: A followup post is available on how to extract accounts metadata.
Is there a way to extract Entity Locked / Unlocked status in HFM through a SQL Query?
Hi,
Thanks for the comment. I would check, but unfortunately, I do not have access to an HFM environment at the moment.
BTW. Very informative blog. Thanks for yr feedback Vijay. I tried looking through the Entiry_Item and security tables but did not find a reference. Any assistance would be helpful.
Ok, I had a chance to take a look. You need to look in the app_name_ICT_ENTITIES table. Here, you will see all the locked entities. When STATUS = 1, that means the entity is locked. If it’s 2, it’s unlocked. You will need to link back to the HFM_ENTITY_ITEM table to get the name of the entity. Hope that helps.
Thanks for your feedback Vijay. Much appreciated!! I will test and get back on this. Do u also happen to know which table would store the current open period year and scenario in HFM – so we can figure out which ENTITY for the current open period/year and scenario is locked or unlocked?
Actually, I haven’t figured out how to translate the period code yet, as it’s using some weird number formatting. The scenario link is easy as you can refer back to the [app_name]_SCENARIO_ITEM table.
Thanks Vijay.. I did get a chance to test it and found that the table columns should have the relevant information – but this table is empty even though we have entities that are locked in HFM.. Thoughts?
That’s strange…it worked for me. Unless there are other tables that I somehow missed. Those are the Intercompany tables, as far as I know. Have you checked the right application? IF you have multiple apps, they will all get stored in the same schema (same names, but prefixed with the different app names).
Yes I did reference the app name as prefix to look at the data within the ICT_ENTITITES table but it has no data. We do hv multiple apps and each app prefix exists on the table names. Thank you again for your help and looking into this
Very informative. Also how do you get information about isPrimary and #root from these tables.
Thanks,
G
Hello,
Thanks. IsPrimary and #root come from the EPMA repository, not the HFM repository. So, you won’t find that here.
Hello,
Am trying to encode Custom1ID and Custom2ID. Could you please give the logic and formula for them
thank you Vijay for this information and all the SQL’s – one thing that I did not find with your post was how to link the custom dimensions to the DCE tables — here is the sql to accomplish it
select c1.label as C1,
c2.label as C2,
c3.label as C3,
c4.label as C4,
d. *
from
(
select bitand (t.lcustom1, to_number (‘00000000ffffffff’, ‘XXXXXXXXXXXXXXXX’)) as C1id,
bitand (t.lcustom1, to_number (‘ffffffff00000000’, ‘XXXXXXXXXXXXXXXX’)) / to_number (‘000000100000000’, ‘XXXXXXXXXXXXXXXX’) as C2id,
bitand (t.lcustom2, to_number (‘00000000ffffffff’, ‘XXXXXXXXXXXXXXXX’)) as C3id,
bitand (t.lcustom2, to_number (‘ffffffff00000000’, ‘XXXXXXXXXXXXXXXX’)) / to_number (‘000000100000000’, ‘XXXXXXXXXXXXXXXX’) as C4id,
t. *
from hfm_DCE_2_2016 t
) D,
(Select ci. * From hfm_custom_item ci, hfm_custom_header ch
where ci.ldimid = ch.ldimid and ch.treelabel = ‘Custom1’) c1,
(Select ci. * From hfm_custom_item ci, hfm_custom_header ch
where ci.ldimid = ch.ldimid and ch.treelabel = ‘Custom2’) c2,
(Select ci. * From hfm_custom_item ci, hfm_custom_header ch
where ci.ldimid = ch.ldimid and ch.treelabel = ‘Custom3’) c3,
(Select ci. * From hfm_custom_item ci, hfm_custom_header ch
where ci.ldimid = ch.ldimid and ch.treelabel = ‘Custom4’) c4
where
d.C1id = c1.itemid and
d.C2id = c2.itemid and
d.C3id = c3.itemid and
d.C4id = c4.itemid
Hi,
Thanks a lot, Lorie! I appreciate it. I couldn’t figure this out, at the time.
Vijay