When a dashboard element is created or edited, Direct Query or a direct link to source data is used to retrieve the data source. Direct Query in Power BI allows you to create visualizations from very huge datasets that would otherwise be impossible to do with pre-aggregation. Changes in the underlying data may necessitate a data refresh.
 

Types of Queries in Power BI

Before data files are brought into Power BI, the Query Editor in Power BI is used to alter or edit them. The Query Editor is a data container that allows you to manipulate data by choosing rows and columns, separating rows and columns, pivoting and unpivoting columns, and so on.

Home, Transform, Add Column, View, Tools, and Help are the four tabs on the Power Query Editor ribbon.

source: docs.microsoft.com
The Transform tab allows you to perform common data transformation activities like:

• Columns can be added or removed.
• Altering the data kinds
• Dividing column
• Additional data-driven tasks

 

What is Direct Query?

Power BI Direct Query is a data retrieval mechanism that gets data from the data source immediately at query time. The final half of the statement is crucial: although Import mode maintains an in-memory snapshot of your data, Direct Query (DQ) does not. It goes straight to the data source (which is 99 percent of situations is SQL database) and pulls the data from there for each request.
 

Which Data Sources Support Direct Query?

Power BI Direct Query is only supported by a few data sources, unlike Import Data, which is supported by all sorts of data sources. A Direct Query connection to an Excel File is not possible. Direct Query mode is usually supported by data sources that are relational database models or have a modeling engine.

Some of the Power BI Direct Query sources are:

• Redshift (Amazon)
• Google BigQuery (Beta)
• IBM Netezza (Beta)
• Impala (Beta)
• Azure HDInsight Spark (Beta)
• Azure SQL Database (Beta)
• Azure SQL Data Warehouse (Beta) (version 2.x)
• Database Oracle (version 12 and above)
• SAP Enterprise Resource Planning (ERP) (Beta)
• SAP HANA
• Snowflake (Beta)
• Spark (version 0.9 and above)
• Teradata Database
• SQL Server
• Vertica (Beta)

 

How Direct Query Works in Power BI?

When you enable Power BI Direct Query, Power BI makes a query to the data source every time you see a visualization and the result is returned. SQL Profiler can be used to examine this procedure. SQL Profiler is a program that allows you to record SQL Server database queries.

img source: mssqltips.com

In the SQL Profiler, you can see that queries were sent to the database. Each visualization has its query. Even if two visualizations display the same item, two messages are sent to separate queries to the database.
 
Power Query Editor Elements

a. Column Navigation

b. Row Navigation

c. Cell Navigation

img source: thepoweruser.com
 

How Do You Write a Direct Query in Power BI?

When a dashboard element (or graphic) is created or edited, Power BI Direct Query or a direct link to source data is used to retrieve the data source. This approach contrasts with the more “traditional” approach used with Power BI, which involves connecting to a data source, transforming, filtering, and modeling a small portion of the data, and then putting all of the required data into the Power BI compressed in-memory data model.

If you’ve used Power BI before, you’ll know that loading and compressing huge data sets from several relational database tables or data warehouse fact and dimension tables can take a long time.

Although using a powerful workstation with loads of RAM, an industrial-strength server, and a fast network can speed up this procedure, it can also slow down your creative data analysis. 

Performance Optimization in Direct Query

You can optimize performance by keeping track of them. You can keep track of performance to spot bottlenecks. The focus of ongoing optimization should be on slow queries—or report visualizations. In Power BI Desktop, monitoring can be done at design time, or on production workloads in Power BI Premium capacity.&nbsp:

Benefits of using Direct Query in Power BI

Using Power BI Direct Query has a number of advantages:

• Direct Query allows you to create visualizations from very huge datasets, which would otherwise be impossible to do with pre-aggregation.
• Changes in the underlying data may necessitate a data refresh. The necessity to display current data in some reports can necessitate huge data transfers, making re-importing data impossible. Direct Query reports, on the other hand, always use current data.
• Direct Query is exempt from the 1-GB dataset limit.

 

Power BI Direct Query Limitations

Limitations of Direct Query in Power BI are

• An error occurs if the Power Query Editor query is extremely complex. Either eliminate the problematic step in Power Query Editor or import the data instead of using Direct Query to fix the error. There is no Power Query Editor for multi-dimensional sources like SAP Business Warehouse.
• The Power BI Service does not support calculated tables and calculated columns that link a Direct Query table from a data source using Single Sign-on (SSO) authentication.
• Direct Query does not support auto date/time. In Direct Query mode, for example, particular treatment of date columns (drilling down by year, quarter, month, or day) is not possible.
• Cloud sources (which are any data sources that are not on-premises) are limited to one million rows, while on-premises sources are limited to a defined payload of around 4 MB per row (based on proprietary compression technology) or 16MB data size for the full visual. When using Premium capacity, some constraints may be raised. The restriction has no bearing on the aggregations or calculations used to generate the Direct Query dataset. It only has an impact on the rows returned. Maximum row limits can be established by premium capacity.
• For Direct Query sources, there is a 125-column limit in a table or matrix for results with more than 500 rows. A scrollbar appears when presenting a result in a table or matrix with more than 500 rows, allowing you to download more data. In that case, the table or matrix can have a maximum of 125 columns. If you need to add more than 125 columns in a single table or matrix, try utilizing MIN, MAX, FIRST, or LAST to create measures that do not count towards the 125-column limit.

 

Security Warning of Power BI Direct Query

A security warning popup will appear if you use Power BI Direct Query datasets with Azure Analysis Services (AAS).

The same security caution applies to mixing Direct Query and import sources in a data model: data can be pushed from one data source to the other.

In-Memory analytics in Power BI Direct Query

In-Memory with Power BI Direct Query is a hybrid model. By default, queries should be replied to using the In-Memory mode; nevertheless, the client’s connection string might specify the Direct Query mode instead.
 

Power BI & Direct Query Best Practices

When utilizing Direct Query mode in Power BI, you should follow the recommended practices listed below –

• Avoid complex Power Query transformations – Power Query will construct a query and send it to a source database each time you apply a transformation to your data model. If you must utilize calculated columns, make an effort to create them on the source database and keep them durable.
• Avoid using complex DAX measures – as your DAX statement must be “translated” into SQL, this can result in expensive SQL queries. If at all feasible, do all of your calculations on the source site.
• Avoid relationships on GUID columns (unique identifier) – Power BI doesn’t support this data type and requires data conversion during query execution, which slows down the query. The solution is to convert this data type before Power BI generates its own queries in the source database.

When possible, limit parallelism — you can specify the maximum number of connections that DQ can open at once.
 

Power BI: Import Mode Vs Direct Query Mode

Import Mode:

Import Data Connectivity mode allows you to import data into the Power BI cache. When the data size is less than 1 GB and the data is not constantly changing, it is highly advised to use the Import connection. To acquire the most up-to-date data, you can import data with a scheduled refresh. You may use the high-performance query engine to its full potential with the Import connection.

DirectQuery:
Direct Query Connectivity mode allows you to connect to data directly. When the data changes frequently and the data volume is big, Direct Query mode is utilized to construct real-time or near real-time BI solutions. There will be no data imported into Power BI. Instead, whether you create a visual or interact with one, Power BI will send queries to the data source. Each query must return no more than or equal to one million rows.
 

How and When to Use Power BI Row-Level Security?

Setup of Row-Level Security in Power BI:
Static RLS:

1. Launch the Power BI Report
2. Go to ‘Modeling’ and then ‘Manage Roles.‘
3. Choose Create’ and give your new position a name.
4. Select the vertical ellipsis under ‘Tables’ and then ‘Add filter’. Choose the table field you wish to filter by.
5. Include reasoning for the criteria you wish to use to filter the field. Check the box (‘Verify DAX Expression’) and then click Save.
6. Validate: Choose ‘Modeling,’ then ‘View As,’ than the role you just created. Check that the data is filtering as expected by selecting ‘Ok.’
7. Save or publish a Power BI report to the Power BI Workspace.
8. Navigate to the Power BI Workspace.
9. Select the vertical ellipsis (‘More choices’) on the dataset (in Orange) and then ‘Security.’
10. Select ‘Save’ after adding people to the roles you created.
11. Select the vertical ellipsis adjacent to the security group (‘More options’) and then ‘Test as role. Verify that the RLS is functioning properly.

Dynamic RLS

Directions:

1. Launch the Power BI Report
2. Include a dimension table.
3. Connect the fact table to the dimension table.
4. Go to ‘Modeling’ and then ‘Manage Roles.’
5. Click ‘Create.’ Give your new role a name (‘Users’ is a good example).
6. Select ‘Tables’ from the drop-down menu. Choose the vertical ellipsis option. Choose ‘Add filter’ from the drop-down menu. Choose the field you wish to filter by from the table (‘user email’).
7. Add logic to filter the field (‘user email = userprincipalename()’) and check the box (‘Verify DAX Expression’). Choose to save.
8. Validate: Choose ‘Modeling’. Choose ‘View As’. Choose the role you just created. Choose ‘Ok.’ Check to see if the data is filtering as it should.
9. Save or publish a Power BI report to the Power BI Workspace.
10. Navigate to the Power BI Workspace.
11. Select the vertical ellipsis (‘More choices’) on the dataset (in Orange) and then ‘Security.’
12. Add users to the role you created (‘Users’). Choose ‘Save’.
13. Select the vertical ellipsis adjacent to the security group (‘More options’) and then ‘Test as role.’ Verify that the RLS is functioning properly.

 

