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

Pull data from a REST API Authentication

I'm trying to pull data from REST API. At the moment I'm facing some difficulties tyring to authenticate.

 

All I have is:

Link:

https://service4.ultipro.com/services/EmployeePerson 

 

Authorization Headers:

"Authorization: Basic c29tZXVzZXI6c29tZXBhc3dvcmQ="

"US-Customer-Api-Key: ABCDE"

 

ultipro.png

 

I get this Error when I click OK:

Details: "The 'Authorization' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, If-Modified-Since, Prefer, Referer"

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: REST API and Authentication

Hi @surfersamu,

 

I have tested in Power BI desktop using the connection information you provided, there is no error throws out. After you fill the connection information like the image you provided and click OK button, please use Anonymous authentication, as you already provide the authorization key. 

 

q6.PNG

 

You can download the attached pbix file to have a look. By the way, here are some samples of calling API to get data in Power BI: 

Calling REST APIs and Parsing JSON made simple with Power BI

Get Data from Twitter API with Power Query

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
surfersamu Helper II
Helper II

REST API and Authentication

Hi guys,

 

I am tyring to get data from UtliPro REST API. The problem that I'm facing is that I don't know to how to authenticate with http headers.

 

This is what I have:

 

Link

https://service4.ultipro.com/services/EmployeePerson 

HTTP Headers

Authorization:Basic FAKECREDSFUlZJQ0U6RzBUUzNhczBuMjAxNyE=

US-Customer-Api-Key:ABCDE

 

ultipro.png

 

 

When I try this on the Get Data from Web dialog I get:

"The 'Authorization' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, If-Modified-Since, Prefer, Referer"

 

 

This is an example provided by UltiPro using XML:

<s:Envelope xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:s="http://www.w3.org/2003/05/soap-envelope">

<s:Header>

  <a:Action s:mustUnderstand="1">http://www.ultipro.com/services/employeeemploymentinformation/IEmployeeEmploymentInformation/GetEmpl...</a:Action>

  <UltiProToken xmlns="http://www.ultimatesoftware.com/foundation/authentication/ultiprotoken">27e17fe3-29cb-48a0-a130-4bad41971dcb</UltiProToken>

  <ClientAccessKey xmlns="http://www.ultimatesoftware.com/foundation/authentication/clientaccesskey">CAN12</ClientAccessKey>

 </s:Header>

 <s:Body>

 <GetEmploymentInformationByEmployeeIdentifier xmlns="http://www.ultipro.com/services/employeeemploymentinformation">

  <employeeIdentifier xmlns:b="http://www.ultipro.com/contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" i:type="b:EmployeeNumberIdentifier">

  <b:CompanyCode>C0014</b:CompanyCode>

  <b:EmployeeNumber>004446049</b:EmployeeNumber>

  </employeeIdentifier>

 </GetEmploymentInformationByEmployeeIdentifier>

 </s:Body>

 </s:Envelope>

malagari Resolver IV
Resolver IV

Re: Pull data from a REST API Authentication

I don't think this is a Power BI issue; the details that are returned are most likely from the API for ultipro.com.  You can try connecting with a tool like Postman to verify that the issue is in the API information.  If you're able to connect via Postman, then it is something isolated to Power BI.

Dan Malagari
Consultant at Headspring
surfersamu Helper II
Helper II

Re: Pull data from a REST API Authentication

You are right. I tried on postman and got nothing. I have started a support ticket with them.

 

Assuming the API wasn't giving me an error. Is that the right way to make a HTTP GET Request? Sorry I'm new to REST APIs.  

Community Support
Community Support

Re: REST API and Authentication

Hi @surfersamu,

 

I have tested in Power BI desktop using the connection information you provided, there is no error throws out. After you fill the connection information like the image you provided and click OK button, please use Anonymous authentication, as you already provide the authorization key. 

 

q6.PNG

 

You can download the attached pbix file to have a look. By the way, here are some samples of calling API to get data in Power BI: 

Calling REST APIs and Parsing JSON made simple with Power BI

Get Data from Twitter API with Power Query

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

surfersamu Helper II
Helper II

Re: REST API and Authentication

 

UltiPro support takes a very long time to reply.

 

Ok I found the reason why is not working. Because UltiPro requires you to get a token to make a SOAP request. I have a token now.

 

Using advanced editor how can I fix this code:

 

I keep getting:

 

DataSource.Error: Web.Contents failed to get contents from 'https://service4.ultipro.com/services/EmployeePerson' (415): Unsupported Media Type

 

let
Source = Xml.Tables(Web.Contents("https://service4.ultipro.com/services/EmployeePerson"
,[Content=Text.ToBinary("<s:Envelope xmlns:a="&Character.FromNumber(34)&"http://www.w3.org/2005/08/addressing"&Character.FromNumber(34)&" xmlns:s="&Character.FromNumber(34)&"http://www.w3.org/2003/05/soap-envelope"&Character.FromNumber(34)&">
<s:Header>
<a:Action s:mustUnderstand="&Character.FromNumber(34)&"1"&Character.FromNumber(34)&">http://www.ultipro.com/services/employeeperson/IEmployeePerson/FindPeople</a:Action>
<UltiProToken xmlns="&Character.FromNumber(34)&"http://www.ultimatesoftware.com/foundation/authentication/ultiprotoken"&Character.FromNumber(34)&">6ddeadsfdsfdsfsdfdsfdsfds6</UltiProToken>
<ClientAccessKey xmlns="&Character.FromNumber(34)&"http://www.ultimatesoftware.com/foundation/authentication/clientaccesskey"&Character.FromNumber(34)&">ABCD</ClientAccessKey>
</s:Header>
<s:Body>
<FindPeople xmlns="&Character.FromNumber(34)&"http://www.ultipro.com/services/employeeperson"&Character.FromNumber(34)&">
<query xmlns:b="&Character.FromNumber(34)&"http://www.ultipro.com/contracts"&Character.FromNumber(34)&" xmlns:i="&Character.FromNumber(34)&"http://www.w3.org/2001/XMLSchema-instance"&Character.FromNumber(34)&">
<b:CompanyCode />
<b:CompanyName />
<b:Country />
<b:EmployeeNumber />
<b:FirstName>like (a%)</b:FirstName>
<b:FormerName />
<b:FullOrPartTime />
<b:Job />
<b:LastHire />
<b:LastName />
<b:Location />
<b:OrganizationLevel1 />
<b:OrganizationLevel2 />
<b:OrganizationLevel3 />
<b:OrganizationLevel4 />
<b:OriginalHire />
<b:PageNumber />
<b:PageSize />
<b:PayGroup />
<b:Status />
<b:SupervisorLastName />
<b:TerminationDate />
<b:TimeClockId />
</query>
</FindPeople>
</s:Body>
</s:Envelope>"),Headers=[#"Accept-Encoding"="gzip,deflate", #"Accept"="Basic U0VfdasdasfdasMjAxNyE=", #"Content-Type"="text/xml;charset=UTF-8"]]
))
in
Source

 

Community Support
Community Support

Re: REST API and Authentication

Hi @surfersamu,

 

It seems your original issue is solved, right? 

 

Regarding the second issue, if you are not familiar with the Power Query, please use features available in Query Editor UI to transform data. As this is another issue, please post in a new thread and share the pbix file if possible. 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ZappySys Helper I
Helper I

Re: Pull data from a REST API Authentication

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

 

 

 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors