cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kelly_Zhu Member
Member

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
Kelly_Zhu Member
Member

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

Ross73312 Super Contributor
Super Contributor

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.


   

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

Proud to be a Datanaut!


   


Kelly_Zhu Member
Member

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!

Ross73312 Super Contributor
Super Contributor

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.

 


   

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

Proud to be a Datanaut!


   


attehaa Regular Visitor
Regular Visitor

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

 

 

Ross73312 Super Contributor
Super Contributor

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.


   

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

Proud to be a Datanaut!


   


attehaa Regular Visitor
Regular Visitor

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

Ross73312 Super Contributor
Super Contributor

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.


   

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

Proud to be a Datanaut!


   


Highlighted
attehaa Regular Visitor
Regular Visitor

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
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 105 members 1,630 guests