Visual Analytics is the union of Data Analytics and Visualizations. This problem-solving approach is concerned with effectively facilitating high-level, complex activities such as reasoning and data-driven decision-making by integrating interactive visual representations with underlying analytical processes.
Power BI is a Business Intelligence (BI) tool and a Visual Analytics engine offered by Microsoft that provides you with real-time high-level analytics, extensive modeling, and custom development. It has led the charge in making business analysis more efficient through services that are intuitive, interactive, and easy to use.
In this article, you will gain information about Power BI Data Sources. You will also gain a holistic understanding of PPower BI, its key features, Power BI Data Sources, connecting Power BI Data Sources and the different types of Power BI Data Sources. Read along to find out in-depth information about Power BI Data Sources.
What is Power BI?
Power BI is a part of Microsoft Power Platform and a proprietary Data Visualization and Business Intelligence platform. It is one of the widely used tools among organizations to analyze business data and generate reports. Power BI uses a collection of various in-built software services, apps, and connectors to deeply integrate with data to provide immersive visuals, interactive reports, and generate insights.
Power BI helps users to aggregate, analyze, visualize and share data. It is a SaaS (Software as a Service) based platform and is also available for Desktop, mobile, and on-premise servers.
Power BI supports connection with many data source connectors to load data into the Dashboard directly from the data source and visualize data stored in Databases or Data Warehouses. Users use the Power BI Chart, graphs, KPIs, reports to analyze the data and get interactive insights.
Key Features of Power BI
Some of the main features of Power BI are listed below.
- Hybrid Development: Power BI offers integrations with many connectors that allow users to connect to various data sources.
- Modeling View: Power BI allows users to divide and slice complex data models into a simpler form, separate diagrams, multi-select objects.
- AI Support: Power BI supports Artificial Intelligence in Data Analytics that users can leverage to prepare data, build Machine Learning models and quickly identify insights from structures as was unstructured data.
What are Power BI Data Sources?
Power BI is built around data. Power BI is a proprietary Data Visualization and Business Intelligence platform. It is one of the most widely used tools in organisations for analysing business data and producing reports. Users can use Power BI to aggregate, analyse, visualise, and share data.
The underlying data for the visualisations and answers in Power BI comes from a dataset. And that dataset is obtained from a data source. And the data sources which can be connected to Power BI to create dataflow are the Power BI Data Sources.
How to connect to a Power BI Data Source?
1) For Power BI Service
The steps followed to get data from any of the Power BI data sources supported by Power BI Service are as follows:
- Step 1: Select the “Get Data” option in the bottom-left corner of the page.
Image Source
- Step 2: Now, you can choose the type of data you want to access.
Image Source
The 2 sections are:
A) Discover content
Image Source
The Discover content section includes data and reports that have already been prepared for you.
B) Create new content
The Create new content section includes options for creating and importing your own content.
There are two ways to create or import your own content in Power BI:
2) For Power BI Desktop
The steps followed to get data from any of the Power BI data sources supported by Power BI Desktop are as follows:
- Step 1: Navigate to the “Home” ribbon.
- Step 2: Click on the “Get Data” button label or the down arrow.
- Step 3: A list of the Common data sources menu will appear. Select the “More” option present at the bottom of the list.
Image Source
- Step 4: The “Get Data” dialog box appears. Select the Power BI data source to which you want to connect to.
Image Source
- Step 5: Click on the “Connect” button.
Web is selected from the Other data connection category in the image given below.
Image Source
- Step 6: A connection window that is specific to the type of data connection is displayed. If credentials are needed, you will be prompted to enter them.
The image below depicts a URL being entered to connect to a Web data source.
Image Source
- Step 7: Enter the URL or the resource connection information. Then click on the “OK” button. Power BI Desktop connects to the data source and displays the data sources that are available in the Navigator.
Image Source
- Step 8: Select the “Load” button at the bottom of the “Navigator” pane to load the data. Select the “Transform Data” button to transform or edit the query in Power Query Editor before loading the data.
Types of Power BI Data Sources
The different Power BI Data Sources available in both the offerings of Power BI are:
I) For Power BI Service
There are two methods for creating or importing your data in the Power BI service.
1) Files
Image Source
- Excel (.xlsx, .xlsm): A workbook in Excel can contain a variety of data types. It could include data that you’ve manually entered into worksheets or that you’ve queried and loaded from external data sources using Power Query. Data can be imported from tables in worksheets or from a data model.
- Power BI Desktop (.pbix): Power BI Desktop can be used to query and load data from external data sources, as well as to create reports. You can also add measures and relationships to your data model, or import a Power BI Desktop file into your Power BI site.
- Comma-Separated Value (.csv): Files are simple text files that contain rows of data. Each row can have one or more values separated by a comma. Although data cannot be imported into a .csv file, many applications, such as Excel, can save simple table data as a.csv file.
You can use Get & Transform to query, transform, and load data into an Excel or Power BI Desktop file first for other file types, such as XML Table (.xml) or text (.txt) files. The Excel or Power BI Desktop file can then be imported into Power BI.
2) Databases
Image Source
From the Power BI service, users can connect live to some of the Databases in the cloud:
- Azure SQL Database
- Spark on Azure HDInsight
- Azure Synapse Analytics (previously SQL Data Warehouse)
Power BI connections to these databases are live. Assume you’re connecting to an Azure SQL Database. You then start digging into its data by creating reports in Power BI. When you slice your data or add a new field to a visualization, Power BI performs a database query.
You would need to connect to, query, and load data into a data model using Power BI Desktop or Excel for other databases types in your organization.
II) Power BI Desktop
The categories of Power BI data sources supported by Power BI Desktop are as follows:
1) All
Under this category, you can find all the available Power BI data sources on Power BI Desktop.
The following image showcases the Get Data window for All data sources.
2) File
Under the File category, you can find the following Power BI Data sources.
- Excel Workbook
- Text/CSV
- XML
- JSON
- Folder
- PDF
- Parquet
- SharePoint folder
The following image showcases the Get Data window for File data sources.
Image Source
When you click the “File” option, it will show you all the available flat file types supported by the Power BI desktop. Select the file type from the list and press the “Connect” button. You must specify the file’s location.
3) Database
Under the Database category, you can find the following Power BI data sources.
- 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)
- Amazon Athena
- BI Connector
- Data Virtuality LDW
- Denodo
- Dremio Software
- Dremio Cloud (Beta)
- Exasol
- Indexima
- InterSystems IRIS (Beta)
- Jethro (Beta)
- Kyligence
- Linkar PICK Style / MultiValue Databases (Beta)
- MariaDB
- MarkLogic
- TIBCO(R) Data Virtualization
- AtScale cubes
The following image showcases the Get Data window for Database data sources.
Image Source
When you click the “Database” option, it will show you all the available database connections supported by the Power BI desktop. Select the database type from the list and press the “Connect” button. You must specify the file’s location.
To connect, you must enter the Server name/User name and password. Using the Advance options, you can also connect via a direct SQL query. You can also choose between Import and DirectQuery connectivity modes.
4) Power Platform
Under the Power Platform category, you can find the following data Power BI data sources:
- Power BI datasets
- Power BI dataflows
- Common Data Service (Legacy)
- Dataverse
- Dataflows
The following image showcases the Get Data window for Power Platform data sources.
Image Source
5) Azure
You can connect to the database in the Azure cloud using the Azure option.
Under the Azure category, you can find the following Power BI data sources:
- Azure SQL Database
- Azure Synapse Analytics SQL
- 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 Synapse Analytics workspace (Beta)
- Azure Time Series Insights (Beta)
- Azure Cost Management
- Azure Databricks
The following image showcases the Get Data window for Azure data sources.
Image Source
6) Online Services
You can also connect Power BI to various online services such as Exchange, Salesforce, Google Analytics, and Facebook.
Under the Online Services category, you can find the following Power BI data sources:
- SharePoint Online List
- Microsoft Exchange Online
- Dynamics 365 (online)
- Dynamics 365 (Dataverse)
- Dynamics NAV
- Dynamics 365 Business Central
- Dynamics 365 Business Central (on-premises)
- 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)
- Assemble Views
- Automation Anywhere
- Automy Data Analytics (Beta)
- Dynamics 365 Customer Insights (Beta)
- Emigo Data Source
- Entersoft Business Suite (Beta)
- eWay-CRM
- FactSet Analytics
- Palantir Foundry
- Hexagon PPM Smart API
- Industrial App Store
- Intune Data Warehouse (Beta)
- Projectplace for Power BI
- Product Insights (beta)
- Quick Base
- SoftOne BI (beta)
- Spigit (Beta)
- TeamDesk (Beta)
- Webtrends Analytics (Beta)
- Witivio (Beta)
- Workplace Analytics (Beta)
- Zoho Creator (Beta)
- Digital Construction Works Insights (Beta)
The following image showcases the Get Data window for Online Services data sources.
Image Source
7) Other
Under the Other category, you can find the following Power BI data sources:
- 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)
- Solver
- Bloomberg Data and Analytics
- Cherwell (Beta)
- Cognite Data Fusion
- Delta Sharing
- EQuIS (Beta)
- FHIR
- Google Sheets (Beta)
- Information Grid (Beta)
- Jamf Pro (Beta)
- Kognitwin
- MicroStrategy for Power BI
- Paxata
- QubolePresto (Beta)
- Roamler (Beta)
- SIS-CC SDMX (Beta)
- Shortcuts Business Insights (Beta)
- Siteimprove
- SumTotal
- SurveyMonkey (Beta)
- Microsoft Teams Personal Analytics (Beta)
- Tenforce (Smart)List
- Usercube (Beta)
- Vena
- Vessel Insight
- Zucchetti HR Infinity (Beta)
- BQE Core
- MicroStrategy for Power BI
- Starburst Enterprise
- Amazon OpenSearch Service (Beta)
- OpenSearch Project (Beta)
- Blank Query
The following image showcases the Get Data window for Other data sources.
Image Source
8) Template apps
By selecting the Template Apps link near the bottom of the Get Data window, you can find template apps for your organization.
The Template Apps that are available to you may differ depending on your organization.
Image Source
Conclusion
In this article, you have learned about Power BI Data Sources. This article also provided information on Power BI, its key features, Power BI Data Sources, connecting Power BI Data Sources and the different types of Power BI Data Sources.
Manisha Jena is a data analyst with over three years of experience in the data industry and is well-versed with advanced data tools such as Snowflake, Looker Studio, and Google BigQuery. She is an alumna of NIT Rourkela and excels in extracting critical insights from complex databases and enhancing data visualization through comprehensive dashboards. Manisha has authored over a hundred articles on diverse topics related to data engineering, and loves breaking down complex topics to help data practitioners solve their doubts related to data engineering.
