Infusion data warehouse: a CFO's guide for multi-site groups

An infusion data warehouse is one secure database where copies of your WeInfuse, billing, general ledger, payroll and distributor records are cleaned up and organized. Every site is then counted the same way, and the numbers tie to your books. I wrote a free 19-page white paper, The CFO's Guide to an Infusion Data Warehouse, for finance leaders of multi-site groups who have decided they need one and want to know how it is built.

You can get the white paper (free PDF). Building data warehouses for multi-site infusion groups is the work I do. If you are weighing scope, platform or partners, you can book a call to talk it through.

Key takeaways

• The design is deliberately plain: scheduled Python code copies your records every night into one data warehouse, such as Snowflake, Microsoft Azure or Google Cloud's BigQuery.
• Inside, data moves through three layers. Bronze keeps records as they arrived, silver cleans and matches them, and gold holds the tables everyone reports from: a single source of truth for every site.
• Gold is compared with the general ledger every month. Collections should tie closely, revenue ties after listed differences, and charges never tie because they are list prices.
• HIPAA is non-negotiable: the services you use must be eligible for patient data, and a business associate agreement with the cloud vendor must be signed before any patient record is loaded.
• Most projects that disappoint fail on unclear definitions, trusting that "the job ran", or dashboards shown before the numbers tie, not on technology.

Quick answers

• Build in-house if: you have, or will hire, a data engineer and an analyst.
• Hire a partner if: you want it built in your own account and handed over.
• Buy a packaged tool if: its standard reports answer your questions and you can live with its definitions.
• Platform: Snowflake, Microsoft Azure or Google Cloud BigQuery, whichever fits the cloud you already use.
• First thing to sign: a business associate agreement with the cloud vendor, before any patient record is loaded.

Who is the guide for?

It is for CFOs and finance leaders of infusion groups running ten, twenty or thirty centers. These leaders are accountable to owners, boards or lenders and need to set budget, scope, timeline and partner.

It assumes you are financially fluent but not technical, so every term is explained in plain words. A one-page appendix covers the detail for whoever builds it. If you are still deciding whether you need a warehouse at all, start with All Your Infusion Data in One Place.

WeInfuse is a trademark of its owner. Greyfield Data is independent and not affiliated with WeInfuse.

Why do multi-site infusion groups hit the wall?

Because spreadsheets that work at two or three sites break at ten, and each break lands on finance.

• Every site reports a little differently, so the group total adds up different things.
• Month-end takes too long, because someone pastes exports together before the close can finish.
• Board and lender packs are built by hand, and each rebuild can come out slightly different. The board monthly finance pack guide shows the one page a warehouse should feed.
• Operating numbers do not tie to the general ledger, so neither side trusts the other's figure.

The multi-site KPI rollup guide covers the definitions that fix the first problem.

How is an infusion data warehouse built?

Four boxes and three arrows: your systems, a copy step, one warehouse with three layers, and the reports you read.

The layer names follow the same idea as in bronze, silver, and gold explained with one order: the names matter less than the jobs.

How do you get data out of WeInfuse?

The typical approach is a program that uses Looker's API to request the same reports your team could export by hand. WeInfuse's reporting runs on Looker.

You ask WeInfuse support for API credentials for your account, and confirm a Looker seat, which may be an add-on. Each night the program copies only what changed, re-reads a small overlap so nothing slips through, and records what it fetched. Because a run can finish while one slice of dates failed, every day's counts are checked against what WeInfuse reports. The WeInfuse Looker API checklist lists those checks for whoever builds it, and WeInfuse reporting help covers the work on that side.

What should tie to the general ledger, and what never will?

Collections should tie closely to cash in the ledger by deposit date. Charges never tie.

Expected revenue for completed visits ties to net patient revenue after listed differences, such as late-month accruals. Drug cost of doses given ties to drug expense after the change in inventory. Charges never tie, because they are list prices. The difference is covered in claims, charges and collections.

In the paper's synthetic March tie-out, gold shows $1,842,000 and the ledger $1,815,500. The $26,500 difference splits into $18,200 of late-month accruals, a $5,800 February adjustment, and $2,500 still to explain.

Which warehouse platform should a multi-site group choose?

Snowflake, Microsoft Azure and Google Cloud (BigQuery) are all reasonable. Pick the one that fits the cloud and IT you already use.

The non-negotiable is HIPAA. Snowflake's documentation says a signed BAA must be in place before patient data is stored and ties patient data support to its Business Critical edition. Microsoft offers a HIPAA BAA through its Product Terms for in-scope Azure services. Google Cloud enters into BAAs and lists BigQuery among the covered services.

Confirm the BAA covers every service you will use before any patient record is loaded.

What does a data warehouse let finance do?

It lets finance see margin, drug spend, labor cost and cash on one basis across every site.

• Margin by site, drug, payer and referring provider, from what was paid and what the drug really cost.
• Drug spend against reimbursement, with the quarterly CMS ASP files beside your invoice costs, and waste billed on JW or JZ lines. The quarterly ASP update guide and the buy-and-bill margin calculator show the per-drug math.
• Labor cost per chair-hour, from payroll, based on booked chair time. The infusion staffing planner and the chair utilization calculator show the inputs.
• Cash forecasting from unpaid completed visits and each payer's history.
• Diligence-ready history, on one basis, with monthly tie-outs to show it matches the books. See the data a buyer or lender will ask for.

Should you build in-house, hire a partner, or buy a tool?

Each fits some groups, and the right one depends on your team and how standard your questions are.

In-house works if you have, or will hire, a data engineer and analyst. A partner works if you want it built in your own account and handed over. A packaged tool works if its standard reports answer your questions and you can live with its definitions. Whichever you choose, keep the account, code and written definitions in your group's name. See choosing an infusion analytics partner for the questions to ask.

Frequently asked questions

What to do next

Get the white paper and work through its one-page CFO checklist with your controller. Before anything is built, agree what each number means with the Infusion Center Metrics Playbook. The infusion analytics resource hub links every related guide.

When you are ready to scope it, book a call and bring your three to five finance questions, the systems you use today, and your number of sites and billing entities.