In this chapter we will take a look at a subset of the fast-growing and wide-ranging set of data sources available over the Internet that you can use as a source of analytical data for Power BI Desktop. While the data sources that you will see in the following pages may be extremely diverse, they all have one thing in common: they are stored outside the enterprise and are available using an Internet connection.
The data sources that are available are available from a multitude of suppliers. Looking at all the available sources would take up an entire book, so I will show you how to access several of the mainstream services that are currently available. Once you have learned how to access a few of them, you should be able to extend the basic techniques to access just about any of the web and cloud services
that can currently be used by Power BI Desktop.
Power BI Desktop is now firmly entrenched as a fundamental part of the Microsoft universe. As PowerBI.com (the cloud service
that you can use to store and share dashboards) is part of Microsoft Azure, it is perhaps inevitable that the Power BI Desktop developers have gone out of their way to ensure that Power BI has become the analytical tool of choice for solutions that are hosted in Azure-the Microsoft Cloud. For this reason, I will explain quite a few of the core services that host data in MS Azure.
Nearly all of the data connections outlined in this chapter require access to a specific online source. Most of these sources are industrial-strength—and not free. However, if your enterprise is not a subscriber to these services, and you wish, nevertheless, to experiment with them, it could be worth taking a look at the free trial offers available from many (if not all) of the service providers whose offerings are outlined in this chapter.
Web and Cloud Services
Before delving into the details of some of the web and cloud services that are available, let’s take an initial high-level look at what these really are. These data sources include (among many others):
- Web pages
- Online services, such as Google Analytics, Salesforce, or MS Dynamics 365
- Microsoft Azure, which covers hosting files in Azure Blob services, storing data in an Azure SQL Database , or storing data in an Azure SQL Data Warehouse (or even reading big data in Azure HD Insight)
- OData , the generic method of accessing data on the Internet
Web Pages
If you need to collect some data that you can see as a table in a web browser, you can use Power BI Desktop
to connect to the URL for the page in question and then load all the data from any table on the page.
Online Services
Online services is a catch-all phrase used to describe data that you can access using the Internet. Most of the online services
available to Power BI Desktop are what are called “platforms.” These are (often huge) software and data resources that either are only available online or were once housed in corporate systems but are now available as services on the Internet. There are currently dozens of online services that are available to connect to using Power BI Desktop. Indeed, the number of available services is growing at a startling pace. Some of the more frequently used include those listed in Table 5-1.
Table 5-1.
Some Online Services
Available to Power BI Desktop
Source
|
Comments
|
|---|---|
Salesforce Objects
| Lets you access data in Salesforce. |
Salesforce Reports | Lets you access the pre-structured data that underlies built-in Salesforce reports. |
Google Analytics | Lets you access the data managed by Google to track web site traffic. |
Facebook | Accesses Facebook data. |
SharePoint Online | Connects to the Cloud version of Microsoft SharePoint. |
Microsoft Exchange Online | Connects to the Cloud version of Microsoft Exchange |
Dynamics 365 Online | Connects to the Cloud version of Microsoft Dynamics 365—the MS CRM and ERP solution. |
OData
| Although OData is not, technically, an online platform, it is certainly an online source of data. This is a standardized method for connecting to different data structures using a URL as a starting point
. |
Note
As the number of available online services
is increasing at an ever-increasing rate, you will probably find many more than those that I have listed here by the time that this book is published. Moreover, there are currently a number of online services that are available in beta. This means that you can test them, but they are not yet finalized.
Microsoft Azure
Azure is the Microsoft Cloud. The Azure data
sources that Power BI Desktop can currently connect to, and can preview and load data from, are given in Table 5-2.
Table 5-2.
Azure Sources
Source
|
Comments
|
|---|---|
Microsoft Azure SQL Database
| Lets you connect to a Microsoft SQL Server cloud-based database and import records from all the data tables and views that you are authorized to access. |
Microsoft Azure SQL data warehouse | Lets you connect to Microsoft’s cloud-based, elastic, enterprise data warehouse. |
Microsoft Azure Marketplace | Lets you load data that you are authorized to access on the Microsoft Azure Marketplace. It requires a Microsoft Azure Marketplace subscription. |
Microsoft Azure HDInsight | Reads cloud-based Hadoop files in the Microsoft Azure environment. |
Microsoft Azure Blob Storage
| Reads from a cloud-based unstructured data store. |
Microsoft Azure Table Storage | Reads from Microsoft Azure tables. |
Azure HDInsight Spark | Lets you connect to Microsoft’s parallel-processing framework in Microsoft Cloud. |
Microsoft Azure DocumentDB (now called CosmosDB) | Lets you connect to Microsoft’s NoSQL database. |
Microsoft Azure Data Lake Store | Lets you connect to Microsoft’s raw data cloud storage. |
Obviously, more Azure connection options are being added to Power BI Desktop
by Microsoft as the Azure offering is extended.
Web Pages
As a first and extremely simple example, let’s grab some data from a web page. Since I want to concentrate on the method rather than the data, I will use a web page that has nothing to do with the sample data in the book. I will not be using this other than as a simple introduction to the process of loading data from web pages using Power BI Desktop.
Assuming that you have launched Power BI Desktop and closed the splash screen…
- 1.Click the small triangle at the bottom of the Get Data button in the Home ribbon.
- 2.Select Web from the menu that appears, as shown in Figure 5-1.
Figure 5-1.The Get Data menu - 3.Enter the following URL (it is a Microsoft help page for Power BI Desktop that contains a few tables of data): http://office.microsoft.com/en-gb/excel-help/guide-to-the-power-query-ribbon-HA103993930.aspx . I am, of course, hoping that it is still available when you read this book. Of course, if you have a URL that you want to try out, then feel free! The dialog will look something like Figure 5-2.
Figure 5-2.The From Web dialog - 4.Click OK. The Navigator dialog will appear. After a few seconds, during which Power BI Desktop is connecting to the web page, the list of available tables of data in the web page will be displayed.
- 5.Click one of the table names on the left of the Navigator dialog. The contents of the table will appear on the right of the Navigator dialog to show you what the data in the chosen table looks like, as shown in Figure 5-3.
Figure 5-3.The Navigator dialog previewing the contents of a table on a web page - 6.Select the check box in the Navigator dialog (shown to the left of Table 4 in Figure 5-3).
- 7.Click Load at the bottom of the window (or double-click the table name).
- 8.Click the Data icon on the top left of the Power BI Desktop window to display the table of data. It should look like Figure 5-4.
Figure 5-4.The Power BI Desktop Query window
Tip
Another way of accessing web pages is to click Get Data ➤ Other. You can then select Web in the list on the right of the Get Data dialog.
This simple example showed how you can load data from a supported data source and load it into Power BI Desktop Query.
Advanced Web Options
In step 3 of the previous example, you could have selected the Advanced button. Had you done this, the From Web dialog would have expanded to allow you to build complex URLs by adding URL parts. You can see an example of this in Figure 5-5.
Figure 5-5.
The Advanced options in the From Web dialog
Clicking the Add Part button allows you to define multiple URL parts.
If necessary, you can also specify HTTP request header parameters that will be used when submitting the URL. These could be required by certain web pages
. A discussion of these is outside the scope of this book.
Table View or Web View
Looking at the tables that a web page contains is not always the most natural way of finding the right data. This is because you are looking at the data tables out of context. By this I mean that you cannot see where they are on the web page. After all, the Web is a very visual medium.
To help you find the correct data table on a web page, the Query Editor lets you switch between two views of the web source:
You alternate between these ways of visualizing the web page by clicking the Table View and Web View buttons that are at the top center of the Navigator dialog. The same web page that you saw previously looks like Figure 5-6 when you switch to Web View.
Figure 5-6.
Web View
in the Navigator dialog
Salesforce
One of the pioneers in the online services
space—and now, indisputably, one of the leaders—is Salesforce. So it is perhaps inevitable that Power BI Desktop will allow you to connect to Salesforce and load any data that you have permission to view using your Salesforce account.
Indeed, Salesforce is such a wide-ranging and complete service that you have two possible methods of accessing your data:
- Objects
- Reports
Briefly, Salesforce objects
are the underlying data tables that contain the information that you want to access. Salesforce reports
are the data that has been collated from the data tables into a more accessible form of output.
Tip
If you do not have a corporate Salesforce account but want, nevertheless, to see how to use Power BI Desktop to connect to Salesforce data
, you can always set up a free 30-day trial account. The URL for this is
https://www.salesforce.com/form/signup/freetrial-sales.jsp
.
Loading Data from Salesforce Objects
Assuming, then, that you have a valid Salesforce account, here is how you can load data from Salesforce objects
into Power BI Desktop:
- 1.In the Power BI Desktop Home ribbon, click the Get Data button.
- 2.Click Online Services on the left, and then select Salesforce Objects on the right. The Get Data dialog will look like Figure 5-7.
Figure 5-7.The Get Data dialog for online services - 3.Click Connect. The Salesforce Objects dialog will appear. It should look like the one shown in Figure 5-8.
Figure 5-8.The Salesforce Objects dialog - 4.Select the Production button and click OK. The Access Salesforce login dialog will appear. It should look like the one shown in Figure 5-9.
Figure 5-9.The Access Salesforce login dialog - 5.Unless you are already signed in, click Sign in. The Salesforce sign-in dialog will appear.
- 6.Enter your Salesforce login and password. The dialog should look something like the one shown in Figure 5-10.
Figure 5-10.The Salesforce sign-in dialog - 7.If this is the first time that you are connecting to Salesforce from Power BI Desktop (or if you have requested that Salesforce request confirmation each time that you log in), you will be asked to verify your identity. The Salesforce Verify Your Identity dialog will appear, as shown in Figure 5-11.
Figure 5-11.The Salesforce Verify Your Identity dialog - 8.Click Verify. Salesforce will send a verification code to the e-mail account that you are using to log in to Salesforce.
- 9.Enter the code in the Verification Code field and click OK. You will see the Allow Access dialog, as in Figure 5-12.
Figure 5-12.The Salesforce Allow Access dialog - 10.Click Allow. You will return to the Access Salesforce dialog, only now you are logged in. You can see this in Figure 5-13.
Figure 5-13.The Access Salesforce dialog - 11.Click Connect. The Navigator will appear, showing the Salesforce objects that you have permissions to access. You can see some of the objects that are available if you are using a trial account in Figure 5-14.
Figure 5-14.The Navigator dialog showing Salesforce Objects - 12.Select the objects whose data you wish to load into Power BI Desktop and click Load. The data will be loaded into Power BI Desktop ready for you to create dashboards and reports based on your Salesforce data .
Tip
To avoid having to confirm your identity to Salesforce every time that you create a new suite of Power BI Desktop reports using Salesforce data
, you can check “Remember me” in the Salesforce sign-in dialog and “Don’t ask again” in the Salesforce Verify Your Identity dialog.
Salesforce objects
contain a vast amount of data. However, you are, in effect, accessing a database structure. This means that you have to have some understanding of how the underlying data is stored. Should you wish to learn about the way that Salesforce data
is structured, then I suggest that you start with the Salesforce documentation currently available at
https://trailhead.salesforce.com/en/modules/data_modeling/units/objects_intro
.
Salesforce Reports
If you find that you are simply submerged by the amount of data that is available in Salesforce
, you can, instead, go directly to the data that underlies standard Salesforce reports
. This will avoid your having to learn about the underlying data structures. The downside is that you cannot easily extend these datasets.
To access Salesforce report data, simply follow the steps outlined in the previous section. However, instead of choosing Salesforce Objects
in step 2, select Salesforce Reports instead. The Navigator dialog will, in this case, look something like the one shown in Figure 5-15.
Figure 5-15.
The Navigator dialog showing the data for Salesforce Reports
From here you can select and load the reports data from Salesforce
that you want to use to create your own dashboards.
Microsoft Dynamics 365
Another online service
that contains much valuable enterprise data is Microsoft Dynamics 365
. As you would probably expect, Power BI Desktop can connect easily to Microsoft online sources such as Dynamics. Here is how to do this:
Tip
If you do not have a corporate Microsoft Dynamics 365 online account but want, nevertheless, to see how to use Power BI Desktop to connect to Microsoft Dynamics 365 data, you can always set up a free 30-day trial account. The URL for this is
https://trials.dynamics.com/CustomerEngagement/ChangeSignup/
. Indeed, this example is from using a free 30-day trial account (that will likely have expired long before this book is in print).
- 1.In the Power BI Desktop Home ribbon, click the Get Data button.
- 2.Click Online Services on the left, and then select Dynamics 365 (online) on the right. The Get Data dialog will look like Figure 5-16.
Figure 5-16.The Get Data dialog for Dynamics 365 - 3.Click Connect. The Dynamics 365 (online) dialog will appear.
- 4.Enter the URL that you use to connect to Dynamics 365 and add /api/data/v8.1 (at least, this was the case as this book went to press). It could look like the one shown in Figure 5-17. Note, however, that this URL will vary depending on where you are in the world.
Figure 5-17.The Dynamics 365 (online) dialog - 5.Click OK. The OData Feed dialog will appear.
- 6.Select Organizational Account as the security access method. The OData Feed dialog will look like Figure 5-18.
Figure 5-18.The OData Feed dialog - 7.Click Sign In to sign in to your Dynamics 365 account and follow the Microsoft sign-in process. Once completed, the OData Feed dialog will look something like the one in Figure 5-19.
Figure 5-19.The OData Feed dialog after sign-in - 8.Click Connect. The Navigator dialog will appear showing all the Dynamics objects that you have permissions to connect to. You can see an example of this in Figure 5-20.
Figure 5-20.The Navigator dialog for Dynamics 365
Note
In step 6 you saw that an MS Dynamics 365 connection is really an OData connection. OData is explained in more detail in a subsequent section of this chapter.
There are a huge number of Dynamics 365 tables—and this number will vary depending on the subscription that your organization has taken out. However, you are, in reality, accessing a database structure. This means that you have to have some understanding of how the underlying data is stored. Should you wish to learn about Dynamics 365 tables, then I suggest that you start with the Microsoft online help at
https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/data-entities/data-entities
.
Google Analytics
Assuming that you have a valid Google Analytics
account set up, you can use Power BI Desktop to connect to the Google Analytics data that you have permissions to access. For this example to work, you will need a valid and functioning Google Analytics account.
Note
To sign up for a Google Analytics account that you can use to test Power BI Desktop, go to
https://www.google.com/analytics
.
- 1.In the Power BI Desktop Home ribbon, click the Get Data button.
- 2.Click Online Services on the left, and then select Google Analytics on the right. The Connecting to a third part service dialog will look like Figure 5-21.
Figure 5-21.The third-party service connector alert - 3.Click Continue. The Google Account dialog will appear. This currently looks like the one in Figure 5-22.
Figure 5-22.The Google Analytics connection dialog - 4.Click Sign In. The Google Choose an Account dialog will be displayed. This currently looks like the image in Figure 5-23.
Figure 5-23.The Google Analytics login dialog - 5.Click the existing account to use for Google Analytics. The Google Analytics permissions dialog will appear. This currently looks like the one in Figure 5-24.
Figure 5-24.The Google Analytics permissions dialog - 6.Click Allow. You will return to the Google Account dialog, but logged in this time. You can see this in Figure 5-25.
Figure 5-25.The Google Analytics dialog when signed in - 7.Click Connect. The Navigator dialog will appear displaying the data tables that you can connect to in Google Analytics.
OData Feeds
OData
is a short way of referring to the Open Data Protocol. This protocol allows web clients to publish and edit resources, identified as URLs. The data that you connect to using OData can be in a tabular format or indeed in different structures.
OData
is something of a generic method of connecting to web-based data. Consequently each OData source could differ from others that you may have used previously. Indeed, you have already seen OData when connecting to Dynamics 365.
However, there are a multitude of OData
sources that are available. Some are public, some are only accessible if you have appropriate permissions. However, the access method will always be broadly similar. Here, then, is an example of how to connect to an OData sample source that Microsoft has made freely available:
- 1.In the Power BI Desktop Home ribbon, click the small triangle at the bottom of the Get Data button.
- 2.Select OData Feed from the menu. The OData Feed dialog will appear.
- 3.Enter the URL that you are using to connect to the OData source. In this example I will use a Microsoft sample OData feed that you can find at http://services.odata.org/northwind/northwind.svc . The dialog should look like Figure 5-26.
Figure 5-26.The OData Feed dialog - 4.Click OK. The Navigator dialog will be displayed and will show the data available using the specified URL. An example is given in Figure 5-27.
Figure 5-27.The Navigator dialog using an OData source
OData Options
The OData
Feed dialog (rather like the From Web dialog) also contains an Advanced button. Selecting this will expand the dialog to allow you to add one or more URL parts to the URL. You can see this in Figure 5-28.
Figure 5-28.
The OData Feed dialog Advanced options
Note
URL parts can be parameterized in the Power BI Desktop Query Editor. I will explain parameterization in Chapter 9.
Azure SQL Database
SQL Server does not only exist as an on-premises database. It is also available as a “Platform as a Service” (also known as PaaS). Simply put, this lets you apply a pay-as-you-go model to your database requirements where you can fire up a database server in the cloud in a few minutes and then scale it to suit your requirements, rather than buying hardware and software and having to maintain them.
Connecting to Microsoft’s PaaS offering, called Azure SQL Database
, is truly simple. If you have the details of a corporate Azure SQL Database, you can use this to connect to. If you do not, and nonetheless want to experiment with connecting Power BI Desktop to Azure SQL Database, you can always request a free trial account from Microsoft and set up an Azure SQL Database database in a few minutes. If this is the path that you are taking, then you can find instructions on how to do this (including loading the sample data that you will connect to later in this section) at the following URL:
https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-get-started-portal
.
Tip
When you are creating the Azure SQL database
, be sure to define the source to be Sample. This will ensure that the MS sample data is loaded into your test database.
Note
If you are setting up an Azure SQL database
, make sure that you include firewall rules to allow connection from the computer where you are running Power BI Desktop to the Azure SQL database.
To connect from Power BI Desktop to an Azure SQL database
:
- 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 More.
- 3.Click Azure in the list on the left, and then Azure SQL Database on the right. The Get Data dialog will look like the one in Figure 5-29.
Figure 5-29.Azure data sources in the Get Data dialog - 4.Click Connect. The SQL Server Database dialog will appear (after all, an Azure SQL database is a SQL Server database—but in the cloud).
- 5.Enter the Azure SQL Database server name that you obtained from the Microsoft Azure management portal (or that was given to you by a corporate DBA). The SQL Server Database dialog will look like the one shown in Figure 5-30.
Figure 5-30.The SQL Server Database dialog for an Azure SQL database connection - 6.Click OK. The credentials dialog will appear.
- 7.Click Database on the left and enter a valid user name and password. The credentials dialog will look like the one shown in Figure 5-31.
Figure 5-31.The SQL Server credentials dialog for an Azure SQL database connection - 8.Click Connect. The Navigator dialog will appear showing the database(s) that you have permission to access in the Azure SQL Server database. This dialog will look like the one shown in Figure 5-32 if you are using the test data supplied by Microsoft for a default Azure SQL database .
Figure 5-32.The Navigator dialog for an Azure SQL database connection showing sample data
If you followed the steps to connect to an on-premises SQL Server database in Chapter 3, then you are probably feeling that the approach used here is virtually identical. Fortunately, the Power BI development team has worked hard to make the two processes as similar as possible. This extends to
- Ensuring that the DataSource settings are stored by Power BI Desktop and can be updated just as you can for an on-premises database connection
- Allowing you either to use DirectQuery or to import data into the Power BI in-memory data model
- Using the same Advanced options (writing your own SELECT queries or using stored procedures) that you can use with an on-premises SQL Server
Azure SQL Data Warehouse
Azure has many available platforms to store data. One that is particularly well adapted to Power BI Desktop is the Azure SQL Data Warehouse
. This is a tabular data warehouse that is hosted in the cloud.
Once again I will presume that, unless you have a corporate Azure SQL Data Warehouse at hand, you will be using a trial Azure account and that you have provisioned an Azure SQL Data Warehouse
using the sample data that Microsoft provides. Setting up a test data warehouse is very similar to preparing a database, as described at the URL at the start of the previous section. Here, too, you need firewall rules to be set up correctly (although this may not be strictly necessary if you have previously set up firewall rules for, say, Azure SQL Database).
Note
When you are creating the Azure SQL Data Warehouse
, be sure to define the source to be Sample. This will ensure that the MS sample data is loaded into your test data warehouse and you will not be querying an empty data warehouse.
- 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 More.
- 3.Click Azure in the list on the left, and then Azure SQL Data Warehouse on the right.
- 4.Click Connect. The SQL Server Database dialog will appear.
- 5.Enter the Azure SQL Data Warehouse server name that you obtained from the Microsoft Azure management portal (or that was given to you by a corporate DBA). The SQL Server Database dialog will look like the one shown in Figure 5-33.
Figure 5-33.The SQL Server Database dialog for an Azure SQL Data Warehouse connection - 6.Select the Import button and then click OK. The credentials dialog will appear.
- 7.Click Database on the left and enter a valid user name and password.
- 8.Click Connect. The Navigator dialog will appear showing the database(s) that you have permission to access in the Azure SQL Server database. This dialog will look like the one shown in Figure 5-34 if you are using the test data supplied by Microsoft.
Figure 5-34.The Navigator dialog for an Azure SQL Data Warehouse connection showing sample data - 9.Select the tables that you need and click Load or Edit to return to Power BI Desktop and begin adding visuals to your report.
Note
Do not be phased by the fact that the title for the dialog where you specify the server and database says “SQL Server Database.” This will connect you to the Azure Data Warehouse
correctly.
As was the case for on on-premises connection, you can choose a Live Connection (even if the dialog calls it DirectQuery) and can expand the Advanced options field to enter a specific DAX query if you are loading data.
Connecting to SQL Server on an Azure Virtual Machine
More and more databases are now hosted outside a corporate environment by cloud services
providers. With a provider such as Amazon (with RDS for SQL Server) or Microsoft (who offers virtual machines—or VMs—for SQL Server in Azure), you can now site your databases outside the enterprise and access them from virtually anywhere in the world.
So, to extend the panoply of data sources available to Power BI Desktop, we will now see, briefly, how to connect to SQL Server on an Azure Virtual Machine
. Admittedly, connecting to SQL Server on an Azure Virtual Machine is nearly the same as connecting to SQL Server in a corporate environment. However, it is worth a short detour to explain, briefly, how to return data to Power BI Desktop from a SQL Server instance in the cloud.
Once again, if you do not have a SQL Server instance that is hosted on an Azure Virtual Machine in your corporate environment, then you can always test this process using an Azure trial account. I cannot, however, explain here how to set up a SQL Server instance on a VM
, as this is outside the scope of this book. There are, however, many resources available that can explain how to do this should you need them.
Note
If you are creating your own virtual machine, then you can connect to this in SQL Server Management Studio using the same connection string that you use in step 5 below and create the sample database manually. You can then load the sample data as described in Appendix B. I will not explain this process in detail as I am presuming a basic level of familiarity with Windows Server and SQL Server if you are setting up your own VM.
To connect to SQL Server on a Virtual Machine
:
- 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 full string that describes the server in the Server text box. Either this will be given to you by a corporate DBA or, if you are using your own Azure account, you can find it in the Azure Management Portal.
- 4.Enter the database name; if you have loaded the sample data that accompanies this book into a SQL Server instance in a VM, it will be CarSalesData. The dialog will look like Figure 5-35.
Figure 5-35.The Microsoft SQL Server Database dialog - 5.Click OK. The Access a SQL Server Database dialog will appear. Select Database as the security mode and enter the user name and password, as shown in Figure 5-36. If you are using your own Azure account, these can be the user name and password that you specified when setting up the virtual machine .
Figure 5-36.The SQL Server Database dialog when connecting to a virtual machine - 6.If you see the encryption support dialog, click OK. The Navigator dialog will appear as shown in Figure 5-37, listing all the tables that you have permissions to see on the SQL Server hosted by the virtual machine .
Figure 5-37.The Navigator dialog when connecting to a virtual machine
As you can see, the process is virtually identical to the one that you followed to connect to SQL Server in Chapter 2. I have, nonetheless, a few points that I need to bring to your attention:
- You use the Azure VM multipart name as the server name.
- As was the case when connecting to an on-premises SQL Server instance, you can select the database if required.
- You can use the server’s IP address as the database name if the VM has specified a public IP address.
- Security is a big and separate question. In a corporate environment, you might be able to use Windows security to connect. You will almost certainly have to use database security for a test VM.
- As is always the case in Azure, firewalls must be set up correctly.
- DirectQuery is not available when connecting to SQL Server on an Azure Virtual Machine .
Azure Blob Storage
The final Azure data source that I want to introduce you to in this chapter is Azure Blob Storage
. To all intents and purposes you can consider this, as far as Power BI Desktop is concerned, as a file share in the cloud. So if you need to access data that is stored as files, you can connect to them via Azure Blob Storage.
Once again, you will need either corporate access to Azure Blob Storage
or an Azure trial account. In either case you need to copy the two sample files that are in the folder C:PowerBiDesktopSamplesCH05 into a container in your Azure Blob Storage. Downloading the sample files is explained in Appendix A.
Once the source data is available in Azure Blob Storage, you can carry out 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 More.
- 3.Click Azure in the list on the left, and then Azure Blob Storage on the right. The Azure Blob Storage connection dialog will be displayed.
- 4.Enter the account name that you are using to connect to Azure Blob Storage . The Azure Blob Storage dialog will look like the one shown in Figure 5-38. If you are using a corporate Azure Blob Storage account, then your system administrator will provide this. In a test scenario you can find this in the Azure Management Portal by opening the Storage Account blade and copying the Blob Service Endpoint.
Figure 5-38.The Azure Blob Storage connection dialog - 5.Click OK. The Azure Blob Storage Account Key dialog will appear.
- 6.In the Azure Management Portal, copy an account key. These can be found in the Azure Management Portal by clicking the Storage Account blade and then clicking Access Keys. If you have been sent an account key by a system administrator, then use that instead.
- 7.Paste the account key into the Azure Blob Storage Account Key dialog. The dialog will look like the one in Figure 5-39.
Figure 5-39.The Azure Blob Storage Account Key dialog - 8.Click Connect. The Navigator will appear, showing the list of files in the selected container. You can see an example of this in Figure 5-40.
Figure 5-40.The Navigator dialog showing available containers in Azure Blob Storage - 9.Click Load. The list of files stored in Azure will appear in Power BI Desktop.
Note
It is important to note that, for the moment at least, what you have returned from Azure is a list of available files. Chapter 8 explains how to select and load data from some or all of the available files into Power BI Desktop, where they can be used as a basis for analytics.
Azure Security
All cloud service
providers take security extremely seriously. As you have seen in this chapter, you will always be obliged to enter some form of security
token and/or specify a valid user name and password to connect to cloud-based data.
All the security information that you entered is stored in the Power BI Desktop Storage Settings. This can be removed or modified in the same way that you learned to update or remove database security information in Chapter 3.
Conclusion
In this chapter you saw, briefly, how to retrieve data that you access using the Internet. This can range from a table of data on a web page to a massive Azure SQL data warehouse. Alternatively, perhaps you need to create dashboards based in your Salesforce
, Google Analytics, or MS Dynamics 365 data. Maybe your organization has decided to move its datacenters to the cloud, and is using SQL Server in Azure or Amazon Redshift. In any case, Power BI Desktop can connect and access the data available in these services and repositories. It can even access big data in Hadoop.
Given the vast number of online sources, this chapter could only scratch the surface of this huge range of potential data repositories. However, as Power BI Desktop is rigorous about standardizing access to data, you should be able to apply the approaches you have learned in this chapter to many other data services, both current and future.
This chapter concludes the set of four chapters that introduced you to some of the many and varied data sources that you can use with Power BI Desktop. In the course of four chapters, you have seen how to load data from a selection of the more frequently used available sources. The good news is that Power BI Desktop can read data from dozens more sources. The bad news is that it would take a whole book to go into all of them in detail.
So I will not be describing any other data sources in this book. This is because now that you have come to appreciate the core techniques that make up the extremely standardized approach that Power BI Desktop takes to loading data, you can probably load any possible data type without needing much more information from me. Should you need any specific information on other data sources, then your best port of call is the Microsoft Power BI web site. This is currently at
http://support.powerbi.com
.
Now that you can find, access, and load the data you need into Power BI Desktop, it is time to move on to the next step. This means cleansing and restructuring the datasets so that they suit your analytical requirements. Handling these challenges is the subject of the next three chapters.