1. Operational to Planning dimension mapping

1.1. Unique hierarchy or "approval hierarchy" definition

Following the creation of each BSEG dimension a "unique hierarchy" dimension for the business segment is created. This dimension is cloned from a hierarchy of the main BSEG dimension. The hierarchy should contain all leaf elements and must not contain any elements more than once. Business segment dimensions can contain multiple rollups to represent multiple different ways of analysing the business, this is normal in TM1 models. However, in order to drive inheritance and manage workflows we need to define one master "approval hierarchy" per BSEG dimension which is the purpose of the unique hierarchy dimensions.

Typically the rollup selected as the approval hierarchy should be the default total rollup of the dimension with principal name "Total <dimensionName>" e.g. for FIN BSEG 1, "Total FIN BSEG 1". The rollup to use for the creation of the unique element hierarchy dimension is defined in the cube }APQ C3 Settings. Running the process }APQ.C3.Dim.Create.UniqHierarchy will create or recreate a dimension called "<dimensionName> Unique Elem Hierarchy"

The resulting dimension should contain all leaves from the parent dimension but only a single rollup. This dimension is used for technical purposes for managing plan dimension mapping, security and workflow.

1.2. Mapping process

After the creation of unique element hierarchy technical dimensions the Planning Business Segment dimension can be mapped and created. By leveraging the technical dimension for inheritance the mapping is now  a very easy and quick process versus being manual and potentially error prone in prior versions. The level of the operational dimension which should represent the LEAF in the dependent planning dimension is the only data entry required. The selected level is inherited down the nominated approval hierarchy.

Each operational dimension has its own mapping cube with naming convention "}APQ C3 <dimensionName> Plan Map"

  • Level: reports the level of the element in the BSEG dimension
  • Map Level Entry: the desired dimension level which should become the LEAF level in the planning dimension. If left blank then the desired level will be interpreted as 0 and the Plan dimension will be a 1:1 clone of the operational dimension
  • Map Level Override: within a branch of the overall tree a finer or coarser granularity may be needed for planning. This parameter allows for this adjustment
  • Map Level Final: the end result of the mapping level, only reported for leaf elements of the source dimension
  • Plan Map ID: the leaf planning element which will be created in the Planning dimension
  • Map Check: checks for leaf elements in the operational dimension without a destination element. If 0 for the whole dimension then no errors

1.3. Plan dimension creation

Following the mapping a generic process }APQ.C3.Dim.PlanDimension.Update is run which created the Planning dimension from the template of the operational dimension. Note that this process will only create the "approval hierarchy" rollup in the Plan dimension.  Additional rollups can be maintained using the }APQ.Dim.Hierarchy.Copy process.

The end result is a copy of the operational dimension with either the same or less detailed granularity (which could be mixed in different parts of the hierarchy).

2. Currency dimension creation

In previous C3 Core FIN versions the FIN Currency dimension was maintained manually based on the set of Legal Entity local currencies and the group consolidation currency. In the latest release this process has been automated.

  1. The group reporting currency and FX lead currency are entered in the }APQ C3 Settings cube. These currencies will be mandatory.
  2. After the FIN Company dimension is created and the Currency attribute is maintained a matrix of required Legal Entity local currencies is automativcally generated in }APQ C3 FIN Company Currency
  3. In addition to the Reporting & FX lead currency all Legal Entity currencies are automatically flagged to be included in the FIN Currency dimension. A provision is also made to manually flag additional currencies to be automatically created.

The "Reporting Currency" is the organization's group consolidation currency and is the default for management reporting. This currency can always be referenced simply as "Reporting Currency".

The "Lead FX" currency will generally be the same as the reporting currency but not necessarily. In the currency exchange rate master lookup cube FIN Currency xR rates are entered only as other currency : lead FX currency. The data entry is required only for these combinations. For all other currency rates of currency pairs not involving the lead FX currency the rate is determined with reference versus the lead FX currency. For example if the lead FX currency is defined as USD then the EUR:AUD cross-rate is determined by EUR:USD * (1/AUD:USD). This assumes nil arbitrage which is a true and reasonable assumption for all traded currencies. This logic ensures that a minimum of data entry is needed in order to maintain a full set of relevant currency cross-rates.

