How to decipher Custom Dimensions in the DCE and Account table of an 11.1.2.2+ HFM App

Hi.

With the help of the posts from Roman Genis (HFM Custom_Map) and Henri Vilminko (Custom Dimensions in 11.1.2.2), I list here two script to decipher Custom Dimensions of DCE table and Account tables in HFM applications later than 11.1.2.2.

Thanks to Roman who wrote the first script.

*Just remember to rename the “APPNAME_” to your application name.

For DCE table:

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 APPNAME_DCE_1_2014 t
) D,
(Select ci. * From APPNAME_custom_item ci, APPNAME_custom_header ch
where ci.ldimid = ch.ldimid and ch.treelabel = ‘Custom1’) c1,
(Select ci. * From APPNAME_custom_item ci, APPNAME_custom_header ch
where ci.ldimid = ch.ldimid and ch.treelabel = ‘Custom2’) c2,
(Select ci. * From APPNAME_custom_item ci, APPNAME_custom_header ch
where ci.ldimid = ch.ldimid and ch.treelabel = ‘Custom3’) c3,
(Select ci. * From APPNAME_custom_item ci, APPNAME_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

 

Account and Account_item tables:

select AccC1.account, AccC1.Custom1, AccC2.Custom2, AccC3.Custom3, AccC4.Custom4 from
(select Acc.label as account, CstI.label as Custom1
from APPNAME_account_item Acc, APPNAME_account_custattr AccCst1, APPNAME_custom_item CstI, APPNAME_custom_header CstH
where Acc.itemid = AccCst1.itemid and AccCst1.nvalue <> -1 and AccCst1.nvalue = CstI.itemid
and AccCst1.customdimnum = CstH.ldimid and CstI.ldimid = CstH.ldimid and CstH.treelabel = ‘Custom1’) AccC1,
(select Acc.label as account, CstI.label as Custom2
from APPNAME_account_item Acc, APPNAME_account_custattr AccCst2, APPNAME_custom_item CstI, APPNAME_custom_header CstH
where Acc.itemid = AccCst2.itemid and AccCst2.nvalue <> -1 and AccCst2.nvalue = CstI.itemid
and AccCst2.customdimnum = CstH.ldimid and CstI.ldimid = CstH.ldimid and CstH.treelabel = ‘Custom2’ ) AccC2,
(select Acc.label as account, CstI.label as Custom3
from APPNAME_account_item Acc, APPNAME_account_custattr AccCst3, APPNAME_custom_item CstI, APPNAME_custom_header CstH
where Acc.itemid = AccCst3.itemid and AccCst3.nvalue <> -1 and AccCst3.nvalue = CstI.itemid
and AccCst3.customdimnum = CstH.ldimid and CstI.ldimid = CstH.ldimid and CstH.treelabel = ‘Custom3’ ) AccC3,
(select Acc.label as account, CstI.label as Custom4
from APPNAME_account_item Acc, APPNAME_account_custattr AccCst4, APPNAME_custom_item CstI, APPNAME_custom_header CstH
where Acc.itemid = AccCst4.itemid and AccCst4.nvalue <> -1 and AccCst4.nvalue = CstI.itemid
and AccCst4.customdimnum = CstH.ldimid and CstI.ldimid = CstH.ldimid and CstH.treelabel = ‘Custom4’ ) AccC4
where AccC1.account = AccC2.account and AccC1.account = AccC3.account and AccC1.account = AccC4.account

 

Regards,

Igor Simoes.

 

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s