Direct Query Mode

Working in DirectQuery Mode

In this post, we will contrast the importing of SQL Server data with
working in DirectQuery Mode.

When you use the DirectQuery option, data is not imported
from SQL Server into the data model of your SQL Server report. Instead, when
the report creator configures visuals in Power BI Desktop, the actual data
source is always queried, not an imported copy. This means that the performance
of the SQL Server instance will have a big impact on the user experience.

DirectQuery is a useful option when reports require very
up-to-date information (which cannot be supplied via a scheduled refresh). It
is also a good solution where the dataset is too large to be imported into
Power BI.

To see it in action, in a blank Power BI file, let us now
connect to the same SQL Server views as we did when looking at the Import
option; but, this time, we will use DirectQuery mode.

In a blank Power BI Desktop file, click Get Data > SQL
Server, enter a server name, select DirectQuery mode and click “OK”.

Select the five views to which we connected earlier (in
Import mode) and click the “Load” button.

Save the new file as “DirectQuery.pbix”.

In file explorer, copy the file and change the file
extension of the copy from “.pbix” to “.zip”.

Open the ZIP file and check the size of the data model. You
will see that it is tiny (around 24k).

Back in Power BI Desktop, switch to Model view to create
relationships. (Power BI does not automatically generate relationships in
DirectQuery mode, unless they already exist). Start by dragging from
Clients[ClientID] to Event[ClientID].

In the Relationship dialog, activate the option “Assume
Referential Integrity”. (Click “Learn more” for more info.)

“Assume referential integrity” helps to improve performance
in DirectQuery mode by optimizing the SQL queries generated by Power BI.

Complete the data model by creating relationships between
Workshops and Events, Tutors and Events and Venues and Events. (Activate
“Assume Referential Integrity” each time.)

Click on “Edit Queries” and rename all five queries by
removing the prefix “PBI_Sales ”.

To see what happens when we perform a transformation which
cannot be query folded, right-click the Gender column of the Clients table and
choose Transform > Capitalize each word.

A warning is displayed explaining that this step is not
support in DirectQuery. Either this step must be deleted, or we must convert
the whole report to Import mode.

Close the Query Editor window and click “Yes” to apply the
changes we have made.

In the bottom right of the screen, the message “Storage
Mode: DirectQuery (click to change)” is displayed.

Clicking this message, displays the following warning
dialog. (Click Cancel to dismiss this warning.)

If we click Modeling > New Table, note how the Mode also
immediately changes from DirectQuery to mixed. (Press Escape to cancel the
table.)

However, creating measures (even complex ones) does not
cause the mode to change. (It’s up to the user to monitor the impact on
performance.)

Similarly, we can create calculated columns. Right-click the
Events table and choose “New Column”.

Enter the formula shown below, then check that the mode
displayed is still “DirectQuery”.

Thus, to summarize the key points for making a DirectQuery connection to a SQL Server database.

  • It is still more efficient to import data from SQL Server views than directly from database tables.
  • All imported tables and views must come from the same database.
  • A data model containing a DirectQuery connection to SQL Server data cannot be mashed up with data from any other sources.
  • Calculated tables cannot be added to the data model.
  • There are no longer any major restrictions on the use of DAX when creating measures and calculated
    columns. However, the impact on query performance must be carefully monitored.
  • When a DirectQuery report is uploaded to the Power BI service, a data source must be created to enable Power BI to connect to the database via an on-premises gateway; however, no refresh schedule needs to be created.

Composite Models

Traditionally, in Power BI, connecting to a data source
using DirectQuery meant that you could not connect to any additional data
sources or import data; it was all or nothing.

Now, however, Power BI allows you to create composite models
which can combine data from a combination of DirectQuery and imported data
sources.

Composite mode is a variant of DirectQuery mode. Any of the
following will change a DirectQuery report to composite mode.

Let’s perform the second of these operations. Open the
“DirectQuery.pbix” file which we saved in the last section and click “Modeling
> New Table”.

Note that the storage mode is now displayed as “Mixed”.

Enter the formula shown below to add a date table to the
data model.

On the left of the screen, we now have all three modes. In
DirectQuery mode, data mode is not available, and, in composite mode, an error
is shown when a table which is in DirectQuery mode is highlighted.

However, when our Date Table is highlighted, it is
displayed.

Let us complete this chapter by linking the Date Table to
the data model. Switch to Model mode and drag Date Table[Date] onto
Events[Start Date].

Storage Modes

The original reason behind making DirectQuery and Import
mutually exclusive was performance. To improve performance when using composite
models, Power BI now offers three different storage modes, which are available
on a per table basis: Import, Direct Query and Dual.

To set the storage mode of a table, switch to Model View
and expand the Properties pane. Select a table and, in the Field Properties
pane, expand the Advanced category then choose one of the three options in the
Storage Mode drop-down menu.

Import

In Import mode, the table data is imported into the data
model and only replaced when the dataset is refreshed. Consider using this mode
for tables which contain data that does not change very often.

DirectQuery

In DirectQuery mode, all queries are sent to the database
server; and no data is imported into the data model.

Dual

In Dual mode a copy of the table data is imported into the
data model. Queries involving data from the dual mode table as well as other
related tables will be sent to the server. However, if a query only requires
data from the dual mode table, the imported data will be used, potentially improving
performance and enhancing the user experience.

Open the “DirectQuery.pbix” file and switch to Model mode.
Highlight the Workshops table and set the mode to “Import”.

Because the Workshops table has a relationship to the Events
table (which is still in Import mode), Power BI displays the following standard
dialog.

Deactivate the option “Set the affected tables to dual” and
click OK.

Highlight the Workshops table and observe that the only
available storage mode is “Import”. (We cannot change the mode back to
DirectQuery or change it to Dual.)

Highlight the Clients, Tutors and Venues tables and set
their storage mode to “Dual”.

Power BI displays a standard warning message regarding the
implications of using the Dual storage mode.

Clients, Tutors and Venues tables and observe that we can
still change the mode back to DirectQuery or change it to Import.

Look in the bottom right of your screen and observe that the
storage mode is now shown as “Mixed”.