Database connections

1. Database connections

Welcome back! In this chapter, we will start looking into different options for data source connections, and topics related to changing our imported data processes.

2. Databases in Power BI

Power BI Desktop gives users over 35 different types of databases to connect to, such as SQL Server, MySQL, and PostgreSQL to name a few. It is possible to connect to databases that are in local or cloud-based environments. Once again, it is important to note that databases are a type of data source for Power BI. They provide the path to the data we want to work with within Power BI Desktop. Data sets are a collection of both the data source and the data contained. Any connection made via a data source to Power BI results in the creation of a dataset from that source.

3. Storage modes

Some data sources have multiple storage options for connecting data to Power BI. This is also true for databases, with some having more than one option for connecting to data sources. In general, there are three main data storage types; Import, DirectQuery, and Live. Import is the default type and is available for all data sources in Power BI. DirectQuery is not available for all data sources that can connect to Power BI. Live connection types are not as common, as only a few data sources can support this type.

4. Storage modes: Pros and cons

Overall, the main pros of Import mode are that once the connection has been made and the data from the source has been cached, it is the fastest possible connection. It also provides users with the full use of Power BI features including Power Query and DAX functions. However, some of the cons of Import mode include file size limitations that can affect how much data you can bring into the file. Also, regular refreshes are required, which can mean a lot of time-consuming processes have to be carried out. DirectQuery is able to support large-scale datasets, and the fact that it queries the data source when required means reports and dashboards are "near real-time" in terms of data displayed. However, it does not support all Power BI features, as they have to be written in native queries understood by the source. Live connections also support large-scale datasets and have unlimited refresh frequency. The cons of this storage mode include that Power Query is not available, and the connection can be much slower.

5. Import mode

As mentioned already Import mode is the default setting that is available for all data sources. This storage mode actually brings in the data from the data source, and it stores it within the Power BI environment.

6. Import mode

The data is cached in Power BI. This means that we can manipulate the data within Power BI without affecting the actual data in the data source. This storage mode is compatible with all features available in Power BI and provides us with the most complete experience of the software.

7. DirectQuery mode

The second most common storage mode is DirectQuery, which is not available for all data sources. For this storage mode, a connection is made between the data source schema and Power BI Desktop, however, data is not pulled into Power BI.

8. DirectQuery mode

What happens is that Power BI communicates in the language of the data source and brings in data as and when required. One main advantage of this storage mode is that data doesn't need to be cached in the Power BI file, so file sizes can be reduced and overall performance is improved.

9. Changing data connections

After creating a data connection, it still might be necessary for you to make changes to the connection. Power BI allows users to make changes to data connections, which can be especially useful when database connections are involved. This can be done from a number of different locations within Power BI. After any changes are carried out to data connections, it is important to refresh the connections that have been made. We will look into these topics in more detail later on.

10. Let's practice!

Alright, time for some practice. Good luck!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.