A Quick overview about Data Model choices that best suit your Finance Reporting requirements.
The Finance Reports are often based on a combination of items which calculation requires different approaches and techniques. The choice of the appropriate Data Model is particularly delicate as it may unwillingly privilege UX or some specific reporting requirements, neglecting its side effects.
The representation of a Chart of Accounts and its structure (as in grouping criteria and Relationships) can be achieved with two different approaches:
- The Account Hierarchy is flattened on a single dimension: its elements belong to all of the different levels of the Account Hierarchy and Grouping criteria. The grouping criteria and the identification of Accounts belonging to a higher level rather than a lower, might be derived by the element coding itself
- The Account Hierarchy is replicated in a BOARD Hierarchy: the relationships among the Entities clarify the relationships among the different Account Elements and their Grouping Logics
The Calculation of the Sub-Totals is given:
- FLATTEN - SINGLE ENTITY: Through the Roll-Up property, or through the definition of Rules
- MULTIPLE ENTITIES in HIERARCHY: Natively though the E/R model for the Hierarchy choice
The calculation of ad-hoc items which are not a simple sum of given items must be done through a definition of a Rule in both cases:
(sample report using flatten approach)
(sample report using hierarchy approach)
The following Table provides a high level overview of main Pros and Cons of both options:
F O R M A T
Thanks to the Cell Template functionality, each Entity Item can be individually formatted with a full compliance with the Statutory Reporting of the Company
The Format of the Report must be given with a Combination of Cell Template and Grid Graphic Options
R E D U N D A N C Y
Items are not redundant
The hierarchy must be normalized: this means that Calculated Item meaningful at higher logic level (e.g. Margin) must have children even in the bottom levels of the Hierarchy; this drives to the creation of redundant items
D A T A E N T R Y
Thanks to the RULE++ capability, Data Entry and Reverse Data Entry are fully supported and manegeable
D R I L L
The Drill function is not available on the Calculated Item, both if they are calculated as a Roll Up property or Rule.
Such Option is available only if the item is physically stored in the cell.
This is achievable with the Rule++ (for data entry scenarios) or with a Data Flow
Layout limitations are related to the usage of the Rule and/or the roll up to calculate Sub-Totals. The Flatten Entity must be the lowest level of the break-down by row and no Parent belonging to the same hierarchy is admitted
There is no limitation in the position of the Entity but the redundancy of the items produce cumbersome structure with repetitions of useless subtotals (e.g. the Margin). This can be managed and optimized with the adoption of the "Unbalanced Hierarchy" Option
On the top of the Flattened Entity, you need to add a Parent to allow slice and dice that differently must always be down at the lowest level
The Model includes full slice and dice capabilities