Businesses need an efficient tool for powerful and creative analysis, to gain access to vital information from different kinds of data sources, create beautiful and understandable reports which can be updated in real-time, to promote growth and ensure the smooth operations of the enterprise, thereby, reducing downtime.
One such platform with these abilities is Power BI designed by Microsoft to help organizations structure data into a model that can be easily interpreted. Microsoft has invested heavily in the development of this product by dishing out constant updates to expand its capabilities and giving ample room for Power BI to be connected to various data structures.
The purpose of this write-up is aimed at showing how data sources can be integrated into Power BI through the available connectors found on Power BI Desktop and explaining these data sources. First, Power BI will be explained and its uses stated before the article will proceed to mention the Power BI data sources and elucidate how they can be integrated into the platform.
Table of Contents
Introduction to Microsoft Power BI
Power BI is an application that helps you to connect, transform, clean, structure, and visualize your data. It gives you the ability to connect to multiple sources of data, therefore, combining them to form a data model in Power BI.
Power BI is essential in business intelligence projects as it connects to diverse data sources, transforms the data, creates sophisticated reports for analysis through the models gotten from the cleaned data, and enables you to share the reports with other members of the project team.
There are different versions of Microsoft Power BI which include Power BI Desktop, Power BI Service which is an online Software as a Service (SaaS) offering, and a selection of mobile apps available on Windows, iOS, and Android devices.
With over 120+ connectors, Power BI allows you to link up with native connectors and cloud data sources to come up with data-driven decisions thereby improving services and overall growth of your organization.
Understanding Integration of Power BI Data Sources
Power BI Desktop has various data sources from which you can connect. The data source can be described as the pathway to the information you want to work with i.e the path to the file on a hard disk, the URL to a database located in the cloud, and so on. The data collected from these sources are integrated with Power BI where you can combine them into datasets by cleaning and transforming them to build consolidated reports and charts.
The data sources on Power BI are constantly being reviewed and more options added and made available to the general public, though some may still be in the developmental stages and may not perform optimally hence, Beta or Preview tags are added to signify this. Some of the data connectors may require access to the internet to be able to authenticate the source.
In the next section, you will dive into how to integrate Power BI data sources and each of the data sources will be explained extensively.
Procedure to Integrate Power BI Data Sources
To select and connect to an available data source of your choice, do the following:
- Click on the Home ribbon.
- Select the Get Data icon.
- When you click on the arrow beneath the Get Data icon, a list of the most Common data sources shows first. Click on the More tab to see the full list of data sources.
- The Get Data dialog box displays where the data sources are displayed and organized in the following categories:
Power BI Data Source: All
The All category when selected displays all the data connection types from all the categories found in Power BI.
Power BI Data Source: File
The File category when clicked on comprises the flat-file types supported in Power BI and include the following data connection types:
- Excel
- Text/CSV
- XML
- JSON
- Folder
- PDF
- Parquet
- SharePoint folder
Power BI Data Source: Database
The Database section has the following data connections which will be displayed when selected:
- SQL Server database
- Access database
- SQL Server Analysis Services database
- Oracle database
- IBM Db2 database
- IBM Informix database (Beta)
- IBM Netezza
- MySQL database
- PostgreSQL database
- Sybase database
- Teradata database
- SAP HANA database
- SAP Business Warehouse Application Server
- SAP Business Warehouse Message Server
- Amazon Redshift
- Impala
- Google BigQuery
- Vertica
- Snowflake
- Essbase
- Actian (Beta)
- AtScale cubes
- BI Connector
- Data Virtuality LDW (Beta)
- Denodo
- Dremio
- Exasol
- Indexima
- InterSystems IRIS (Beta)
- Jethro (Beta)
- Kyligence
- Linkar PICK Style / MultiValue Databases (Beta)
- MariaDB (Beta)
- MarkLogic
Power BI Data Source: Direct Query
In Database data sources, you will be required to put in the server name/user name and password to connect. The connection can also be done directly through a direct SQL query using Advanced options or by selecting Data Connectivity mode– Import or DirectQuery.
In DirectQuery, the data stays in the SQL database limiting data manipulation and is real-time therefore scheduling is not needed as an update is done automatically whereas, in Import, data transformation and manipulation is allowed but you have to schedule it to enable constant refresh.
Power BI Data Source: Power Platform
The Power Platform provides the following options:
- Power BI datasets
- Power BI dataflows
- Common Data Services (Legacy)
- Dataverse
- Power Platform dataflows (Beta)
Hevo Data, a No-code Data Pipeline helps to transfer data from 100+ sources to a Data Warehouse/Destination of your choice and visualize it in your desired BI tool such as Power BI. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.
It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. It allows you to focus on key business needs and perform insightful analysis using a BI tool of your choice.
Check out what makes Hevo amazing:
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
- Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
- Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
- Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Simplify your data analysis with Hevo today! Sign up here for a 14-day free trial!
Power BI Data Source: Azure
Under the Azure section, you can connect to the following as they are available in the Azure cloud for easy integration:
- Azure SQL Database
- Azure Synapse Analytics (SQL DW)
- Azure Analysis Services database
- Azure Database for PostgreSQL
- Azure Blob Storage
- Azure Table Storage
- Azure Cosmos DB
- Azure Data Explorer (Kusto)
- Azure Data Lake Storage Gen2
- Azure Data Lake Storage Gen1
- Azure HDInsight (HDFS)
- Azure HDInsight Spark
- HDInsight Interactive Query
- Azure Cost Management
- Azure Databricks
- Azure Time Series Insights (Beta)
Power BI Data Source: Online Services
Power BI gives you the ability to access various online sources. The Online Services category includes the following connectors:
- SharePoint Online List
- Microsoft Exchange Online
- Dynamics 365 (online)
- Dynamics NAV
- Dynamics 365 Business Central
- Dynamics 365 Business Central (on-premises)
- Microsoft Azure Consumption Insights (Beta)
- Azure DevOps (Boards only)
- Azure DevOps Server (Boards only)
- Salesforce Objects
- Salesforce Reports
- Google Analytics
- Adobe Analytics
- appFigures (Beta)
- Data.World – Get Dataset (Beta)
- GitHub (Beta)
- LinkedIn Sales Navigator (Beta)
- Marketo (Beta)
- Mixpanel (Beta)
- Planview Enterprise One – PRM (Beta)
- QuickBooks Online (Beta)
- Smartsheet
- SparkPost (Beta)
- SweetIQ (Beta)
- Planview Enterprise One – CTM (Beta)
- Twilio (Beta)
- Zendesk (Beta)
- Asana (Beta)
- Dynamics 365 Customer Insights (Beta)
- Emigo Data Source
- Entersoft Business Suite (Beta)
- eWay-CRM (Beta)
- FactSet Analytics
- Palantir Foundry
- Hexagon PPM Smart API
- Industrial App Store
- Intune Data Warehouse (Beta)
- Microsoft Graph Security (Beta)
- Projectplace for Power BI
- Product Insights (beta)
- Quick Base
- Spigit (Beta)
- TeamDesk (Beta)
- Webtrends Analytics (Beta)
- Witivio (Beta)
- Workplace Analytics (Beta)
- Zoho Creator (Beta)
Power BI Data Source: Other
The Other data sources have the following options under this category:
- Web
- SharePoint list
- OData Feed
- Active Directory
- Microsoft Exchange
- Hadoop File (HDFS)
- Spark
- Hive LLAP
- R script
- Python script
- ODBC
- OLE DB
- Acterys : Model Automation & Planning (Beta)
- Anaplan Connector v1.0 (Beta)
- Automation Anywhere (Beta)
- Solver
- Cherwell (Beta)
- Cognite Data Fusion
- FHIR
- Information Grid (Beta)
- Jamf Pro (Beta)
- MicroStrategy for Power BI
- Paxata
- QubolePresto (Beta)
- Roamler (Beta)
- Shortcuts Business Insights (Beta)
- Siteimprove
- Starburst Enterprise Presto (Beta)
- SurveyMonkey (Beta)
- Tenforce (Smart)List
- TIBCO(R) Data Virtualization (Beta)
- Vena (Beta)
- Vessel Insight (Beta)
- Zucchetti HR Infinity (Beta)
- Blank Query
- After selecting your data source from the options listed such as All, File, Database, etc, click on Connect found at the bottom of the Get Data window to integrate your data to Power BI.
A connection window is shown specifically for the type of data connection selected and credentials might be added where necessary depending on the data source chosen. The various connection types have been explained in the different sections above. To get a comprehensive list of all the available data sources found on Power BI, visit the official site where a complete list is found.
Using Power BI Data Source (PBIDS) Files to Get Data
This is an alternative method in getting data for analysis on Power BI. Power BI Data Source (PBIDS) files can be described as Power BI Desktop files that have a defined structure with the extension .PBIDS for identification as a Power BI data source file. It is created to streamline the options from Get Data and this can be done easily from an existing report.
When a PBIDS file is opened, a prompt is shown where you will fill the credentials to authenticate for onward connection to the data source that is specified in the file. After doing this, a Navigation dialog box displays where you are required to specify the table from that data source to load in the model from which visualizations can be built and additional sets of tables can be added to the model by using Recent Sources.
Currently, you can only add data from one data source to a file, and trying to do otherwise would result in an error message.
Creating a PBIDS Connection File
A PBIDS file can either be created manually or from an existing Power BI Desktop (.PBIX) file connected to the data you want to extract from. If you have an existing .PBIX file, you can simply export the files from within the Power BI desktop and they would be auto-generated. For the creation of a PBIDS file from an existing file, do the following:
- Select File.
- Click on Options and Settings.
- Select Data source settings.
- In the dialog box that shows up, specify the data source you wish to export as a PBIDS file, then select Export PBIDS and Power BI Desktop will generate the PBIDS file.
PBIDS files can also be opened in a text editor where further modifications to your file can be done.
For the manual method, below are a few examples of commonly used data sources in the creation of PBIDS files. The examples show simple codes that can be used for these popular sources. Important to bear in mind though, is the examples here are not exhaustive, PBIDS file does not include authentication information, table, and schema information, and the PBIDS file type only supports data connections that are also supported in Power BI Desktop except for Wiki URLs, Live Connect and Blank Query. For a full list of supported data connections, see here.
Example of creating a PBIDS file for Azure AS:
{
"version": "0.1",
"connections": [
{
"details": {
"protocol": "analysis-services",
"address": {
"server": "server-here"
},
}
}
]
}
Example of creating a PBIDS file for Folder:
{
"version": "0.1",
"connections": [
{
"details": {
"protocol": "folder",
"address": {
"path": "folder-path-here"
}
}
}
]
}
Example of creating a PBIDS file for SQL Server:
{
"version": "0.1",
"connections": [
{
"details": {
"protocol": "tds",
"address": {
"server": "server-name-here",
"database": "db-name-here (optional) "
}
},
"options": {},
"mode": "DirectQuery"
}
]
}
Example of creating a PBIDS file for Text File:
{
"version": "0.1",
"connections": [
{
"details": {
"protocol": "file",
"address": {
"path": "path-here"
}
}
}
]
}
Example of creating a PBIDS file for Web:
{
"version": "0.1",
"connections": [
{
"details": {
"protocol": "http",
"address": {
"url": "URL-here"
}
}
}
]
}
Conclusion
In this article, you have been shown a comprehensive list of the available Power BI Data Sources and have been put through on how they can be integrated into Power BI for further analytical performances.
Getting to know all of these data types and when to apply them can be very strenuous, Automated integration with your Data Warehouses/multiple data sources and the Analytics database can make your choice much simpler as a lot of necessary features can be integrated readily.
Integrating and analyzing data from a huge set of diverse sources can be challenging, this is where Hevo comes into the picture. Hevo Data, a No-code Data Pipeline helps you transfer data from a source of your choice in a fully automated and secure manner without having to write the code repeatedly. Hevo with its strong integration with 100+ sources & BI tools, allows you to not only export & load Data but also transform & enrich your Data & make it analysis-ready in a jiffy.
Get started with Hevo today! Sign up here for a 14-day free trial!
Ofem is a freelance writer specializing in data-related topics, who has expertise in translating complex concepts. With a focus on data science, analytics, and emerging technologies.