Today’s post is just a quick follow up to the last one, on using HFM dimension tables. The disclaimers from the last post still apply. We will be looking at additional code to generate the dimension extracts from HFM’s Account tables. As mentioned in the last post, the accounts dimension has additional attributes and additional tables to take into account while setting up the metadata extract.
For instance, the ACCOUNT_ITEM table tells us that we can derive attributes such as Account Type, IsICP, PlugAccount etc. from the base table.
EPMA confirms the above, but in a more user-friendly manner. I hope the direct correlation between the columns in the screenshot above and below is not lost on anyone.
The base query to do this might look like the code snippet below:
|
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 35 36 |
select par.label FPARENT, -- Parent ch.label FCHILD, -- Child de.description FDESCRIPTION, -- Description decode(ch.accounttype, 0, 'Revenue', 1, 'Expense', 2, 'Asset', 3, 'Liability', 4, 'Balance', 5, 'Flow', 7, 'CurrencyRate', '') FACCTTYPE, decode(ch.iscalculated, 0, 'False', 'True') FISCALCULATED, -- HFM IsCalculated decode(ch.isconsolidated, 0, 'False', 'True') FISCONSOL, -- HFM IsConsolidated decode(ch.isicp, 0, 'N', 'Y') FISICP, -- HFM IsICP ch.numdecimalplaces FDEC, -- HFM DecimalPlaces decode(ch.useslineitems, 0, 'False', 'True') FLINEITEMS, -- HFM UsesLineItems ch.userdefined1 FUD1, -- User Defined1 ch.userdefined2 FUD2, -- User Defined2 ch.userdefined3 FUD3, -- User Defined3 decode(ch.securityclass, -1, '', sec.label) FSECCL, -- SecurityClass ch.calcattribute FCALC, -- Calc Attribute ch.submissiongroup FSUBG -- Submission Group from hfm_account_layout ly, -- Layout [stores hierarchy relationships] hfm_account_item ch, -- Get Child values hfm_account_item par, -- Get Parent values hfm_account_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.securityclass = sec.itemid (+) -- Outer join for sec classes order by par.itemid, -- Ordering ly.prevsiblingid, ch.itemid, ly.nextsiblingid; |
At this point, we have all the account-related master data, except for attributes like ICPTopMember, PlugAccount and Custom Top Members. To link up that data with our base query above, we will need help from a few extra tables:
- ACCOUNT_ITEM table: This will help us get the PlugAccount. The join can be done through the ITEMID and the PLUGACCOUNT columns.
- ICP_ITEM: This will allow us to get the ICP top member name.
- ACCOUNT_CUSTATTR: This will allow us to retrieve the Custom dimension top members. The NVALUE column will be used to perform the join.
- CUSTOM_ITEM: this table, which we are familiar with, from the last post, will provide the names for the custom dimension top members.
Once we have those extra joins set up, our accounts query may look like:
|
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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
select par.label FPARENT, -- Parent ch.label FCHILD, -- Child de.description FDESCRIPTION, -- Description decode(ch.accounttype, 0, 'Revenue', 1, 'Expense', 2, 'Asset', 3, 'Liability', 4, 'Balance', 5, 'Flow', 7, 'CurrencyRate', '') FACCTTYPE, decode(ch.iscalculated, 0, 'False', 'True') FISCALCULATED, -- HFM IsCalculated decode(ch.isconsolidated, 0, 'False', 'True') FISCONSOL, -- HFM IsConsolidated decode(ch.isicp, 0, 'N', 'Y') FISICP, -- HFM IsICP plg.label FPLUG, -- HFM PlugAccount ch.numdecimalplaces FDEC, -- HFM DecimalPlaces decode(ch.useslineitems, 0, 'False', 'True') FLINEITEMS, -- HFM UsesLineItems ch.userdefined1 FUD1, -- User Defined1 ch.userdefined2 FUD2, -- User Defined2 ch.userdefined3 FUD3, -- User Defined3 decode(ch.securityclass, -1, '', sec.label) FSECCL, -- SecurityClass icp.label FICPTOP, -- ICP Top Member ch.calcattribute FCALC, -- Calc Attribute ch.submissiongroup FSUBG, -- Submission Group c1.label FC1TOP, -- Custom1 Top Member c2.label FC2TOP, -- Custom2 Top Member c3.label FC3TOP, -- Custom3 Top Member c4.label FC4TOP -- Custom4 Top Member from hfm_account_layout ly, -- Layout [stores hierarchy relationships] hfm_account_item ch, -- Get Child values hfm_account_item par, -- Get Parent values hfm_account_desc de, -- Description table hsv_hfm_secclasses sec, -- Security Classes table hfm_account_item plg, -- Account table to get Plug value hfm_icp_item icp, -- ICP table to get ICP Top hfm_account_custattr attr1, -- Account attribute table Custom members hfm_custom_item c1, -- Custom dimension table hfm_account_custattr attr2, hfm_custom_item c2, hfm_account_custattr attr3, hfm_custom_item c3, hfm_account_custattr attr4, hfm_custom_item c4 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.securityclass = sec.itemid (+) -- Outer join for sec classes and ch.plugaccount = plg.itemid (+) -- Outer join for plug account and ch.icptopmember = icp.itemid (+) -- Outer join for ICP top and ch.itemid = attr1.itemid (+) -- Outer join for Custom1 top member and attr1.customdimnum = 1 and c1.ldimid = 1 -- Focus on Custom1 only and attr1.nvalue = c1.itemid (+) -- Outer join for Custom1 top member and ch.itemid = attr2.itemid (+) -- Rinse & repeat for additional custom dimensions and attr2.customdimnum = 2 and c2.ldimid = 2 and attr2.nvalue = c2.itemid (+) and ch.itemid = attr3.itemid (+) and attr3.customdimnum = 3 and c3.ldimid = 3 and attr3.nvalue = c3.itemid (+) and ch.itemid = attr4.itemid (+) and attr4.customdimnum = 4 and c4.ldimid = 4 and attr4.nvalue = c4.itemid (+) order by par.itemid, -- Ordering ly.prevsiblingid, ch.itemid, ly.nextsiblingid; |
Notice, there are additional joins needed for each custom dimension you may have in your application.
|
1 2 3 |
ch.itemid = attr1.itemid (+) and attr1.customdimnum = 1 and c1.ldimid = 1 and attr1.nvalue = c1.itemid (+) |
For each custom dimension in your application, you will need to retrieve member names from the CUSTOM_ITEM table. Of course, there may be more elegant ways to do this, but, this works for me.
And finally, our query gives us the results that we need.
