Forum Discussion

lavdeep's avatar
lavdeep
Advocate I
7 years ago
Solved

Data connectivity import mode work perfect but getting error direct Query mode

Hi,

 

I want to load data from SQL Server I write SQL query for it. My SQL query code work perfect when I select import Data connectivity mode but getting below error.

please help why I am getting error  ??

 

Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    Hi  lavdeep ,

     

    There are currently a few limitations to using DirectQuery:

     

    • All tables must come from a single database, unless using composite models
    • If the Query Editor query is overly complex, an error will occur. To remedy the error you must either delete the problematic step in Query Editor, or Import the data instead of using DirectQuery. For multi-dimensional sources like SAP Business Warehouse, there is no Query Editor
    • Relationship filtering is limited to a single direction, rather than both directions (though it is possible to enable cross filtering in both directions for DirectQuery as a Preview feature). For multi-dimensional sources like SAP Business Warehouse, there are no relationships defined in the model
    • Time intelligence capabilities are not available in DirectQuery. For example, special treatment of date columns (year, quarter, month, day, so on) is not supported in DirectQuery mode.
    • By default, limitations are placed on DAX expressions allowed in measures; see the following paragraph (after this bulleted list) for more information
    • There is a one-million-row limit for returning data when using DirectQuery. The limit does not affect aggregations or calculations used to create the dataset returned using DirectQuery, only the rows returned. For example, you can aggregate 10 million rows with your query that runs on the data source, and accurately return the results of that aggregation to Power BI using DirectQuery as long as the data returned to Power BI is less than 1 million rows. If more than 1 million rows would be returned from DirectQuery, Power BI returns an error.

     

    You can learn more :https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery#limitations-of-directquery,

    https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about#implications-of-using-directquery .

     

    Best Regards,

    Amy

     

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

3 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi lavdeep ,

     

    From your error message, there is something wrong with the bracket ")" in your SQL query code, maybe miss , excess or in wrong place. Please check it.

     

    Best Regards,

    Amy

     

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

     

    • lavdeep's avatar
      lavdeep
      Advocate I
      7 years ago

      Hi v-xicai

       

      thanks for reply 

      If SQL query code incorrect then it should not work with data connectivity import mode but SQL query code execute without any error.  I am getting the error when I select  direct Query mode with same SQL query code

      • v-xicai's avatar
        v-xicai
        Community Support
        7 years ago

        Hi  lavdeep ,

         

        There are currently a few limitations to using DirectQuery:

         

        • All tables must come from a single database, unless using composite models
        • If the Query Editor query is overly complex, an error will occur. To remedy the error you must either delete the problematic step in Query Editor, or Import the data instead of using DirectQuery. For multi-dimensional sources like SAP Business Warehouse, there is no Query Editor
        • Relationship filtering is limited to a single direction, rather than both directions (though it is possible to enable cross filtering in both directions for DirectQuery as a Preview feature). For multi-dimensional sources like SAP Business Warehouse, there are no relationships defined in the model
        • Time intelligence capabilities are not available in DirectQuery. For example, special treatment of date columns (year, quarter, month, day, so on) is not supported in DirectQuery mode.
        • By default, limitations are placed on DAX expressions allowed in measures; see the following paragraph (after this bulleted list) for more information
        • There is a one-million-row limit for returning data when using DirectQuery. The limit does not affect aggregations or calculations used to create the dataset returned using DirectQuery, only the rows returned. For example, you can aggregate 10 million rows with your query that runs on the data source, and accurately return the results of that aggregation to Power BI using DirectQuery as long as the data returned to Power BI is less than 1 million rows. If more than 1 million rows would be returned from DirectQuery, Power BI returns an error.

         

        You can learn more :https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery#limitations-of-directquery,

        https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about#implications-of-using-directquery .

         

        Best Regards,

        Amy

         

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