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 data sources are a powerful enough and yet free BI platform. Microsoft is investing a lot in developing this product, 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 monitors and analyzes 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 their help, you can load the necessary data set from various sources into a program in 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 reasonably powerful ETL functionality. Loadable tables can be edited, and based on their columns, calculated columns and measures can be created. In this way, you can convert arrays of information into data sets with the structure necessary to build a visualization.

Datasets and their updates, reports, 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 wholly but initially 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 the source of the data.

A data set is, in turn, a collection of both the data source, 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. As a rule, these are used when creating reports and visualizations.

Connecting to some data sources sometimes requires advanced query functions, 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 of these types 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 must be supported by Power BI. Usually, there are no problems with this. However, some data types are ready to work with Power BI, and some must be “prepared.” So, for example, when creating datasets from sources such as Google Analytics or Twilio, synchronization has no difficulties. Similarly it 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 located on your company’s servers (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, is 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 are the same OneDrive, updated about once daily. And, of course, you can customize the update time at will or manually update the report on a newly modified file at any time.

The answer to the question “What is needed for updates” also depends on the data type. Sometimes nothing is required, and sometimes you must use the Power BI Gateway personal gateway. This is a secure data transfer bridge, which, for example, will be needed to update Excel spreadsheet data 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 exciting data types that Power BI is familiar 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 by 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. Power BI Desktop divides data loading into the File, Database, Azure, and Other categories.

• Some files are uploaded to Power BI without problems (for example, Excel tables or PBI files), and some (regular .txt or .xml tables) must 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 valuable Power BI analytics can be pulled from diverse 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 organizations, it is necessary to use the Power BI Pro version.

If you use Project Online, everything is simple – take FluentPro’s ready-to-use. If you wish, you can connect to almost any data source that supports the OData protocol (and the absolute majority). Plus, you can make any local databases not in the cloud but a data source. For example, FluentPro has the “Cake Mix” for you. More so, if you need something custom, they can “attach” practically anything, and their consultants will help you 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. In addition, it 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 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, and 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 menu section. 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.