top of page

Know your facts Part 2: Power BI, Financial Statements & Kimball

Updated: May 10, 2022


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)

625 views0 comments

Comments


bottom of page