So, I realize this is duplicating efforts. I expect “real developers” will find this tiresome. However, lots of folks have asked me for help getting Grant Quick’s awesome custom connector for Blackbaud’s SKY data up and running for their organization, so this is my more-detailed, no-assumptions, illustrated, 20-step version of the original setup instructions.
 
I’m also planning a screencast that will eventually live at the end of this post that shows the whole procedure – invite to Blackbaud RE NXT > developer account > app > custom connector (don’t worry, I’ll regenerate the secret keys and ids). Also, if it makes Grant or real developers feel better, I did write the rest of this post in markdown….

BlackbaudSkyApi

A Power BI custom data connector for the Blackbaud SKY API. Currently supports Raiser’s Edge. Financial Edge support may be added in the future. The connector is not owned, developed or supported by Blackbaud, and as such is defined by Power BI as an uncertified connector.

Getting Started – Detailed Documentation

This version of the setup instructions are written in superfluous obsequious extreme detail for non-techncial users. Developers and more tech-savvy folks who are used to unzipping files and renaming file extensions may wish to stick with Grant’s original README.md on Github.

These instructions will describe how to configure the SKY API connector in order to connect to your organiszation’s data. See Microsoft’s guide to installing and using custom data connectors in Power BI here.

You will need to create and register an application with SKY API for the purpose of generating the requisite IDs used when making a connection to your data. A Blackbaud developer account is required in order to create an application. Once an application has been created, you will need the client_id of the application, the client_secret of your application, and your developer account’s api_subscription_key.

Please note Blackbaud calls the client_id the Application ID and the client_secret the Application secret. In your Blackbaud Developer account, once you create the application, the Application ID will always show and you can get the Application secret by clicking on Show

Your api_subscription_key can be found under the My profile link once you are logged in to https://developer.sky.blackbaud.com. The api_subscription_key is just your SKY API Standard Edition subscription Primary Key

Setting up a Blackbaud developer account

Follow the instructions at https://apidocs.sky.blackbaud.com/docs/getting-started/ to create a developer account and acquire your api_subscription key (remember, this is just your SKY API Standard Edition subscription Primary Key)

Creating an app

Follow the instructions at https://apidocs.sky.blackbaud.com/docs/createapp/ to register and activate your app. Registering your app will generate a client_id and client_secret for the app. Ensure that at least one of the Redirect URLs for the app you create is set to https://oauth.powerbi.com/views/oauthredirect.html

Installing the connector

1. If one does not already exist, create a [Documents]\Power BI Desktop\Custom Connectors directory.
   Please note some versions may reference this directory as [My Documents]\Microsoft Power BI Desktop\Custom Connectors – either version should work, but Microsoft recommends [Documents]\Power BI Desktop\Custom Connectors
2. Download the SkyApi.mez file from this repo by clicking the Download ZIP link that appears after clicking the Clone or download button near the top of the BlackbaudSkyApi Github page https://github.com/GrantQuick/BlackbaudSkyApi
   
3. For most users, this will put the file in your Downloads folder, which you can access by right-clicking the file and selecting “Show in folder”
   
4. Double-click on BlackbaudSkyApi-master.zip then click the Extract all icon in ribbon
   
5. Accept default location [should be a sub-folder in your Downloads folder called BlackbaudSkyApi-master] and accept prompt to show Extracted files when finished
6. Double-click the BlackbaudSkyApi-master folder inside of This PC\Downloads\BlackbaudSkyApi-master\BlackbaudSkyApi-master and don’t fret about all the nested, similar folder names. Now’s a good time to make sure you can see File name extensions, so check that box in the menu ribbon
   
7. Right-click on the SkyApi.mez file, select Rename and rename to SkyApi.zip
   
8. Select Yes when warning appears about renaming file extensions
   
9. Hold down Windows Key and type letter e on your keyboard to launch another Windows Explorer instance, Go to your Desktop and create a new folder called SkyApi. You should now have two windows open
   
10. Double-click the SkyApi.zip, select Extract All and select the SkyApi folder your created on your desktop as the Destination when prompted
11. Congratulations – we can now edit the files and add in your specific Blackbaud SKY API subscription, Application ID, and Application secret. To do this, start by double-clicking the api_subscription_key and select Open with Notepad to enter your keys and ids
12. Enter the appropriate values or keys, remember api_subscription_key = your SKY API Standard Edition subscription Primary Key, client_id = Application ID, client_secret = Application secret. Close each file in notepad after entering value and click save when prompted but DO NOT give files an extension
13. Select all files, then right-click on one of the selected files and choose the option Send to > Compressed (zipped) folder and be sure to name/rename the resulting .zip file SkyApi.zip
14. Change the extension on your brand-new SkyApi.zip back to SkyApi.mez
15. Copy the SkyApi.mez file you just created and put it in your [Documents]\Power BI Desktop\Custom Connectors directory
16. Enable the Custom data connectors preview feature in Power BI Desktop (under File | Options and settings | Custom data connectors)
17. As of the July 2018 release, Power BI will additionally alert users to change their security settings in order to enable uncertified custom data connectors to be used. To do this, go to File | Options and settings | Security, and under Data Extensions, enable (Not Recommended) Allow any extension to load without validation or warning
18. Restart Power BI Desktop
19. In Power BI Desktop, click Get Data > Other > Blackbaud SkyAPi (Beta)
20. The first time you use the connector you will need to log in using your Blackbaud account and authorise the app to work with your data

Supported Endpoints

Additional endpoints will be added in time. Currently the connector supports:
* Constituent list
* Phone list
* Email Address list
* Address list
* Education list
* Constituent Code list
* Online Presence list
* Relationship list
* Constituent Custom Field list
* Gift list
* Gift Custom Field list
* Appeal list
* Campaign list
* Fund list
* Opportunity list
* Notes list
* Action list
* Memberships list

Additional Information

The connector will only generate a barebones data model. List or record type data fields will have to be expanded when designing your own data model. For example, when connecting to the Gifts endpoint, in Power BI you will need to click Edit Queries in order to expand and view the gift amounts.

Handling of rate limiting has now been implemented, meaning that the connector will now gracefully wait and retry a call following a 429 error.

When using the connector, it is recommended to only select the endpoints that are necessary for any given reporting purpose. Endpoints that are not required as part of a specific Power BI dashboard/report should be omitted from your connection as they will introduce superfluous calls to the SKY API, and could cause throttling/quota issues. The Actions endpoint, for example, may be unnecessary for most reporting purposes and may include large a large number of records. If this is the case in your organisation, including the Actions endpoint will result in a significant increase in the time it takes to refresh your data as well as cause a substantial increase in consumed bandwidth.

Scheduled Refresh – Power BI Service

The connector now supports scheduled refresh through the Power BI service via a Power BI On-Premises Data Gateway (Personal mode). In order to take advantage of this, the following steps need to be performed:

1. Install the Power BI On-Premises Data Gateway in Personal mode
2. Enable Custom Connector support in the Gateway – see guide here
3. Publish a workbook that uses your connector to PowerBI.com
4. Configure scheduled refresh – see guide here and follow the instructions from After publishing, go to PowerBI.com and find the dataset…

Known Issues

Blackbaud have recently added a list endpoint for an optional module, the Membership list. Support for this endpoint has been added to the connector, but limited testing has been performed as this is not a module which is available in my organisation. Issues/feedback on this particular endpoint are welcome. For all organisations that have not opted to purchase this additional module from Blackbaud, if the Memberships endpoint is selected by the user during the Get Data process, Power BI will produce an error stating that Access to the resource is forbidden as the connector attempts to preview data. This means that your Blackbaud account does not have access to that specific module. Other endpoints will be unaffected, and can be selected as usual. This will be the case for all optional modules for which Blackbaud subsequently add SKY API support, where that module is not available in your organisation.

Depending on dataset size, data load time can be long, although Blackbaud have improved the speed of call processing in recent months. As a maximum of 500 records can be returned by any single call to an endpoint, datasets of several hundred thousand records will require many hundred calls to the API. The API also employs rate limiting to prevent overload of Blackbaud servers. This can mean that data loads of all endpoints will take several minutes. Connecting to only the required endpoints will reduce data load time.

As an indicator of expected performance, the connector has been tested with an initial concurrent load on all (currently supported) endpoints on a database with the following record counts per endpoint:

• 150,000 addresses
• 100,000 constituent codes
• 350,000 constituent custom fields
• 100,000 constituents
• 100,000 education
• 40,000 emails
• 1,000 online presences
• 55,000 phones
• 45,000 relationships
• 30 appeals
• 6 campaigns
• 70 funds
• 1,000 gift custom fields
• 50,000 gifts
• 100 opportunities

In this time there were 2,200 calls to the API, which used 370MB of bandwidth.

Authors

• Grant Quick – Initial work – GrantQuick

Acknowledgments

• This connector is based on the custom data connector samples provided by Microsoft
• Thanks to Microsoft’s CurtHagenlocher for the Web.ContentsCustomRetry function.

Sub-Author / Hanger-on

• Graham Getty – Added overly-detailed instructions and lots of screenshots for beginners. Errors and added confusion are, no doubt, on me – Graham Getty

Eventual, All-Encompassing Screencast Will Be Posted Here Soon!