Connect Power BI to Query Service

This document covers the steps for connecting Power BI Desktop with Adobe Experience Platform Query Service.

Getting started

This guide requires that you already have access to the Power BI desktop app and are familiar with how to navigate its interface. To download Power BI Desktop or for more information, see the official Power BI documentation.

To acquire the necessary credentials for connecting Power BI to Experience Platform, you must have access to the Queries workspace in the Platform UI. Please contact your organization administrator if you do not currently have access to the queries workspace.

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.

Under “Npgsql GAC Installation” on the custom setup screen, select Will be installed on local hard drive.

To ensure that Npgsql has been properly installed, please restart your computer before proceeding to the next steps.

Connect Power BI to Query Service connect-power-bi

To connect Power BI to Query Service, open Power BI and select Get Data in the top menu ribbon. Next, enter “PostgreSQL” in the search bar to narrow the list of data sources. From the results that appear, select PostgreSQL database, followed by Connect.

The PostgreSQL database dialog appears, requesting values for your server and database. Additional instructions on how to connect to a PostgreSQL database from Power Query Desktop can be found in the official PowerBI documentation.

These required values are taken from your Adobe Experience Platform credentials. To find your credentials, log in to the Platform UI and select Queries from the left navigation, followed by Credentials. For more information on finding your database name, host, port, and login credentials, please read the credentials guide.

In the Server field of the PostgreSQL database dialog, enter the value for the host found in the Query Service Credentials section. For production, add port :80 to the end of the host string. For example, made-up.platform-query.adobe.io:80.

The Database field can be either “all” or a dataset table name. For example, prod:all.

Data Connectivity mode data-connectivity-mode

Next, you can select your Data Connectivity mode. In the PostgreSQL database dialog, select Import followed by OK to display a list of all available tables, or select DirectQuery to query the data source directly without importing or copying data directly into Power BI.

To learn more about the Import mode, please read the section on importing a table. To learn more about DirectQuery mode, please read the section on querying a dataset without importing data.

Select OK after confirming your database details.

Authentication authentication

After confirming your data connectivity mode, a prompt asking for your username, password, and application settings appears. The username in this case is your Organization ID and the password is your authentication token. Both can be found on the Query Service credentials page.

Fill in these details, then select Connect to continue to the next step.

Import a table import

By selecting the Import Data Connectivity mode, the full dataset is imported which allows you to use the selected tables and columns within the Power BI desktop application as-is.

To import a table, enter the server and database details as described above and select the Import Data Connectivity mode, followed by OK. The Navigator 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.

General information on connecting to data in the PowerBi desktop app can be found in the official documentation.

Import tables using custom SQL

Power BI and other third-party tools like Tableau do not currently allow users to import nested objects, such as XDM objects in Platform. To account for this, Power BI allows you to use custom SQL to access these nested fields and create a flattened view of the data. Power BI then loads this flattened view of the previously nested data as a normal table.

From the PostgreSQL database dialog, select Advanced options to enter a custom SQL query in the SQL statement section. This custom query should be used to flatten your JSON name-value pairs into a table format. The official documentation also provides information on how to connect PowerBI using an SQL statement in the advanced options.

After you have entered your customized query, select OK to continue with connecting your database. See the authentication section above for guidance on connecting a database from this part of the workflow.

Once authentication is complete, a preview of the flattened data appears in the Power BI Desktop dashboard as a table. The server and database name are listed at the top of the dialog. Select Load to complete the import process.

The visualizations are now available for editing and exporting from the Power BI Desktop app.

Query the dataset without importing data direct-query

The DirectQuery Data Connectivity mode queries the data source directly without importing or copying data into the Power BI Desktop. Using this connection mode, you can refresh all visualizations with current data through the UI. However, the time required to produce or refresh the visualization will vary depending on the performance of the underlying data source.

More information on the use of DirectQuery as well as a comprehensive discussion about its connectivity options, use cases, and limitations can be found in the official PowerBI documentation.

To use this Data Connectivity mode, select the DirectQuery toggle then Advanced options to enter a custom SQL query in the SQL statement section. Ensure that Include relationship columns is selected. Once you have completed your query, select OK to continue.

A preview of your query appears. Select Load to see the results of the query.

Next steps

By reading this document, you should now understand how to connect to the Power BI Desktop app and the different data connection modes available. For more information on how to write and run queries, refer to the guidance for query execution.