Accounting Fact Summary
Proof of concept. See Feature Request: Accounting Fact Summary
Financial reports run off the Fact_Acct table can be very slow to run if there are a large number of facts. In the past there was a Fact_Acct_Balance table that contained daily balances of the accounting facts to provide precalculated values to reduce the amount of work the report engine had to do. However this did not offer much improvement as the Fact_Acct_Balance table typically contained 70+% the number of rows of the original fact table. The balance table had to be updated before reporting which was a slow process, leading to an overall slower process than running the report directly from the fact table.
The purpose of this feature is to provide a generic means of precalculating summary data for use in financial reports. As financial reports are period based, the basic level of grouping will be by period. This should immediately provide a significant reduction (~1/20th) in the number of rows needing to be processed when preparing the report.
Secondly, business are not generally interested in reporting across all the dimensions available in the accounting fact table. Summarisation can occur at a higher level by only grouping on those dimensions that are relevant to the business reporting requirements. This information will be obtained by allowing the creation of a user-definable "report cube".
The aim is to create a user customisable "materialised view" of the accounting fact table.
The reporting calendar for the cube must be selected, and any accounting dimensions that are required for reporting.
Multiple cubes can be defined for different calendars or dimensions. Of course, the more cubes there are, the more overhead in calculating the summary data.
A sample process has been developed to populate the summary data. Currently the process must be run manually prior to reporting, or perhaps as a scheduled task on a regular basis. It deletes the existing summary data for each cube and recalculates from scratch. The intention is that future enhancements will provide a means of updating the summary data dynamically when running the report.
An optional parameter is added to the Financial Report allowing a Report Cube to be selected. If selected, pre-summarised data is obtained from the cube. If empty, the report behaves as currently, running directly off the accounting fact table.