Power BI works best when you feed it a few clean, narrow tables and let it do the joining and summing. Altru queries can supply those tables, but only if you build them for that job. A query that looks fine as a staff report, with one row per constituent and a dozen smart fields, will often give you double-counted revenue or blank visuals once it lands in a data model.
Below is the small set of queries we’d build first, how to design each one, and the honest picture of how the data gets from Altru to Power BI.
First, how the data will travel
Altru is hosted by Blackbaud, so you don’t get a direct database connection the way you would with an on-premises SQL server. In practice, organizations move Altru data into Power BI in one of these ways:
- Manual exports. Run each query, export to CSV or Excel, drop the files in a shared folder, and point Power BI at that folder. It works, but someone has to remember to do it, and the dashboard is only as fresh as the last export.
- An intermediate sheet or database. Query results get pushed on a schedule to something Power BI already connects to, such as Google Sheets, and Power BI refreshes from there.
- API-based options. If you’re considering a custom integration, ask Blackbaud support what API access your Altru subscription includes before you plan around it. Don’t assume it’s there.
Whichever route you take, the query design below stays the same. Good queries make every transport method easier.
The core rule: one grain per query
Before choosing fields, decide what one row means. “One row per membership.” “One row per ticket line.” “One row per revenue record.” Then refuse to add anything that breaks that grain.
The usual offender is a one-to-many field. Add email addresses, phone numbers, or attributes to a revenue query and a donor with two emails suddenly has two copies of every gift. Power BI will add them both up without complaint. If you need contact details, put them in a separate constituent query and let Power BI join on the ID.
The queries worth building
1. Constituents (your main lookup table)
One row per constituent. Include the constituent lookup ID, constituent type (individual or organization), household, ZIP code, city and state, and a “constituent since” or date-added field if you track it. Keep names only if your dashboards actually need them; most BI work doesn’t. Every other query should carry the same constituent ID so Power BI can relate them.
2. Revenue
One row per revenue record or per application, not both. Pick the level that matches how finance reports, and stick with it. Useful fields: revenue ID, constituent ID, date, amount, transaction type, application (donation, membership, ticket sale, and so on), designation or fund, appeal, and payment method. Compare monthly totals against an Altru revenue report before anyone trusts a chart built on it.
3. Memberships
One row per membership term. Include membership ID, constituent ID, program, level, status, join date, start date, expiration date, and whether the transaction was a join, renewal, rejoin, or upgrade if you can surface it. This table drives retention, lapse, and renewal-rate visuals. Keep the raw dates; calculate “active on date X” in Power BI rather than baking a status snapshot into the query, since a status field only tells you how things look today.
4. Ticket and admission sales
One row per order line. Include order ID, constituent ID (blank for anonymous walk-up sales is normal), sale date, visit or event date, program or event, price type, quantity, amount, and sales channel (daily sales, advance sales, online, group). Keeping sale date and visit date separate is what lets you answer both “how much did we sell this week” and “how busy will next Saturday be.”
5. Event and program capacity (optional)
One row per scheduled event or time slot, with date, time, program, and capacity. Joined to the ticket query, it gives you utilization. Skip it if you don’t run timed or capped programs.
Field habits that save rework
- Always include IDs. Names change and collide; lookup IDs don’t.
- Export dates as dates. Avoid formatted text dates and month-name fields. Power BI builds its own calendar table.
- Go easy on smart fields. They’re handy for staff lists, but pre-aggregated values like “lifetime giving” are better calculated in Power BI from the revenue table, where you can see exactly how they were derived. If a smart field is the only practical source, document what it counts.
- Use rolling date filters. Filter on something like “last three fiscal years” rather than fixed dates, so the query doesn’t quietly stop pulling new records.
- Name columns once. Renaming output fields after you’ve built the model breaks visuals. Settle on headers early.
- Limit personal data. If a dashboard only needs ZIP code and giving totals, don’t ship full addresses and birthdates to a shared workspace.
Check the numbers before you share
Do one reconciliation pass per table. Total revenue by month should match Altru’s own revenue reporting. Active member counts should match what the membership team sees. Ticket quantities for a sample week should match daily and advance sales reports. When they don’t match, the cause is almost always row duplication from a one-to-many field or a filter that excluded a transaction type.
Also confirm query performance. Very large queries can be slow or time out when run on demand. If that happens, split by date range or trim fields rather than giving up on the grain.
Automating the refresh
The query design is the hard part; the remaining chore is getting fresh results out of Altru regularly. Social Good’s Data Bridge syncs Altru query results to Google Sheets and other tools, which gives Power BI a source it can refresh from without someone exporting files by hand. Altru remains where the data lives and gets edited; the sheet is just a copy for reporting.
Quick answers
Can I just build one big query with everything in it?
You can, but it will duplicate rows and be hard to maintain. Several narrow queries joined in Power BI are easier to check and faster to fix.
Should totals like lifetime giving come from Altru or Power BI?
Calculate them in Power BI from the revenue table when you can. Use an Altru smart field only when you need its specific logic, and label it clearly.
