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.
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.
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: 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
PBIDS 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
PBIDS 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)
PBIDS 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)
PBIDS 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)
PBIDS 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
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.
Get started with Hevo today! Sign up here for a 14-day free trial!
Ofem Eteng is a seasoned technical content writer with over 12 years of experience. He has held pivotal roles such as System Analyst (DevOps) at Dagbs Nigeria Limited and Full-Stack Developer at Pedoquasphere International Limited. He specializes in data science, data analytics and cutting-edge technologies, making him an expert in the data industry.
