HFM 11.1.2.2 – New Features: Part – 2

May 15th, 2012 by

Configurable Dimensionality:

‘Configurable dimensionality’ is the significant update to this version. We can probably say that this is the long awaited and biggest change that developers made to HFM.

So what is ‘Configurable dimensions’ is all about? There had always been a need for HFM customers to go beyond the four Custom dimensions, which are provided by default for their business needs. For e.g. they worked by stuffing two different details like Balance Sheet movements and Products details into a single Custom dimension. This was being addressed by Oracle in this release. Now, the users can have as many custom dimensions as required for their implementation. Let us see how this can be done starting by creating a new classic application.

Creating application:

Desktop Client: Many existing functions are cut down in Win32 client. Now, it has only Profile Manager and Metadata manager, rest all functions are available via Web including Create Application task. Profile Manager is used to define the application profile (.per). It can be installed in any machine since it can only be used in offline mode and has no dependency on HFM server component.

In addition to defining Year, Periods and Frequencies, we can now define the number of Custom dimensions as a part of the application profile. So basically, when you create application using this profile file, all these custom dimensions are created. The size (Small, Medium or Large) for a custom dimension must be determined depending on the number of members its hierarchy contains. Custom1&2 by default, are Large size.  As shown in the screenshot, we’ve created a total of 6 Custom dimensions.

Login to the Workspace to use this profile and create the application. As you can see, web server URL need not be provided.

I’ve loaded some sample metadata and data for few periods. !CUSTOM_ORDER section is introduced which specifies the order in which the custom dimensions are displayed. This has to be specified in the metadata load file.

.app
.xml

Now, let’s skip the complexities and move the data up the Value dimension. At this point, I’m eager to see the application database structure.

Sub-cube Architecture:

Financial Management stores its data in database blocks called subcubes rather than in records. Subcube contains Page and Subcube dimensions. The Page dimensions are Scenario, Year, Entity, Value and the Subcube dimensions contain all the members of ICP, Account, View and Custom dimensions (in our case 6 custom dimensions). So typically, for a single Page dimension intersection, if we have 100 accounts and 10 valid members for each Custom dimension and all intersections are populated, then it would be 100000000 records.

HFM stores all this data for these dimensions in three tables:

  • DCE (Currency Subcube)

Stores Entity and Parent Currency values and their adjustments.

  • DCN (Parent Subcube)

Stores remaining value dimension members

  • DCT (Journal Transactions)

Stores Journal Transactions and when posted, they are transferred to DCE (<Entity Currency Adjs>/<Parent Currency Adjs>) or DCN ([Parent      Adjs]/[Contribution Adjs])

In the below table, DPx_INPUT and DPx_INPUTTRANSTYPE are repeating fields for each period that the application contains. The columns are numbered from zero so for 12 periods, the column names will be DP0_INPUT to DP11_INPUT and DP0_INPUTTRANSTYPE to DP11_INPUTTRANSTYPE.

Compared to the older versions, the column structure almost remains the same. Interestingly, if you’ve noticed, in older versions, there are four LCUSTOMx columns one for each custom dimension. In this version, I anticipated that for each extra custom we add to the application, there would be a new column added to these tables like LCUSTOM5 etc. But the structure doesn’t even contain LCUSTOM3 & LCUSTOM4 and it is tempting to know how data for other Custom dimensions are identified. We are working to find out the same and any comments from the readers are really appreciated.

The supporting products for HFM viz. Financial Reporting, FDM, Application Upgrade Utility and existing HFM rule functions were also being updated to support HFM’s Configurable Dimensionality. Although we have the flexibility of adding any number of custom dimensions, time taken for calculations, consolidation and other performance issues need to be considered and application should be designed accordingly.

 

Tags: , ,

Comments

  1. Richard Says:

    did you associate all the 6 custom dimensions to a single account and then store data against non [None] dimensions for each of the 6 custom members? If so it must keep track of that in the DCE tables?

  2. Werner Says:

    Again, thanks for doing all the hard work and research. Invaluable post!
    What happened to the TopCustom1-4 properties on accounts? Are those now TopCustom1-n and where are those properties defined? Still on the account or in a new property by account?

  3. Werner Says:

    Learned at OOW that TopCustom property on acconts is no extended to have one such property for every custom dimension. In other words, you make 6 custom dims, you get 6 TopCustomx properties for the accounts.

  4. Vignana Vikas Says:

    Thanks for your comments Werner. I’ve already noticed the additional TopCustomX properties, but I’m trying to differentiate the underlying table structures between the previous and current versions.

  5. Ashwin Says:

    Hi Vikas,
    Its a superb post and great analysis. Were you able to find where the data for Custom3 and Custom 4 dimension is stored in the new version 11.1.2.2? We are able to understand the same in our new version to query the tables but are stuck on same point.

    Thanks again for the analysis

  6. Lian Says:

    Hi Vignana,
    Could you provide a help on how lcustom1 dimension is calculated? I would like to find a link between lcustom1 of DCE with CUSTOM_ITEM table. Thanks.

  7. Sanjay Says:

    I would like to know how the new Custom Dimensions will be used in the Business Rules.As in the previous versions Custom1 was refrenced as C1 and others as C2,C3 and C4. How the new dimensions will be refrenced.

Website Design & Build: tymedia.co.uk