Nonprofit Business Intelligence: One Data Model for Donors, Funds and Grants

Nonprofit business intelligence is the shared foundation underneath every report an organization produces: one model in Power BI that joins gifts from the donor CRM, restricted and unrestricted balances from fund accounting, results from program records and spending against each grant budget. Build it once, and the board packet and the funder report draw on the same reconciled figures.

Lightray Solutions builds that foundation for nonprofits on Microsoft's platform, and Edward Pinela, MBA, does the modeling himself. The dashboards that sit on top, and the definitions of donor metrics, have their own pages; this one is about the plumbing that makes them trustworthy.

The work is quoted at a fixed price after a free 30-minute strategy call and a short look at your systems. Changes after launch can run on Power BI Flex Support, from $2,500 for 20 hours, in prepaid blocks whose hours never expire.

What does a nonprofit data model need to join?

Four sources cover most organizations: the donor CRM for gifts, pledges and campaigns; the accounting system for revenue, expenses and net assets by fund; program records for people served and outcomes; and grant agreements for budgets, periods and reporting dates. The model links them through shared keys, chiefly fund, grant, program and date.

The hard part is rarely the connection. It is agreeing that a gift recorded in the CRM and the deposit posted in accounting are the same money, and deciding which system wins when they disagree. That reconciliation rule is written down before anything is built, and the model lists unmatched items instead of hiding them.

• Fund: one list, used by both the CRM gift designation and the general ledger
• Grant: award number, funder, period and budget lines
• Program: the same names in finance and in program records
• Date: the fiscal year and each grant period side by side

How are restricted and unrestricted funds handled?

Since FASB's ASU 2016-14, nonprofit financial statements show two classes of net assets: with donor restrictions and without donor restrictions. The model carries that classification on every fund, so a dashboard can show how much is truly available for operations and how much is waiting for a purpose or time restriction to be met.

Releases from restriction are where reports usually drift apart. When spending satisfies a restriction, accounting records the release; if the CRM still shows the gift as restricted, the two systems tell the board different stories. The model treats the accounting entry as the authority for balances and uses the CRM for donor and campaign detail.

Your accountant or auditor decides how each fund is classified. The model only reports those decisions consistently, and Lightray gives no accounting or compliance advice.

How does the model produce board and grant reports?

From the same tables, filtered differently. A board view summarizes revenue by source, spending by program and available unrestricted funds against the operating budget. A grant view filters to one award and its period, comparing spending with each budget line. Because both read one model, a figure that appears in both reports always matches.

Federal awards make that budget comparison more than a courtesy. The Uniform Guidance at 2 CFR 200.302 asks recipients to keep records that identify the amount, source and expenditure of federal funds, and to compare expenditures with budget amounts for each award. A model keyed on grant and budget line turns that comparison into a filter rather than a spreadsheet exercise.

Funders that want a fixed layout, page numbers and a signature line can receive a paginated report generated from the same model. Which dashboards to build first is covered under nonprofit data dashboard services, and how retention or cost per dollar raised are calculated under nonprofit data analysis.

Who can see donor data, and how is it protected?

Fewer people than can see the financials. Donor names, addresses and giving history are personal data, so the model separates them from aggregate figures. Board members and program staff see totals; development staff see the donors they manage; only a named few see everything. Power BI enforces this with workspace roles and row-level security.

Row-level security filters the rows each user sees, based on their sign-in, inside one published report, so there is no separate copy per audience; the options are explained on our row-level security implementation page. Two more settings matter for donor data. Sensitivity labels, Microsoft notes, carry their protection into files exported to Excel, PowerPoint or PDF, but CSV files leave unprotected, so which export formats are allowed gets decided in the same session.

Anonymous donors and gifts marked private are handled by rule, not by memory: the flag in the CRM hides the name everywhere the model would show it.

What does building the foundation cost, and how does it run?

It is quoted at a fixed price after discovery, because the work depends on how many systems you run and how clean the fund and grant lists already are. The engagement runs remotely, by video and screen share inside your own Microsoft tenant, in four stages: inventory, reconciliation rules, model build and walkthroughs.

Licensing is a separate line. Microsoft publishes nonprofit pricing for Power BI, and our Power BI and Tableau comparison for nonprofits sets out the figures. The build itself is covered by the Power BI guarantee: should you decide within the first 4 hours that you don't like working with us, the fee is refunded.

Edward has 20+ years of business and data work behind him across 200+ projects, and Lightray holds a 4.9 Google rating and an A+ rating from the BBB.

Further reading: Microsoft Learn: Sensitivity labels in Power BI.