This document covers the steps for connecting Power BI with Adobe Experience Platform Query Service.
This guide assumes you already have access to Power BI and are familiar with how to navigate its interface. More information about Power BI can be found in the official Power BI documentation.
Additionally, Power BI is only available on Windows devices.
After installing Power BI, you will need to install
Npgsql, a .NET driver package for PostgreSQL. More information about Npgsql can be found in the Npgsql documentation.
You must download v4.0.10 or lower, as newer versions result in errors.
Under “Npgsql GAC Installation” on the custom setup screen, select Will be installed on local hard drive.
To ensure that npgsql has properly installed, please restart your computer before proceeding to the next steps.
To connect Power BI to Query Service, open Power BI and select Get Data in the top menu ribbon.
Select PostgreSQL database, followed by Connect.
You can now enter values for the server and database. For more information on finding your database name, host, port, and login credentials, visit the credentials page on Platform. To find your credentials, log in to Platform, then select Queries, followed by Credentials.
Server is the host found under the connection details. For production, add port
:80 to the end of the host string. Database can be either “all” or a dataset table name.
Additionally, you can select your Data Connectivity mode. Select Import to display a list of all available tables, or select DirectQuery to directly create a query.
To learn more about Import mode, please read the section on previewing and importing a table. To learn more about DirectQuery mode, please read the section on creating SQL statements. Select OK after confirming your database details.
A prompt asking for your username, password, and application settings appears. Fill in these details, then select Connect to continue to the next step.
If you’ve selected Import mode, a dialog appears, displaying a list of all the available tables. Select the table you want to preview, followed by Load to bring the dataset into Power BI.
The table is now imported into Power BI.
If you’ve selected DirectQuery mode, you will need to fill out the Advanced options section with the SQL query you want to create.
Under SQL statement, insert the SQL query you want to create. Ensure that the checkbox labelled Include relationship columns is selected. Once you’ve written your query, select OK to continue.
A preview of your query appears. Select Load to see the results of the query.
Now that you’ve connected with Query Service, you can use Power BI to write queries. For more information on how to write and run queries, please read the guide on running queries.