cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Re: [Unable to combine data] accessing data sources that have privacy levels

Hi everyone!

@mithrandir, I'm having the same problem as you after modify the JIRA content pack to get information of every Issue.
Have you find any answer?

 

Thanks!

kavaat Frequent Visitor
Frequent Visitor

Re: [Unable to combine data] accessing data sources that have privacy levels

Hi!

 

@mithrandir@Anonymoushave you managed to find a solution to this? I'm struggling with similar issues.

 

Thanks!

Super User IV
Super User IV

Re: [Unable to combine data] accessing data sources that have privacy levels

What if you had to turn off the privacy settings and then see if that works successfully?

 

After which you can look at this blog post by Chris Webb for the different privacy settings and how they work. It is a 5 part blog post

https://blog.crossjoin.co.uk/2017/05/24/data-privacy-settings-in-power-bipower-query-part-1-performa...





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Power BI Blog
kavaat Frequent Visitor
Frequent Visitor

Re: [Unable to combine data] accessing data sources that have privacy levels

As my last resort I changed my URL in Data Source setting of the On-Premises Gateway Settings from https://www.url.com/ to https://www.url.com and changed all the privacy levels within the file and in the On-Premises Gateway Settings to Public instead of None. And suddenly my queries started to refresh! I'm not sure which one did the trick, but I'm glad that it's working now.

 

At first I tried to do this query by using a function dedicated to opening the URLs. But naturally the function approach failed in the cloud environment. Now my query goes through a list of IDs by using a RelativePath variable in the options parameter of the Web.Contents() -function with fixed string as the url parameter. The URL is in the form of the "https://www.url.com/api/rest/" which is different from the one I entered to the gateway settings. I was almost certain that the reason that it basically uses different URLs ("https://www.url.com/api/rest/ID1", "https://www.url.com/api/rest/ID2" etc.) in a single query was the reason for the gateway to fail at refreshing the data. But it seems like that this wasn't the case.

Anonymous
Not applicable

Re: [Unable to combine data] accessing data sources that have privacy levels

Hi @kavaat,

 

I´m trying to connect to the API of JIRA so I don't need some kind of Gateway.
But in your explanation you said that you connect to urls like this: https://www.url.com/api/rest/ID1

Looks like and api, isn't it? How you do this?

Super User IV
Super User IV

Re: [Unable to combine data] accessing data sources that have privacy levels

Hi @Anonymous

 

What I have seen is other people use the Power BI Custom Data https://powerbi.microsoft.com/en-us/blog/data-connectors-developer-preview/Connector SDK to connect to APIs but in such a way that it is a Data Connector in Power BI Desktop

 

Here are the details: 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Power BI Blog
mithrandir Regular Visitor
Regular Visitor

Re: [Unable to combine data] accessing data sources that have privacy levels

@Anonymous@kavaat@GilbertQ

 

Sorry to reply to this so late. I am consuming several RESTful API's, so this ended up being a blocking issue for me. I spoke with Power BI support about the issue and there a few scenarios.

 

The first is "https://www.domain.com/rest/api" or similar. If you try authenticating against this URL alone, you'll usually find yourself unsuccessful or redirected. The way the Power BI service online was explained to me, is that it tries to run through and authenticate the endpoints in your dataset before allowing/attempting a scheduled refresh. In hindsight, this makes sense because your desktop application can implicitly be trusted by being your physical property, where the Power BI service on Microsoft's servers needs an extra step to verify access. Doing authentication this way also avoids tying up resources that are going fail anyway.

 

The second is "https://www.domain.com" with a parameter passed and appended to the baseurl. This fails for the same reason, but the difference is the Power BI service is set up to inform you that your datasource doesn't support scheduled refresh before it ever attemtps to authenticate against the endpoint. This is to avoid you putting in any arbitrary parameter that could potentially cause issues, therefore tying up resources. It would also take some work to "build" the URL's, if you will, in order to authenticate them ahead of time and support parameterized baseurls.

 

