{"id":265,"date":"2022-05-01T20:00:02","date_gmt":"2022-05-01T23:00:02","guid":{"rendered":"https:\/\/gestiaconsultores.com.ar\/en\/?p=265"},"modified":"2022-12-01T17:11:37","modified_gmt":"2022-12-01T20:11:37","slug":"connection-types-in-power-bi","status":"publish","type":"post","link":"https:\/\/gestiaconsultores.com.ar\/en\/news\/connection-types-in-power-bi\/","title":{"rendered":"Connection types in Power BI: Live Connection, DirectQuery, or Import Data."},"content":{"rendered":"

\"2017-09-13_12h13_49\"<\/p>\n

Power BI supports three methods for connecting to data sources, called Live Connection, DirectQuery, and Import Data (also called Scheduled Refresh). In the following points we will analyze what each of these types means, their characteristics, pros, cons, and which one to choose in each situation.<\/p>\n

Determining which type of connection to choose between these (or Composite mode that integrates DirectQuery + Import Data) is a major decision in defining the architecture of a Power BI solution, since it is expensive to change the connection type once when one of the ways is defined.<\/p>\n

Import Data<\/h2>\n

This method is called “Import Data” because the data is loaded into Power BI. Some call it \u201cScheduled Refresh\u201d since you must schedule the upload and refresh of data in the Power BI service. Loading the data into Power BI means consuming memory and disk space, either on the development machine’s memory and disk space (when using Power BI Desktop) or in the Power BI service (when the report is published to the cloud). The greater the number of tables and data, the greater the use of resources.<\/p>\n

There is no simple, straight forward calculation to know how much your source data is going to occupy in Power BI memory (i.e., it is not rows by columns by data type), as Power BI uses the xVelocity<\/strong> compression engine, which compresses the data and stores them in a compressed format. A 1 GB Excel file might be as small as 10 MB in Power BI, depending on the number of unique values in the column, data types, and other variables. In short, the size of the data in Power BI will typically be much smaller than the size of the data source.<\/p>\n

Import data important pros and cons<\/h3>\n

PRO \u2013 Power BI 100% functional<\/h4>\n

With this method you can use all Power BI features and components, both Power Query to combine data from multiple sources and DAX to write advanced time intelligence expressions or visualizations. There are no limitations on Power BI functionality with this method.<\/p>\n

CON \u2013 Size limitation<\/h4>\n

Size and capacity limits apply with this method. On the one hand, each model or file in Power BI cannot occupy more than 1 GB (with Premium capacity or Premium licenses this limit is 100 GB), always considering the compressed model. Then, you have a size of up to 10 GB in the account, while Power BI Premium has a limit of 100 TB. If using Power BI Report Server (the on-premises version of the Power BI service), the size limit is 2 GB per file.<\/p>\n

PRO \u2013 Fastest method<\/h4>\n

This connection method is the fastest viable option. Report queries will be evaluated against the uploaded data and used from server memory, with no latency issues.<\/p>\n

DirectQuery<\/h2>\n

DirectQuery is a direct connection to the data source. The data is NOT stored in Power BI, so Power BI is primarily a visualization layer and queries the data from the data source whenever it requires it. Power BI only stores the metadata of the tables (table names, column names, relationships), but not the data. The Power BI file size will be much smaller and will most likely never reach the size limit because there is no data stored in the model.<\/p>\n

DirectQuery is only feasible for certain data sources. Here are a few (full list in Power BI data sources)<\/a>:<\/p>\n