Introduction
Data analysts use Power Bi to build insightful dashboards. Where does this data come from? This led me through a rabbit hole, where I discovered that Power Bi has over 200 distinct data sources through its native connectors, ranging from local Excel files to cloud-based data lakes. The following are the built-in connectors:
- Files: Excel, CSV, XML, JSON, PDF, and Parquet
- Databases: SQL Server, Oracle, Snowflake, Google BigQuery, PostgresSQL, and Amazon Redshift
- Cloud Services: Google Analytics, Salesforce, Dynamics 365, SharePoint, and Adobe Analytics
- Online/Web APIs: OData feeds, REST APIs and unstructured web pages.
- Power Platform: Dataverse and Power BI Datasets.
The Database Connection
How do you connect Power BI to a database? Their are different types of databases but we will focus on PostgreSQL for the local and cloud server option.
Option A: Connecting to postgreSQL database locally
To connect your Power Bi to a local postgreSQL database you will need to have the password, database name, username and port (default:5432) for your postgreSQL
- Launch Power BI: Open Power BI Desktop and click on Get Data on the Home ribbon.
- Select the connector: Type: PostgreSQL in the search box. Select PostgreSQL database and click Connect
- Enter Server & Database Details:
- Server: Enter localhost:5432 or 127.0.0.1:5432 if the database is on your machine. Set the port to 5432.
- Database: Enter the specific name of your database
- Data Connectivity Mode: Choose Import(loads data to memory, best for speed and offline work) or DirectQuery (queries the live database for real-time data)
- Authenticate: Go to the Database tab on the left menu. Enter your postgreSQL Username and Password, then click Connect
- Load Data: Check the boxes next to the tables you want to analyze and select Load (to pull data directly) or Transform Data (to clean and shape it in the Power Query Editor)
Option B: Connecting to PostgreSQL database remotely
A) Log in to your aiven console and navigate to your postgreSQL project. Ensure that your project is running.
B) Locate the Overview or Connection Information tab. Copy and save these individual parameters.
C) Locate the CA certificate option in the same section and click the download icon to save the file locally to your machine.
The CA(Certificate Authority) certificate from Aiven to connect to Power BI because Aiven requires mandatory SSL/TLS encryption for all external database connections.
The certificate serves two main purposes:
Data Encryption: It encrpts the connection so your credentials and data remain safe while traveling over the public internet.
Server Verification: It allows Power BI to verify that you are connecting to your actual Aiven database rather than a malicious imposter.
D) Install the SSL certificate in your local machine: search for the manage user certificates. Click the Trusted Root Certification Authorities.
Right click the folder and navigate to import. The certificate Import Wizard will pop up.
Load the downloaded certificate after ensuring all the files are selected.
E) Establish the connection in Power BI Desktop and add the server details from aiven similar to what was illustrated in the local database connection.
Conclusion
Connecting Power BI to both local and cloud-hosted databases is a straightforward process once the required configurations are in place. However, users may encounter common connection issues, such as using localhost instead of 127.0.0.1 when connecting to a local database, or failing to install the appropriate CA certificate required by a cloud database provider. Addressing these configuration requirements ensures secure and reliable connectivity.
In practice, data analysts are typically provided with the necessary database credentials and access permissions by database administrators. Once connected, they can retrieve, transform, and visualize data in Power BI to create meaningful reports and interactive dashboards that support informed business decision-making.
Top comments (0)