Forum Discussion

JHob's avatar
JHob
Advocate I
6 years ago

Direct query connection to Mongo DB using ODBC

Hi

 

I would be grateful for some help.

 

I have a Gateway setup that allows me to connect to Mongo DB using ODBC.  I have setup a Power BI Dataflow to connect to the Datasource and connect to the Power BI Dataflows in Desktop to access the necessary information.

 

Is it possible to also use Direct query with this for some collections?  I would like to have some information updated more frequently.

 

When I try switch to DirectQuery I get the message "This query contains transformations that can't be used for direct query".  All I have done in the Power BI Dataflow is connect to the MongoDB collection. I haven't added any transformations.

 

Many thanks in advance for your help

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    JHob , Is it listed under direct query source? If yes then you can.

     

    Second, if you have configured in import mode, there many transformations which can do, which are not allowed in direct query mode.

     

    refer :https://radacad.com/directquery-live-connection-or-import-data-tough-decision

    https://radacad.com/directquery-connection-in-power-bi-how-does-it-work-limitations-and-advantages

    https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery

    • JHob's avatar
      JHob
      Advocate I
      6 years ago

      Thanks amitchandak

       

      If I cange the data connectivity mode to direct query I get the following message "This step results in a query that isnot supported in DirectQuery Model

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    Hi JHob ,

    It is a warning message that remind your it is not import type. 

    Direct Query doesn't support all Power Query transformations. If you try to use one that is not allowed you get this message. Please remove the query step causing  issue and try to look for alternate for that step.

    Please refer this document about limitations of DirectQuery:

    Implications of using DirectQuery 

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    Hi JHob ,

     

    If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly. Thanks!


    Best Regards,
    Yingjie Li

  • HamidBee's avatar
    HamidBee
    Power Participant
    3 years ago

    I was able to fix the following by firstly using Import mode instead of DirectQuery. Here are the full steps I followed:

     

    No I need to use the EnableFolding=true command and this is only available when using the Value.NativeQuery function. I created a new step and typed the following:

     

     

     

     

    = Value.NativeQuery( Source, "
SELECT OID.OrderID,O.OrderDate,UnitPrice*Quantity AS Total 
FROM Orders As O
FULL OUTER JOIN [Order Details] AS OID
ON O.OrderID = OID.OrderID", null, [EnableFolding=true])

     

     

     

     

    Now if I apply any parameter based filters to the dataset it will work without any errors.