cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Can't refresh data after appending queries which are from different data source kinds

Hi Everyone,

 

 

Can PBI support to run a query which appends two different data source queries now?

 

 

One of data source is from VSTS and the other is from SQL Server. I append these 2 queries as a new query.


It works well when I refresh on PowerBI Desktop, but it refreshs failed on PowerBI Service and notes me as below.

 

 

"The provider is not support or due to invalid data source. Please check the data source connection string."

 


Thank you in advence!

 

 

Regards,


Kelly

19 REPLIES 19
Highlighted
Helper III
Helper III

Can't refresh after append two different data source queries

Hi Everyone,

 

Can PBI support to run a query which appends two different data source queries now?

 

One of data source is from VSTS and the other is from SQL Server. I append these 2 queries as a new query.

It works well when I refresh on PowerBI Desktop, but it refreshs failed on PowerBI Service and notes me as below.

 

"The provider is not support or due to invalid data source. Please check the data source connection string."

 

Thank you in advence!

 

Regards,

Kelly

Highlighted
Community Champion
Community Champion

Re: Can't refresh data after appending queries which are from different data source kinds

I'd query whethere VSTS is a valid data source for a gateway.  I think thats going to be your problem.  You can manually refresh things in your Power BI desktop that your gateway cannot.  I know from my own projects that Active Directory is an example of a source that i've run into with this limitation.

Highlighted
Helper III
Helper III

Re: Can't refresh data after appending queries which are from different data source kinds

Thanks for your reply!

My data source credentials work well now and the message PBI notes is:

"Underlying error message:The given data source kind is not supported."

 

I have never heard Active Direstory before. Could you give me some introduction about this?

 Thanks a lot!

Highlighted
Community Champion
Community Champion

Re: Can't refresh data after appending queries which are from different data source kinds

Active Directory is what our Exchange client uses to keep track of everyone.  It knows their computer, their email, their computer passwords.  Its used for Single Sign on, which is the ability to log onto your computer and have that log-on act as authentication for other secure systems.

 

I needed to report on our employee users and all of the data is contained within Active Directory.  Sadly since the gateway can't refresh from it, i've had to rely on an SQL server that replicates the data for me.

 

Highlighted
Helper II
Helper II

Re: Can't refresh data after appending queries which are from different data source kinds

@Kelly_Zhu

 

@Ross73312

Hi, did you get your report to work at Power BI service - that is - get the automatic refresh working?

I have a similiar problem: I have a PBI workbook with SQL Server and documentDB data and I have appended a new query from these two data sources. 

The automatic refresh was not completed and gives me an error indicating that the Appended Query table was the root of the problem (CRM_ERP_total_sales).

Any idea how to fix this? I am combining old sales data from SQL Server and combining it with the new sales data from Azure DocumentDB.

 

edit: I am using for this case the new Gateway in Personal mode and I have defined the data sources in Edit Credentials. I had to install this GW in Personal mode just for this report, because our existing On-premises GW did not function with both on-premises SQL server data and cloud data (Azure DocumentDB).

07112017 pbi data sources appended query.PNG

 

edit2: I could swear this worked yesterday when I tried to refresh the data in Power BI service On demand (it refreshed the data, there's 8 second difference what the dashboard tile refresh time shows and Refresh History), but now when I look the refresh history it says all of them failed, for the same reason

 

07112017 pbi data sources appended query refresh history.PNG

 

 

Highlighted
Community Champion
Community Champion

Re: Can't refresh data after appending queries which are from different data source kinds

The work around solution for the moment is to list the file in your gateway.  So rather than place the Folder in your gateway, you will need to list the exact files.  Its awful but it works.  It is what we are having to do until this issue is properally corrected.

Highlighted
Helper II
Helper II

Re: Can't refresh data after appending queries which are from different data source kinds

@Ross73312

 

Okay, thanks for the tip. Do you mean I have to add some exact file paths in the Power BI Gateway (Personal) application on the server machine which I have installed it? Do you know what is the name of executable file on the actual file drive? I feel like a dummy trying to find the actual new Power BI personal gateway files, since the file gateway paths & folder etc. are not very explicit IMHO

Or do I have to define the exact DocumentDB colletions in Power BI Service as above (app.powerbi.com)? Now I have in a way just the root URL of our DocumentDB cloud database.
081102017 powerbi settings.png

Highlighted
Community Champion
Community Champion

Re: Can't refresh data after appending queries which are from different data source kinds

Hi @attehaa

I'm not sure how you would do it with the Personal Gateway, but the bug I am talking about affects the On-Premise Data Gateway and you add these into your "Manage Gateways" section within the Power BI Service.

Highlighted
Helper II
Helper II

Re: Can't refresh data after appending queries which are from different data source kinds

@Ross73312

 

@Simon_Hou-MSFT

 

Hi again Ross and thanks for the fast reply,

 

The root cause why I am using Personal GW instead of On-premises GW is because I cannot use On-premises GW when I am using multiple data sources - in this case online source Azure DocumentDB and on-premises SQL Server data.

 

I made a thread about this problem and mod Simon_Hou-MSFT gave me a tip to use Personal GW.

 

In essence, this whole ordeal is about making a neat Power BI report using our old sales data from a local SQL Server and new sales data from Azure DocumentDB. I am pulling these two data sources into a single report, aligning the columns and appending those two queries as a New Query ---> One query with old & New sales data --> clear 2017 sales data to report. We are launching our new ERP with DocumentDB database soon.

 

https://community.powerbi.com/t5/Service/Making-a-auto-refresh-Power-BI-report-using-Azure-DocumentD...

 

I couldn't image that combining Azure DocumentDB and local SQL Server data would be so complex. Please, please dear Microsoft gurus, help me solve this problem! Again, when I refresh the report in Power BI Desktop if works fine, but I cannot add the data sources and make the report refreshed automatically at Power BI Service when I tried to use On-premises GWI can add the data sources when using the Personal GW, but it gives me the error below. I don't know if this is related to the problem, but the Azure DocumentDB datasource loads quite slowly (my report with max a couple ten thousands rows and just tens of columns take 10-20 mins to refresh) in Desktop, but it still refreshes the data. It was quicker before, like 4-5 weeks ago, but something might have happened with azure cookies or something.

 

0911 refresh failed appended query.PNG

 

PS: Making a support ticket is useless. I've made two of them. It goes to MS out-sourced tech support in India (Mindtree co.) . They paste web links (which I've already read) and keep me playing this email ping-pong game which does not end anywhere. They give me useless, vague advice like (direct quote):

"

Solution 1

  1. Get data from cosmos using connector without ODBC
  2. Get data from on premise source (any data)
  3. Create the report and publish it
  4. Setup the gateway and refresh "

I know how the game goes as a general process, but I now have problem with this detail and they cannot provide me with a solution.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Users online (831)