Wednesday, February 3, 2016

Decoding Oracle HFM Custom Dimensions

The Business Case:

You are looking to pull out HFM Journal Line Item detail from an HFM release of Hyperion Financial Management.  Maybe you are looking to source a "Drill-Through" Essbase application, or you are going to load the data to the database for OBIEE.  Or, perhaps, you have spare time, and you just want to see if you can do it.  The challenge is Oracle does not make it easy to understand how the data is stored, and translating the "IDs" to useful member names can be a challenge.

**Disclaimer**  Direct querying of the HFM database is generally frowned upon and not supported by Oracle.  Proceed at your own risk...  :)

The Problem:

Well, Oracle doesn't generally make this easy.  Extended Analytics doesn't allow you to pull the line item detail, and exporting journals to a flat file may not give you everything you are looking for either, and definitely is not a great option for automating anything.  So, what to do?

A Solution:

...but before we get there...  Let's have a basic database lesson for the HFM back-end DB:

The first thing to know is that the table names have certain items embedded in them, the application name, the scenario ID, and the year (i.e. APPNAME_DCE_1_2016).  If you need to find out the scenario ID, you can get this in the appname_SCENARIO_ITEM table.  Here is an example of what you would see:

Secondly, you'll need to know about the "custom" dimensions.  You can gain more insight into the technical aspects of the "unlimited" custom dimensions by checking out the Oracle Documentation or from various blogs. Here are a couple of references that can help:

Oracle Hyperion Financial Management (HFM) Configurable Dimensions FAQ

The Finnish Hyperion Guy: Maximum Number of Custom Dimensions In HFM

Basically, There are 3 "custom" fields in HFM DB back-end data tables.  How your custom dimension ItemIDs are stored within these fields is defined in the APPNAME_CUSTOM_MAP table.  This table lists out each custom dimension, what "Custom" column they are tagged to, their Offset, and their Length.  Again, refer to the blog posts above for some good explanations on this.  The offset and length field represent the bytes that are being occupied by the custom dimension in the custom fields (important later).  Again, I won't get into the technical math too much, as it is a little over my head as well, but we will work through how to functionally use the information to build a query.

Here is an example appname_CUSTOM_MAP table:

You will notice that there are 7 customs in this example, The first two are in the 1st custom field, the next four are in the 2nd custom field, and the last one is in the 3rd custom field.  The Offset and length columns will come in handy later as we write our query later.

Now there are a number of data tables to understand, like DCE, DCN, JL, JLENT, etc.  However, in this post, we'll only be focused on the JL and JLENT tables.   The JLENT table is more like a Journal Header table.  It has summary amounts, POV information, debit/credit indicator, journal ID, etc.  The JL table has the line item detail with line item descriptions, posting status, posted by UID, approved by UID, posted date, etc. with amounts for the journals identified in the JLENT table.

Let's take a look at an example record from the JLENT table:

For the main dimensions in HFM (i.e. Account, Entity, etc.), the lookups in a query are very straightforward as the ID is explicitly identified.  However, when we look at the three custom fields, the number makes no sense on it's face.  How will we process this in order to to find the appropriate member label for all 7 custom dimensions?

The solution is in a "BITAND" function (for OracleDBs).  The oracle definition of this function can be found here:

I will not attempt to explain the math or expound on the technical definition of the function.  Honestly, I don't think I can explain the math, as it is way over my head!  However, I will try to explain it in everyday, non-math wizard, Hyperion Business Admin functional terms.

For the 1st custom dimension in each custom ID field, the equation looks like this:

BITAND (jlent.custom1_ID, to_number ('00000000ffffffff', 'XXXXXXXXXXXXXXXX'))

The number of digits represented in the embedded "to_number" function is 16.  The "0" indicates that the bit is not being used.  If you recall from our appname_CUSTOM_MAP, dimension 8, is in column number 0 (Custom1_ID), with a 0 offset and a 4 length.  How this translates to the "f" versus "0" is from the right.  You start at the offset number * 2 (in our case, offset 0 * 2 = position 0).  For the number of "f" characters, compute the length value * 2 (4 * 2 = 8).  Fill the remaining spots with a 0.  The result of this equation will be the Custom1 ItemID.  You can then do a lookup on the appname_CUSTOM_ITEM table for the label.

For subsequent Custom dimensions represented in the same Custom1_ID field, the equation gets a little more complicated.  Again, using our appname_CUSTOM_MAP table, here is what the "Custom4" Dimension (dimension ID 11 in the table) decode equation would look like:

BITAND (jlent.CUSTOM2_ID, to_number ('00000000ffff0000', 'XXXXXXXXXXXXXXXX'))/ to_number ('000000000010000', 'XXXXXXXXXXXXXXX')

You'll notice that we are pulling the code in the Custom2_ID field (COLNUM = 1).  The offest is 2 and the length is 2.  So, offset of 2 * 2 means we need to have 4 0's before putting in the f's.  The length of 2 * 2 means we add 4 f's and then fill the remaining spots with 0's to the 16 character point.  The new part is the "/ to_number ('000000000010000', 'XXXXXXXXXXXXXXX')" addition to the equation.  You'll notice that there are only 15 characters in this one.  For this, the you take the offset number again * 2 + 1 to get to the position to put the "1".  For the Custom4 dimension, it is 2 * 2 + 1.  The "1" should be in position 5, again counting from the right.  Fill the rest of the characters with 0.

If you were to ask me the "why" this works, I can not explain it to you.  All I know is that it does work, and it took some pain to figure it out...  So, how does this translate in a query?  Based on our example appname_CUSTOM_MAP table, a decode/lookup query would look something like this:

SELECT jlent.JOURNAL_ID, C1.LABEL AS custom1, c2.label as custom2, c3.label as custom3,
c4.label as custom4, c5.label as custom5, c6.label as custom6, c7.label as custom7
FROM  appname_JLENT_1_2016 jlent
INNER JOIN  appname_CUSTOM_ITEM c1 ON bitand (jlent.custom1_ID, to_number ('00000000ffffffff', 'XXXXXXXXXXXXXXXX')) = c1.ItemID AND c1.lDimID = 1
    INNER JOIN  appname_CUSTOM_ITEM c2 ON bitand (jlent.custom1_ID, to_number ('ffffffff00000000', 'XXXXXXXXXXXXXXXX')) / to_number ('000000100000000', 'XXXXXXXXXXXXXXXX') = c2.ItemID AND c2.lDimID = 2
    INNER JOIN  appname_CUSTOM_ITEM c3 ON bitand (jlent.CUSTOM2_ID, to_number ('000000000000ffff', 'XXXXXXXXXXXXXXXX')) = c3.ItemID AND c3.lDimID = 3
    INNER JOIN  appname_CUSTOM_ITEM c4 ON bitand (jlent.CUSTOM2_ID, to_number ('00000000ffff0000', 'XXXXXXXXXXXXXXXX'))/ to_number ('000000000010000', 'XXXXXXXXXXXXXXX') = c4.ItemID AND c4.lDimID = 4
    INNER JOIN appname_CUSTOM_ITEM c5 ON bitand (jlent.CUSTOM2_ID, to_number ('0000ffff00000000', 'XXXXXXXXXXXXXXXX'))/ to_number ('000000100000000', 'XXXXXXXXXXXXXXX') = c5.ItemID AND c5.lDimID = 5
    INNER JOIN appname_CUSTOM_ITEM c6 ON bitand (jlent.custom2_ID, to_number ('ffff000000000000', 'XXXXXXXXXXXXXXXX'))/ to_number ('001000000000000', 'XXXXXXXXXXXXXXX') = c6.ItemID AND c6.lDimID = 6
    INNER JOIN  appname_CUSTOM_ITEM c7 ON bitand (jlent.custom3_ID, to_number ('000000000000ffff', 'XXXXXXXXXXXXXXXX')) = c7.ItemID AND c7.lDimID = 7
Now back to our original request to pull line item detail to our query results...  Simply change the FROM table to be the appname_JL_1_2016 table and add another INNER JOIN to the JLENT table:

SELECT jlent.field1, jl.field2, c1.label as custom1, field3...
FROM  appname_JL_1_2016 jl
INNER JOIN appname_JLENT_1_2016 jlent on jl.JOURNAL_ID = jlent.JOURNAL_ID
INNER JOIN  appname_CUSTOM_ITEM c1 ON bitand (jlent.custom1_ID, to_number ('00000000ffffffff', 'XXXXXXXXXXXXXXXX')) = c1.ItemID AND c1.lDimID = 1

This allows you to pull "Journal Header" information and the line-item detail.  Add as many or few fields that you are looking for from the JLENT and JL tables.  You can also add additional inner joins for looking up the Entity, Account, Value, etc. dimension member labels as well.

Well, that's it.  Hope this post was helpful, and best of luck!  I have not validated this against or applications, so if you try it, please share your findings in the comments below!