Thursday, August 23, 2012

Maximum Number of Custom Dimensions In HFM 11.1.2.2

Note: I have now updated the post with some additional research. Scroll to the bottom of the page to see my later findings.


"Unlimited" number of custom dimensions is one of the biggest changes introduced in HFM 11.1.2.2. The actual amount of dimensions is not unlimited though - and it is pretty hard to find solid information about the real limits at the moment. So far the best resource explaining how to use the expanded dimensionality seems to be this post on the Oracle OBI/EPM PAE (Product  Assurance Engineering) blog:


The most interesting part from a technical point of view is the comment that has been added to the post by "Ahmed":

Depending on the type of database, we can allocate number of "physical" columns for the custom dimensions. This will have direct impact as to maximum number of custom dimensions you can create for the application.  
With 11.1.2.2, you can define the “size” for each custom dimension. (Large – 4 bytes, Medium – 2 bytes, Small – 1 byte). Internally each “physical” column we allocate for the custom dimension is 8 bytes. 
For example, if we have ONE physical column for custom dimension, we can have either 2 large, or 1 large and 2 medium, or 1 large and 1 medium and 2 small or 1 large and 4 small and etc…. Again, you can have any combination as long as the total does not exceed 8 bytes. 
For SQL server, we support 5 physical custom columns. (Maximum 40 bytes – 8 bytes * 5)
For Oracle DB, we support 21 physical custom columns. (maximum 168 bytes – 8 bytes *21)
For DB2, we support 100 physical custom columns. (Maximum 800 bytes – 8 bytes * 100)
Based on number of bytes available for the DB, you can figure out the maximum number of custom you can create for the app depending on size of your custom.

I made the following diagram based on the information given in the comment. Please note that this is my interpretation and might not be the Absolute Truth (feel free to correct me if you have exact information about the matter).




It seems that you can create any combination of custom dimensions as long as their total size does not exceed the maximum limit dictated by the type of the database. And it looks like Oracle database and DB2 would support a much higher number of customs than MS SQL does (42 and 200 Large dimensions respectively).


Update - August 23th - Comparison Of HFM Data Tables Between Versions

I did some experiments to figure out how the data really is stored in HFM 11.1.2.2. As you probably know HFM stores the base level data in so called DCE tables (for example COMMA_DCE_1_2007 where 1 identifies the Scenario and 2007 the Year). Each row in the DCE table contains data for all periods of a given combination of dimensions (also called an intersection). In previous versions the DCE table has included columns for Entity, Value, Account and ICP dimensions plus four columns to define the custom dimensions (Custom1-4). As the number of Custom dimensions is now variable this structure has been replaced by a more flexible one. The lCustomX columns have been repurposed to host an index number that identifies all Custom dimensions and their members. The number of lCustom columns depends on the number of dimensions that have been defined in the application profile. If I have understood things right these are the 8-byte "physical columns" that were mentioned earlier in this post.



Below you can see how a value is stored in the HFM 11.1.2.2 database when no custom dimensions are defined in the Point Of View. The value "234" gets stored to dP0_Input (January) and both lCustom1 and lCustom2 are zero.



When selecting members for one or more custom dimensions (Custom1/Products and Custom7/Brands in this case) HFM calculates some an index value that tells it which members have been selected in each dimension and stores it in the lCustom columns. How this index is calculated is beyond my comprehension at this point...



There are some other interesting tables related to configurable dimensionality like APPNAME_CUSTOM_MAP which stores the location and offset of the custom dimensions in the physical columns.


At this point I have probably strayed far enough from my original topic... The new custom dimension structure clearly is a complex topic that will take quite a while to get to grips with. I hope you will find the presented information useful - and please remember to share your own findings in the comments.

4 comments:

  1. Has the HFM SDK been updated to allow for the extra custom dimensions? The download and documentation still seems to only have the 4 custom dimensions.

    ReplyDelete
    Replies
    1. The SDK package you can download from the Oracle Technology Network site (http://www.oracle.com/technetwork/middleware/financial-management/overview/index.html) is very old (dated 2008 and refers to version 11.1.1). I checked the EPM Documentation Library and even the latest HFM Developer's Guide that's supposedly updated for EPM 11.1.2.2.300 doesn't mention configurable dimensions at all as most function calls still include methods to query only the traditional custom dims (1-4). Unfortunately I don't know when the SDK and the documentation will be updated to match the changes...

      Delete
  2. That's a great article. I had a query on similar lines .Currently we have 11.1.1.3 HFM environment with 4 custom dimensions and are working on upgrading it to 11.1.2.2 in our development environment. We have couple of processes where we query the HFM data tables. While trying to do the same in 11.1.2.2, we can see the columns for Custom1 and Custom 2 but we can not find any physical columns for Custom 3 (Function) and Custom 4(Source data type) dimensions and hence unable to query them. Can you provide any insights on what we are missing?

    Regards,
    Ashwin Tandon
    Hyperion Consultant

    ReplyDelete
  3. Hi Henry.
    Please share if you find an iformation on how the index is calculated for custom1 and custom2 dimension. I want to know how to link dce table with the custom_item table. Thanks

    ReplyDelete