After all currencies are flagged for inclusion the process }APQ.C3.FIN.Dim.Currency.Setup then automatically created all currencies flagged to be included in the model.

3. Addition of currency to planning concept

FIN Currency has been added as a dimension to the Expense and Revenue Planning Driver cubes. For companies with a mix of transaction currencies and a currency exposure risk which is significant this allows planning to be done in individual transaction currencies if desired versus only in the local currency of each Legal Entity.

4. Managing Chart of Accounts

4.1. Checking all required attributes are configured

The Chart of Account dimensions FIN OCoA and FIN PCoA are extremely important to model integrity and somewhat more complicated than the Business Segment dimensions due to additional element properties which must be set in order for calculations to work.

The cube }APQ C3 Attr Check FIN OCoA has been designed for the purpose of facilitating a check that all required attributes have been set to ensure calculation integrity and data flows. The "Count" measure is a numeric counter of all instances where a leaf account does not have an attribute value set. (note that not all attributes are mandatory). Below a control check is illustrated for mandatory attributes where a value should be set for all leaves.

Using null suppression it is simple to find the accounts with missing mappings and make a correction. The attributes in the illustrated view are mandatory for the following reasons:

  • TB_Mult : the "trial balance multiplier" this is used to convert between the Financial Accounting Point of View and Management Reporting Point of View. If this attribute is missing a weighting of 0 will be assumed with the result that data will not flow from the trial balance view to the management reporting view
  • FIN CCoA ID Parent : the "cash flow account" which the account ultimately rolls up to for the indirect cashflow method. If this is not set or is not set correctly then the cashflow will not be correct
  • FIN PCoA ID : the Planning chart of account ID. This is created by the mapping process described below
  • BS_Indicator : the "balance sheet account indicator" is used to control whether closing balances for the year carry over versus whether the opening balance is cleared to zero

4.2. Mapping process

The mapping process for FIN OCoA to FIN PCoA is identical to the process used for besiness segment mapping. After the creation of the FIN OCoA unique element hierarchy technical dimension the FIN PCoA dimension can be mapped and created.

4.3. Creation of Planning Chart of Account

Due to some additional complexities of the Chart of Account (handling attributes, some -1 element weightings, etc.) a special purpose process }APQ.C3.Dim.PlanDimension.Update.PCoA is used to create FIN PCoA from FIN OCoA.

The end result is analogous to the business segment operational and planning dimension analogues.

4.4. Accounts without scaling or currency conversion

For some statistical accounts (e.g. Headcount) and some KPIs (e.g. Revenue/Employee) it is desirableor standard practice to always report in the same unit of measure regardless of the scaling of other figures. For example depending on company size we may produce financial reports in base units, thousands, millions or billions. However, the statistical account "FTEs" should always be expressed in simple whole numbers. Likewise, generally statistical accounts and derived KPIs should not have currency translations applied but should always be 1:1 regardless of the currency being viewed.

C3 Core Fin solves this reporting problem via 2 properties "No Scaling" and "No FX" which are optionally set for FIN OCoA elements. In the following example note that the FTEs - Headcount item maintains a fixed point of reference regardless of the scale or currency being viewed.

4.5. Managing KPI calculations

In previous C3 Cor Fin releases KPI calculations were handled in a separate reporting cube FIN KPI. In the current release there is a new streamlined interface for managing KPI calculations with easily understandable formulas and all KPI calculations are performed directly in the FIN General Ledger cube itself.

Definition of KPI calculations (and definition of fixed name mappings) are now handled together in the }APQ C3 FIN KCoA Mapping cube. KPI calculations are easily defined via PickList to select the calculation method from a menu of 30 pre-defined formulas which are intuitive to understand.

After selecting a calculation method all that is required to have a working KPI calculation is to define as necessary each of the calculation variables A, B, C & D.

An automatic maintenance process then adds all mapped KPI calculations to the FIN OCoA and FIN PCoA dimensions and the calculations as per the selected KPI definition are performed directly in the General Ledger cube / Planning cube.

4.6. Trial Balance view vs. Management Reporting view

For financial accountants / controllers, it is helpful to know when loading the General Ledger that all jornal transactions for the period loaded do actually balance. Version 2.0.2 of C3 Core Fin incorporates a financial accounting point of view in addition to the management reporting point of view used in reporting, analysis and planning. For both points of view the interaction between the two General Ledger dimensions FIN Consolidation Value Measure and FIN OCoA is important

