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.
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.
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:
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,
decode(ch.switchtype, 0, 'False', 'True') FSWITCHTYPE,
decode(ch.securityclass, -1, '', sec.label) FSECCL,
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
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
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.
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.