cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kelly_Zhu
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
Anonymous
Not applicable

@Kelly_Zhu

 

@Anonymous

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

 

 

I have a similar issue. However, in my case, I have only a single data source, my redshift db. The issue occurs while going for schedule refresh using on-premise gateway(personal mode). I am able to publish it from Power BI Desktop, but unable to schedule it from power bi online. 

 

 

powerbi_redshift.png

 

 It was mentioned in one of the troubleshooting docs of on-premise data gateway that you might have to install a data provider in the same system where the gateway is installed. But that too is not working. I have already got the 64 bit ODBC driver for Amazon Redshift installed in my machine. It still doesn't work. 

doc_powerbi.png

 

Anonymous
Not applicable

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.

Anonymous
Not applicable

@Anonymous

 

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

Anonymous
Not applicable

Hi @Anonymous

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.

Anonymous
Not applicable

@Anonymous

 

@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-DocumentDB-and/m-p/295985/highlight/true#M34583

 

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.

Anonymous
Not applicable

Hi, I'm bumping this for Friday 10th Nov.

 

Any, any idea? Is this is a Power BI tech limitation? Is there any way to go around this?

Anonymous
Not applicable

It seems to be so, that I cannot make a new append query from On-premises SQL Server data + cloud Azure DocumentDB.

 

When I delete the append query table from Power BI report while still maintaining the both data source tables in the report, I can setup the autorefresh features etc. and everything works fine n' dandy.

 

Plz Microsoft, fix this. It's retarded that I cannot use powerful Query Editor features when using cloud + on-premises data. RETARDED.

 

Pardon me, that was a relief. Please fix.

 

Thanks.

 

any update on this?

Anonymous
Not applicable

No Update. It still does not work.

 

I've just concluded that it is a MS tech limitation, so I have to go around it somehow. It would be nice if it worked, but it does not. I'm just going to do a Q1 2017and 2018 comparison and since I cannnot mash the data into a single table, I have to figure out a way to compare data efficiently

you can use UNION in DAX instead of appending/merging it in power query. it worked for me.

 

table_final = UNION(SELECTCOLUMNS(table_1,"col1",table_1[col1],"col2",table_1[col2]),SELECTCOLUMNS(table_2,"col1",table_2[col1],"col2",table_2[col2]))

 

When you say you can use UNION in DAX, do you mean on the front end visualization in PBI (not in Power Quary)? Sorry I've experimented with it in visualizations but am by no means an expert.

 

The reason I'm asking is I have a Common Data Service (v2) table (coming from xxxx.crm.dynamics.com and an onsite Analysis Services cube. I merge data from the CDS entity (table) to data imported from the cube, do a bunch of calcs and then use the resulting table(s) for even more calcs all in Power Query.

 

Everything refreshes fine on my desktop, but fails on PBI online once I do a merge/append/whatever in PowerQuery even though individual data sources are fine (gateway or direct credentials)

jayeshv
Frequent Visitor

Yes.DAX means in front end visualization of power bi desktop app and not in power query.

For append you can use union as mentioned above for merge you can use calculated column with lookup function

gotcha, thanks!

 

So does anyone know if this is a bug that will be fixed or is there a technical reason cloud based DB's don't play nice with on-site sources via gateways? It's really inconvinient when mashing up data from 4-5 different sources and I need to do a lot of complex manipulation that is not easily done in DAX. I.e. I need to merge tables and then do multiple other calculations / create follow-on tables off of that merged table.

 

I could pull the on-site data into my CDS Environment which really really seems counterproductive, i.e. we're then duplicating DB tables in our data warehouse and also pulling same data into CDS just to get reporting working.

Hi. Just wanted to recheck if there is anything in place to fix this issue. It's a pretty big bug when you can't append/merge cloud data sources with on-premises even though both are being imported.

Kelly_Zhu
Helper III
Helper III

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

Anonymous
Not applicable

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.

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!

Anonymous
Not applicable

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.

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors