Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Eric_Zhang
Employee
Employee

@Anonymous

 

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

56 REPLIES 56
eLrOs
New Member

Many years ago, but this is an actual/functional solution:

 

https://pbi-guy.com/2023/05/21/create-a-post-request-with-power-bi/

ZappySys
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'
)

 

 

 

iotplus
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

Eric_Zhang
Employee
Employee

@Anonymous

 

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.

Hi,

 

I am getting below error::

Asha_2021_0-1630308074852.png

 

Hi,

 

I am getting below error::

Asha_2021_0-1630308074852.png

 

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. @Anonymous , did you get this to work for you? – did this solution work?

 

Please let me know.

 

Thanks.

@Eric_Zhang and @Anonymous 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&format=json")),
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.

@GGetty will this work for BB K-12 school APIs? I'm trying to be able to run this through Power BI or export using Power Automate. 

Blackbaud SKY developer website 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors