Direct Query in PowerBI

In the latest update of PowerBI Desktop we have the opportunity to read data directly from data sources using DirectQuery technology! So from now our model is no longer just a static copy of the source data and doesn’t require manual refresh – our model can be only the reporting interface between our report and data source.

So far, most of the models that i worked with (in Pivot Power or Power BI Desktop form ) require automatic data refresh. It was a problem to do that because in most cases i my clients don’t have Sharepoint Server installed and PowerBI infrastructre enabled, but from now this problem is partially solved with a new version of PowerBI Dektop.

How to implement DirectQuery in PowerBI Desktop? It is very easy – Let’s run Power BI Desktop. Open new document and after that we need to click GetData. After that from menu we need to choose SQL Server as you can see in the following screenshot:

Get data from SQL Server

Next steps is also very easy because we need to provide server name and optionally database table where our data is located (or specific SQL Server query that retrieve data for us).

 

DirectQuery2

In this example we will use Production.Product table in AdventureWorks OLTP database.

DirectQuery3

Now we can choose Connection Mode that we will use in our model. We can choose:

  • Import – all data will be loaded directly to our model (classic approach)
  • DirectQuery – data will be retrieved from data source, our model doesn’t store any data

DirectQuery4

So to depict new DirectQuery technology let’s choose that. From now every change in a report will send TSQL query to data source – to be sure we  create very simple report with Count of ProductID and slicer with ProductLine.

DirectQuery5In SQL Server Profiler trace we can notice that when we use slicer to filter data, Power BI Desktop sent few queries to retrieve current data.

DirectQuery6

With  DirectQuery and proper indexing eg . by using columnstore indexes we can create  very powerful real-time, or very close to real-time reporting solution. For more information about the changes in the PowerBI you can read on the official blog . Thanks!

Leave a Comment

Your email address will not be published. Required fields are marked *