Forum Discussion
Can't refresh data after appending queries which are from different data source kinds
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
@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.
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 GW. I 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.
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
- Get data from cosmos using connector without ODBC
- Get data from on premise source (any data)
- Create the report and publish it
- 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.
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?
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?