Forum Discussion

mak82pk's avatar
mak82pk
Helper I
9 years ago
Solved

Power BI Direct query real-time refresh and data upload

If I am using direct-query for SQL database on my server [not Azure SQL DB] configured by on-premises gateway there is no limit for data refresh?

Also, can I get real-time data using the same?

When using direct-query is there still a limit for 1 GB upload? As only the dataset will be updated with new records, not the whole dataset.

 

Need Help
Show and Tell
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi mak82pk,

     

    How clever you are. :smileyvery-happy:

     

    As the SQL Server database is on-premise data source, the gateway is required. As you use DirectQuery mode, on-premise data gateway is necessary. See: Live connections and DirectQuery to on-premises data sources.

     

    Data source Live/DirectQuery User configured manual or scheduled refresh Gateway required
    Analysis Services Tabular Yes Yes Yes
    Analysis Services Multidimensional Yes Yes Yes
    SQL Server Yes Yes Yes
    SAP HANA Yes Yes Yes
    Oracle Yes Yes Yes
    Teradata Yes Yes Yes

     

    Best Regards,
    Qiuyun Yu

6 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    Hi mak82pk,

     

    Based on my research, there is a 1 million row limit for returning data when using DirectQuery. See: Limitations of DirectQuery. As the dataset is DirectQuery mode, we can manually adjust the refresh frequency in the Scheduled cache refresh option in the Power BI service. See: Live connections and DirectQuery to on-premises data sources.

     

     

    Besides, I will confirm it internally whether the 1 G limit apply for the pbix file which use DirectQuery mode.

     

    Best Regards,
    Qiuyun Yu

    • mak82pk's avatar
      mak82pk
      Helper I
      9 years ago

      Thanks for you prompt response. I found the answer in the link you provided

      The 1 GB dataset limitation does not apply to DirectQuery

       

      Also, is it possible not to use on-premises gateway and get real-time data using SQL Server [Not Azure SQL Server]?

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Community Support
        9 years ago

        Hi mak82pk,

         

        How clever you are. :smileyvery-happy:

         

        As the SQL Server database is on-premise data source, the gateway is required. As you use DirectQuery mode, on-premise data gateway is necessary. See: Live connections and DirectQuery to on-premises data sources.

         

        Data source Live/DirectQuery User configured manual or scheduled refresh Gateway required
        Analysis Services Tabular Yes Yes Yes
        Analysis Services Multidimensional Yes Yes Yes
        SQL Server Yes Yes Yes
        SAP HANA Yes Yes Yes
        Oracle Yes Yes Yes
        Teradata Yes Yes Yes

         

        Best Regards,
        Qiuyun Yu