Applying SQL and Power BI reporting to financial services

Innovation D3

Mid Yorkshire Hospitals NHS Trust

North East and Yorkshire, Acute

A transformative innovation which embraces technology and enhances the skills of the department.

Peer Reviewer

A exciting game changer in financial reporting, analysis and interpretation.

Peer Reviewer

This innovation allows real time analysis and can turn queries around much quicker.

Peer Reviewer

The Problem

The finance team needs to be able to effectively and efficiently analyse millions of transactions to gain key insights. Being limited to a finance system without a modern analytics tool, most current processes involve lengthy downloads into spreadsheets to manually process data using Pivot Tables and vlookups.

The Challenge

  • How might we hold our data so we are not reliant on disparate and disconnected sources of data or time consuming ledger extracts?
  • How might we hold data in a way that we know what we hold and can manage and redact that data according to date retention requirements? Some 200,000 excel spreadsheets on a file server built up over 20 years is not an acceptable way to manage data in 2022.
  • How might we transform from being data rich but information poor, to information rich? Information needs to be at your figure tips when you need it, not a few weeks later once a time-consuming analysis has taken place.

The Outputs

  • Information rich reporting allowing the analysis of millions of transactions in real time.
  • Interactive dashboards – being able to take an interactive accounts receivable dashboard to a meeting with our director of finance and being able answer every question in-situ was a revelation.
  • Power BI data models with transaction level detail all linked to cost centre and general ledger hierarchies for:
    • accounts payable dashboard
    • accounts receivable dashboard
    • unprocessed invoices dashboard.
  • All transactions dataset – linked to PowerBI and Power Pivot – knitting together all subledger extracts: general ledger, accounts receivable, accounts payable, payroll, budgets, balances brought forward, goods received not invoiced, Supply Chain. This was used to identify all transactional samples for audit as well as the agreement of balances dataset for the TAC returns.
  • Account payable spend dashboard – showing purchase order /non-purchase order, PEPPOL compliance, as wells as geographical information.

The Outcomes

  • Information rich reporting allowing the analysis of millions of transactions in real time.
  • Interactive dashboards – being able to take an interactive accounts receivable dashboard to a meeting with our director of finance and being able answer every question in-situ was a revelation.
  • Power BI data models with transaction level detail all linked to cost centre and general ledger hierarchies for:
    • accounts payable dashboard
    • accounts receivable dashboard
    • unprocessed invoices dashboard.
  • All transactions dataset – linked to PowerBI and Power Pivot – knitting together all subledger extracts: general ledger, accounts receivable, accounts payable, payroll, budgets, balances brought forward, goods received not invoiced, Supply Chain. This was used to identify all transactional samples for audit as well as the agreement of balances dataset for the TAC returns.
  • Account payable spend dashboard – showing purchase order /non-purchase order, PEPPOL compliance, as wells as geographical information.