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:
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).
In this example we will use Production.Product table in AdventureWorks OLTP database.
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
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.
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!
Latest posts by Adrian Chodkowski (see all)
- SQLDay 2017 – one of the best data platform conferences - January 16, 2017
- Analysis Services Tabular translations and Tabular model explorer - December 18, 2016
- SQL Server 2016 is available! - June 1, 2016