Power BI Data Sources: What You Can Connect & How

Anna Shalomova

PPM Consultant

Modern business needs a powerful service for information processing to create understandable reports and update them in real-time Power BI is a powerful enough and yet free BI platform. Microsoft is investing a lot in the development of this product, in connection with which there are often updates that expand its capabilities. You can use these versions of the program:

• Power BI Desktop is designed to develop a data model and reports; For Microsoft Windows 10 users, there is a separate version of Power BI Desktop.
• Power BI Service is an online counterpart that specializes in monitoring and analyzing ready-made reports, so the possibilities of designing and working with data there are very limited. As a rule, the desktop version is used as a constructor, after which the developed file is published to the Power BI Service.

Where Can I Download Data From?

Power BI has many built-in connectors for various services and databases, with the help of which you can load the necessary data set from various sources into a program in a matter of minutes, link them together and build consolidated reports and charts. At the time of this writing, all available connectors are divided into four groups:

As you can see, you can import data from the best-known databases and services into Power BI using various file formats. After downloading information from available sources in Power BI, you have great opportunities for cleaning and transforming data, since the platform has a fairly powerful ETL functionality. Loadable tables can be edited, and based on their columns, create calculated columns and measures — this way you can convert arrays of information into data sets with the structure necessary to build a visualization.

Datasets and their updates, reports, and dashboards, tiles and visualizations, which all of these blocks consist of, which of them are included in others, whether it is possible (and how) to create a report based on several data sets at the same time – all this and much more we lay out on the shelves. So, the data and how to prepare them.

Sources and Datasets

It is necessary to divide the related initially, but completely different concepts, which sometimes mix (they partially overlap): “data source” and “data set.” The data source is only the final path to the information you want to work with. The path to the file on the hard disk, the URL to Facebook, or the database’s location in the cloud – all this is the way, and all this is the source of the data.

A data set is, in turn, a collection of both the data source and the data itself, and the accounts that allow Power BI to connect to these data sources. Each time you connect to data (static or dynamic) or import it, Power BI automatically creates a data set. Which then, as a rule, are used when creating reports and visualizations.

Connecting to some data sources sometimes requires advanced query functions and Excel modeling or an installed Power BI Desktop application. Before you go directly to the analysis of data types that can be loaded into Power BI, I would like to draw your attention to the general features that these types have or, more precisely, what characteristics your sources should have to be working in Power BI. And the main thing you need to know about it:

Data Format

The data format, of course, must be supported by Power BI. Usually, there are no problems with this; however, some data types are already ready to work with Power BI, and some will have to be “prepared.” So, for example, when creating datasets from sources such as Google Analytics or Twilio, there are no difficulties with synchronization. Similarly and simply downloads data from cloud databases such as SQL Azure. They are fully operational. Enjoy.

But in some cases, direct data import is not possible. In particular, when trying to connect to data that is located on the servers of your company (data with closed access) or when trying to load a simple TXT file. In such a situation it is necessary:

• Extract this data;
• Use all your favorite Excel or Power BI Desktop for the initial data download;
• Save the received data as a file;
• Import the file into Power BI.

Data Updates

Want always to have the latest information? Surely you want, and Power BI can provide you with this opportunity. Using the connection information (which, as we already said, are included in the data set along with the indication of the data source), Power BI connects to the necessary data, checks them for changes, updates its data set and, at the same time, everything related to this set (reports, visualizations, etc.).

How often is the data updated and what needs to be done for this? The update frequency depends on the type of data being updated, but, for example, for some cloud data stores (such as OneDrive service files), Power BI checks the source about once per hour. However, it is worth remembering that the packages themselves are the same OneDrive updated about once a day. And, of course, you can customize the update time at will or update the report on a newly modified file at any time manually.

The answer to the question “what is needed for updates” also depends on the type of data. Sometimes nothing is required for this, and sometimes you will have to use the Power BI Gateway personal gateway. This is such a secure data transfer bridge, which, for example, will be needed to update Excel spreadsheet data located on a local disk. Or to configure updates. We will also tell you more about the gateway in the data update article. Or contact the professionals if you can not wait.

