From the course: Power BI Data Methods (2019)

Connecting to databases

From the course: Power BI Data Methods (2019)

Start my 1-month free trial

Connecting to databases

“

- [Instructor] Relational databases consist of tables in a data warehouse that typically use SQL for querying and maintaining these databases. We will find it easiest to work with these databases if we at least understand the database configuration to connect to the optimal and correct tables. Relational database connections in Power BI include SQL Server, Oracle, and HANA, among many other options. Multidimensional cubes, OLAP cubes, or SAS models work as predefined queries referencing databases where the user does not write a query to access the data, but rather connects to a model with predefined dimensions and calculations. For example, SQL Server Analysis Services uses MDX queries to create a cube database model instead of getting data from the relational database using SQL commands. If we cannot find the database connector in Power BI, we can check to see if a platform like Microsoft Azure supports it. Azure functions as a cloud computing service supporting software platforms and infrastructure as services for both Microsoft products and third party software and systems. It supports connections for both relational databases and cubes. We can also access databases through an ODBC connection if we can install the vendor's ODBC driver on our own computer. ODBC stands for Open Database Connectivity. It functions as an interface for Microsoft, enabling access to data management systems using SQL. In order to connect to the database, we need our own set of database access credentials, including the server name, the database name if applicable, and any access credentials. Microsoft systems often use shared Windows credentials across multiple platforms. Note that the database credentials in Power BI Desktop may not transfer between users when you share Power BI files, so make sure other users also have database access credentials if you share it with them. When they open up the file on their computer, they will have to enter their own credentials to refresh the data from the databases. We can also customize configurables connection settings options, such as credentials, encryption, privacy levels, and native database queries. In some organizations, the IT group may control the privacy level and we cannot change it ourselves. The premise behind changing the privacy levels becomes imperative when sharing Power BI files because privacy levels will likely vary across a wide user base. I'm in the CDC flu data Power BI file we created earlier by connecting to a CSV connection. I'm going to show how to connect this to a SQL Server and SQL Server Analysis Service model. However, you will unlikely be able to access the database yourself, so just follow along and see how database connections work in Power BI. We select from New Source, first we're going to select SQL Server. I'm going to enter my Server name, and enter my database name. Notice the radio button to select between Import and DirectQuery. Let's set up our query using Import. If we want to get the data using a custom SQL query, we would enter it by expanding the box below. I would recommend testing SQL queries out beforehand to make sure they work in an application like Visual Studio. In this example, we will not use a SQL query, so go ahead and close out this Advanced Options menu, and proceed to the next screen by clicking OK. From here, we select the ILINet table we want to use from the database, confirm. Now we see we have our connection to the database table set up in SQL Server. Now I'm going to set up or show how you would access a SQL Server Analysis connection. Again, go to New Source. Let's click More to see more of the options that are available. In Database, Azure, and Other, we see the ODBC, the Azure options, and here in the Database tab, we see the databases. Connect to the SQL Server Analysis Services database. Notice that similarly to connecting to SQL Server, we also see the Server and Database options available on this screen. We also see two radio button options like we saw in the SQL Server. However, this time it's between Import and Connect Live. If we select the Import option, we will see an area to enter the MDX or DAX query below. We can choose to enter a query here, or if we do not enter a query, we can access tables for the model in screens after that. If we select Connect Live, it will disable the MDX or DAX query, and Power BI will become a front end connection only. Let's exit out of here, and we see how we would set up these database access connections.

Contents