Much of the world’s corporate data currently resides in relational databases, data warehouses, and data warehouse appliances. Power BI Desktop can connect to many (if not most) of the world’s leading databases and data warehouses. Not only that, but it can also connect to many of the lesser-known or more niche data sources that are currently available. This chapter will show you how to extract data from several of these data sources to power your analytics using Power BI Desktop. Indeed, you will discover that once you have learned how to connect to one or two databases, you have learned how to use nearly all of them, thanks to the standardized approach that Power BI Desktop brings to data extraction.
Once again, in this chapter I will be using a set of example files that you can find on the Apress web site. If you have followed the instructions in Appendix A, then these files will be in the C:PowerBIDesktopSamplesCH03 folder. Be aware, however, that not all the examples in this chapter use sample data that is available on the Apress web site. In some cases, such as Oracle and FileMaker Pro, I will let you load your own sample data, or use the sample data that can be installed with the databases themselves.
Relational Databases
Being able to access the data stored in relational databases is essential for much of today’s business intelligence. As enterprise-grade relational databases
still hold much of the world’s data, you really need to know how to tap into the vast mines of information that they contain. The bad news is that there are many, many databases out there, each with its own intricacies and quirks. The good news is that once you have learned to load data from one of them, you can reasonably expect to be able to use any of them.
In the real world, connecting to corporate data could require you to have a logon name and possibly a password that will let you connect (unless the database can recognize your Windows login). I imagine that you will also require permissions to read the tables
and views that contain the data. So the techniques described here are probably the easy bit. The hard part is convincing the guardians of corporate data that you actually need the data and you should be allowed to see it.
The databases that Power BI Desktop can currently connect to, and can preview and load data from, are given in Table 3-1.
Table 3-1.
Database Sources
Database | Comments |
|---|---|
SQL Server database | Lets you connect to a Microsoft SQL Server on-premises database and import records from all the data tables and views that you are authorized to access. |
Access database | Lets you connect to a Microsoft Access file on your network and load queries and tables. |
SQL Server Analysis Services database | Lets you connect to a SQL Server Analysis Services (SSAS) data warehouse. This can be either an online analytical processing (OLAP) cube or an in-memory tabular data warehouse. |
Oracle database | Lets you connect to an Oracle database and import records from all the data tables and views that you are authorized to access. |
IBM DB2 database | Lets you connect to an IBM DB2 database and import records from all the data tables and views that you are authorized to access. |
IBM Informix database | Lets you connect to an IBM Informix database and import records from all the data tables and views that you are authorized to access. |
IBM Netezza | Lets you connect to an IBM Netezza data warehouse appliance and import records from all the data tables that you are authorized to access. |
MySQL database | Lets you connect to a MySQL database and import records from all the data tables and views that you are authorized to access. |
PostgreSQL database | Lets you connect to a PostgreSQL database and import records from all the data tables and views that you are authorized to access. |
Sybase database | Lets you connect to a Sybase database and import records from all the data tables and views that you are authorized to access. |
Teradata database | Lets you connect to a Teradata database and import records from all the data tables and views that you are authorized to access. |
SAP HANA database | Lets you connect to a SAP HANA in-memory database and import records from all the objects that you have permission to access. |
Amazon Redshift | Lets you connect to an Amazon Redshift database and import records from all the data tables and views that you are authorized to access. |
Impala | Lets you connect to an Impala database and import records from all the data tables and views that you are authorized to access. |
Note
As the list of database
and data warehouse sources that you can connect to from Power BI Desktop continues to evolve, this list could see several new items by the time that you read this book.
As well as connections for specific databases, Power BI Desktop contains generic connectors that can help you to read data from databases that are not specifically in the list of available databases. These generic connectors are explained in Table 3-2.
Table 3-2.
Generic Database Access
Source | Comments |
|---|---|
ODBC data source | Lets you connect over Open Database Connectivity to a database or data source. |
OLE DB data source | Lets you connect over Object Linking and Embedding, Database to a database or data source. |
Be warned that these generic connectors will not work with any database. However, they should work with a database for which you have procured, installed, and configured a valid ODBC or OLE DB driver.
Note
Although Power BI Desktop classifies Microsoft Access as a relational database, I prefer to handle it as a file-based source. For this reason, MS Access data was discussed in the previous chapter.
SQL Server
Here I will use the Microsoft enterprise relational database—SQL Server—as an example to show you how to load data from a database into Power BI Desktop. The first advantage of this setup is that you probably do not need to install any software to enable access to SQL Server (although this is not always the case, so talk this through with your IT department). A second advantage is that the techniques are pretty similar to those used and applied by Oracle, DB2, and the other databases to which Power BI Desktop can connect. Furthermore, you can load multiple tables or views from a database at once. To see this in action (and presuming that you have created the database CarSalesData
as described in Appendix A), take the following steps:
- 1.Open a new Power BI Desktop application.
- 2.In the Power BI Desktop ribbon, click the small triangle at the bottom of the Get Data button and then click SQL Server. The SQL Server Database dialog will appear.
- 3.Enter the server name in the Server text box. This will be the name of your SQL Server or one of the SQL Server resources used by your organization.
- 4.Enter the database name; if you are using the sample data, it will be CarSalesData.
- 5.Click the Import button. The dialog will look like Figure 3-1.
Figure 3-1.The Microsoft SQL Server Database dialog - 6.Click OK. The SQL Server Database dialog will appear. Assuming that you are authorized to use your Windows login to connect to the database, leave “Use my current credentials” selected, as shown in Figure 3-2.
Figure 3-2.The credentials Database dialog - 7.Click Connect. While the data is being loaded, Power BI Desktop will display the Load dialog and show the load progress for each selected table. You can see this in Figure 3-3.
Figure 3-3.The Load dialog displaying data load progress - 8.Power BI Desktop will connect to the server and display the Navigator dialog containing all the tables and views in the database that you have permission to see on the server you selected. In some cases, you could see a dialog saying that the data source does not support encryption. If you feel happy with an unencrypted connection, then click the OK button for this dialog.
- 9.Click the check boxes for the Clients, Colors, Countries, Invoices, InvoiceLines, and Stock tables. The data for the most recently selected dataset appears on the right of the Navigator dialog, as shown in Figure 3-4.
Figure 3-4.The Navigator dialog when selecting multiple items - 10.Click Load.
- 11.The Power BI Desktop window will open and display the tables that you selected in the Fields list in the Report window when you click OK.
Since this is very similar to the way in which you loaded data from Access in the previous chapter, I imagine that you are getting the hang of how to use database sources by now. Once again the Navigator dialog is a simple and efficient way to select the datasets that you want to use in your reports and dashboards.
Tip
When selecting multiple tables or views, you will only ever see the contents of a single data source in the Navigator dialog. However, you can preview the contents of any of the selected data sources (or even any that are not selected) simply by clicking the table or view name. This will not affect the choice of selected tables and views that you want to load into Power BI Desktop.
Note
You can enter the server IP address instead of the server name if you prefer. If there are several SQL Server instances on the same server, you will need to add a backslash and the instance name. This kind of detailed information can be obtained from corporate database administrators.
Automatically Loading Related Tables
Relational databases are nearly always intricate structures composed of many interdependent tables. Indeed, you will frequently need to load several tables to obtain all the data that you need.
Knowing which tables to select is not always easy. Power BI Desktop tries to help you by automatically detecting the links that exist in the source database between tables; this way, you can rapidly isolate the collections of tables that have been designed to work together.
Do the following to see a related group of tables:
- 1.Connect to the source database as described in the previous section.
- 2.In the Navigator dialog, click a table that contains data that you need.
- 3.Click the “Select related tables” button.
Any tables that are linked in the database are selected. You can deselect any tables that you do not want, of course. More importantly, you can click the names of the selected tables to see their contents.
Note
Sometimes you have to select several tables in turn and click “Select related tables” to ensure that Power BI Desktop will select all the tables that are necessary to underpin your analysis.
Database Options
The world of relational databases is—fortunately or unfortunately—a little more complex than the world of files or MS Access. Consequently, there are a few comments to make about using databases as a data source; specifically, how to connect to them.
First, let’s cover the initial connection to the server. The options are explained in Table 3-3.
Table 3-3.
Database Connection Options
Option | Comments |
|---|---|
Server | You cannot browse to find the server and you need to type or paste the server name. If the server has an instance name, you need to enter the server and the instance. Your IT department will be able to supply this if you are working in a corporate environment. |
Database | If you know the database
, then you can enter (or paste) it here. This restricts the number of available tables in the Navigator dialog and makes finding the correct table or view easier. |
SQL statement | You can enter a valid snippet of T-SQL that returns data from the database. |
These options probably require a little more explanation. So let’s look at each one in turn.
Server Connection
It is fundamental that you know the exact connection string for the database that you want to connect to. This could be the following:
- The database server name.
- The database server name, a backslash, and an instance name (if there is one).
- The database server IP address.
- The database server IP address, a backslash, and an instance name (if there is one).
- If the SQL Server instance is using a custom port, you must end the server name with a comma followed by the port number. This is, inevitably, a question for corporate DBAs.
- If you are running a single SQL Server instance on your own PC, then you can use the name localhost to refer to the server.
Note
A database instance is a separate SQL Server service running alongside others on the same physical or virtual server. You will always need both the server and this instance name (if there is one) to successfully connect. You can also specify a timeout period if you wish.
Most SQL Server
instances host many, many databases. Sometimes these can number in the hundreds. Sometimes, inevitably, you cannot remember which database you want to connect to. Fortunately, Power BI Desktop can let you browse the databases on a server. To do this, do the following:
- 1.In the Power BI Desktop ribbon, click the small triangle at the bottom of the Get Data button and then click SQL Server. The SQL Server Database dialog will appear.
- 2.Enter the server name in the Server text box and click OK. Do not enter a database name. The Navigator window opens and displays all the available databases, as shown in Figure 3-5. Of course, the actual contents depend on the server that you are connecting to.
Figure 3-5.The Navigator dialog when selecting databases
You can see from Figure 3-5 that if you click the small triangle to the left of a database, then you are able to see all the tables and views that are accessible to you in this database. Although this can mean an overabundance of possible choices when looking for the table(s) or view(s) that you want, it is nonetheless a convenient way of reminding you of the name of dataset that you require.
Tip
The actual databases
that you will be able to see on a corporate server will depend on the permissions that you have been given. If you cannot see a database, then you will have to talk to the database administrators to sort out any permissions issues.
Searching for Databases, Tables, and Views in Navigator
If you are overwhelmed by the sheer volume of table(s) and view(s) that appear in the left panel of the Navigator dialog, then you can use Navigator’s built-in search facility to help you to narrow down the set of potential data sources.
Searching for Databases
To isolate specific databases, do the following:
- 1.Carry out steps 1 and 2 in the earlier “SQL Server” section to connect to a SQL Server instance without specifying a database.
- 2.In the Search box of the Navigator dialog, enter a few characters that you know are contained in the name of the table or view that you are looking for. Entering, for example, data on my server gives the result that you see in Figure 3-6.
Figure 3-6.Using Search with Navigator to find databases
Searching for Tables
If you are searching for tables, do the following:
- 1.Expand any databases that you want to search for specific tables.
- 2.In the Search box of the Navigator dialog, enter a few characters that you know are contained in the name of the table or view that you are looking for. Entering, for example, cli on my server gives the result that you see in Figure 3-7.
Figure 3-7.Using Search with Navigator to find tables
When searching for objects, you can enter the text in uppercase or lowercase, and the text can appear anywhere in the names of the tables or views—not just at the start of the name. With every character that you type, the list of potential matches gets shorter and shorter. Once you have found the dataset that you are looking for, simply proceed as described earlier to load the data into Power BI Desktop.
If your search does not return the subset of tables in any views that you were expecting, all you have to do is click the cross at the right of the Search box. This cancels the search and displays all the available tables, as well as clears the Search box.
If you are not convinced that you are seeing all the tables and views that are in the database, then click the small icon at the bottom right of the Search box (it looks like a small page with two green circular arrows). This is the Refresh button, which refreshes the connection to the database and displays all the tables and views that you have permission to see. Finally, it is worth noting that filtering database tables is very similar to searching for Excel objects—a technique that you saw in the previous chapter.
Note
SQL Server databases
can also be accessed using the DirectQuery option. This technique is explained in the next chapter.
Database Security
Remember that databases are designed to be extremely secure. Consequently, you only see servers, databases, tables, and views if you are authorized to access them. You might have to talk to your IT department to ensure that you have the required permissions; otherwise, the table that you are looking for could be in the database, but remain invisible to you.
Tip
If you experience a connection error when first attempting to connect to SQL Server, simply click the Edit button to return to the Microsoft SQL Database dialog and correct any mistakes. This avoids having to start over.
Using a SQL Statement
If there is a downside to using a relational database such as SQL Server as a data source, it is that the sheer amount of data that the database stores—even in a single table—can be dauntingly huge. Fortunately, all the resources of SQL Server can be used to filter the data that is used by Power BI Desktop before you even load the data. This way, you do not have to load entire tables of data at the risk of drowning in information before you have even started to analyze it.
The following are SQL Server techniques
that you can use to extend the partnership between SQL Server and Power BI Desktop:
- SQL SELECT statements
- Stored procedures
- Table-valued functions
These are, admittedly, fairly technical solutions. Indeed, if you are not a database specialist, you could well require the services of your IT department to use these options to access data in the server. Nonetheless, it is worth taking a quick look at these techniques in case they are useful one day.
Any of these options can be applied from the SQL Server Database dialog. Here is an example of how to filter data from a database table using a SELECT statement
:
- 1.In the Power BI Desktop ribbon, click the small triangle at the bottom of the Get Data button and then click SQL Server. The SQL Server Database dialog will appear.
- 2.Enter the server name and the database.
- 3.Click the triangle to the left of SQL Statement (optional). This opens a box where you can enter a SQL command.
- 4.Enter the SQL command that you want to apply. In this case, it is SELECT ClientName, Town, Region FROM Data.Clients ORDER BY ClientID. The dialog will look like Figure 3-8.
Figure 3-8.Using SQL to select database data - 5.Click OK. A sample of the corresponding data is displayed in a dialog like the one shown in Figure 3-9.
Figure 3-9.Database data selected using the SQL Statement option - 6.Click Load or Edit to continue with the data load process. Alternatively, you can click Cancel and start a different data load.
Tip
When entering custom SQL (or when using stored procedures, as is explained in the following section) you should, preferably, specify the database name in step 3. If you do not give the database name, you will have to use three-part notation in your SQL query. That is, you must add the database name and a period before the schema and table name of every table name used in the query.
Stored Procedures in SQL Server
The same principles apply when using stored procedures of functions to return data from SQL Server. You will always use the SQL Statement option to enter the command that will return the data. Just remember that to call a SQL Server stored procedure or function, you would enter the following elements into the Microsoft SQL Database dialog:
- Server: <your server name>
- Database: <the database name>
- SQL Statement: EXECUTE (or EXEC) <enter the schema (if there is one, followed by a period) and the stored procedure name, followed by any parameters>
This way, either you or your IT department can create complex and secure ways to allow data from the corporate databases to be read into Power BI Desktop from enterprise databases.
To see this in practice
, you can use a SQL Server stored procedure that is included in the sample SQL Server database to return only a subset of the available data. The stored procedure is called pr_DisplayUKClientData
, and you apply it like this:
- 1.In the Power BI Desktop ribbon, click the small triangle at the bottom of the Get Data button and then click SQL Server. The SQL Server Database dialog will appear.
- 2.Enter the server name and the database.
- 3.Click the triangle to the left of SQL Statement (optional). This opens a box where you can enter a SQL command.
- 4.Enter the SQL command that you want to apply. In this case, it is EXECUTE dbo. pr_DisplayUKClientData. The dialog will look like Figure 3-10.
Figure 3-10.Using SQL to select database data - 5.Click OK. A sample of the corresponding data is displayed in a dialog like the one shown in Figure 3-11.
Figure 3-11.Database data selected using the SQL Statement option - 6.Click Load or Edit to continue with the data load process. Alternatively, you can click Cancel and start a different data load.
The data that is returned in this example is only a subset
of the available data that has been selected by the stored procedure. You need to be aware that stored procedures can perform a multitude of tasks on the source data. These can include selecting, sorting, and cleansing the data.
Note
A SQL statement or stored procedure will only return data as a single table. Admittedly, this table could contain data from several tables or views, but filtering the source data will prevent Power BI Desktop from loading data from several tables as separate queries. Consequently, you could have to create multiple queries rather than a single load query to get data from a coherent set of tables in the data source.
Oracle Databases
There are many, many database vendors active in the corporate marketplace today. Arguably the most dominant of them is Oracle. So while I have used Microsoft data sources
to begin the journey into an understanding of how to use databases with Power BI Desktop, it would be remiss of me not to explain how to access databases from other suppliers.
So now is the time to show you just how open-minded Power BI Desktop really is. It does not limit you to Microsoft data sources-far from it. Indeed, it is every bit as easy to use databases from other vendors as the source of your analytical reports. As an example of this, let’s take a look at loading Oracle data into Power BI Desktop.
Installing and configuring an Oracle database is a non-trivial task. Consequently, I am not providing an Oracle sample database, but will leave you either to discover a corporate database that you can connect to or, preferably, consult the many excellent resources available that do an excellent job of explaining how to set up your own Oracle database and install the sample data that is available.
Be aware that connecting to Oracle will require installing Oracle client software
on the computer where you are running Power BI Desktop. This, too, can be complex to set up. So you might need some help from a corporate resource if you are planning to use Oracle data with Power BI Desktop.
Should you be feeling brave, you can use the following URLs to find the Oracle client software. For 32-bit versions of Power BI Desktop, use the following link to download and install the 32-bit Oracle client
:
www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html
For 64-bit versions of Power BI Desktop, use the following link to download and install the 64-bit Oracle client
:
www.oracle.com/technetwork/database/windows/downloads/index-090165.html
If you need to check which version of Power BI Desktop you are using (32 bit or 64 bit), click File ➤ Help ➤ About. You will see a dialog that tells you which version you are using.
So, assuming that you have an Oracle database available (and that you know the server name or SID as well as a valid user name and password), the following steps show how you can load data from this particular source into Power BI Desktop. I will be using standard Oracle sample data that is often installed with sample databases in this example.
- 1.Open a new Power BI Desktop application.
- 2.In the Power BI Desktop ribbon, click Database.
- 3.In the Get Data dialog, click Oracle database on the right. The dialog will look like Figure 3-12.
Figure 3-12.Connecting to an Oracle database - 4.Click Connect. The Oracle Database dialog will appear.
- 5.Enter the server name in the Server text box. This will be the name of your Oracle server or one of the Oracle Server resources used by your organization.
- 6.Click the Import button. The dialog will look like Figure 3-13.
Figure 3-13.The Oracle Database dialog - 7.Click OK. The Oracle Database security dialog will appear. Assuming that you are not authorized to use your Windows login to connect to the database, click Database on the left of the dialog.
- 8.Enter the user name and password that allow you to log into Oracle, as shown in Figure 3-14.
Figure 3-14.The Oracle Database security dialog - 9.Click Connect. Power BI Desktop will connect to the server and display the Navigator dialog containing all the tables and views in the database that you have permission to see on the server you selected. In some cases, you could see a dialog saying that the data source does not support encryption. If you feel happy with an unencrypted connection, then click the OK button for this dialog.
- 10.Expand the HR folder. This is a standard Oracle sample schema that could be installed on your Oracle instance. If not, you will have to choose another schema. Click the check boxes for the tables that interest you. The data for the most recently selected data appears on the right of the Navigator dialog, as shown in Figure 3-15.
Figure 3-15.The Navigator dialog using Oracle data - 11.Click Load. The Power BI Desktop window will open and display the tables that you selected in the Fields list in the Report window when you click OK. You can see the data in Power BI Desktop in Figure 3-16.
Figure 3-16.Oracle data loaded into Power BI Desktop
If you have already followed the example earlier in this chapter to load data from SQL Server, you will probably appreciate how much the two techniques have in common. Indeed, one of the great advantages of using Power BI Desktop is that loading data from different data sources follows a largely similar approach and uses many of the same steps and dialogs. This is especially true of databases, where the steps are virtually identical-whatever the database.
Of course, no two databases are alike. Consequently, you connect to an Oracle instance (or server) but cannot choose a database as you can in SQL Server (or Sybase, for instance). Similarly, where Oracle has Schemas to segregate and organize data tables, SQL Server has databases. Nonetheless, the Power BI Desktop Navigator will always organize data into a hierarchy of folders so that you can visualize the data structures in a clear, simple, and intuitive manner, whatever the underlying database.
Note
Oracle databases can also be accessed using the DirectQuery option. This technique is explained in the next chapter.
Other Relational Databases
Table 3-1 at the start of this chapter contains the list of relational databases that Power BI Desktop could connect to as this book went to press. I imagine that the list has grown since this book was published. However, the good news is that you probably do not need much more information to connect to any of the databases that are available for you to use as data sources. Simply put-if you know how to connect to one of them, you can probably connect to any of them.
So I am not going to fill out reams of pages with virtually identical explanations of how to get data from a dozen or more relational databases. Instead I suggest that you simply try to connect, using the techniques that you have learned in this chapter for Oracle and SQL Server.
Be warned, though, that to connect to a relational database
you will inevitably need to know the following details:
- The server name
- A database name (possibly)
- A valid user name (depending on the security that has been implemented)
- A valid password for the user that you are connecting as (this, too, will depend on the security in place)
However, if you have these elements, then nothing should stop you from using a range of corporate data sources as the basis for your analysis with Power BI Desktop. You will, of course, need all the necessary permissions to access the database and the data that it contains.
It is also worth knowing that connecting to DB2, MySQL, PostgreSQL, Sybase, IBM Informix, IBM Netezza, SAP HANA, or Teradata can require not only that the database administrator
has given you the necessary permissions, but also that connection software (known as drivers or providers) has been installed on your PC. Given the “corporate” nature of the requirements, it may help if you talk directly to your IT department to get this set up in your enterprise IT landscape.
One way to find out if the software that is required to enable a connection to a specific database has been installed is to select the database from the list available in the Get Data dialog. If the drivers have not been installed, you will see a warning similar to the one in Figure 3-17.
Figure 3-17.
The missing driver alert
Clicking the “Learn more” link will take you to the download page for the missing drivers. Be warned, however, that configuring data providers can, in some cases, require a little specialist knowledge as well as access rights on the computer where the drivers have to be installed.
Microsoft SQL Server Analysis Services Data Sources
An Analysis Services database is a data warehouse technology that can contain vast amounts of data that has been optimized to enable decision making. SSAS cubes (as these databases are also called) are composed of facts (measures or values) and dimensions (descriptive attributes). If your enterprise uses Analysis Services databases
, you can access them by doing the following steps:
- 1.In the Power BI Desktop ribbon, click Get Data ➤ More➤ Database and select SQL Server Analysis Services Database in the Get Data dialog.
- 2.Click Connect. The SQL Server Analysis Services Database dialog will appear.
- 3.Enter the Analysis Services server name and the database (or “cube”) name, if you know it. If you are using the sample data from the Apress site for this book, the database is CarSalesOLAP; otherwise, you have to specify your own SSAS database name. In any case you will need to use the name of your own SSAS server. The dialog will look like Figure 3-18.
Figure 3-18.Connecting to an SSAS (multidimensional) database - 4.Click OK. If this is the first time that you are connecting to the cube, then the Access SQL Server Analysis Service dialog will appear so that you can define the credentials that you are using to connect to the Analysis Services database, as shown in Figure 3-19.
Figure 3-19.SQL Server Analysis Services Credentials dialog - 5.Accept or alter the credentials and click Connect. The Navigator dialog will appear.
- 6.Expand the folders in the left pane of the dialog. This way, you can see all the fact tables and dimensions contained in the data warehouse.
- 7.Select the fact tables, dimensions, or even only the dimension elements and measures that you want to load. The dialog will look something like Figure 3-20.
Figure 3-20.Selecting attributes and measures from an SSAS cube - 8.Click Load. The Power BI Desktop window will open and display the measures and attributes that you selected in the Fields list in the Report window.
Note
If you did not enter the cube (database) name in step 3, then the Navigator dialog will display all the available cubes on the SSAS server.
SSAS cubes
are potentially huge. They can contain dozens of dimensions, many fact tables, and literally hundreds of measures and attributes. Understanding multidimensional cubes and how they work is beyond the scope of this book. Nonetheless, it is important to understand that for Power BI Desktop, a cube is just another data source. This means that you can be extremely selective as to the cube elements that you load into Power BI Desktop, and only load the elements that you need for your analysis. You can load entire dimensions or just a few attributes, just like you can load whole fact tables or just a selection of measures.
Note
You can filter the data that is loaded from an SSAS cube by expanding the MDX or DAX query (optional) item in the SQL Server Analysis Services Database dialog. Then you can enter an MDX query in the box that appears before clicking OK. Be warned that SSAS cubes use queries written in MDX—a specialist language that is considered not always easy to learn. The good news is that if an Analysis Services expert has set up a cube correctly, you can see SSAS display folders in PowerBI Desktop Query (which is another term that people use to describe the Query Editor).
Analysis Services Cube Tools
Analysis Services data sources allow you to tweak the selection of source elements in a way that is not available with other data sources. Essentially, you have two extra options:
- Add Items
- Collapse Columns
Add Items
When using an SSAS data source, you can at any time add any attributes or measures that you either forgot or thought that you would not need when setting up the initial connection.
- 1.In Power BI Desktop, click the Edit Queries button. The Power BI Desktop Query Editor window will be displayed. Assuming that there is only one query, the Manage ribbon will appear, as shown in Figure 3-21. Otherwise, click the SSAS query that you have previously established.
Figure 3-21.Cube Tools - 2.In the Manage ribbon, click the Add Items button. The Add Items dialog will appear, as shown in Figure 3-22.
Figure 3-22.The Add Items dialog - 3.Expand any measure groups and select all the measures and attributes that you want to add.
- 4.Click OK.
- 5.In the Power BI Desktop Query Editor activate the Home ribbon and click Close and Apply.
Any changes that you made are reflected in the data and the selected measures and attributes are added as new columns at the right of the dataset.
Note
Power BI Desktop Query will not detect if any new measures and attributes that you add are already in the dataset. So if you add an element a second time, it will appear twice in the query.
Collapse Columns
Do the following to remove any columns that you no longer require from the data source (which can accelerate data refresh).
- 1.In the Query pane, click the SSAS query that you have previously established. (The sample data for this connection will be displayed in the center of the Query window-this is yet another term for the Query Editor window.) The Manage ribbon will appear.
- 2.In the Manage ribbon, click the Collapse Columns button.
The columns are removed from the connection to the SSAS cube and, consequently, from the Fields list at the right of the Power BI Desktop window. They are also removed from any visualizations that use them.
Note
Removing columns from Power BI Desktop Query can have a serious domino effect on reports and dashboards. Consequently, you need to be very careful when removing them.
SSAS Tabular Data Warehouses
The previous section showed you how to connect to a SQL Server Analysis Services cube. However, there are now two types of SQL Server Analysis Services data warehouses:
- The “traditional” cube
- The “newer” tabular warehouse
As more and more data warehouses (at least the ones that are based on Microsoft technologies) are being built using the newer, tabular technology, it is probably worth your while to see how quickly and easily you can use these data sources with Power BI Desktop. Indeed, the steps that you follow to connect to either of these data warehouse sources are virtually identical. However, as Power BI is rapidly becoming the tool of choice to query tabular data warehouses, it is certainly worth a few minutes to learn how to connect to SSAS Tabular (as it is often called, for short).
- 1.In the Power BI Desktop ribbon, click Get Data ➤ More ➤ Database and select SQL Server Analysis Services Database in the Get Data dialog.
- 2.Click Connect. The SQL Server Analysis Services Database dialog will appear.
- 3.Enter the Analysis Services server name and the tabular database name (we don’t tend to call these cubes), if you know it. If you are using the sample data from the Apress site for this book, the database is CarSalesTabular; otherwise, you have to specify your own tabular database name. In any case you will need to use the name of your own SSAS server.
- 4.Click Import.
- 5.The dialog will look like Figure 3-23.
Figure 3-23.Connecting to an SSAS (multidimensional) database - 6.Click OK. If this is the first time that you are connecting to the tabular warehouse, then the Access SQL Server Analysis Service dialog will appear so that you can define the credentials that you are using to connect to the Analysis Services database, where you will have to accept or alter the credentials and click Connect. The Navigator dialog will appear.
- 7.Expand the folders in the left pane of the dialog. This way, you can see all the tables contained in the data warehouse. These may—or may not—be structured as facts and dimensions as was the case with a “classic” SSAS data warehouse.
- 8.Select the tables that you want to load. The dialog will look something like Figure 3-24.
Figure 3-24.Selecting attributes and measures from an SSAS tabular data source - 9.Click Load. The Power BI Desktop window will open and display the measures and attributes that you selected in the Fields list in the Report window.
Tip
You can filter the data that is loaded from an SSAS tabular database by expanding the MDX or DAX query (optional) item in the SQL Server Analysis Services Database dialog. Then you can enter a DAX query in the box that appears before clicking OK. SSAS tabular databases use queries written in DAX, which is the language the Power BI itself uses (and which you will start to learn in Chapters 11 through 13).
Import or Connect Live
So far in this chapter I have suggested that you use the Import option when sourcing data from Microsoft SQL Server databases and data warehouses. This is because the alternative, Connect Live (which is also known as DirectQuery), is such an important part of Power BI Desktop
that I have preferred to make it the subject of a whole separate chapter. You will discover how to use this far-reaching and impressive technique in the next chapter.
ODBC Sources
As you have seen in this chapter and the preceding one, Power BI Desktop can connect to a wide range of data sources. However, there will always be database applications for which there is no specific connector built in to Power BI Desktop.
This is where a generic solution called Open Database Connectivity (or ODBC) comes into play. ODBC is a standard way to connect to data sources, most of which are databases or structured like databases. Simply put, if an ODBC driver exists for the application that you want to connect to, then you can load data from it into Power BI Desktop.
Hundreds of ODBC drivers have been written. Some are freely available, others require you to purchase a license. They exist for a wide spectrum of applications ranging from those found on most PCs to niche products.
Although ODBC is designed as a standard way of accessing data in applications, each ODBC driver is slightly different from every other ODBC driver. Consequently, you might have to spend a little time learning the quirks of the interface for the driver that comes with the application that you want to connect to.
In this section we will use FileMaker Pro
as a data source. This product is a desktop and server database system that has been around for quite some time. However, there is currently no specific Power BI Desktop connector for it. The good news is that FileMaker Pro does have an ODBC driver. So we will use ODBC to connect to FileMaker Pro from Power BI Desktop.
I have to add that I am not expecting you to install a copy (even if it is only a trial copy) of FileMaker Pro and its companion ODBC driver to carry out this exercise. What I do want to explain, however, is how you can use ODBC to connect to a wide range of data sources where an ODBC driver is available. So feel free to download and install FileMaker Pro and its ODBC driver if you wish, but you will have to refer to the FileMaker Pro documentation for an explanation of how to do this.
Assuming that you have an ODBC-compliant data source and a working ODBC driver for this data source, here is how to load data into Power BI Desktop
using ODBC:
- 1.Run the ODBC Data Source Administrator app. This is normally in the folder C:ProgramDataMicrosoftWindowsStart MenuProgramsAdministrative Tools. Be sure to use the 64-bit version if you are using 64-bit Power BI Desktop or the 32-bit version if you are using 32-bit Power BI Desktop.
- 2.Click the System DSN tab. You should see the dialog shown in Figure 3-25.
Figure 3-25.The ODBC Data Source Administrator - 3.Click Add. You will see the list of all currently installed ODBC drivers on your computer. This should look something like the dialog shown in Figure 3-26.
Figure 3-26.The list of installed ODBC drivers - 4.Select the appropriate ODBC driver corresponding to the data source that you want to connect to (FileMaker ODBC in this example). If you cannot see the ODBC driver, you need to install—or reinstall—the driver.
- 5.Click Finish. The configuration dialog for the specific ODBC driver that you have selected will appear. If you are using FileMaker Pro, the dialog will look like Figure 3-27.
Figure 3-27.The FileMaker Pro ODBC configuration assistant - 6.Click Next, and enter a name and a description for this particular ODBC connection. This should look something like the dialog shown in Figure 3-28.
Figure 3-28.Naming the ODBC connection for FileMaker Pro - 7.Click Next and enter localhost as the hostname if you are using a FileMaker trial version on your local computer. Otherwise, enter the IP address of the FileMaker server. You should see the dialog shown in Figure 3-29.
Figure 3-29.Specifying the host for the ODBC data - 8.Click Next and select the database in FileMaker Pro that you want to connect to. You will see the dialog shown in Figure 3-30 (if you are not using FileMaker Pro—remember that these dialogs can vary depending on the specific ODBC driver).
Figure 3-30.Specifying the database for the ODBC data - 9.Click Next. The ODBC configuration dialog will resume the specifications for the connection. This could look something like the one shown in Figure 3-31.
Figure 3-31.The ODBC connection confirmation dialog - 10.Click Done. You will return to the ODBC Data Source Administrator, where you will see the System DSN that you just created. The ODBC Data Source Administrator dialog should look something like the one shown in Figure 3-32.
Figure 3-32.The ODBC Data Source Administrator dialog with an ODBC driver configured - 11.Click OK. This will close the ODBC Data Source Administrator dialog.
- 12.Launch Power BI Desktop.
- 13.Click Get Data ➤ Other.
- 14.Select ODBC from the available data sources on the right.
- 15.Click Connect. The From ODBC dialog will appear.
- 16.Expand the list of available DSNs. The From ODBC dialog will look something like the one in Figure 3-33.
Figure 3-33.The Power BI Desktop From ODBC dialog to select an ODBC data source - 17.Select the DSN that you created previously (FileMakerForPowerBI in this example).
- 18.Click OK. The Credentials dialog will appear.
- 19.Choose Windows integrated security or click Database on the left and enter the user name that has permissions to connect using the ODBC driver. The Credentials dialog will look something like the one in Figure 3-34.
Figure 3-34.The ODBC driver security dialog - 20.Click Connect. You will see the data that is available in the ODBC data source in the Navigator window.
- 21.Select the table(s) that you want to load into Power BI Desktop. You can see the data contained in the selected table in Figure 3-35.
Figure 3-35.The Navigator dialog when using an ODBC source dialog - 22.Click Load to load the data from the ODBC source into Power BI Desktop.
I realize that this process may seem a little laborious at first. Yet you have to remember that you will, in all probability, only set up the ODBC connection once. After that you can use it to connect to the source data as often as you want.
You need to be aware that each and every ODBC driver is different. So the appearance of the dialogs in steps 5 to 10 will vary slightly with each different ODBC driver that you configure. The key elements
will, nonetheless, always be the same. They are
- Name the DSN.
- Specify the host computer for the data.
- Define the data repository (or database).
There is much more that could be written about creating and using ODBC connections to load data into Power BI Desktop—or indeed into any number of destination applications. However, I will have to refer you to the wealth of available resources both in print and online if you need to learn more about this particular technology. A good starting point is the Microsoft documentation that explains the difference between System, User, and File DSNs and describes many of the key elements that you might need to know.
Note
FileMaker Pro must be open and/or running for an ODBC connection to work. Other ODBC sources could have their own specific quirks.
As a final, I can only urge you to procure all the relevant documentation for the ODBC driver that you intend to use with Power BI Desktop. Indeed, if you are using an enterprise data source that uses ODBC drivers, you may have corporate resources who can configure ODBC for you.
OLE DB Data Sources
OLE DB (short for Object Linking and Embedding, Database) is technically what is known as an application programming interface (API)
. Less technically it is a technique for connecting to database sources in a generic manner.
So, in a somewhat similar fashion to ODBC, you can use OLE DB to connect to data sources (which are often databases, although they can be other sources of data). Indeed, you may find that OLE DB is a useful way to connect to a database even if another method exists.
So, whatever the use that you find for OLE DB, it is well worth getting to know how it works. In this example I will use OLE DB to connect to SQL Server
and the sample database that you first saw at the start of this chapter.
- 1.Open a new Power BI Desktop application.
- 2.In the Power BI Desktop ribbon, click the Get Data button.
- 3.Click Other on the left then click OLE DB on the right. The dialog will look like Figure 3-36.
Figure 3-36.Starting an OLE DB connection - 4.Click Connect. The From OLE DB dialog will appear. It should look like Figure 3-37.
Figure 3-37.The From OLE DB dialog - 5.If you have a fully working connection string, enter it in the Connection String text box.
- 6.If you do not have a connection string, click the Build button. The OLE DB Data Link Properties dialog will be displayed, as shown in Figure 3-38.
Figure 3-38.The OLE DB Data Link Properties dialog - 7.Select the OLE DB data provider that you want to use. In this example, it will be Microsoft OLE DB Provider for SQL Server.
- 8.Click Next. The Connection properties pane of the OLE DB Data Link Properties dialog will appear.
- 9.Select an available SQL Server (or enter its name) from the “Select or enter a server name” popup.
- 10.Select the type of security, and enter a user name and password if you have selected to use a specific user name instead of using Windows NT Integrated security.
- 11.Select the source database from the “Select the database on the server” popup. The dialog will look something like the one shown in Figure 3-39.
Figure 3-39.The Connection properties of the OLE DB Data Link Properties dialog - 12.Click the Test Connection button to ensure that the connection is valid. You should see the message in Figure 3-40.
Figure 3-40.The test connection alert - 13.Click OK. Power BI Desktop will build the connection string and insert it into the From OLE DB dialog, as shown in Figure 3-41.
Figure 3-41.The From OLE DB dialog with a valid connection string - 14.Click OK. The Navigator window will be displayed, as shown in Figure 3-42.
Figure 3-42.The Navigator window for an OLE DB connection - 15.Click Load to load the data into Power BI Desktop.
Note
If this is a first connection to an OLE DB source, you may be asked for a user name and password, as was the case with earlier examples in this chapter.
You need to be aware that an OLE DB connection requires that the OLE DB driver (or “provider”) is installed on the computer where you are running Power BI Desktop. However, what is really interesting is that an OLE DB connection can be reduced to a simple connection string. So if you need to share the connection with other users, you can simply e-mail the connection string to them in many cases. Your colleagues can then simply paste the connection string into the From OLE DB dialog in Power BI Desktop. In other words (and using this example as a model), you can simply send the following text to a co-worker:
provider=SQLOLEDB.1;initial catalog=CarSalesData;data source=ADAM03SQLSERVER2016
They can use this string to connect to a specified database by pasting it into the From OLE DB dialog.
There are other advantages to using OLE DB connections too. Specifically, you (or your IT department) can provide a high level of configuration in the connection string to speed up or otherwise ameliorate the access to the data. This could be by specifying a mirrored server that is to be used for reporting to relieve the pressure on a main server, for instance. At this level the technical ramifications will depend on the OLE DB data source as well as the driver used, and consequently are outside the scope of this book.
Modifying Connections
If you are working in a structured development environment-or even if you are testing dashboards on a dataset that is either an old version or possibly on a non-live server-you could want at some point to switch from a current data source to another source. Power BI Desktop will let you do this. However, switching data sources will only work if the structures of the source and the destination data are identical. Practically, this means that the server and database can be named differently, but the tables and fields must have the same names.
To see this in action, you can do the following:
- 1.In an existing Power BI Desktop file, click the small triangle at the bottom right of the Edit Queries button in the Home ribbon, and select Data Source Settings. The Data Source Settings dialog will be displayed. Figure 3-43 shows you this dialog for the SQL Server connection that you saw at the start of this chapter.
Figure 3-43.The Data Source Settings dialog - 2.Click the connection that you want to modify.
- 3.Click Change Source. The dialog that originally allowed you to specify the data source (in this example that is the SQL Server Database connection dialog) will appear. You can see this in Figure 3-44.
Figure 3-44.The connection dialog - 4.Specify a different server and/or database as the data source.
- 5.Click OK. You will return to the Data Source Settings dialog.
- 6.Click Close.
- 7.Click the Apply Changes button that appears under the Power BI Desktop ribbon at the top of the screen.
Note
If this is the first time that you are establishing a connection to this new server and database, you will have to specify the credentials to use.
Assuming that the new data source
contains the same table names and structures, the existing data will be replaced with the data from the new source that you specified.
I have to stress again that this technique will only work if the underlying database metadata is identical across the two servers. If the data structures are not the same, you will see an error dialog similar to that shown in Figure 3-45.
Figure 3-45.
Modifying a database connection
In cases like this, you may well have to rebuild a new Power BI Desktop file using the new data source. This could mean re-creating or copying any data mashups and formulas (as well as actual visualizations) from the old version to the new file.
Changing Permissions
It is all too frequent when working with databases and data warehouses to encounter permissions problems. It could be that you set up a connection to a database which required you to change the password at a later date. Meanwhile, the password stored in Power BI Desktop is the old version. So when you try to update your dashboard, you hit a blocker.
Fortunately (assuming, at least, that you know the new password), you can update your stored credentials in Power BI Desktop. As an example, let’s suppose that you want to update your Oracle password. Here is how:
- 1.In an existing Power BI Desktop file, click the small triangle at the bottom right of the Edit Queries button in the Home ribbon, and select Data Source Settings.
- 2.Click Global Permissions. The Data Source Settings dialog will be displayed. Figure 3-46 shows you this dialog for the current Power BI Desktop connections on my PC.
Figure 3-46.Permissions for current connections - 3.Click the name of the connection that you want to change (pdborcl in this example).
- 4.Click the Edit Permissions button. The Edit Permissions dialog will be displayed, as shown in Figure 3-47.
Figure 3-47.The Edit Permissions dialog - 5.Click Edit. The connection permissions dialog will be displayed (shown earlier in Figure 3-14 in the section that explains how to connect to an Oracle database).
- 6.Modify the password (or even the user name if this is required).
- 7.Click Save. This returns you to the Edit Permissions dialog.
- 8.Click OK. You will return to the Data Source Settings dialog.
- 9.Click Close to return to Power BI Desktop.
You can now refresh the data using the new permissions that you have just entered.
Refreshing Data from Databases and Data Warehouses
Loading data from databases and data warehouses
only means that a snapshot of the source data is copied into Power BI Desktop. If the source data is updated, extended, or deleted, then you will need to get the latest version of the data if you want your analyses to reflect the current state of the data.
Essentially you have two options to do this:
- Refresh all the source data from all the data sources that you have defined.
- Refresh one or more tables individually.
Refreshing the Entire Data in the Power BI Desktop In-Memory Model
There is only one way to be certain that all your data
is up to date. Refreshing the entire data may take longer, but you will be sure that your Power BI Desktop file contains the latest available data from all the sources that you have connected to.
To carry out a complete refresh:
- 1.In the Home ribbon, click the Refresh button. The Refresh dialog will appear showing all the data sources that are currently being refreshed. This dialog will look like the one in Figure 3-48.
Figure 3-48.The Refresh dialog
Note
A full data refresh can take quite a while if the source data is voluminous or if the network connection is slow.
Refreshing an Individual Table
If you are certain that only one or more tables need to be refreshed in your Power BI Desktop data model, then you can choose to refresh tables individually. To do this:
- 1.In the Fields pane, right-click the table that you want to refresh.
- 2.Select Refresh Data in the context menu. This is illustrated in Figure 3-49.
Figure 3-49.Refreshing a single table
The refresh dialog will appear (possibly only briefly) and the existing data for this table will be replaced with the latest data
.
Conclusion
In this chapter, you have seen how to connect Power BI Desktop to some of the plethora of databases and data warehouses that currently exist. Moreover, you have seen that Power BI Desktop comes equipped “out of the box” with connections to most of the databases that currently exist in a corporate environment.
Power BI Desktop does not limit you to a predefined set of available data sources. Provided that your source database comes complete with one of the generic data providers-ODBC or OLE DB-then Power BI Desktop can, in all probability, access these sources too.
Moreover, corporate data sources can evolve and change. You also saw how Power BI Desktop allows you to update the permissions that you originally specified for a connection with the latest access details. You even saw how to switch between data sources and update the data both selectively and globally.
Despite their usefulness in storing and structuring large quantities of information, corporate databases can present one small drawback: the time that it can take to load the data from the database into Power BI Desktop’s in-memory data model. The development team at Microsoft is clearly aware of this potential shortcoming. The team has come up with a solution, called DirectQuery
(or Connect Live), which you can discover how to implement in the next chapter.