Data Types

So, we come to the most interesting, to the data types that Power BI is friends with. We combined them into groups — files, content packs, and databases. If you are upset by the small number of data types to which you can connect directly through Power BI, once again we draw your attention to the fact that is using Power BI Desktop, you get about fifty new features. It just takes a little more extra action. And, perhaps, the help of a specialist. To cover the maximum amount of data you can access and not be limited to Power BI alone. With Power Bi Desktop, data loading is divided into the File, Database, Azure, and Other categories.

• Some files are uploaded to Power BI without problems (for example, Excel tables or PBI files), some (regular .txt or .xml tables) need to be converted first. The File category in the Power BI Desktop download menu offers the following data options for the connection: Excel, CSV, XML, Text, JSON, and Folder.
• Power BI can connect to both local databases and those in the cloud. At the same time connections to cloud storage are dynamic, i.e., each time you, for example, create a visualization in a report, Power BI requests the data set. To connect to local databases, you need a corporate Power BI gateway. To set it up, you need to contact your local administrator or, again, contact your consultant for help. Currently, Power BI Desktop provides connectivity to the following databases: SQL Server, Access, SQL Server Analysis Services, Oracle, IBM DB2, MySQL, PostgreSQL, Sybase, Teradata, and SAP HANA.

Better, Faster, Stronger with Custom-Made Solutions

As you can see, with the right amount of knowledge, beautiful and useful analytics using Power Bi can be pulled from a diverse number of data sources. However, it can get even better than that with “Content Packs.” “Data packaging” is incredibly convenient. You need to get started. As, sorry for the comparison, an “Easy bake cake mix.” You can’t even download it. There are often restrictions on using packages. For the services of organizations, it is necessary to use the Power BI Pro version.

If you are using Project Online, then everything is simple for you – take FluentPro’s ready to use. If you wish, you can connect to almost any data source that supports the OData protocol (and their absolute majority). Plus, you can make any local databases that are not in the cloud, but a data source. For example, FluentPro has the “Cake Mix” for you. More so, if you need something custom, then they can “attach” practically anything, and their consultants will help you to make both custom “made changes.” Like, create your own data set. One of our clients has asked to add Financials* into Power BI reporting capabilities. So, we’ve created a Power BI+Financials conjunction. This is a standard feature of our Power BI for Project Online packs, available to everyone, but initially, its development was commissioned by one of the clients.

* FluentPro Financials is a financial management app that provides a seamless experience of project budgeting, gives full control over your project finances, and improves profitability. Helps with estimation, planning, budgeting, project finances monitoring, and control

Power BI Desktop features

At the end of the article we will briefly discuss the data connectivity provided by Power BI Desktop:

• “Other” data Power BI Desktop. The “Other” category includes a decent number of possible connections: Web Application, SharePoint List, OData Web Feed, Hadoop File (HDFS), Active Directory, Microsoft Exchange, Dynamics CRM Online, Facebook, Google Analytics, SalesForce Objects, SalesForce Reports, ODBC and beta versions of the following connections: R Script, appFigures, GitHub, MailChimp, Merketo, QuickBooks Online, Smartsheet, SQL Sentry, Stripe, SweetIQ, Twilio, ZenDesk, Spark. It is unlikely that a beginner to learn Power BI will not find the necessary here. By the way, when connecting to the “Web,” the program itself searches for information on the page and presents it in the preview mode in the Navigator area. Choose what you need, edit, download, work.
• “Azure” Power BI Desktop. Power BI Desktop provides solid connectivity and integration with Azure services, so this data connection option is even highlighted in a separate section of the menu. It includes the following connection options: Microsoft Azure SQL database, Microsoft Azure SQL data store, Microsoft Azure Marketplace, Microsoft Azure HDInsight, Microsoft Azure Blob Storage, Microsoft Azure Tabular Storage. And to the listed three more beta versions of products: Azure HD Insight Spark, Microsoft Azure DocumentDB, and Microsoft Azure Data Lake Storage. In general, there