From the course: Power BI Data Methods

Working with relational databases - Power BI Tutorial

From the course: Power BI Data Methods

Start my 1-month free trial Buy for my team

Working with relational databases

“

- [Instructor] Databases are organized data stored in database management systems. One type of database is a relational database. This consists of separate tables where we use SQL code to query data. Relational database connections in Power BI include SQL server, Oracle, HANA, ODBC, and many others. In general, my own personal approach to working with large sets of data is to use a database where I can. There's a lot of cool things that we can do with ETL frameworks, especially for testing them out, but ultimately databases can help us save a lot of time, especially if we're using Power BI models for heavy duty things that get refreshed frequently. SQL database connections also offer us an option for connecting to the data via Import or DirectQuery mode. We've been using the Import connection mode for all our connections so far, but some relational databases give us the option of using DirectQuery. We use Import as the typical default connection type. Microsoft recommends this method because it takes advantage of the high performance query engine and allows us to leverage the full range of options for working with data. DirectQuery allows us to connect to data in its original source repository. This option works best when the underlying data provides interactive queries returning data in less than five seconds or if this data changes frequently and we need the latest updates. Power BI supports DirectQuery for several database options, but I recommend checking the documentation to see if this is updated and your database is available. I set up the SQL server on my desktop to use in these examples. They're similar to the data we've seen so far in the exercise files in this course if you want to upload them to a database to try it out yourself. We'll choose to get data from a new source and then we'll choose SQL Server. I'm going to enter the server name and I'm also going to enter the database name. In order to connect to a database, we need our own set of database access credentials, which include the database name, the server, and any other security details we need to connect to the database. Note that the database credentials in Power BI may not transfer between users when you share files, so make sure that others accessing your report also have access to the database as well themselves. Notice the radio button to select between Import and DirectQuery. Let's set up our query using Import first. This takes us to the Navigator pane where we can select our table or tables to use in the model. Let's choose the weather and dates table. Selecting more than one table creates two separate queries like we saw earlier in this course for other connection types. From here, we can work with database tables like we would any other query. We can also input an SQL query to get the data directly. We'll choose to create another connection. We'll then enter the same server and database name that we entered earlier. We'll then enter our SQL code in the advanced options. If you do make the choice to include a query here, you want to make sure you optimize as much of the code as possible before entering it into this box. Any additional transformations after this can slow the query down substantially. I also recommend testing it in the IDE first to make sure that it works. We can then confirm that our code works and it adds it as another query. I'm going to rename this one SQL Query. We can also set up the DirectQuery connection to tables and to SQL queries that we input like we just did. When we choose DirectQuery, we can also enter the SQL statement below in the same way that we would do for the import. Let's just use the tables, though. There's a continuous development for these tools in Power BI including these connectivity modes. If you're wondering if we can support this option of Import and DirectQuery as the connection modes in the same model for different tables, the answer is yes we can, and we can do this with the help of composite models once we load the data.

Contents