The first part of this series examined the TRANSACTIONAL fact table as the optimal solution for Statement of Comprehensive Income (SOCI). The Statement of Financial Position (SOFP) is, however, fundamentally different. Namely, the SOFP is a view of an organisations Assets, Liabilities and Equity at a point in time. This point in time means that - unlike the SOCI - it is non-additive, the:
SOFP cannot add elements (i.e., Current Assets) from one month to the next and get the assets across the two months
SOCI can add the element (i.e., Gross Profit) from one month to the next and get the profit across the two months
Therefore, instead of using a TRANSACTIONAL fact table for the SOFP there are 2 different fact tables that could be used:
SNAPSHOT fact table
ACCUMULATING SNAPSHOT fact table
Both options are outlined below along with the key considerations; these outline why it advisable to use the former approach over the latter wherever possible.
Option 1 : SNAPSHOT (also referred to as a PERIODIC SNAPSHOT)
The definition for a SNAPSHOT table is:
A row in a periodic snapshot fact table summarizes many measurement events occurring over a standard period, such as a day, a week, or a month. The grain is the period, not the individual transaction.
Kimball & Ross - The Data warehouse Toolkit 3rd edition and Kimball Online
This intuitively feels like the most appropriate approach:
SOFP is a point in time (usually month, quarter or year)
SNAPSHOT fact tables are a point in time (i.e., day, week or month)
To further explain, let's build on the example of a coffee shop where the following transactions are recorded each month for LedgerKey 70100 (Current Assets):
Date | LedgerKey | Amount |
|---|---|---|
Jan 2021 | 70100 | £2,000 |
Feb 2021 | 70100 | £2,100 |
Mar 2021 | 70100 | £1,600 |
Apr 2021 | 70100 | £1,800 |
May 2021 | 70100 | £500 |
Jun 2021 | 70100 | £1,000 |
Jul 2021 | 70100 | £1,400 |
Aug 2021 | 70100 | £2,800 |
Sep 2021 | 70100 | £2,400 |
Oct 2021 | 70100 | £2,700 |
Nov 2021 | 70100 | £3,100 |
Dec 2021 | 70100 | £1,900 |
Jan 2022 | 70100 | £2,100 |
Feb 2022 | 70100 | £2,400 |
Mar 2022 | 70100 | £2,900 |
Apr 2022 | 70100 | £2,200 |
Each line represents the balance of Current Assets on that day, i.e., the balance on Mar 2021 was £1,600.
Key Observations for SNAPSHOT fact table
1) Additivity
If you add the figures together for 2021 you get a total of £23,300; this is a meaningless figure because we cannot say that "Current Assets " for 2021 where £23,300 at any point. Instead we would make an observations such as "Current assets" for 2021 closed at £1,900. This is because the SOFP is a snapshot in time and assets carry over from one period to another.
2) Challenges with DAX
As most fact tables are TRANSACTIONAL the DAX language is more geared towards this type of fact. The flexibility of the language means we can handle these non-additive calculations, however they are generally more complicated to write and require a deeper understanding of DAX.
3) Sparsity vs density
Whilst the SOCI is sparse, the SOFP is dense. SOCI does not need to contain records for each of its foreign keys, i.e. if there are no sales of Americano on one day the SOCI does not require a blank line to show this. The SOFP, however, is dense; there will (almost) always be a line for every combination of foreign keys in the fact.
OPTION 2: ACCUMULATING SNAPSHOT
Many finance systems hold the SOFP in an ACCUMULATING SNAPSHOT format. The definition of which is as follows:
A row in an accumulating snapshot fact table summarizes the measurement events occurring at predictable steps between the beginning and the end of a process.
Kimball & Ross - The Data warehouse Toolkit 3rd edition and Kimball Online
The below is the same coffee shop data as before, but presented as an ACCUMULATING SNAPSHOT where we're assuming the financial year runs from January to December:
Date | LedgerKey | Amount |
|---|---|---|
Jan 2021 | 70100 | £2,000 |
Feb 2021 | 70100 | £100 |
Mar 2021 | 70100 | (£500) |
Apr 2021 | 70100 | £200 |
May 2021 | 70100 | (£1,300) |
Jun 2021 | 70100 | £500 |
Jul 2021 | 70100 | £400 |
Aug 2021 | 70100 | £1,400 |
Sep 2021 | 70100 | (£400) |
Oct 2021 | 70100 | £300 |
Nov 2021 | 70100 | £400 |
Dec 2021 | 70100 | (£1,200) |
Jan 2022 | 70100 | £2,100 |
Feb 2022 | 70100 | £300 |
Mar 2022 | 70100 | £500 |
Apr 2022 | 70100 | (£700) |
The start of each financial period shows the balance for "Current Assets", then the following 11 financial periods show the change in balance from the previous period. For instance, the Mar 2021 balance is still £1,600 (Jan £2,000 + £100 - £500).
Key Observations for ACCUMULATING SNAPSHOT fact table
There are 2 main additional complexities when using an ACCUMULATED SNAPSHOT instead of a PERIODIC SNAPSHOT
1) Additional DAX complexity
Previously the DAX could take a single figure to calculate the balance for a given month; i.e., March 2021 could take the single row of £1,600. In the ACCUMULATING SNAPSHOT approach, the starting balance for the year is required as are all the balances up until the month of interest. Additional complexity means a less performant solution - it is irrelevant how good you are at DAX, the calculation will be slower than can be achieved with the SNAPSHOT approach.
2) Additional Cash Flow Complexities
If using the indirect method for the Statement of Cash Flows (SOCFs) - one of the 2 methods permissible under IFRS and US Gaap - figures are required from the SOFP for the SOCF. Any additional complexity in the SOFP (and the subsequent degrade in performance that goes with this) is inherited by the SOCFs.
Conclusion
For the SOFP, it is advisable to work with a SNAPSHOT table even if this requires transformation from the source system. As with all transformations, this should be done inline with Roache's maxim:
Data should be transformed as far upstream as possible, and as far downstream as necessary.
Matthew Roache - blog
The third part of this series focuses on the SOCF and how we mix and match data across the different types of fact table .
NEXT: Statement of Cash Flows (SOCF)
Comments