PowerBI Gateway - RENXT

@Mark Palfrey
I don't use power bi connetor for my 30+ power bi dashboards/reports, so no gateway needed.

I use an Azure SQL server as a data warehouse. Every 4 hours (i.e. 5am, 9am, etc), I have 20+ power automate flows that does either full sync (i.e. fund, appeal, etc) or iterative sync (i.e. constituent, gift, FE transactions distribution, etc) of all data needed. All my power bi connects to the azure sql data warehouse to get data needed, refresh of the power bi is also on every 4 hours, 1 hour after power automate sync the data (i.e. 6am, 10am, etc).

There is also a cost to using azure sql server, but from our perspective it is minimal compare to the benefits we get from all the automation and reporting that can be done.

I don't know what your cost is for the gateway route, so you will need to gauge that talking to your IT team.

@Mark Palfrey
I see no drawback with the data warehouse route I take as compare to using Power BI connector, only benefits.

Pro:

• data sync happens every 4 hours, so with the data warehouse sync and updated, no matter how many power bi dashboard/report I create, it will not affect our daily API limits.
  • Power BI connector will have a hard time and more “loading” the more reports are created. UNLESS, you have ONE main Power BI that loads/refresh all the data needed by all reports one time, and multiple power bi referencing and using this power bi dataset, still very limiting
• there are other data that only comes available using “SINGLE API" call (i.e. constituent request no email field, constituent no valid address field, appeal attribute, fund attribute, etc), which is VERY time consuming to do using power automate flow and SKY API, (and eats up a lot of API quota), for those, I uses RE:Queue scheduled export of data from database view and process using power auto flow SFTP connector to get CSV into data warehouse, meaning, ALL data in 1 place for querying.
  • Power BI connector will be very difficult to do this

Con:

• much more technically involve to setup, Power BI connector is much easier to use for no/low-coder without caring about performance, API quotas, etc. (again as long as not creating a LOT of reports that using some of the same data point)
• Time
  • a lot of issue has to be resolved b/c of how the API works and poor documentation, for example:
    • initially I was doing one full constituent sync (400K+ record) and then use iterative sync. however, then I realize this isn't good enough b/c deleted record will not be removed from data warehouse, so then I have to setup webhook for constituent delete, and delete from data warehouse
    • initially same I was doing for gift (which is 4 separate SQL table: gift, gift split, gift soft credit, and gift solicitor), but have even more problems (i.e. if constituent is merged/deleted, the soft credit constituent id is changed, however, the gift is not consider “changed” so will NOT show up in iterative sync). Spent a huge amount of time thinking through how to use minimal processing to keep all data accurate

If you are able to spend the time and has the technical know-how (or has IT team that has that expertise), data warehouse is much better. I know you already mention cost, but I will say that data warehousing of RE and FE data is also available from a few partners of Blackbaud, but with a high premium cost.

@Oscar Ira
I don't have any link or written down steps on setting azure data warehouse with RE NXT data via Power Automate. I think i might have talked about it in many of the power automate user group previously, but I don't know if that has been cateloged @Erik Leaver maybe able to help

@Alex Wong:

@Mark Palfrey
I see no drawback with the data warehouse route I take as compare to using Power BI connector, only benefits.

Pro:

• data sync happens every 4 hours, so with the data warehouse sync and updated, no matter how many power bi dashboard/report I create, it will not affect our daily API limits.
  • Power BI connector will have a hard time and more “loading” the more reports are created. UNLESS, you have ONE main Power BI that loads/refresh all the data needed by all reports one time, and multiple power bi referencing and using this power bi dataset, still very limiting
• there are other data that only comes available using “SINGLE API" call (i.e. constituent request no email field, constituent no valid address field, appeal attribute, fund attribute, etc), which is VERY time consuming to do using power automate flow and SKY API, (and eats up a lot of API quota), for those, I uses RE:Queue scheduled export of data from database view and process using power auto flow SFTP connector to get CSV into data warehouse, meaning, ALL data in 1 place for querying.
  • Power BI connector will be very difficult to do this

Con:

• much more technically involve to setup, Power BI connector is much easier to use for no/low-coder without caring about performance, API quotas, etc. (again as long as not creating a LOT of reports that using some of the same data point)
• Time
  • a lot of issue has to be resolved b/c of how the API works and poor documentation, for example:
    • initially I was doing one full constituent sync (400K+ record) and then use iterative sync. however, then I realize this isn't good enough b/c deleted record will not be removed from data warehouse, so then I have to setup webhook for constituent delete, and delete from data warehouse
    • initially same I was doing for gift (which is 4 separate SQL table: gift, gift split, gift soft credit, and gift solicitor), but have even more problems (i.e. if constituent is merged/deleted, the soft credit constituent id is changed, however, the gift is not consider “changed” so will NOT show up in iterative sync). Spent a huge amount of time thinking through how to use minimal processing to keep all data accurate

If you are able to spend the time and has the technical know-how (or has IT team that has that expertise), data warehouse is much better. I know you already mention cost, but I will say that data warehousing of RE and FE data is also available from a few partners of Blackbaud, but with a high premium cost.

Hi Alex,

This is fascinating, and incredibly helpful. I currently have a semantic model that uses scheduled refreshes with Power BI to the Blackbaud connector and some files in SharePoint. I noticed that we cannot filter out the API calls when using the Blackbaud connector (only pull gifts after this date). Is each refresh pulling ALL available items from the Blackbaud connector (for example, all constituents in the constituents connector), and THEN filtering them in the applied steps in Power Query? If so, I see what you mean about API limits and quotas.

@Lawrence Kinkopf, you can use a Gift List or Constituent List to limit the gifts or constituents you are pulling in the Power BI Connector. Note the connector's navigation lets you select Public or Private lists. This feeds into the respective end point's “list_id” parameter. For example: API Reference - GET Gift List - SKY API.