Power BI – Connect with DirectQuery

Yes, you can connect with DirectQuery in Power BI. DirectQuery is a mode in Power BI that allows you to connect to a data source and retrieve data in real-time, without having to import it into Power BI. This can be useful when you need to analyze large datasets or when you need to work with data that is constantly changing.

DirectQuery and import connectivity modes differ in the following ways:

Import

In Power BI Desktop, a copy of the data from the selected tables and columns is imported. When you create or interact with visualizations, Power BI Desktop uses the imported data. It is necessary to import the full dataset again to refresh the data after the initial import or most recent refresh.

DirectQuery

No data is imported into Power BI Desktop. You can select tables and columns to appear in the Power BI Desktop Fields list for relational sources. A cube’s dimensions and measures are displayed in the Fields list for multidimensional sources such as SAP Business Warehouse (SAP BW). Power BI Desktop continuously queries the underlying data source as you create or interact with visualizations.

When using DirectQuery, visualizations require querying the underlying data source to create or interact with them, and the time needed to refresh them depends on the performance of the data source. 

If the required data was recently requested, Power BI Desktop uses the recent data to reduce the time needed to display the visualization. Clicking Refresh in the Home ribbon updates all visualizations with current data.

Although some performance-based limitations exist, DirectQuery still allows for various data modeling and transformations.

Benefits of DirectQuery 

• Using DirectQuery allows you to create visualizations over large datasets that would be impractical to import with pre-aggregation. 
• DirectQuery reports always reflect current data, but refreshing the data is necessary to see any underlying changes. 
• Reimporting large datasets for data refresh may not be practical. Additionally, the 1-GB dataset limitation does not apply when using DirectQuery.

Here are the steps to connect with DirectQuery in Power BI:

• Open Power BI Desktop.
• Click on “Get Data” in the Home tab.

• Select the data source you want to connect to and select “DirectQuery” under Data connectivity mode.

• Enter the required information to connect to your data source, such as the server name, database name, and credentials.
• Once you are connected, you can select the tables or views you want to use in your report.
• Create visuals in Power BI by dragging and dropping fields onto the report canvas.
• When you interact with the visuals, Power BI sends queries to the data source in real-time to retrieve the necessary data.

Note that when you connect with DirectQuery, your report performance may be affected by the performance of your data source. Also, some features in Power BI, such as calculated columns and certain types of visuals, are not available in DirectQuery mode. Additionally, some data sources may have restrictions or limitations when using DirectQuery.