Dimension Financial Accounting PoV Management Reporting PoV
FIN Consolidation Value Measure
TB - Trial Balance
OPV - Operating Value, CON - Consolidated Fair Value
Total GL Accounts
Reporting Rollups

The combination of TB and the flat rollup Total GL Accounts sums up all accounts in the natural DR/CR accounting sign in which all GL data is loaded. This allows for simple reconciliation vs. the source system(s) and allows a simple trial balance to zero over the sum of all accounts reconciliation check which has no dependency on the account structure or account attributes being correctly mapped. The management reporting point of view is the one that we are generally used to when viewing financial information; revenue, profit, assets are represented as positive numbers whereas costs and liabilities are represented as negative numbers and a balancing balance sheet is the result of the classical accounting equation A = L + E.

5. Management of fiscal to calendar month time conversion

The cube }APQ Time Month Conversion combined withe the TI process }APQ.Dim.Time.Update.Month.CalToFin.Conversion is designed to convert standard month & year-month time dimensions to support alternate fiscal calendars. The process assumes that the principal element name is a code which will remain unchanged and will update all alias and text attribute values via a defined find/replace process.

The following example illustrates the data entry required to convert a year-month dimension from a calendar financial year to July-June financial year.

6. Data transfer from Planning to GL cube

Data transfer from the planning model to the General Ledger cube has been significantly enhanced in version 2.0.2 versus earlier version 2 releases. Previously Revenue, Expense and Balance Sheet Plan were transferred individually to the General Ledger cube in a 3 step process managed by a wrapper. In the new data transfer process all planning data is transferred from the Balance Sheet Planning cube in a single step. This offers a significant performance improvement versus earlier versions.

7. Subset syncing between operational & planning dimensions

The C3 Core Fin model has the concept of business segment dimensions which come in both operational and planning level of granularity. The planning granularity is generally less detailed than the operational reporting granularity but may be the same level of detail. After the creation of the operational business segment dimension and the mapping exercise to decide on the appropriate planning granularity the planning dimension is automatically created.

Following the principal of centralizing maintenance in one place, for any subsets defined manually in the operational dimension these can be pushed automatically to the planning dimension. If the planning granularity is the same as operational then subset elements will be identical. Any elements existing in the operational dimension but not in the planning dimension will be skipped.  

An apliqode framework process }APQ.Dim.Sub.Copy does most of the heave lifting and an apliqo C3 framework process }APQ.C3.Dim.PlanDimension.SyncSubsets provides a wrapper to keep all subsets in sync between operational and planning dimensions

  1. FIN BSEG 1 => FIN BSEG 1 Plan
  2. FIN BSEG 2 => FIN BSEG 2 Plan
  3. FIN BSEG 3 => FIN BSEG 3 Plan
  4. FIN BSEG 4 => FIN BSEG 4 Plan
  5. FIN OCoA   => FIN PCoA

Note that usage of this feature is optional and subsets can be maintained in planning dimensions independently if desired. Subsets which are maintained in the planning dimension and don't exist in the operational dimension are unaffected by the sync process.

8. Minor Enhancements

8.1. Bulk export of dimensions

For model portability and facilitating migration between environments bulk export via wildcard name match of dimensions in apliqode framework csv format has been added.

8.2. Import/Export of apliqode framework and apliqo C3 framework settings

To facilitate the upgrade process the ability to bulk export and import manually entered cube settings data in standard bedrock cube export csv format has been added.

8.3. Half year reporting

At the request of customers using half yearly reporting, the concept of financial half year has been added to all time dimensions in addition to quarters.

8.4. Deployment of Workforce planning module now available via C3 UX in addition to TM1 Web

The current release features data entry screens for workforce planning in the C3 UX.

8.5. All currency cross-rates are obtained with reference to the lead FX currency reducing the need for unnecessary data entry

8.6. ibcs-class attribute added to all time dimensions

Apliqo C3 UX adopts International Business Communication Standards (IBCS) for standard representation of concepts such as actual, budget, forecast. To facilitate the front end visual representation all C3 Core Fin time dimensions have automatically calculated IBCS classes for the time period type.


Add your comment

E-Mail me when someone replies to this comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.