SQL Centre of Excellence

For my session in SQL bits on Financial Modelling in PowerBI there are couple of useful downloads if you want to follow or dissect at home.

1) AdventureWorksDW2017
This data warehouse has an albeit basic data warehouse version of an accounting table and Account Dimension, so good to use as a demo and starting point.
https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2017.bak

2) PowerBI P&L Report using “Parent Child” Chart of Accounts
https://github.com/ProdataSQL/FinancialModelling/raw/main/downloads/01_P%26L_ParentChild.pbix

This sample report shows some of the aspects of a basic P&L Statement using a parent/child hierarchy.

  • Dealing with Expense and Income signage (Credit/Debit)
  • Ragged Hierarchies and Parent/Child
  • Pro format layout and sorting

image

3) PowerBI Report using “Many to Many” Custom Chart of Accounts

https://github.com/ProdataSQL/FinancialModelling/raw/main/downloads/02_P%26L_CustomM2M.pbix

This report uses a “custom rules” table to extend the account codes into a reporting ranges and headers in a Many to Many basis.

image


4) Sample Balance Sheet Report

https://github.com/ProdataSQL/FinancialModelling/raw/main/downloads/03.%20Balance%20Sheet.pbix

image

5) Sample GL Range Rules for “Many to Many” Custom Chart of Accounts

This is sample master data spreadsheet showing Account Ranges and also Rules on how
GL or Account Codes map to those ranges.

Typically when we get “custom rules” from Financial Accountants we get something like this in a spreadsheet that becomes master data.

https://github.com/ProdataSQL/FinancialModelling/raw/main/downloads/glRules.xlsx

image

image

6) Customised AdventureworksDW2017.bak

Here. we have taken the “out of the box” AdventureworkDW2017 and added the extra "custom rules” table to support a Custom Chart of Accounts.

https://github.com/ProdataSQL/FinancialModelling/raw/main/downloads/AdventureWorksDW2017.bak

image

blog comments powered by Disqus

Page List

Page List