The most typical way to utilize Power BI is to import the data into the tool and define the data model structure there. This is the ideal method to use Power BI in many situations. It is, however, not the only way to use Power BI. There are multiple ways to connect your data to Power BI. We have exceL, SQL or even paste our data into a blank table. Here in this article we will see how we can connect our data from excel.

To view accessible data sources, use the Get data button label or down arrow in the Home group of the Power BI Desktop ribbon to open the Common data sources list. If the desired data source is not displayed under Common data sources, click More to access the Get Data dialogue box.

Excel spreadsheets

Let’s make a connection to an Excel data source. The worksheets contain some fictitious store information. You may get the file here. The Navigator pane displays the data in the file once Power BI Desktop loads the worksheet and reads its contents.

Once the data is loaded in Power BI you can view it in the Field Plane, where you can bring it on your canvas and build your visualizations.

Types of Data Connections

Power BI provides three options for connecting to a data source. Each of these connection types has its own set of advantages. Each has cases where it is the best approach. These are as follows:

1. Import
2. Live Connection
3. Direct Query

Import

When a Power BI model is established, the Import connection type loads and stores the data within it. The original source of the data is not retrieved again until the model’s data has to be updated. This enables Power BI to maintain the model data in memory when queried, resulting in excellent performance.

After we’ve loaded the data into the Power BI model, we can add more organisation and structure. Table and column names can be altered to make them more user-friendly. Hierarchies can be established. It is possible to form relationships. Measures and KPIs can be used to add business definitions. These additions form the data model’s structure. All of this structure is saved as part of the Power BI model when using the Import connection type.

To conclude, when Import mode is used, both the data and the model structure are kept within the Power BI model. The bottom row of the Power BI Connection Types diagram represents this. The main benefit of Import mode is the speed gained from using the in-memory model in comparison to the query performance of whatever is providing the underlying data. The main disadvantage of Import mode is that the data from the data source is refreshed on a regular basis. As a result, the data may become outdated. In addition, each time the data is reloaded, some model downtime is necessary.

Live Connection

The Live Connection mode understands how to use the unique properties of a Tabular or multidimensional model hosted by SQL Server Analysis Services. Power BI does not contain any data. Instead, Power BI connects with the SQL Server Analysis Services model to obtain the data required for each request made by a user.

Furthermore, no data model structure is created inside Power BI. Any definition of how we calculate numbers, what tables are related to one another, and so on comes from the enterprise data model to which we are connected. With Live Connection mode, we enable Power BI to function as a powerful data visualization and exploration tool for data stored in enterprise-level data models.

The Live Connection mode is fully dependent on the data and model structure provided in the data source.  The benefit of using Live Connection mode is that we can easily leverage existing enterprise-level models. We don’t need to recreate the effort that went into developing that business model. Furthermore, numerous users can access the same data model, reducing duplication of work. The downside of Live Connection is that we cannot add our own content to the model specification. Using the existing data model, we can undertake self-service data exploration, but we cannot develop really self-service business intelligence.

Direct Query

Direct Query sits between the functionality of Live Connection and Import. Direct Query, like Live Connection mode, leaves the data in the data source alone. It does not import the data into the Power BI model. Instead, it queries the data from the source when responding to a user request. In this situation, T-SQL queries are built behind the scenes to retrieve the relevant data from the database. This ensures that we are always getting live, up-to-date data for our analyses.

This implies that we always have access to live, up-to-date data for our analyses. The disadvantage is that performance may suffer, particularly when searching and aggregating big amounts of data.

The model structure is preserved within the Power BI model, just as it is in Import mode. That means we have control over the structure and can shape it whatever we see fit. We are able to provide true self-service business intelligence. 

We can use Direct Query mode with:

• SQL Server relational databases
• Azure SQL Database
• Azure SQL Data Warehouse
• SAP HANA

To conclude, when Import mode is used, both the data and the model structure are kept within the Power BI model. The bottom row of the Power BI Connection Types diagram represents this. The main benefit of Import mode is the speed gained from using the in-memory model in comparison to the query performance of whatever is providing the underlying data. The main disadvantage of Import mode is that the data from the data source is refreshed on a regular basis. As a result, the data may become outdated. In addition, each time the data is reloaded, some model downtime is necessary.