Best Of

A very good question you are asking, a question that many would have once they start building many reports out of Power BI.

Let me first start by saying, this part is more of an Art and it is Science.

So let me start with what my setup is, my thought process, and then maybe you can get some idea on how you want to go forward with yours.

I use Azure SQL data warehouse (this is better than using the Blackbaud custom connector for Power BI) but not for reason you are asking your question about. I pull data from the data warehouse. I do not have ONE Power BI data source, I have many, but this all depends on the report I'm building.

• Master Donor Report
  • This pulls in data from constituent table, gift table, constituent custom fields, gift custom fields, and a few others. Modeling on gift is done through soft credit (so not exactly financially good, but it's fundraising good, if you know what I mean).
  • This Power BI dashboard power a donor report that allows various filtering that uses our org's business logic, have heat map, and donor category
  • This Power BI dataset (semantic model) powers 12 other reports that utilize the data in the same data modeling: Donor Retention Analysis; Giving Society (membership), Monthly Giver, etc etc
• Gift Analysis
  • this is like a gift report, where data is model with direct credit and specific additional data pulled in for pledge's detail (installment, payment write off).
  • This report itself is like a filterable report for various business logic we build into how we record our gifts
  • This report's dataset (semantic model) is used by 5 other reports that is more focus on REAL financial numbers: ScoreBoard (goal vs actual contribution), Contribution Source Analysis; Quick Fundraising Matrics, etc)
• Pledge Report
  • this Power BI supports 2 dashboards
    • Pledge report that shows all pledge's commitment, payment to date with column to payment for each year, and write off for each year, balance remaining, and how much is coming in based on installment for next few years
    • Pledge Aging report that tells the story of overdue pledges.

Above are 3 examples of my reports build out. When I need to create a new report for a new business need, my first question is going to be, which of my dataset (semantic model) has all the data I need to build my report, if one exists, I use by connecting my new power bi to the power bi service and attach the semantic model. While I build the new report, I MAY come across additional needs (new measures to display visualization, or new calculated column to filter on, etc). Most of the time, I will just add these new needs directly into the original power bi semantic model.

My reasoning is, I "CAN" put everything into ONE dataset and all reports run through that one dataset. However, that will create a MASSIVE dataset and sometime can be conflicting (direct credit vs soft credit). Duplicated data will make it hard to work within the model too.

The biggest benefits here using Azure SQL data warehouse over Blackbaud custom connector is going to be filtering option and loading time. For example, you cannot filter to get only pledges that has a balance. Azure SQL can easily just give me that using SQL querying. Blackbaud custom connector will need to get all Pledges, then filter down on the balance property. Given the "5000 limit" per call to get data from Blackbaud server, it is a big limitation and performance issue.

I have experience with the Encodian connector and tend to use that for instances like this, depending on your usage the free trial may cover what you need.

Here's more info on the "Free Subscription" for the Flowr/PA Connector API calls - https://www.encodian.com/pricing/

check the user you are using to run SKY API for one of the red circled rights

@Alex Wong that did it! Thank you!!!

Hi Suzie,

Someone more knowledgeable than me will comment on the record limit issue. But I would say that using Power Automate to schedule Query exports to SharePoint, then pointing Power BI at that file (also on a scheduled refresh) give better data options for Power BI reporting. You're in the UK I think so could probably arrange a Teams walk through of that process and the trip hazards I found when I first did it. I'm just using one of the templates kindly shared by I think either Alex Wong/Ashley Moose so its mostly just tweaking those.

Sharing my Power Automate flow: Get query results using the new Query connectors! — Blackbaud Community

Shaun

@Suzie Capps Check out this post where Matt Thacker & Alex Wong discuss the methodology of getting larger data sets: