At the recent Blackbaud K-12 Conference, I spent some time with Grahm Getty who did a presentation on using PowerBI with the Blackbaud ON products.  The ability to visualize your data is really powerful and can be used to find insights and problems in your data.  Grahm and I spent some time figuring out how to use the Blackbaud API to get the data in real-time into PowerBI so that it is always up-to-date and doesn’t require exporting and using CSV files.  We were able to get this to work with the desktop version of PowerBI, but it doesn’t seem to work with the web version.  I haven’t given up on that, but for now I thought I would share a tutorial on how to get it to work with the desktop version of PowerBI.  What follows is a tutorial that assume you know nothing of the Blackbaud API nor Microsoft PowerBI.

Prerequisites
This tutorial requires:

1. You have the Blackbaud API enabled.  There is an annual fee for this, so you need to contact your account manager if you do not already have it.  It comes bundled with ODBC access, so if you have that, then you have API access.
2. A machine running some modern version of Windows.  It can be emulated in VMware or Parallels if your a Mac user.  PowerBI requires Windows.

Setup within the Blackbaud “ON” products

1. Create a new user within the “ON” products to use to make API calls.  It is best practice to use a separate account to make API calls.
   1. As a Platform Manager, go to Core>Users/Access>Profile
   2. Click “Add New User”
   3. Fill out the Last Name, First Name, Username and pick and confirm a temporary password.  I used “PowerBI” as the First Name, “API” as the Last Name and “powerbi-api” as the username.
   4. In the screen that follows, edit the user.
   5. Under “System Information” for the user we just created, click “Role Membership”.
   6. Click “Edit”.
   7. Enable “Web Services API Manager” and click on “Save & Exit”.
   8. Sign Out of the ON products and login with your newly created account.
   9. When you login, you will be forced to change your password.  Change it.
2. Sign Out of the ON products and login to your normal account.
3. Go to lists (Faculty>My Day>Schedule & Performance>Lists>View All).
4. At this point, we need to create the list that we want to use in PowerBI.  For the purposes of this demo, we will use one based on a list template.
   1. Click on List Templates.
   2. Under “Template Category” pick “Constituent Information”
   3. Find “Students by Current Grade” and click on “View/Copy”
   4. Under name, give it a name (I used “PowerBI student information”)
   5. Let’s add Ethnicity.
   6. Click on the “Select Objects” tab.
   7. Under “Constituent Information”, click “User Citizenship/Residency”
   8. Click on the “Display Fields” tab.
   9. At the bottom of the screen, click on “Select Fields…”
   10. Expand “User Base” by clicking on the +
   11. Expand “User Citizenship/Residency”
   12. Check the checkbox next to “Ethnicity” and “Citizenship” and then click the “Select” button.
   13. Click the “Preview” button on the bottom right of the screen to preview the list.
   14. Click “Save & Exit” on the top right.
5. Next we need to get the list ID number so that we can use it in an API call.  This ID number is somewhat hidden.
   1. Control-click on the “Run” link next to the list we just created.
   2. Select “Copy Link” from the pop-up menu that appears.
   3. Go to the text editor of your choice and paste the link.  (note: if you only see “Run” when you paste you’ll need to paste it as unformatted text (on a Mac: command-option-shift-v).
   4. You should see something like: javascript:__pdL(‘52586′,’Advanced%20List:%20API%20-%20list%20example’,%20’1′,%20’~slid=49748~ml=False~sln=API%20-%20list%20example’,%20”,%20’False’,%20’0′,%20”,%20’default.aspx’)
   5. after “slid=” there is a number (in the example 49748).  This is the list ID.  Make a note of your list ID.
   6. Next we have to grant the API account we setup earlier with access to the list we want to use.
      1. Click on the “User Access” link to the right of the link. 
      2. Click on “Add User(s)”.
      3. Search for the API user you created.  (I searched for last name of “API). 
      4. To the left of the API user in the search results, click on the blue “>>” link to move it to “Added Users” and then click on “Save & Exit”.
      5. Leave it with the defaults of “Run” and press “Save & Exit” again.

Microsoft PowerBI Setup

1. If you do not have the desktop version of Microsoft PowerBI installed on your Windows machine, go to https://powerbi.microsoft.com and click on “Sign up for free” in the top right.  Then click “download free”.  Install it on your machine.
2. Launch PowerBI Desktop (this takes a while on my machine).
3. Cancel out of the splash screen. 
4. Press the “Edit Queries” button in the tool ribbon. 
5. Press the “New Source” button and then select “Blank Query” and press the “Connect” button.
   
6. Right click on the new query that was created and select “Advanced Editor”.
7. PowerBI makes use of a query language that can be used for advanced queries.  Because of how the Blackbaud ON API handles authentication, we need to use this advanced query language.  Copy the code below into the advanced editor, replacing the code in red with your school’s ON website address, API username/password, and the list ID.  When you edit the text, make sure you don’t use smart quotes.
   

   let
   Source = Json.Document(Web.Contents(“https://myschool.myschoolapp.com/api/authentication/login?username=apiusername&password=apipassword&format=json”)),T = Source[Token],
   GetList = Function.InvokeAfter(()=>Json.Document(Web.Contents(“https://myschool.myschoolapp.com/api/list/listIDnumber/?t=” & T & “&format=json”)), #duration(0,0,0,5))
   in
   GetList

8. Click on the “Done” button in the Advanced Editor. 
9. At this point there are a few setting changes related to the PowerBI security model that I don’t quite understand. Next to “Please specify how to connect” press “Edit Credentials.”
10. Pick “Anonymous” from the list on the left and press “Connect”. 
11. Next, you should see an alert “Information is required about data Privacy.”  Press the “Continue button. 
12. In the pop-up to the right of your website URL pick “Public” then press “Save”.
13. You should then see results as a column of “Records”.  We will need to expand those. 
14. Right click on the column header and select “To Table”.
15. Then an icon will appear on the right side of the column header.  Click on it and you will be able to pick what columns of data you want to view from the record.  Uncheck “Use original column name as prefix” and then click “OK”.
16. Press the “Close & Apply” button on the left side of the tool ribbon.
17. Now it’s time for us to build our data visualization!
18. On the right side of the screen, click on the first icon “Stacked bar chart”.
19. From the right side of the screen, drag the “GradYear” field to the Axis box, and drag “Gender” to “Value” and then drag it again to “Legend”.
    
20. We should now see a chart.  My sample data is pretty boring.
21. Make sure you’ve clicked on the chart and then click on the paint roller icon.
22. Click on “Data colors” on the right and pick a light pink for “F” and a light blue for “M”. 
23. Turn on “Data Labels”, change the font to a larger size and switch the position to “Inside Center”.
24. Resize the box around the chart to make it bigger.
    
25. Now click on the stacked bar chart icon again, and put “GradYear” in the axis and “User ID” in the value field.  Note that it automatically switches “User ID” to “Count of User ID”.
26. Next click on the “Card” icon (it has the 123 on it).  Drag User ID to it’s value box and click on the triangle next to it to switch it to “Count of User ID (Distinct)”.
27. Now try clicking on one of the graduation years in the chart of them and notice how the count we just added changed and what it did to the other chart.
28. Finally save your file.