Every manager’s dream is to have a big screen in front of him with a dashboard that provides information about (almost) everything that happens in his company. And can this work in real time? Yes, and this feature has been around since October 2019 for all Power BI Professional and Premium users who use DirectQuery to load the Data Model. Recently, some novelties are available that make this process even more efficient …
DirectQuery is one of the ways Power BI connects to data sources (databases). On this occasion, they are not imported or copied to the Data Model, but a connection is established with the database, the data are downloaded when needed and to the necessary extent. Therefore, it can be said that users who view reports created using DirectQuery always view up-to-date data.
How does this work in practice? First of all, when creating a report, it is possible to set Automatic page refresh in the settings of each page. Power BI Professional subscription users can have reports that are refreshed every 30 minutes, and Premium users basically get the option to refresh every 5 minutes, but in the Admin panel it is possible to further reduce this period so that the refresh period is shorter. The shortest refresh period is 1 second.
In addition to the basic way to automatically refresh the page, the Change Detection option has been introduced since June this year. The point is that at the level of the data model, it is possible to add a measure that monitors changes in the system, i.e. refreshes the data every time an event occurs. For example, if someone adds a new partner to the system, if a sales transaction occurs, etc. We run this option after selecting Change Detection from the Modeling ribbon. A window will open in which we first need to choose whether to create a new variable or change an existing one. To the right, in the Fields section, is a list of all available reporting dimensions. It is only necessary to drag one that we want to follow, e.g. IDT (transaction code), in the Choose field to apply it to box. Then, in the Choose a calculation drop-down list, we change the function that performs the aggregation. If e.g. we choose Count (Distinct) the system will detect the change every time the number of unique transactions changes or, simply put, when someone sells something. In the end, you only need to choose after which period Power BI should check whether this measure has changed. We can do this in the page setup as well as in the Power BI Admin panel.
How effectively does all this work? How busy are the resources and how fast is the report displayed? To get answers to a number of questions we need to run Performance Analyzer. This option is located within the View ribbon, and when we start it, the panel on the right side of the Power BI Desktop window will open. To see the changes you need to click on the Start Recording button. We will get a table with 2 columns: Name and Duration. The first indicates the query, measure or report and the second shows the refresh period. Based on this data, we can analyze reports and try to optimize them to work faster. In a similar way, we can monitor capacity usage using the Capacity metrics application running within the Power BI service.