Forum Discussion
MongoDB Atlas SQL connection using 2 connections with 1 data gateway problem
Hi Anonymous
I contacted Microsoft support and they were helping me, but finally I managed to find a solution with my colleagues. It turns out that it is possible to create a kind of ‘view’ in a section that Mongo Atlas offers called Data Federation. There I put together both collections, each one from a different cluster and I only needed one connection for both Desktop and Service.
So as this has helped me, I'll give this answer as a solution.
Anyway, thank you very much for the help provided in your comments!
Hi nbustos ,
am having few problems while connecting with MongoDB Atlas SQL if i connect through Import and Transform the JSON file in power query mode than the power bi is taking very long time to Refresh as i have used pivot , groupby how to resolve this ,
and if i connect via Direct Query it is fast but am able to FLATTEN and UNWIND the few columns like k and v array of objects , but unable to to extract Array of object specific column am getting error as 1007 column not found i tried many ways but not getting correct solution , can you please guide me which method is good
Hi Irah24,
If you want to extract values from an array using DirectQuery mode, I recomend you to use SQL native query when you first load data (that's the solution I'm applying). Here's where you can write SQL code:
For extracting array values, only you need is to write this:
SELECT array.value1, array.value2, array.value3, var1, var2Hope this can help you!!