Reply
Regular Visitor
Posts: 15
Registered: ‎09-11-2017

Re: Using a REST API as a data source

[ Edited ]

@GGettyNo worries, thanks for the feedback and the localhost tip. I've updated the git readme to include a note about setting the correct Redirect URL when creating your app.

 

For info, the app creation part of the tutorial is purely for the purposes of generating the necessary IDs. Blackbaud employ rate limiting and quotas on the API, meaning that if I were to share the application ID for the app I made, the limiting/throttling would be hit very quickly as the number of API calls would be the sum of all calls made by all organisations using the app. Creating your own app means that any limiting is only based on calls you have made locally in your organisation.

 

I'll add in as many list endpoints as I can in the coming days, before I start looking at the endpoints that take parameters (those that require a specific constituent ID to be passed in the header, for example). They're likely to add a fair bit of complexity to the connector!

Regular Visitor
Posts: 15
Registered: ‎09-11-2017

Re: Using a REST API as a data source

@GGettyI've updated the connector to include the gifts endpoint now, if you want to download the new mez file and give it a go.

 

I won't keep flooding the thread here with every minor update, but being able to report on gifts is fairly significant for us!

Regular Visitor
Posts: 23
Registered: ‎12-22-2016

Re: Using a REST API as a data source

[ Edited ]

@Quicky this is great. I got the new mez working and can see the gift endpoints but, oddly, gift amount is no where to be found. Did I mess something up? I can see funds, gift dates, dates added, post date, but no gift amount anywhere.

Screenshots here:

 

 

 

Here are the values from the gift table that I'm seeing:

 

headers                               values
id                                         4859
lookup_id                             4463
date                                      4/1/2014 0:00
type                                      Donation
constituent_id                      9759
is_anonymous                      FALSE
post_status                           NotPosted
post_date                              5/27/2011 0:00
gift_status                             Active
batch_number
date_added                           5/27/2011 11:27
date_modified                       11/22/2017 13:30

Regular Visitor
Posts: 15
Registered: ‎09-11-2017

Re: Using a REST API as a data source

@GGettyNo worries - the connector doesn't really do antything with regards to building a data model for you as yet, beyond pulling back all the data and setting the correct data types. The gift amounts are there, but because they are records themselves, you would need to tell the data model how you want them to be handled. In the case of the gift amounts, you would just need to expand the record. To do this, click Edit Queries, and you'll see all the data pulled back. You can then click the expand button in the corner of a column's header to surface the data.

 

You may want to expand some records into separate tables. You can follow this blog post to do that: https://powerbi.microsoft.com/en-us/blog/how-to-expand-a-list-of-records-in-a-query/

 

Most of the endpoints have record-type fields, so I'd recommend editing the queries for each endpoint you connect to in order to see if any additional data can be exposed. At some point I'll handle this in the connector directly, but the initial focus is to bring back all possible data.

 

I should get the chance to update the connector today with a solution to the issue of rate limiting on larger datasets.

Regular Visitor
Posts: 23
Registered: ‎12-22-2016

Re: Using a REST API as a data source

@Quicky Ah-ha! Here I was thinking the custom connector handled all the query editing, else. I've got it now. All I had to do was click the [-] and rename to amount. I'm good now. Of course, now I'm going to poke around on the other queries to see what else is "hidden". Thanks again for all this!

New Member
Posts: 1
Registered: ‎06-11-2018

Re: Using a REST API as a data source

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?

Regular Visitor
Posts: 23
Registered: ‎12-22-2016

Re: Using a REST API as a data source

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

Frequent Visitor
Posts: 10
Registered: ‎06-18-2018

Re: Using a REST API as a data source

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!!

 

calling-ultipro-api-power-bi-zappysys-json-rest-api-driver.jpgStep-1 : Create your ODBC DSN to call REST API or read JSON / XML datainformatica-json-rest-api-query-examples.pngStep-2 : Build / Test your REST API Querypower-bi-import-from-rest-api-url-odbc-json-driver.pngStep-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'
)

 

 

 

Frequent Visitor
Posts: 5
Registered: ‎08-19-2017

Re: Using a REST API as a data source

I don't have the perfect solution for this discussion but for the REST API, I have found an Excellent tutorial where you can learn how to use a REST API as a data source. It includes JSON Source Connector, XML Source Connector, and REST API Task.
Check it out, https://zappysys.com/blog/howto-import-json-rest-api-power-bi/

 

Hope it Helps!

Frequent Visitor
Posts: 5
Registered: ‎08-19-2017

Re: Using a REST API as a data source

As I am beginner in this field so I research a lot and found another beneficial Article, check it out https://zappysys.com/blog/howto-import-json-rest-api-power-bi/
So thanks to all those people who share their knowledge.

 

Hope it Helps!

Best regards