Using Power BI Desktop, the tool provided by Microsoft with its online service, allows users to view interactive reports on the cloud by accessing specific queries or by preparing the model directly with Power BI Desktop as it allows direct access to views or tables where the information source is located. These 2 alternatives have their pros and cons, which we will review next in such a way to to establish the best way to use.

Direct queries

Through direct queries you get all interactivity and online updating, but according to the attributes, columns and time of the query may show restrictions when you want to evaluate and deep your analysis. Many of these queries are intended to the end users on Excel and therefore they are restricted to a limited timing, which prevents using all of the Power BI functions to perform time intelligence. And working with a single table causes that results produced by Power BI do not perform well when dealing with tables with a lot of data.

This alternative allows in the short term the current queries to be utilized by the users and programmed directly in Power BI Desktop, and allows to analyze the current reports in a synchronized and interactive way. However, if the user currently has to process the queries to obtain new attributes and he must create several dynamic tables to analyze the information, then he will not have many options with Power BI Desktop to use the queries directly to view the reports. In this case the user must know about the support system provided by Power BI Desktop, the Query Editor, which allows to set the query and get the table with the required structure to analyze the attributes simultaneously.

Preparing a model directly with Power BI

On the other hand, when preparing a model directly with Power BI relating the fact tables and their dimensions, adding time tables and parameter tables, a compact model will be obtained with all the attributes and data. This will allow to measure, to evaluate and to deepen into the analysis, and get a huge number of reports with the same model with all the power of Power BI to obtain KPIs and detail of data to be evaluated.

By having models in this way, the user will visualize the data interactively by establishing different filter contexts automatically, that is, it will be possible to filter through tables and reports using rows, columns, slicers, all of the attributes to analyze the information in detail while letting the user to observe different periods of time in the same table or the ability to control the information to be evaluated on each column, which results in strengthening the analysis by easily creating indicators along with variations and intersections.

To prepare these models only requires your credentials for data Access, and runnig the Query Editor provided by Power BI Desktop to set up the models and performance required to carry out time intelligence.

Pedro Nel Pérez - Power BI Consultant

Pedro Nel Pérez

Power BI Consultant