Audience: Data Owners and Users
Content Summary: Power BI is a Microsoft 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.
Power BI for Windows
The primary Power BI mechanism for working with data is to use the Power BI desktop application for Windows to build analytics and dashboards.
Step 1: Install Power BI
To start using Power BI with Immuta, acquire, download, and install Power BI from Microsoft.
Power BI can connect to the Immuta Query engine leveraging Power BI's PostgreSQL connection capability because Immuta's Query Engine speaks the PostgreSQL wire protocol. However, to connect to PostgreSQL, Power BI requires a free and open source helper application, Npgsql.
Step 2: Install Npgsql
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.
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.
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.
Although optional, it is recommended that you require an SSL connection to the Immuta Query Engine. When connecting via SSL, 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.
Step 3: Connect to Immuta
To connect Power BI to data virtualized by Immuta,
- Open Power BI, select the Home menu, and click Get Data.
In the dialog window that appears, scroll in the right pane to PostgreSQL database and select that option as shown in the image below.
Next, you'll be prompted to enter connection information for the Immuta Query Engine as shown below. Replace
Your.Immuta.Instance.Hostnamewith the actual hostname of your Immuta instance.
- 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
:5433to the hostname you enter.
- For database, enter
- Finally, enter a SQL statement to fetch data from Immuta. This
may be as simple as
select * from <table_of_interest>or selecting the data source table.
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 data transfer, and potentially joins on incomplete data sets, you may want to import the data into Power BI with a JOIN statement.
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.
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.
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.
Like Power BI for Windows, the Power BI Data Gateway requires Npgsql to be installed on the machine running the data gateway. Follow the Installing Npgsql instructions found above.
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.