Before you can start creating reports and visuals in Power BI, it is essential to first connect your Power BI to the data source. Once your data is loaded, it is then shaped and modelled before we begin our analysis. Power BI supports a large variety of connectors. Some of the most common ones are discussed below:
Getting data from flat files:
A flat file is a file that does not have any hierarchies and has only one table with each row having the same structure. Examples of flat files include those in.csv,.txt, or.xlsx formats.
To locate and access your files, they may be stored in one of the following locations:
- On your computer’s local hard drive.
- On your organization’s OneDrive for Business account.
- On your personal OneDrive account.
Click on Get Data in the home ribbon on Power BI Desktop. You can select more to view all the types of connectors available in Power BI. For this example, we will select Excel Workbook and Text/CSV from the given options and click on Connect.
Once you select the file location, a navigator window will appear. In this window, you can choose the specific tables or sheets you want to import and click on Load (to upload the data as it is) or Transform Data (to open Power Query Editor to shape and transform the data).
Getting data from a Relational Data Source
You can connect Power BI to relational databases such as SQL Services Database, SQL Services Analysis Database, Access Database, Oracle Database, etc. You can choose the option so desired and click on connect.
You will be prompted to enter in the server’s name and your login credentials (Windows, Database, Microsoft Account) and select an appropriate connectivity mode: Import or Direct Query. You can also click on advanced options to specify the tables and columns that you need.
Import: This is the default option for connectivity in Power BI. Import allows you to keep a cached copy of the dataset within Power BI, thereby enhancing the performance of your reports. In cases of import, we must schedule a periodic data refresh to ensure that our data is up-to-date.
Direct Query: This allows you to directly query the database. Every analytical query will be stored as native queries, and the results are fetched from the database; this causes performance latency. However, Direct Query does ensure your database and reports are always updated in near real time.
Advanced Settings: This option can be selected when you want to perform simple select statements within the SQL database to insert certain columns only.
Getting data from a NOSQL database
NoSQL databases are non-relational databases that store data differently than relational tables. Click on Get data and select the more option, for this example, we will select Azure Cosmos DB, which can be located within the Azure option.
If you are working with JSON files, then it is necessary to normalize the data first.
Click on the load option, this opens the Power Query Editor. Click on the expand icon next to the columns to choose the required columns you wish to load on to Power BI Desktop. Select Close and Apply to load your changes on Power BI Desktop.
Getting data from online services
We can load data from online services on to Power BI. Data can be uploaded from multiple online sources, such as SharePoint Online List, Microsoft Exchange Online, Adobe Analytics, GitHub, etc. You will be asked to enter the URL of the online source. After which you can select your tables in the navigator as usual.
Getting data from Power Platforms
You can connect to Power platforms such as Power BI dataflows, Dataverse, and common Data Service Legacy using this option. You can choose from Direct Query or Import depending on the requirements of your organization.
Getting data from Microsoft Fabric
Microsoft Fabric is an analytics and data platform designed for enterprises, providing them a unified solution. You can now also connect your Power BI desktop with Microsoft Fabric. The Microsoft fabric connector includes Power BI semantic models, dataflows, dataMarts preview, warehouses, lakehouse and KQL databases. You can now leverage semantic models created in Microsoft fabric within Power BI. Overall, this integration provides a powerful, cohesive solution for managing and analyzing data, combining the strengths of Microsoft Fabric’s data services with Power BI’s visualization and reporting capabilities.
