Power BI is a tool that allows you to connect to various types of data sources and analyse and visualize your data. Power BI supports different types of data connectors, which are classified into three categories:
• Import connectors: These connectors allow you to import data from the data source into Power BI. The imported data is stored in a compressed and optimized format in Power BI, which enables fast and interactive analysis. the imported data needs to be refreshed periodically to reflect any changes in the data source. Some examples of import connectors are Excel, CSV, JSON, SQL Server, Oracle, etc.
• DirectQuery connectors: These connectors allow you to query data from the data source without importing it into Power BI. The query results are displayed in Power BI as visuals, but the underlying data remains in the data source. This enables real-time and dynamic analysis, but it may also affect the performance and functionality of Power BI. Some examples of DirectQuery connectors are Azure SQL Database, Azure SQL Data Warehouse, SAP HANA, etc.
• Live Connection connectors: These connectors allow you to connect to an existing model that is hosted on a server, such as Analysis Services or Power BI datasets. The model contains the data and the metadata, such as measures, calculated columns, relationships, etc. You can use Power BI to create visuals based on the model, but you cannot modify the model or add new data sources. Some examples of Live Connection connectors are Analysis Services (Tabular or Multidimensional), Power BI datasets, etc.
Different types of Power BI Connectors
Files Connectors
The File category provides the following data connections:
1. Excel
Excel is a powerful, flexible tool for every analytics activity.
It is the most common way to collect and maintain data in organizations.
You can connect Excel with Power BI to get broad data analytics and visualization capabilities.
Combine the versatility of Excel with the governance and security standards of Power BI.
Connect Power BI datasets to Excel, and interact with them using PivotTables, charts, slicers, and other Excel features.
2. PDF
PDF Connector in Power BI enables users to source data from PDF Files.
Using Power Query in Excel & Power BI you can extract tables or entire pages of information from PDF files for inclusion in our spreadsheets or data models.
By creating persistent connections to PDF files, you can auto-update your spreadsheets/Power BI whenever the PDF file receives new data.
3. XML
XML files are used to store data that is both human-readable, and structured for software to process.
The XML format is one of the widely used formats to store/process or analyze data at scale.
You can easily load data from an XML file in Power BI using the Get Data option.
Encode and visualize database records, transactions, and many other types of XML data.
4. JSON
JSON (JavaScript Object Notation) is again, one of the most widely used formats for data exchange between different platforms and applications.
With Power BI, you can export data from JSON files in form of a table to create different types of visualization.
Power Query uses automatic table detection to seamlessly flatten the JSON data into a table. You can then continue to transform & visualize the data.
5. Text/CSV
CSV file stores tabular data (numbers and text) in plain text.
Connecting a CSV file to Power BI is as simple as connecting an Excel workbook.
From Get Data, you can either transform the data in the Power Query Editor by selecting Transform Data or load the data by selecting Load.
Database Connectors
The Database category provides the following data connections:
1. SQL Server
Power BI Connector offers increased advantages regarding functionalities that come in handy for Data Scientists working in SQL.
Power BI provides an optimized, live connector to SQL Server to easily create charts, reports, and dashboards by directly working with a large amount of data.
2. Azure SQL Database
Power BI Connector allows you to explore and monitor data stored in your Azure SQL database directly, without requiring a data model as an intermediate cache.
With Azure and Power BI, you can utilize multi-source data processing, huge real-time systems, Stream Analytics, etc.
You can also connect Power BI to the Azure SQL database using a single sign-on (SSO) such as Azure Active Directory (Azure AD) or OAuth2.
3. Amazon Redshift
Redshift is Amazon’s analytics database that is designed to process large amounts of data as a data warehouse.
You can connect Power BI to Amazon Redshift directly using the in-built connector.
The AWS Redshift to Power BI connectivity allows users to analyze the entire set of data that is stored in the S3 data lakes with Redshift.
4. Google BigQuery
Power BI can be connected to Google BigQuery in two ways:
Using Organizational account
Using Service Account Login that requires JSON key file contents.
The Power BI connector is available in Power BI Desktop and in the Power BI service for Google BigQuery.
In the Power BI service, the connector can be accessed using the Cloud-to-Cloud connection from Power BI to Google BigQuery.
5. Oracle Database
Oracle users can track, aggregate, and visualize data from internal apps by connecting Power BI to Oracle Database.
You can directly load data from the Oracle database using the Power BI connector.
As a prerequisite, it requires the installation of Oracle Client beforehand.
You can also want to use an external Connector for Oracle such as CData for ease of use.
6. Snowflake
Snowflake database is a favorite for many organizations. Snowflake provides the flexibility to store both structured and semistructured data.
Power BI Connector eliminates the need for on-premises Power BI Gateway implementations since the Power BI service uses an embedded Snowflake driver to connect to Snowflake.
Power BI Service users can connect to Snowflake using Identity Provider credentials and implement to access Snowflake with single sign-on (SSO).
7. IBM
The IBM Db2 database is used by organizations of all sizes for both transactional and analytical operations.
Power BI Connector for IBM Db2 allows Data Scientists to perform complex modeling and visualizations.
You can create a real-time link to visualize live data, and refresh data on demand or use the schedule refresh option to view the latest data in Power BI.
SaaS Connectors
The Online Services category provides the following data connections:
1. Salesforce
Salesforce is a cloud-based CRM tool that lets you manage your organization’s interactions with its customer base.
Power BI Connector gives users the ability to easily connect Salesforce accounts, to retrieve data in Power BI.
To execute the connection:
Launch Power BI on your workstation
Click on the Get Data button
Select the “analytics for salesforce” option
The connector will power your visuals for analyzing your marketing/sales performances.
It provides key metrics, such as your sales pipeline, best accounts, and KPIs.
2. Google Analytics
In today’s world, it’s hard to find a business without a website. The website is a business’s first impression of a customer.
Google Analytics is a widely used Website Data Analytics tool that tracks data on SEO, transactions, user behavior, Sales, Marketing, etc.
Connecting Power BI to Google Analytics simplifies your website performance analysis.
Using the Power BI Connector, you can easily establish a connection between the two & analyze your data in real-time.
Just like the Power BI desktop, the SaaS application also supports connectivity to Google Analytics with simple configuration & authentication.
3. Zoho Creator
Visualize Zoho CRM customers, transactions, invoices & sales data in Power BI with an in-built Connector.
The Zoho Creator connector facilitates the data from your Zoho Creator account and links it to your Power BI application.
It enables you to pull in the data stored in Zoho Creator and create reports and resourceful insights in Power BI.
4. Marketo
Marketo is a cloud lead management and marketing solution.
Power BI Marketo Connectors such as CData can easily connect Microsoft teams Power BI with live Marketo data for up-to-date visual analysis and reporting.
The CData Marketo Connector is based on a highly-efficient query engine that has been optimized down to the socket level with streaming and compression capabilities.
Other Analytics Tool Connectors
It’s an interesting fact that Power BI, as one of the widely used BI tools can connect to other BI tools and use them as a data source!
1. OBIEE & Oracle Analytics
BI Connector is the most efficient Power BI Certified connector for connecting to Oracle Business Intelligence and Analytics data sources.
It supports Power BI Connectivity for
Oracle Business Intelligence Enterprise Edition (OBIEE)
Oracle Analytics Cloud (OAC)
Oracle Analytics Server (OAS)
BI Connector is easy to use, secure, and compliant with Oracle’s security model.
It supports Import, Direct Query, and Power BI Pro/Premium versions.
With BI Connector, you can reuse your OBIEE joins even if the primary keys are not exposed to the presentation layer!
You can schedule an automatic refresh of datasets, Publish reports to Power BI Cloud Service & Reuse OBIEE subject area joins.
2. Microstrategy
MicroStrategy is an enterprise business intelligence (BI) application.
Power BI-MicroStrategy Custom Connector allows Power BI users to seamlessly connect with MicroStrategy reports from the Get Data window.
The MicroStrategy Connector for Power BI allows Power BI users to fetch datasets from cubes or reports in their MicroStrategy projects.
You can import datasets with up to four million rows and 30+ columns.
Other Connectors
The Other category provides the following data connections:
1. ODBC
The ODBC connector in Power BI lets you import data from any third-party ODBC driver simply by specifying a Data Source Name (DSN) or a connection string.
You can also specify a SQL statement to execute against the ODBC driver.
Additionally, you can enable DirectQuery mode, allowing Power BI to dynamically generate queries at runtime without pre-caching the user’s data model.
2. OData Feed
OData is an open protocol that allows the creation and consumption of queryable and interoperable REST APIs in a simple and standard way.
Using Power BI Connector you can connect to an OData feed and use the underlying data just like any other data source.
Identify data fields such as text, numerical, location, date/time data, and more, to generate meaningful charts and reports.
Power BI Connectors: Desktop & Server Edition
Power BI Connector: Desktop Edition
Power BI Desktop is a free application that can be downloaded and installed on your local computer.
It is a comprehensive data analysis and reports creation tool for connecting to, transforming, visualizing, and analyzing your data.
Power BI Desktop Connectors are mainly for data analysts, developers, and super users.
Power BI Connector: Server Edition
The Power BI Service is a Cloud-based or Software-as-a-Service (SaaS) offering of Power BI.
It allows teams and organizations to edit reports and collaborate on them.
Power BI Server Connectors are mainly for sharing datasets and reports across teams and groups.
It includes support for scheduling automatic refresh of datasets to Power BI Cloud Service using Power BI On-Premise Gateway.
Not all Power BI connectors support connectivity to Power BI service/cloud (or Report Server) edition from the gateway.
Only a few connectors provide gateway connectivity to the Power BI Service.
