Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hlewisNYC
Frequent Visitor

Direct Query not updating data

I have created a report in PBI Desktop with an on-premise SQL Server database as the data source. I have enabled Direct Query (rather than Import), yet changes in the SQL Server database are not being reflected in the report. Are there any common errors related to this problem? Ideally I plan to publish this report to my org's PBI Report Server and have it update (based on changes in the SQL Server database) in as cloes to real-time as possible.

 

Thanks,

Harrison

2 REPLIES 2
v-ljerr-msft
Employee
Employee

Hi @hlewisNYC,

 

When using Get Data in Power BI Desktop to connect to a data source, and you choose DirectQuery, the behavior of that connection is as follows:

  • During the initial Get Data experience, the source is selected. For relational sources, this means a set of tables are selected and each still define a query that logically returns a set of data. For multidimensional sources like SAP BW, only the source is selected.
  • However, upon load, no data will actually be imported into the Power BI store. Instead, upon building a visual within Power BI Desktop, queries will be sent to the underlying data source to retrieve the necessary data. The time then taken to refresh the visual will depend on the performance of the underlying data source.
  • Any changes to the underlying data will not be immediately reflected in any existing visuals. It is still necessary to Refresh, whereupon the necessary queries will be resent for each visual, and the visual updated as necessary.
  • Upon publishing the report to the Power BI service, it will again result in a Dataset in the Power BI service, just as for import. However, no data is included with that dataset.
  • When opening an existing report in the Power BI service, or authoring a new one, the underlying data source is again queried to retrieve the necessary data. Depending upon the location of the original data source, it might be necessary to configure an on-premises data gateway, just as is needed for Import mode if the data is refreshed.
  • Visuals, or entire report pages, can be pinned as Dashboard tiles. To ensure that opening a dashboard will be fast, the tiles are automatically refreshed on a schedule (for example, every hour). The frequency of this refresh can be controlled, to reflect how frequently the data is changing, and how important it is to see the very latest data. Thus, when opening a dashboard, the tiles will reflect the data as of the time of the last refresh, and not necessarily the very latest changes made to the underlying source. An open dashboard can always be Refreshed to ensure it is up-to-date.

For more details about using DirectQuery in Power BI, you can refer to the following tow article. Smiley Happy

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

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about

 

Regards

hlewisNYC
Frequent Visitor

I have created a report in PBI Desktop with an on-premise SQL Server database as the data source. I have enabled Direct Query (rather than Import), yet changes in the SQL Server database are not being reflected in the report while still in PBI Desktop (we are beginning to use Report Server at our org but it seems like RS reports can only use DQ in relation to Analysis Services). Are there any common errors related to this problem?

 

Thanks,

Harrison

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.