cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate II
Advocate II

Using a REST API as a data source

I'd like to be able to connect to a Blackbaud REST API to pull data from that system and use it in a Power BI Desktop report: http://on-api.developer.blackbaud.com/getting-started/

 

That said, I don't have experience using REST APIs.

 

Is that's possible, either for a live data connection or for a scheduled refresh? If it's not possible in Power BI Desktop, would it be possible if I managed the report on the Power BI Service?

 

Further, if that's possible, could someone give me a quick and dirty of how to accomplish that and perhaps point to toward the materials I need to read to figure out how to do it? Part of my misunderstanding here is how the Blackbaud and Power BI REST APIs interact (if at all).

 

I have an E5 license that allows me access to Power BI Pro features.

1 ACCEPTED SOLUTION
Microsoft
Microsoft

@caseyh

 

I don't know the Blackbaud REST API in your case and it is not in the list of suppported "Online Service".

 

But according to the content in the link that the API reponses in JSON, you can get the data by using "Get Data"-->"Other"-->"Web". And then extend JSON to a table in query editor.

 

eg. http://jsonplaceholder.typicode.com/posts/1/comments

You can replace it with a link like "http://[school].myschoolapp.com/api/academics/department/?t=[token returned from previous step]" in your link.

Capture.PNG

 

In the query editor,

Capture.PNG

 

Capture.PNG

Done

Capture.PNG

 

Regarding refreshing, you can publish it to Power BI Service and schedule refresh.

View solution in original post

52 REPLIES 52
Helper I
Helper I

ZappySys has introduced powerful API Drivers to query virtually any JSON / XML API services in Power BI (e.g. Zendesk, Twitter, Google API, Amazon AWS)

Check this blog for more info:  https://zappysys.com/blog/howto-import-json-rest-api-power-bi/

 

Basically, Calling REST API in Power BI can be a challenging process because you have to deal with many complex issues. If its simple JSON / XML then fine but in the real world, many other issues arise such as pagination, flatten the hierarchy, error handling, security (OAuth, Basic, Certificate). 

 

Hope this helps!!

 

Step-1 : Create your ODBC DSN to call REST API or read JSON / XML dataStep-1 : Create your ODBC DSN to call REST API or read JSON / XML dataStep-2 : Build / Test your REST API QueryStep-2 : Build / Test your REST API QueryStep-3: Import REST API data in Power BI using ODBC connection (Under Get Data >> More >> Other)Step-3: Import REST API data in Power BI using ODBC connection (Under Get Data >> More >> Other)

 

 

You can write queries like below (JSON Driver Example)

 

SELECT * FROM $
WITH(
src='https://service4.ultipro.com/services/EmployeePerson'
,Header='Authorization: Basic c29tZXVzZXI6c29tZXBhc3dvcmQ= || US-Customer-Api-Key: ABCDE'
,RequestMethod='GET'
)

 

 

 

New Member

I am new to BI. I am using REST API as data source, I am trying to get list of sensors readings to BI but the API only allow to call 1 week readings period if i try to call the readings for 1 month period the API will not return any result, 

 

Question:

Can I create a dataset which will keep all historical readings so that I can call the API on daily basis and it saves all new readings permenantly?