The last scenario relevant to this topic is "https://www.domain.com" and the use of the RelativePath property defined with a variable in the options list parameter. If you've tried this, you know that your dataset in the Power BI service will have no indication of an issue until it tries to refresh. It fails for the same reasons above, but Power BI doesn't have a check built in like it does when appending a parameter to the baseurl.  For those who mentioned using JIRA data, I know that API key auth support is coming down the pipe on their end sometime soon and the current content pack implementation (https://www.domain.com/jira/rest/api/2/search) works with scheduled refresh today. If you are trying to access different resource perspectives, one of the three above scenarios should describe any issues you are having (I'm consuming the remotelinks endpoint which blocks scheduled refresh for me)

 

At the time, support told me that supporting scheduled refreshing for parameterized Web datasources was not on their roadmap given the large changes they have made this year and are continuing to make concerning Power BI. That led me to look into setting up a gateway, but I don't believe the scheduled refresh there differs from the Power BI service concerning the Web datasource (please correct me if I'm wrong). My current solution is refreshing my reports manually as often as I need which is not really a workaround, but I wanted to share my experience so far just in case.

 

One suggestion that seemed promising was to store the report in OneDrive for Business locally for automatic syncing from desktop, but the only repo to interact with Power BI desktop is using a UI testing framework and hasn't been updated in awhile. I was hoping to find a useful set of cmdlets for Powershell to interact with Power BI desktop or some other way to refresh a report automatically to make this a good workaround, but haven't found anything so far. The last option that @GilbertQ mentioned is creating a custom connector for the datasource you are working with. This currently does not support OAuth and I've heard a handful of other scenarios where consuming an API that you don't own may not be supported. I'm dealing with about a 50/50 split as far as the API's I'm consuming, so I decided not to commit considering I wouldn't be gaining scheduled refresh anyway.

 

Hopefully this is helpful. I am continuously investigating solutions to this problem, so I wll try to update this thread if I find any other potential routes.

jaykilleen Regular Visitor
Regular Visitor

Re: [Unable to combine data] accessing data sources that have privacy levels

I have to chime in here and just say that the refreshability of PowerBI has to be the most disappointing feature I've experienced. I understand there are alot of moving parts here and managing resources etc but it's never been stable since release.

 

If I am using LiveQuery or doing things on one database alone (ie SQL Server) then things go ok. But as soon as I try multiple sources (ie Salesforce, SQL Server and a PostgreSQL Ruby on Rails app) things get very unstable.

 

One day it works, the next day it doesn't. Error messages are very vague and little support to get something that appears to work OOB actually working as it is intended. I also find solutions like disabling privacy settings to be concerning. I don't understand the innards of this enough to warrant disabling security features.

 

Putting aside an hour a day to refresh and republish reports is not suitable yet this kind of functionality is what everyday users are expecting.

 

I can't recommend PowerBI if scheduled reports are a necessity.

 

My next step to resolve this is to set daily jobs to copy ALL third party databases locally to our SQL Server databases on so I can run a refresh via one channel.

 

I'm looking forward to this situation improving though.

Super User IV
Super User IV

Re: [Unable to combine data] accessing data sources that have privacy levels

That is really interesting that your refreshed are happening in that way.

I have been using On-Premise data sources and I have not had any errors with the Gateways at all really. It has always almost been something from the source systems.

I have not done extensive work with Salesforce, but connectors like OneDrive and SharePoint I have not had any issues with the refreshing.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Power BI Blog
Highlighted
jaykilleen Regular Visitor
Regular Visitor

Re: [Unable to combine data] accessing data sources that have privacy levels

Cheers for the reply.

 

I'll keep digging in and trying to find out why these errors are happening and see if I can provide any insight.

 

It is just odd that for something designed to mashup data between different sources that it would fail so consistently. I'm probably pushing the limits on something but I don't feel what I am doing should be out of the ordinary.

 

I do find though that the Salesforce connector does tend to return alot of Salesforce database errors (ie invalid query locators, too many cursors in the database etc etc).

 

It seems that nothing beats a good ole' "extract the data you need from all the databases, store it in your own database, query everything from one database". Even if you look at Salesforce's Heroku Connect it seems that is all they are doing aswell. Seems Salesforce really doesn't want you to hit their API despite their CEO and Microsoft CEO shaking hands on stage at Dreamforce 😛

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors