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:

• Oracle Hyperion Financial Management (HFM) Configurable Dimensions FAQ

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.