let
Source = Sql.Database("localhost", "AdventureWorksDW2012"),
dbo_DimEmployee = Source{[Schema="dbo",Item="DimEmployee"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_DimEmployee, each [HireDate] >= #date(2000, 1, 1) and [HireDate] <= #date(2002, 1, 1))
in
#"Filtered Rows"

 

You can then change hard-coded values to parameters. More info here: 

 

http://blog.pragmaticworks.com/power-query-controlling-m-query-functions-with-user-driven-parameters

Microsoft
Microsoft

@caseyh

 

I don't know the Blackbaud REST API in your case and it is not in the list of suppported "Online Service".

 

But according to the content in the link that the API reponses in JSON, you can get the data by using "Get Data"-->"Other"-->"Web". And then extend JSON to a table in query editor.

 

eg. http://jsonplaceholder.typicode.com/posts/1/comments

You can replace it with a link like "http://[school].myschoolapp.com/api/academics/department/?t=[token returned from previous step]" in your link.

Capture.PNG

 

In the query editor,

Capture.PNG

 

Capture.PNG

Done

Capture.PNG

 

Regarding refreshing, you can publish it to Power BI Service and schedule refresh.

View solution in original post

Hi

 

Please can you help me find a solution. I tried what you suggested above, but it does not work for me. Note that I am not very experienced in doing this and this is my first time.

 

The provider gave me the below details:

 

When you are ready to begin, use the following URL for the API: https://resellers.accounting.sageone.co.za/api/2.0.0.

We use basic auth for authentication API calls and you would use the same login that you created in the step above.

 

Your API key for the Sandbox environment is: (they gave me a key).

 

I entered the URL above into the Web connector box in PowerBi. Then it gave me several options to choose from: Anonymous, Windows Credentials, Basic, Web API and Organisational Account.

 

I tried, anonymous, Basic and Web API options, none of them worked. Each time I did it, the loading preview window opened, and did not load, even after waiting 15 minutes.

 

I then also closed the window and tried to look for the “to table” option in your solution, and I could not find this either or it was greyed out.

 

Please can you help me find out how to get connected. @caseyh , did you get this to work for you? – did this solution work?

 

Please let me know.

 

Thanks.

@Eric_Zhang and @caseyh Here's some sample code from the Advanced Query editor in Power BI desktop that would work for making an API call to Blackbaud's ON products, get and store the token from Blackbaud API as variable (NOTE: these are fakes URLs, usernames, passwords, and SLI id for list, but I promise it works in Power BI desktop just be sure your user has access to the Web Services API Manager role and that the list you're pulling from also gives access to the Web Services API Manager role and/or your username).

 

let
Source = Json.Document(Web.Contents("https://2018036.myschooltraining.com/api/authentication/login?username=jdoe&password=openthedoor&for...")),
T = Source[Token],
GetList = Json.Document(Web.Contents("https://2018036.myschooltraining.com/api/list/99999/?t=" & T & "&format=json"))
in
GetList

 

@Eric_Zhang can you tell me why this query can't refresh in power bi service - only desktop? I get error message saying this data source can't be refreshed in Power BI online.

I had rest API Call working in Power BI Desktop but its give me error when I push it on power BI Service that it can't be refreshed. Anybody refreshed succesfully REST API DataSource in Power BI Service?

I think you can schedule a refresh on the dataset once you publish a report and dataset is created. 

I've also managed to get Power BI to communicate with the Blackbaud Sky API for Raiser's edge NXT. It can be refreshed via Power BI Desktop, but not via the service. The error message is: "Data source error: Unable to refresh the model (id=2519924) because it references an unsupported data source."

 

The other problem I have is that of token refresh. Having to manually generate an OAuth token every hour or so, and plugging that into a parameter in Power BI Desktop to be able to refresh data is a real pain.

@Quicky yes I still haven't found a way around the error in Power BI service. But you can write a variable that will ask, get, store, and enter your token for you. Check this out: http://duff.io/2017/07/17/using-microsoft-powerbi-with-the-blackbaud-api/ 

Cheers GGetty. The Blackbaud Sky API appears to have a different authentication, and instead of returning a Json, the access token is returned in the URL itself when using Implicit Flow: https://apidocs.sky.blackbaud.com/docs/authorization/implicit-flow/ although it does return a Json containing the token when using Authorisation Code Flow https://apidocs.sky.blackbaud.com/docs/authorization/auth-code-flow/ but only after you've processed the access code in the initial call to the authorisation service, which returns an access code in the return URL again. Both methods require the user to authorise the access via clicking an Authorise button.

 

My Power Query knowledge isn't great; do you kow of a way to automate that? Send a request to the Blackbaud authorisation page, wait for the user to authorise, and grab and process the returned URL? It's annoying that the authentication methods differ between the APIs!

 

At the moment I'm using a PowerShell script to perform the actions and return the token, which I'm then manually copying and pasting into the authorisation parameter in Power BI. The script also optionally queries the api and exports the returned data into a JSON file which I'm half tempted to use as the Power BI data source rather than querying the API directly since there's so many hoops to jump through.

 

I reckon I could modify the PowerShell to run on a schedule and programmtically "click" the link to get the access token. It's a shame though because the Power Query script I'm using at the moment nicely handles the API pagination of the endpoints (I modified this: https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78) and it would save having to have a separate datasource. That said though, if I did do that, it would likely solve the problem of the datasource not being able to be refreshed in the Power BI Service.

@Quicky you would want to write an advanced query in the M language. The token is part of the URL in K12 API as well. Try something like this:

 

let
Source = Json.Document(Web.Contents("https://YOURURLHERE/api/authentication/login?username=QUICKEYDOE&password=12345&format=json")), //SWITCH FROM JSON TO WHATEVER YOUR API RETURNS//
T = Source[Token],
GetList = Json.Document(Web.Contents("https://YOURURL2HERE?",[Query = [t=T, format="json"]])),
#"Converted to Table" = Table.FromList(GetList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"FirstName", "LastName", "EMail", "NickName", "BadEMail", "PushpageUnsubscribe", "ChildFirstName", "ChildLastName", "ChildGradYear", "ChildGender", "ChildGradeLevelDescription"}, {"FirstName", "LastName", "EMail", "NickName", "BadEMail", "PushpageUnsubscribe", "ChildFirstName", "ChildLastName", "ChildGradYear", "ChildGender", "ChildGradeLevelDescription"})
in
#"Expanded Column1"

 

I think separating out the login / authentication from the target request to pull data will get you what you need.

 

This leads to two queries listed as sources in your Power BI desktop file Data Sour Settings that look like this outside of advanced query editor:

 

powerbi_sources.png

Hi Quicky (love the name).

 

We started trying to connect to PBI directly through the Sky API and quickly came to the realization that it was not designed for that.

 

We've built a reporting data warehouse product that provides a proper data model to connect and build with PBI. It uses the APIs to refresh and we've been working with BB to incorporate some additional missing end points.

 

We will be releasing the product later this year. Contact me directly if this is something that would be of interest to your organization.

 

Thanks,

William

Calls from Power BI into REST APIs still have a purpose. Two things I want to note: 1. http://on-api.developer.blackbaud.com/ (Blackbaud K12 API) and https://developer.sky.blackbaud.com/ (Blackbaud RE / FE API) are different APIs but both useful with Power BI (you can also do direct query calls from within RE if your SQL is good enough). 2. Power BI as a tool to tell a story with your data or explore your data is just as important (maybe more important) than an entire, complicated extract-transform-load > data warehouse model > reporting solution like @williamdasilva describes. I think the entire data warehouse and reporting model begins to compete with Blackbaud's own SKY Reporting / Insights available through analyze (currently using GoodData). I'm much more interested in EVERYONE, ANYONE being able to tell a story with their data and get from a query output / advanced lists / export into Power BI and explore. I think it will be great when folks want to see all of last year's donors at $5000 and up and they start with the question, "How can I SEE this visualized in Power BI?"

@GGetty, I could not agree more! In fact, that's exactly what we are trying to offer through our ETL DW, and easy way for customers to plug PBI onto their RE NXT instance. This whole thread is case and point that it's too complicated right now.

 

The new insights through GoodData is great. In fact, we've been using it (in beta) since about April and were hesitant to release a DB product because of this included analysis tool. Our take on things is that the built in insights will work for most orgs using RE NXT. It's still missing key end points, but BB should add them in as demand for them comes in. What it will never do is more custom reporting for those complex orgs that want to cross key data points that are not/will never be available throuhg insights. Perhaps it's grouping on a subset or prefix of an appeal code structure, or something that requires a littlle data massaging before visualization. 

 

In short, we are providing the easy way to plug PBI onto RE NXT... for those that need PBI.

@GGetty Thanks for that, but I'm not sure that it would work from what I've read so far, since the Sky API uses OAuth v2 for authentication. At this point I'm veering towards the creation of a custom data connector, but even if one was to be created, it would only work in Power BI Desktop and not the service. Microsoft have provided some example data connectors and this one https://github.com/Microsoft/DataConnectors/tree/master/samples/MyGraph implements OAuth v2 and isn't exactly trivial!

 

@williamdasilvaThere's definitely scope for a proper warehouse implementation with Power BI for RE NXT. My org hasn't even completed the migration to RE, but I'm trying to get ahead of the game - from what I've seen of the Sky Reporting tool, it is substantially more limited than is required by BI teams, particularly with respect to the examples you've given, as well as the opportunity to incorporate separate data sources.

@Quicky preaching to the choir! 

The GoodData/Insights that @GGetty was referring to are great. We've been told BB is set to go live with Insights somewhere around BBCON this fall. They will be great for building regular dashboards and other day to day reporting for a smaller orgs. The insights are also great for major gift dashboards as they have opportunity endpoints available (a few still missing). But try to build a dashboard around a direct mail performance, pulling in Appeal information and calculating response rates and you will hit the limit of insights quite quickly.

Just for info, tonight I built a custom data connector to the SKY API. I followed the tutorial here: https://github.com/Microsoft/DataConnectors/tree/master/samples/MyGraph and tailored it to fit - the example provided didn't actually need much work to get it to function. Once built and deployed, it now appears as a connector in the Get Data menu in my install. It solves the problem of having to update the token periodically, as it's performed automatically, but it won't solve the issue of the data source not being refesh-able in the service because the service doesn't yet support custom data sources (although I believe it's on the roadmap).

 

The connector I've build currently only returns data from the constituents list endpoint (just a proof of concept for now), but it will handle paging and return all records. Unfortunately I haven't been able to test it too heavily since as I mentioned, we're yet to transition to Raiser's Edge, and so I'm curently working with a database of only 10 records! I suspect for larger orgs (and mine when we finally transition), there may be some work required to sort out any quota limitations, but it should just be case of introducing a delay inbetween calls.

 

I haven't yet parameterised it so it's running with my app credentials hardcoded within it, otherwise I'd host somewhere tonight to get some feedback on performance/issues from any other RE users who might be interested in the connector. Annoyingly, I'm off on holiday for a couple of weeks so I'll have to put further development on hold until I get back - the other half will kick my ass if I fire up my laptop abroad 😉 That said, it wasn't too difficult a process to undertake if anyone is interested in making one themselves.

@Quicky @caseyh @williamdasilva Can anyone convince Blackbaud to just build connectors for Power BI for both the ON API and SKY API? My dream scenario would be direct query via Power BI on built-in connectors created by Blackbaud - just like I can do with my Mailchimp data.

 

Imagine how great it would be to just click on GET DATA and see this listed under Online Services: 

 

Blackbaud APIs as services in Power BI.png

@GGetty Love the screen shot! Had me fooled for a sec!

That would definitely help convince the powers that be. BB is a publically traded company, there needs to be positive ROI. They also listen to their customers, so if there's a need for it and a positive ROI, then it might get put in their pipeline!

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors