Making a auto refresh Power BI report using Azure DocumentDB and other data sources = impossible?

[Anonymous]

Hi

Our organization is moving towards new ERP-system using DocumentDB as a database solution. However, while testing the new DocumentDB data in Power BI Desktop and other data sources in the same report, I can bring the data nicely into Power BI Desktop and create reports, but when I publish the report to app.powerbi.com and I try to add DocumentDB as a data source - I cannot do this! I have used Azure DocumentDB connector in Power BI Desktop

I have tried to make a test report using _only_ our companys DocumentDB data and for this test report I can add the data source @ app.powerbi.com and thus make it automatically refreshed.

But for doing the same for using mixed data sources report - unfortunately I cannot add the DocumentBD data source as a Data source.

Is this possible? Do I have to use different connector for DocumnetDB data in Power BI desktop - like MongoDB or ODBC?

I am really stunned If I cannot make the reports.

Thanks

edit: I have to mention that I have succesfully created many reports using SQL Server, external MySQL, excel file etc. data sources in many reports and they work fine. So adding a data source is not a new thing for me, but using DocumentDB as a data is new for me.

[v-sihou-msft · Accepted solution]

@Anonymous

In this scenario, if you have Azure DocumentDB and other data source within same report, you can't use On-Premise Gateway. The data source configured in gateway is for the dataset of entire report level. Now you have multiple data source within same report, you should use Personal Gateway. 

See a similar thread: 

https://community.powerbi.com/t5/Integrations-with-Files-and/power-BI-gateway-connection-issues/td-p/84197

Regards,

[Anonymous]

Okay, thanks for the info. Any tips on this? Can I install Personal gateway on the same on-premises server as our On-Premises Gateway?

BR,
Atte H

[v-sihou-msft]

@Anonymous

Yes, you can install both Personal and On-Premise gateway on same machine. 

Regards,

[Anonymous]

Thanks a lot Mr. moderator. I installed Personal Gateway on the same server and my report using mixed data-sources is now working.

Last week I was googling like a mad man and didn't find this solution - that is - one cannot use mixed-data sources (cloud + on-premise) if you are using On-premises Gateway (Not Personal GW).

For example this page should be updated with this information:
https://docs.microsoft.com/en-us/azure/cosmos-db/powerbi-visualize/

[Anonymous]

Hello again,

Does anybody have any idea? I have a ticket open also to MS Support and it feels quite frustrating to me, that is it even technically possible to have DocumentDB + other data sources in a single report file? MS Support is giving me vague advice like:

"

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

"

Like I understand how to make reports - it is not that. How do I setup the DocumentDB data source in the Manage Gateway - setting so that I can make a report using multiple data sources in a single report - using DocumentDB + SQL server + xls-files? I have used and thus defined SQL and xls-files as a data source already because I use them in different reports succesfully. My actual sales data is being changed from SQL server to DocumentDB and this is the reason for this hubbub.

One key info might be: I have old data from on-premises server (SQL Server) and new sales data (Azure DocumentDB) appened as a new query in the Query Editor. The reason for this: we are making the switch from old system to new system next week --> we want to have clear picture of this year for the total sales so we need old + new data = whole year sales data. Might this be a problem?