Skip to content

You are viewing documentation for Immuta version 2.8.

For the latest version, view our documentation for Immuta SaaS or the latest self-hosted version.

cancel

Microsoft Power BI

Audience: Data Owners and Users

Content Summary: Microsoft Power BI is a tool for exploring data, performing analysis, and creating visualizations. Power BI can connect to the Immuta Query Engine and consume data via Immuta's SQL Access Pattern. This page outlines how to install and connect Power BI to Immuta for Windows and Azure.

Overview

The primary Power BI mechanism for working with data is to use the Power BI desktop application for Windows to build analytics and dashboards.

Setting up Power BI Desktop

Power BI can connect to the Immuta Query engine leveraging Power BI's PostgreSQL database connector.

1. Install Npgsql

However, to connect to PostgreSQL, Power BI requires a free and open source helper application, Npgsql.

  1. Obtain the MSI installer for version 4.0.10 of Npgsql. Because of bugs in recent versions of Npgsql, 4.0.10 should be used.

  2. After you've downloaded the MSI file, run it. You'll be taken through a standard installation wizard. Accept the license and choose the Will be installed on local hard drive option under Npgsql GAC Installation.

    Npgsql GAC installation configuration option

    Note: If the Power BI application was open while Npgsql was installed, you will need to close and reopen Power BI to enable PostgreSQL connectivity.

2. Update Windows Trusted Root Certificates

It is recommended that you require an SSL connection to the Immuta Query Engine. To do this, Power BI requires full verification of the server's certificate. In order to verify the certificate, the root certificate authority for the server's certificate must be loaded into your Windows operating system.

It is likely that the root certificate authority for the server's certificate is already trusted by your operating system. If it is not, you'll need to obtain the root certificate and install it per Microsoft's instructions on Managing Trusted Root Certificates.

3. Connect to Immuta

To connect Power BI to Immuta data sources,

  1. Open Power BI, select the Home menu, and click Get Data.

  2. In the dialog window that appears, scroll in the right pane to PostgreSQL database and select that option as shown in the image below.

    Connecting Power BI to Immuta

  3. Next, you'll be prompted to enter connection information. You can find this information in your Immuta Profile page, under the SQL Credentials tab.

  4. If your Immuta Query Engine is not running on port 5432, you'll need to append that port. For example, if the port is 5433, you would append :5433 to the hostname you enter.
  5. For database, enter immuta.
  6. If this is your first time connecting Power BI to the Immuta Query Engine, you'll be prompted for your username and password. Note that this is the SQL username and password set either in your Immuta profile or in a project within the Immuta UI.
  7. Finally, select the tables from which you'd like to query data.

Additionally, when establishing relationships between data sets in Power BI, Power BI does not always create SQL JOIN queries. Instead, it typically will fetch multiple sets of data and perform JOIN operations within Power BI itself. As this leads to extra latency in data transfer, and potentially joins on incomplete data sets, you may want to import the data into Power BI with a JOIN statement.

Setting up Power BI for Azure

Power BI running in Azure can connect to an Immuta instance running within your enterprise or in the cloud via Microsoft's on-premises data gateway. The data gateway can be installed in personal mode or enterprise mode. Immuta recommends running the gateway in enterprise mode. Personal mode is useful for initial testing on a single laptop without the need for administrative privileges.

Note: Once data is exposed within Power BI for Azure, data sharing must be managed by Power BI for Azure access and data management features.

Installation

  1. Follow Microsoft's instructions for installing the data gateway , and choose the appropriate operating mode: personal or enterprise. Be sure that the computer on which you are installing the data gateway has access to the internet on standard web ports and network access to Immuta's Query Engine, typically port 5432 on the server(s) on which Immuta is installed.

  2. Like Power BI Desktop, the Power BI Data Gateway requires Npgsql to be installed on the machine running the data gateway. Follow the Installing Npgsql instructions found above.

  3. Once installed, manage the gateway online by going to Settings and selecting Manage Gateways. Personal gateways will not be accessible in Power BI until a data source is shared by a local Power BI for Windows installation.