1. Exploring connections
As we have seen, the database of choice for WWI is MySQL. This was set up using this window. MySQL database only provides us with the Import storage mode option.
Let’s see how the process starts for some other database connections with different storage mode options. If we go to Get data and select SQL Server we see a similar window appear. However, for this database connection, we have the option to choose the storage mode type. We can either select Import or DirectQuery, and Power BI will do the rest of the work in the background.
When a connection has already been set up, Power BI still gives us the ability to make changes to those connections. When in the Home tab, we can click on the Transform data button in the Queries section, and click on the Data source settings option.
We will then see a new window appear that shows us the Data sources that are currently active in the file. Here we have options to change the source, edit permissions, or clear permissions.
If we click on Change Source, we will see the MySQL database connection window appear. If a database has changed its name, or there is a new server name that is being used, we would be able to make changes from here.
If we click on Edit Permissions, we will see a new window appear. Here we can Edit or Delete the Credentials of the database connection. This is useful if there are changes to usernames or passwords for specific databases. In addition, we can also change the Privacy Level from this window for the database.
It is also useful to note that Power BI allows us to make changes to data connections through Power Query Editor. This time, let’s click on Transform data. We now have the Power Query Editor window appear, that you will have seen before. Once again there is the Data source settings option here.
Another use of the data source settings option is the ability to change any query that has been used before importing any data. To see this in practice, we will create a new data connection to an ODBC data source. The data stored in this data source is the same as the MySQL one, we are just using different software as an intermediary between the database and Power BI. Once we click on the ODBC option in Get data, a new window will appear. Here we are able to choose the data source name. In our case, we will choose the Learner data source name. We will click on Advanced options and enter an SQL query using the native language that is related to our original MySQL database.
Once that query has been entered, we can click OK and our new table will be created. The table created has the name Query1, indicating that it has been formed using a coding query. Let's make a quick card visual using a variable from the new table. Let’s use the total excluding tax variable, and visualize the total amount.
Now, we wish to remove a certain Package type from the data we are bringing in from the ODBC connection.
We can see this column in the WWI_FactSale table, and check the current unique values contained. Let's go to Data source settings to do this. We choose our connection and edit the SQL query we had entered previously. This will now include a clause that will remove the “Each” package from the dataset being brought into Power BI.
Once we have updated the query, we can click OK. And then on Close in the Data source settings window. Power BI is warning us that there are pending changes in our queries that are not yet applied. Let’s click on apply changes.
Aha! We see a change in the total sum of the total excluding tax that was created using the ODBC connection. By changing our query, we added additional filters to our data import, and so the data being brought into Power BI has now been changed.
Great, we are learning more and more data connection features of Power BI. Now it’s back to you.
2. Let's practice!