Unlike other programs, or solutions for creating advanced reports, as we have already seen what Power Bi is, the Microsoft tool for Business Intelligence Power Bi offers different ways of working and relating to the data source, or origin of the data. data; Although importing data is the most common and used option, due to the habit of working in this way with other programs, Power Bi also offers a direct connection service to other data sources uploaded or saved in the cloud. The objective of Power Bi as an "all in one" tool is to offer the user a single solution in data modeling, understanding as "unique" the possibility of working only with a single tool that makes the connection with the data, which offer functions to build tables,
To build a house, we must start with the foundation, and not with the roof. Using logic and common sense, before starting to use Power Bi, and carrying out our first projects, we must know how we first got to visualize the data within Power Bi desktop, we must know what methods exist and Power Bi offers to be able to work and model data. Import or connect the data with Power Bi? In short, we must understand the different ways that Power Bi has of relating to data, the pros and cons that exist in each case in order to find the way of working that best suits our needs, and not the other way around..
Like any other tool, Power Bi offers the possibility of importing data from a file or file, but it also offers us the possibility of making a connection with the data source. This function is called Direct Query, a very useful function, depending on the needs, to be able to make dynamic and visual reports that avoids having to export data from tools and integrate them into the Power Bi environment manually.
Both in the data import mode and in Direct Query, there are some limitations that we must know before creating models or developing a Dashboard. These limitations are mainly located in the volume of data or weight allowed, as well as in the functions available in each case. Let's say that the weight of the database, and the functions to use, will determine which model is best for us in each case: import or connection to the data source..
1 Import data in Power Bi: What is importing data in Power Bi, advantages and disadvantages
Importing data into Power Bi is the most used method because of Power Bi's simplicity and usability, but also because of the "traditional" way of working with data and with other tools. In general, we save the data in the PC's memory, or in a cloud storage system such as OneDrive, and we open it directly from the file or using a program for editing and calculation, such as Excel.
In this case, in import mode, the data is imported directly into Power Bi, commonly from a file with an Excel extension. It is precisely this characteristic that makes the import mode a very interesting way of working with data, due to its simplicity and the wide range of extensions allowed in Power Bi, but which in turn presents some limitations, underlining that only in case of exceeding the allowed weight of publication in import mode.
IMPORTANT
You will see that, in many cases, the weight limitations of Power Bi's import mode are mentioned as an impediment and problem with this model. However, in practice it is likely that you will never have a problem even working with very large databases, and preparing complex reports in Power Bi.
When we talk about the weight limitations, in any case, these limitations refer to the weight limit when publishing a file, and not to the limitation in uploading the data in Power Bi . This limitation is 1 GB, expandable to 10 GB in the Power Bi Premium version
When we import data into Power Bi, an important factor to also consider is that we are hosting the data again by creating a new database, although in this case it will be compressed and will have less weight thanks to Power Bi's compression capabilities. (compresses the data until it is reduced to 10% of the weight of the original file).
When updating the Power Bi report that you are going to build, using the import method, a manual update would be required when the source data changes, either occasionally or periodically. If, for example, you work with source data that changes weekly because data is added every week, and every week you have to create reports that reflect the updated data, you should take into account that you will have to import changes weekly. If there are changes to the source data, you must import all the data again. This would not be a problem when working with data import in Power Bi, because as we will see later, this update can be scheduled automatically or done manually if required..
Now, importing data is one of the best ways to work in Power Bi, one of the most stable ways in terms of features, tool capabilities, and fluidity. Thus, when importing data, the 3 available scenarios that would be Reports, Data and Model will be enabled.
As the data is integrated directly into Power Bi, any action to be carried out will be executed in a much faster, more dynamic way, without latencies; We can also view the data and make changes and introduce new calculations.
With the import of data, as we already mentioned, we have the weight limitation in the publications, and that depending on the volume and weight, the free version of Power Bi could fall short. In any case, to reach or exceed the weight limit (1GB) the volume of the publication would have to be excessively large, and you will probably never reach it. If so, as we already mentioned, there is a premium version of Power Bi, which extends the weight limit of the publication from 1 to 10 GB. These limits are also likely to increase over time, in the good sense of the word, allowing users greater weight in the publication of reports.
Advantages Power Bi import mode
• All Power Bi features are enabled in import mode, including the data tab
• We can transform the data inside Power Bi before visualizing data in graphs and tables
• This model is much more fluid and faster, when it comes to perform queries, apply filters, or view data
• Greater control when updating data, or when establishing automatic updates
Disadvantages Import Power Bi Mode
• Limitation in the weight of the models (1GB)
• Manual process by having to manually import the data initially
• To update the data it must be done manually or configured for automatic updates. By default, the data in your Power Bi reports will not be updated automatically
• Possible mismatch of data with respect to the source data if no data update has been performed
2 Direct Query: Connecting Power Bi with databases or external data sources
Direct Query, as we already discussed in "what is Power Bi" is a way to work with data in Power Bi without the need to import or store the data. When working in Power Bi with Direct Query, every time a filter is performed on the information, the view is changed, or you want to see the data in another way, Power Bi connects directly where the data source is uploaded, having to perform the query in real time.
Unlike Import mode, with Direct Query, we don't have to make updates to the data. Since Power Bi is connected to the external data source, changes to the source data, updates, or new records in the data source will be taken into account by Direct Query to update our tables, charts, and reports. Also, since you don't have to import and save the data to work on it, there is no weight limit as there is in import mode. It is precisely this detail that is the main advantage with the import mode, a detail as we have previously commented could be irrelevant in the more than likely case of not exceeding the weight limit in the publications.
Against Direct Query, there are precisely the disadvantages of not hosting the data, and therefore every time we perform a query, change the view, or filter information, a query or call to the data source must be made, resulting in Ultimately, an experience that in some cases can be less fluid, depending on our connections and the power of our PC. If we need to constantly make changes to the visualizations, this is probably not the most interesting model for you.
Also, although all the Power Bi modules (Reports, Data and Model) were available in the data import, the data module in “Direct Query” is not available. Translated into the language we care about, this means not being able to perform data transformation.
Also, we must take into account that not all data sources are valid from Power Bi. Although Power Bi Desktop in its Direct Query function is already compatible with most of the important programs and applications that save data in the cloud, you could find some cases of non-compatibility. In this case we would have to solve it by exporting the database and converting it into a "readable" format for Power Bi, or by saving the database in an application that does currently have a connection to Power Bi.
Direct Query Advantages
• No data is saved, it does not generate extra weight in storage
• The weight limit of 1 BG that we find in the import mode, in Direct Query does not exist
• Data always updated with respect to the source data
Direct Query Disadvantages
• Some Power Bi functions are not available in Direct Query
• The tab or data module is not available with Direct Query (It is not a way designed to edit the tables or transform the data, but to take advantage of the visualizations of the Power Bi report mode, to make reports and Dashboard)
• One of the main drawbacks of Direct Query is the slowness that it can cause to perform a query, perform a filter in the reports. Every time a query is made, in Direct Query mode Power BI has to make a call to the source of the data, which is hosted on another server.
3 Import or DirectQuery?
This is a question that arises when we start working with Power Bi, an initial doubt that we will clear up by using the application. With use and time, you will surely lose the initial "fear" that the import mode will fall short, it will not be enough.
Although it is true that the answer will be different depending on the circumstances of each user, also depending on the need in each case, taking into account the important arguments, such as the functionalities, it is advisable to start using the import mode.
Although there are limitations as we have been commenting, it is possible that you will never get even close to the limits established in the import mode that we remember are 1 GB at the time of the publication of the service. And in case you get close, or reach the established limit, you can always upgrade to a Power BI Premium account, where the limit would become 10 GB. In exchange, we have a model where we can work freely with 100% of Power Bi's capabilities, which, as we have already said and repeated, are fully available only in data import mode.
We can also attest that you can work with Excel files that reach the maximum number of rows allowed (1,048,575 rows) and we have been able to import the data and create graphs and Dashboards without finding errors.
Direct Query mode certainly provides an advantage because of the ability to directly connect to external databases, and the system works. But you must take into account the Handicap of the Direct Query mode when using Power Bi: we will not be able to use the data tab, and the fluidity in the queries can result in an unpleasant experience. Many professional users use the Direct Query mode due to the diversification of data sources, having different databases on different servers and with different formats. In some cases, the Power Bi function is "limited" to building a report or Dashboard; No edits are made to the tables and no data is transformed. In these cases,
In any case, the choice and use of one model or another (Import vs. Direct Query) will depend on the use, the volume to work with. But we must first keep in mind what use we are going to give Power Bi in principle, and pay attention to the existing limitations in each case. Using the import mode in the first instance will help us to better understand the capabilities of Power Bi Desktop,