Skip to main content

Get Data into Power BI

Microsoft Power BI is a data visualization tool with a focus on business intelligence. Virtual Data Platform integrates into Power BI as a SQL Server and thereby enables full functionality of Power BI. Data can be loaded fully virtually, such that the source systems keep full control over authorization and permissions. Using Direct Query is highly recommended to manage and keep control over the data and its access where it should be implemented: in the source system.

Connect to VDP Using Power BI​

  1. Select "SQL Server" as data source.
  2. The hostname is defined by your VDP Tenant subdomain combined with the deployment domain, e. g. vdlprod.eu01.virtualdataplatform.com. You can find it at the bottom of each page in the Portal https://portal.virtualdataplatform.com/ after "Backend" and "https://": Tenant hostname Enter this in the Server field: Enter hostname and select Direct Query
  3. Sign in using Microsoft Account: Sign in using Microsoft Account. You will be redirected to Microsoft Entra ID for the login.
  4. Press "Connect".
Authentication error

If you choose an account for authencation that is not authorized to use VDP, you will receive an "Unauthorized" error on connection. You will have to clear the permissions for the datasource under Options -> Data source settings. No connection to VDP will be possible before doing so.

When successfully connected, Power BI will show the Navigator grouping functions into Workspaces. Functions (and Models and Code-Files) are listed with their (Category)[/fundamental-concepts/category#defCategory] and name.

Power BI Navigator View

Potential caveats and problems shown by Power BI​

When Power BI is not able to execute a Virtual Dataset, it will show an error message. Such errors usually only indicate that there is some action required occur when there is some Virtual Dataset configuration is not compatible with Power BI requirements (e. g. required parameters without default values).

Authentication error​

If you choose an account for authencation that is not authorized to use VDP, you will receive an "Unauthorized" error on connection. You will have to clear the permissions for the datasource under Options -> Data source settings. No connection to VDP will be possible before doing so.

You will have to clear the permissions for the datasource under Options -> Data source settings. No connection to VDP will be possible before doing so.

Refresh required​

When you have never before executed a Virtual Dataset, Power BI might indicate "Refresh required": Power BI Navigator: Refresh required Simply hit the refresh button in the top right corner of the Navigator window.

If a required parameter of a dataset is missing, the Power BI Navigator is not able to show a preview. Either define a default value or use SQL to set parameters.

Missing required parameter​

If a required parameter of a dataset is missing, the Power BI Navigator is not able to show a preview. Either define a default value or use SQL to set parameters.

Power BI Navigator: Required parameter missing

Other errors​

Power BI will also show an error if a dataset cannot be used for other reasons, e. g. when there is no agent available:

Power BI Navigator: No agent available

Parameter Usage with the UI​

Only Virtual Datasets with default values for all their parameters can be used from Power BI using the Navigator.

Parameters will be shown as "pseudo" columns with a exclamation mark as prefix. Their value is always null. See column !YEAR in this sample:

Parameter column in Navigator preview

Parameter columns can be used in filters for single visualizations, pages, or all pages. You can use basic filters only if a parameter has a Value List.

Basic filter on parameter YEAR Advanced filter on parameter YEAR

Of course the supported selections for both basic filters and advanced filters are depending to the parameter definition in VDP and support in the source system. For instance, selecting more than one filter value for a parameter that only supports single values will lead to an error.

Parameter Usage with SQL code​

You can specify parameter values in the SQL Server dialog by using the Advanced options button at the bottom. Then you can enter a SQL statement (instead of using the Power BI Navigator). The Workspace name has to be entered in the input field Database.

This is a sample SQL statement:

SELECT *
FROM SAP.SalesPolandA
WHERE [!YEAR] = 2011

The FROM clause contains the Category name, a dot, and the name of the Virtual Dataset. Parameters can be referenced using their names with an exclamation mark as prefix. To comply with SQL syntax, you need the to add square brackets. A parameter YEAR can be referred to in a SQL statement by [!YEAR].

SQL Server Advanced Connection Dialog