...Records & Subcubes in HFM

A few years ago, I decided to spend time to understand how HFM calculation engine works with aim to improve the performance of the rules. The first stop was the HFM manual, the basic design considerations and the section that is referring to subcubes to understand the calculation mechanism.

What is a record?

As per Oracle manual: “A record in Financial Management holds the data for all base periods for a given intersection of dimension members”. So for every unique each combination of Account, ICP, View and Custom dimensions, a new row will be created in the data tables and this row will have 12 values - 1 value per month.

Where the records are stored?

Every time that you create a new scenario, HFM will create several tables. The tables with prefix DCE will be used to store records related from the value dimension members Entity Currency/ Entity Curr Adjs and Parent Currency/ Parent Curr Adjs. The additional information of the journals will be maintained in the tables with prefix DCT. Finally, the tables with prefix DCN will be used to store records related with the rest of the value dimension members. The suffices of these tables are the Scenario ID and the Year ID.

This means that the name of a table will look like MYApp_DCE_1_2017. MyApp is the name of the application, DCE shows that the data will be either EC, ECA, PC or PCA, the 1 is the first scenario that you create (for example Actual) and 2017 is the year.

What is a subcube? 

As per Oracle manual: “A subcube is a collection of records in the RAM that all belong to the same Entity, Scenario, Year, and Value (currency)”. It represents all the records for one entity-one parent entity-one year-one scenario-one currency combination. The subcube is used by HFM to process the records in the XFMDatasource processes. So, when we are working with the data of one subcube, HFM will not only retrieve from the database the data that we need but it will retrieve all the data of the subcube that is related with our POV. This means that the larger the subcube, the bigger the impact on the calculation performance.

Do I forget anything? I am sure I do… these tables may change as Oracle releases new versions.