When should I use Row-Level Security?

Consider whether your dataset contains sensitive information to see if RLS is required for your Power BI report (e.g., company financials, customer information, patient information, payroll information, or employee information).

Second, assess whether end-users will need to use filters to find the information they need. If you agree with these assertions, you should use RLS in your report.
 

Determining the queries sent by Power BI Desktop

If you’re not familiar with SQL Server Profiler or other “conventional” tools, you can use Power BI Desktop’s Performance Analyzer capability in conjunction with DAX Studio to achieve the same result.

• You should be able to view total times for each visual on your report canvas if you simply refresh the visuals:
• Go straight to a DAX Studio after selecting Copy Query. Scroll down after pasting the copied query into the main window to see the SQL query.
• Within DAX Studio, enable the “All Queries” button and wait a few moments for the Query trace to begin:
• Return to Power BI Desktop and refresh the visualizations. After that, reopen DAX Studio and go to the All queries tab:
• All of your queries will now be collected at the same time, you can also click on an individual query after turning off the All Queries tab and turning on the Server Timings tab:
• When You double-click the selected query, DAX code appears in the main window, allowing you to click Run and then scroll down to the Server Timings tab:
• There are several measures relating to server performance, such as CPU time, as well as SQL queries generated by Power BI. You can also look at how long Formula Engine (FE) and Storage Engine (SE) took to complete the request.

 

Understanding the form of the query sent by Power BI Desktop

The left (Queries) pane

The Queries pane on the left shows the number of ongoing queries as well as the query’s name. When you choose a query from the left pane, the data from that query appears in the center pane, where you may shape and change it to match your needs.

The right (Query Settings) pane

All steps related to a query are displayed in the right pane or Query Settings pane.

Implementing a Power BI push dataset solution

Push datasets: The tables are imported into the dataset, and new rows are incrementally added to the tables. This solution has no impact on the data source database performance because there are no additional SQL queries sent to the data source. However, you cannot create push datasets using Power BI Desktop.

You can only create reports connected to published datasets. Push datasets require specific APIs to create the model and update the tables. Push datasets have limitations in the features supported.

Implementation:

The following steps are required for the push dataset to be implemented:

• As a push dataset, define and deploy the data model.
• Copy the history data from the imported dataset into the model.
• Every 30 seconds, write new transactions to the Sales table, grouping the rows in a single POST rows call.

The first two steps can be completed without any programming knowledge. Instead, you can use the Power BI Push Tools that we previously described. This open-source project consists of the following components:

• A library that makes it easier to use the Power BI API libraries to develop and maintain a push dataset model.
• Create, maintain, and test a push dataset with this command-line tool.

 

Behavior in the Power BI Service

When using Get Data in Power BI Desktop to connect to a data source with Direct Query, the connection behaves as follows:

• The source is chosen during the initial Get Data experience. A set of tables is chosen for relational sources, and each defines a query that logically returns a set of data. Only the source is selected for multidimensional sources like SAP BW.
• However, no data is imported into the Power BI store when it is loaded. Rather, queries are sent to the underlying data source to retrieve the appropriate data when you create a visual in Power BI Desktop. The amount of time it takes to refresh the visual is determined by the speed of the underlying data source.
• Any underlying data changes aren’t immediately reflected in any existing visualizations. Refreshing is still required. For each visual, the appropriate queries are resent, and the visual is changed as needed.

 

Publish to the Power BI service

Direct Query-generated reports can be published to the Power BI service. You must supply credentials before the Power BI service presents the published report if the used data source doesn’t require the On-premises data gateway (Azure SQL Database, Azure Synapse Analytics (previously SQL Data Warehouse), or Redshift). To provide the credentials, follow these steps:

1. Open Power BI and log in.
2. Select the Settings gear icon in the Power BI service and then the Settings menu item.
3. Go to the Power BI service’s Settings page, choose the Datasets tab, select the Direct Query dataset, and then click Edit credentials.
4. Fill in your credentials. Otherwise, when you open a published report or explore a dataset produced with a Direct Query connection, an error occurs.

Install an On-premises data gateway and register the data source for data sources other than Azure SQL Database, Azure Synapse Analytics (previously SQL Data Warehouse), Redshift, or Snowflake Data Warehouse that use Direct Query.
 

Conclusion

If you’re considering utilizing Direct Query, you might as well conclude that choosing the correct tool for the job is the best possible recommendation. Before making a final decision, thoroughly consider your potential workloads and try to find the pros and cons of the Direct Query approaches.